Passing a non-executable SQL object to pandas read_sql() method

Question

I'm facing this new warning within some Python 3.9 code:

/usr/local/lib/python3.9/site-packages/pandas/io/sql.py:761:
  UserWarning:
    pandas only support SQLAlchemy connectable(engine/connection) or
    database string URI or sqlite3 DBAPI2 connectionother DBAPI2
    objects are not tested, please consider using SQLAlchemy

on such snippet:

import pandas as pd
from psycopg2 import sql

fields = ('object', 'category', 'number', 'mode')

query = sql.SQL("SELECT {} FROM categories;").format(
    sql.SQL(', ').join(map(sql.Identifier, fields))
)

df = pd.read_sql(
    sql=query,
    con=connector() # custom function which returns db parameters as a psycopg2 connection object
)

It works like a charm for the moment, but according to the warning message, I'd like to switch to SQLAlchemy.

But by doing so:

from sqlalchemy import create_engine

engine = create_engine('postgresql+psycopg2://', creator=connector)

df = pd.read_sql(
    sql=query,
    con=engine
)

it says:

sqlalchemy.exc.ObjectNotExecutableError: Not an executable object:
  Composed([SQL('SELECT '), Composed([Identifier('object'), SQL(', '),
  Identifier('category'), SQL(', '), Identifier('number'), SQL(', '),
  Identifier('mode')]), SQL(' FROM categories;')])

So I have to tweak it this way to avoid this error:

engine = create_engine('postgresql+psycopg2://', creator=connector)

conn = connector()
curs = conn.cursor()

df = pd.read_sql(
    sql=query.as_string(conn), # non-pythonic, isn't it?
    con=engine
)

I'm wondering what's the benefit of using an SQLAlchemy engine with pandas if I have to "decode" the query string using a psycpg2 connection context... (in some specific cases where the query string is a binary string I have to "decode" it by applying .decode('UTF-8')...)

How can I rewrite the DataFrame construction in a proper (i.e. the best) way by using an SQLAlchemy engine with pandas?

The pandas doc is not 100% clear for me:

Parameters

sqlstr or SQLAlchemy Selectable (select or text object)

    SQL query to be executed or a table name.

Version info:

python: 3.9
pandas: '1.4.3'
sqlalchemy: '1.4.35'
psycopg2: '2.9.3 (dt dec pq3 ext lo64)'

snakecharmerb · Accepted Answer · 2022-09-04 17:13:09Z

2

The query can be expressed in SQLAlchemy syntax like this:

import pandas as pd
import sqlalchemy as sa

fields = ('object', 'category', 'number', 'mode')

# Adjust engine configuration to match your environment.
engine = sa.create_engine('postgresql+psycopg2:///test')
metadata = sa.MetaData()

# Reflect the table from the database.
tbl = sa.Table('categories', metadata, autoload_with=engine)

# Get column objects for column names.
columns = [tbl.c[name] for name in fields]

query = sa.select(*columns)

df = pd.read_sql(sql=query, con=engine)
print(df)

answered Sep 4, 2022 at 17:13

snakecharmerb

57.1k13 gold badges136 silver badges200 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ziggy Over a year ago

This just reads from a single table. What if you want to run a query with multiple joins?

snakecharmerb Over a year ago

@ziggy it would be the same in principle, except you would have to apply .join_from to the select.

jean pierre huart · Accepted Answer · 2023-11-15 10:51:45Z

Using pandas v.2.1.2 and SQLAlchemy >= 2.0.22

You can use psycopg2 syntax for parameters and the "native" strsql string.


import pandas as pd
from sqlalchemy import create_engine

engine = create_engine('postgresql+psycopg2://...')
strsql = "SELECT * FROM table WHERE id = %(myid)s;"
params = {"myid": 1}
df = pd.DataFrame()
with engine.connect() as db_connection:
    df = pd.read_sql(
        sql=strsql,
        con=db_connection,
        params=params,
    )

Using pandas v 1.5.3 and SQLAlchemy >= 2.0.22

Here you have a workaround to do the same thing. In short, you have to use the text() function of SQLAlchemy and use the SQLAlchemy syntax for parameters.


import pandas as pd
from sqlalchemy import create_engine, text

engine = create_engine('postgresql+psycopg2://...')
strsql = "SELECT * FROM table WHERE id = :myid;"
params = {"myid": 1}
df = pd.DataFrame()
with engine.connect() as db_connection:
    df = pd.read_sql(
        sql=text(strsql),
        con=db_connection,
        params=params,
    )

Collectives™ on Stack Overflow

Passing a non-executable SQL object to pandas read_sql() method

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related