0

We currently use a get_connection functionality for our postgres via

import psycopg2


def get_db_connection(conn_str: str = '') -> psycopg2._psycopg.connection:
    conn_str = conn_str if conn_str else os.environ.get(PG_CONNECTION_STRING)
    return psycopg2.connect(conn_str)

Now that we're working with pandas dataframes, and we want to write them to the DB as new tables, I see in this answer and the pandas docs that you can use an sqlalchemy engine or connection.

df.to_sql(con=sqlalchemy_cnx, 'table_name')

Is there a relationship between a psycopg2 connection and an sqlalchemy connection such that I could create the latter from the former?

Something like

get_pandas_compliant_db_cnx(cnx: psycopg2._psycopg.connection) -> sqlalchemy.engine.Connection:
    # this is what i'm trying to implement to avoid managing both systems
Joey Baruch
  • 4,180
  • 6
  • 34
  • 48
  • FWIW the SQLA `connection` is lazy and will close itself. Depending on your library setup, you might need only create a single `connection` object somewhere in a config object and just pass that around where it's needed – roganjosh Feb 15 '23 at 18:16
  • Do the df's have a common schema btw or are these free-wheeling uploads and you want dynamic schemas for each table? I have a great distrust of using SQL with a `pandas` wrapper – roganjosh Feb 15 '23 at 18:18
  • The intended use is a common schema - but what is your reason to distrust the `pandas` sql wrapper? – Joey Baruch Feb 16 '23 at 04:57

0 Answers0