Question 304219 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Trouble retrieving DataFrame using SQLAlchemy and psycopg2 – works with create_engine but not with psycopg2

# I'm facing an issue when trying to execute the following code snippet using psycopg2 in place of create_engine.

# Code snippet using create_engine (works fine):
from sqlalchemy import create_engine
import pandas as pd

db_url = "your_database_url_here"
engine = create_engine(db_url)

# Establish a connection
connection = engine.connect()

sql_query = "your_table_name"
df = pd.read_sql(sql_query, con=connection)

# This successfully returns a DataFrame if the 'your_table_name' table is present in the database.

# When I replicate the exact same code using psycopg2, it throws an error. Here's the modified code:

# Code snippet using psycopg2 (throws error):
import psycopg2
import pandas as pd

db_url = "your_database_url_here"
conn = psycopg2.connect(db_url)
cursor = conn.cursor()

sql_query = "your_table_name"

# The following line throws an error
df = pd.read_sql(sql_query, con=conn)

# I've verified that the table exists in the database. Any ideas on what might be causing this issue?

In the provided code snippets, I used SQLAlchemy with create_engine to establish a connection and retrieve a DataFrame using pd.read_sql. This worked successfully when querying a table named "your_table_name."

However, when attempting to replicate the same functionality using psycopg2, an error occurred during the execution of pd.read_sql. The specific error message was not provided in the original question, so you should replace Execution failed on sql 'transaction': syntax error at or near "your_table_name" LINE 1: your_table_name ^

Tags:

2

Answers


  1. 0

    According to the documentation:

    sql: str or SQLAlchemy Selectable (select or text object)
    SQL query to be executed or a table name.

    con: SQLAlchemy connectable, str, or sqlite3 connection
    Using SQLAlchemy makes it possible to use any DB supported by that library. If a DBAPI2 object, only sqlite3 is supported. The user is responsible for engine disposal and connection closure for the SQLAlchemy connectable; str connections are closed automatically. See here.

    So if you don’t use SQLAlchemy, you should only use sqlite connection even if psycopg2 is compliant to DBAPI 2.0 specification else you will receive a UserWarning:

    UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.

    However, this is not the reason why your code raised an error. Actually you can only pass a table name when using SQLAlchemy otherwise you have to pass a SQL query.

    sql_query = "SELECT * FROM your_table_name"
df = pd.read_sql(sql_query, con=conn)
...
UserWarning: ...

    My advice: either you use SQLAlchemy with Pandas, or you use connectorx to load your data (read-only!):

    #! pip install connectorx
import connectorx as cx

db_url = "your_database_url_here"
sql_query = "SELECT * FROM your_table_name"
df = cx.read_sql(db_url, sql_query)
    Login or Signup to reply.
  2. 0

    If you want to go this route then:

    import pandas as pd
import psycopg2
from psycopg2.extras import RealDictCursor

con = psycopg2.connect("dbname=db_name user=user_name", cursor_factory=RealDictCursor)

cur = con.cursor()
cur.execute("select * from some_table")

df = pd.DataFrame.from_records(cur.fetchall())

con.close()
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search