Bug with cursor initialized from SQLalchemy on PGSpecial

[tonykploomber]

Hello, I noticed one issue after upgrading pgspecial to 2.1.0.

The issue is I used sqlalchemy to construct my connection,

Following code works fine in pgspecial.2.0.1, but not 2.1.0 version

Way to reproduce the issue:

from sqlalchemy import create_engine, text
try:
    from pgspecial.main import PGSpecial
except ImportError:
    PGSpecial = None
eng = create_engine("YOUR_POSTGRESQL_URL").connect()

pgspecial = PGSpecial()

pgspecial.execute(eng.connection.cursor(), "\d")

Trace

---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
Cell In[1], line 12
---> 12 pgspecial.execute(eng.connection.cursor(), "\d")

File ~/miniconda3/envs/jupysql/lib/python3.9/site-packages/pgspecial/main.py:116, in PGSpecial.execute(self, cur, sql)
    114     return special_cmd.handler()
    115 elif special_cmd.arg_type == PARSED_QUERY:
--> 116     return special_cmd.handler(cur=cur, pattern=pattern, verbose=verbose)
    117 elif special_cmd.arg_type == RAW_QUERY:
    118     return special_cmd.handler(cur=cur, query=sql)

File ~/miniconda3/envs/jupysql/lib/python3.9/site-packages/pgspecial/dbcommands.py:894, in describe_table_details(cur, pattern, verbose)
    892 # This is a simple \d[+] command. No table name to follow.
    893 if not pattern:
--> 894     return list_objects(cur, pattern, verbose, ["r", "p", "v", "m", "S", "f", ""])
    896 # This is a \d <tablename> command. A royal pain in the ass.
    897 schema, relname = sql_name_pattern(pattern)

File ~/miniconda3/envs/jupysql/lib/python3.9/site-packages/pgspecial/dbcommands.py:491, in list_objects(cur, pattern, verbose, relkinds)
    488     params["table_pattern"] = SQL("")
    490 formatted_query = sql.format(**params)
--> 491 log.debug(formatted_query.as_string(cur))
    492 cur.execute(formatted_query)
    494 if cur.description:

File ~/miniconda3/envs/jupysql/lib/python3.9/site-packages/psycopg/sql.py:80, in Composable.as_string(self, context)
     72 """
     73 Return the value of the object as string.
     74 
   (...)
     77 
     78 """
     79 conn = context.connection if context else None
---> 80 enc = conn_encoding(conn)
     81 b = self.as_bytes(context)
     82 if isinstance(b, bytes):

File ~/miniconda3/envs/jupysql/lib/python3.9/site-packages/psycopg/_encodings.py:90, in conn_encoding(conn)
     87 if not conn or conn.closed:
     88     return "utf-8"
---> 90 pgenc = conn.pgconn.parameter_status(b"client_encoding") or b"UTF8"
     91 return pg2pyenc(pgenc)

AttributeError: 'psycopg2.extensions.connection' object has no attribute 'pgconn'

Versions:
Python 3.9
sqlalchemy-2.0.8

[tonykploomber]

Seems the error is thrown from psycopg package, but I am think the issue is calling from log.debug(formatted_query.as_string(cur)) in dbcommands.py

[dvarrazzo]

It seems that you are passing a psycopg 2 connection where a psycopg 3 one is expected. Maybe you upgraded the package, but not the dependencies.

[dbaty]

I cannot reproduce this issue with recent versions of everything:

$ pip install sqlalchemy pgspecial
[...]
Successfully installed [...] pgspecial-2.1.1 psycopg-3.1.18 sqlalchemy-2.0.30 [...]

Then, in a Python shell:

>>> from sqlalchemy import create_engine
>>> from pgspecial.main import PGSpecial
>>> eng = create_engine("postgresql+psycopg://pgcli-dev:pgcli-dev@localhost/pgcli-dev").connect()
>>> pgspecial = PGSpecial()
>>> pgspecial.execute(eng.connection.cursor(), "\d")
[(None, <psycopg.Cursor [TUPLES_OK] [INTRANS] (host=localhost database=pgcli-dev) at 0x7f440080e770>, ['Schema', 'Name', 'Type', 'Owner'], 'SELECT 7')]

Note that the connection string must start with postgresql+psycopg:// and not just postgresql:// for SQLAlchemy to use psycopg3 (see SQLAlchemy docs here).