SQLAlchemy Connection as as_string() argument -- TypeError: argument 2 must be a connection or a cursor

[skwzrd]

Hello,

I wasn't sure if I should post this here, or in the sqlalchemy repo. Just let me know if there is a better place for this.

I am trying to use psycopg2 to run parameterized queries with SQLAlchemy connections, but I am getting an error.

from sqlalchemy import text, create_engine
from urllib.parse import quote_plus
from psycopg2 import sql

database = {
    'host': "127.0.0.1",
    'port': 3306,
    'db': "db",
    'user': "user",
    'password': "password",
    'schema': 'schema',
}

url = (
    f'postgresql://'
    f'{database["user"]}:{quote_plus(database["password"])}'
    f'@{database["host"]}:{database["port"]}'
    f'/{database["db"]}'
)

table_name = 'buildlist'
column_name = 'list_item_cat'

engine = create_engine(url, connect_args={'options': f'-csearch_path={database["schema"]}'})
with engine.connect() as conn:
    sql_string = sql.SQL(
        """select * from {0} where {1} like 'A%' limit 5;"""
    )
    sql_string = sql_string.format(sql.Identifier(table_name), sql.Identifier(column_name))
    sql_string = sql_string.as_string(conn) # Error !
    # Traceback (most recent call last):
    # File "c:\Users\%user\Desktop\t.py", line 40, in <module>
    #     sql_string = sql_string.as_string(conn)
    #                 ^^^^^^^^^^^^^^^^^^^^^^^^^^
    # File "C:\Users\%user\AppData\Local\Programs\Python\Python311\Lib\site-packages\psycopg2\sql.py", line 122, in as_string
    #     rv.append(i.as_string(context))
    #             ^^^^^^^^^^^^^^^^^^^^
    # File "C:\Users\%user\AppData\Local\Programs\Python\Python311\Lib\site-packages\psycopg2\sql.py", line 350, in as_string
    #     return '.'.join(ext.quote_ident(s, context) for s in self._wrapped)
    #         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    # File "C:\Users\%user\AppData\Local\Programs\Python\Python311\Lib\site-packages\psycopg2\sql.py", line 350, in <genexpr>
    #     return '.'.join(ext.quote_ident(s, context) for s in self._wrapped)
    #                     ^^^^^^^^^^^^^^^^^^^^^^^^^^^
    # TypeError: argument 2 must be a connection or a cursor

    sql_string = text(sql_string)
    results = conn.execute(sql_string)
    for result in results:
        print(result)

Libraries, such as pandas show warnings if I do not use SQLAlchemy, and use DB APIs like psycopg2 (see below). So I was wondering how to marry the two.

UserWarning: pandas only support SQLAlchemy connectable(engine/connection) or
database string URI or sqlite3 DBAPI2 connection
other DBAPI2 objects are not tested, please consider using SQLAlchemy

[dvarrazzo]

The SA connection is (I think) a wrapper of psycopg connections. If you want to use the psycopg2.sql object you should "extract" the psycopg connection out of the SA wrapper.

How to do that is probably a matter of just pringing conn.__dict__ and see what's inside that, or maybe it's documented in SA docs, or you could ask that project how to obtain it.

[skwzrd]

I've made a few attempts with these suggestions, and couldn't figure it out. A discussion has been opened at sqlalchemy/sqlalchemy#10222.

Edit:

From the SA discussion, we got the following code go work. Just need to escape wildcards (%%) and use exec_driver_sql().

with engine.connect() as conn:
    sql_string = sql.SQL(
        """select * from {0} where {1} like 'A%%' limit 5;"""
    )
    sql_string = sql_string.format(sql.Identifier(table_name), sql.Identifier(column_name))

    results = conn.exec_driver_sql(sql_string)
    for result in results:
        print(result)