1

I am new to using postgreSQL in Python and using Pandas.

I am trying to create a pandas dataframe from a query, with the following:

with conn.cursor() as cur:
    cur.execute('SELECT * FROM payment')

postgres_df = pd.read_sql_query('SELECT * FROM payment', conn)
print(postgres_df)

conn.close()

But when I run the code I do get a result, but also a UserWarning.

UserWarning: pandas only support SQLAlchemy connectable(engine/connection) ordatabase string URI or sqlite3 DBAPI2 connectionother DBAPI2 objects are not tested, please consider using SQLAlchemy
  warnings.warn(

I am using the psycopg2 package to connect to PostgreSQL.

I would like to know if the code to creating a dataframe can be more efficiently written.

Then, should I be using SQLAlchemy instead of psycopg2 or is there a way to continue using the same package without getting a warning.

David A.
  • 25
  • 4
  • Read the docs [to_sql](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html) – Adrian Klaver Apr 09 '22 at 22:02
  • @AdrianKlaver didn't understand anything related to this from the mentioned to_sql docs, can you tell if anything specific? – Nikhil VJ Apr 23 '22 at 19:17
  • 1
    My mistake I grabbed the wrong link, it should be [read_sql_query](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_sql_query.html). In either case the function signature is `... (sql, con, ...)` as in `read_sql_query(sql, con, ...)` where `con` is a SQLAlchemy connection. This is pointed out in the error message. The OP is using a direct `psycopg2` connection and that is not going to work. – Adrian Klaver Apr 23 '22 at 20:56

0 Answers0