paramstyle not propagated to pg8000

[sandro-felicioni]

Bug description
When executing a raw sql statement using connection.execute(..) the specified paramstyle in create_engine(..) is not propagated to pg8000. In turn, the parameters cannot be resolved correctly and the sql statement fails. When I manually debug and override the cursor.paramstyle parameter to e.g. 'named' it works as expected.

To Reproduce
Execute following code reproduces the error.

engine = create_engine(f"postgresql+pg8000://{username}:{password}@{host}:{port}/{database}",  paramstyle="named")
with engine.connect() as connection:
    connection.execute("INSERT INTO mytable (user_id) VALUES (:p1)", p1="user1")

The schema I used was the following:
CREATE TABLE mytable( user_id VARCHAR(30) );

Error

Traceback (most recent call last):
  File "/Users/Sandro/projects/dummy/main.py", line 10, in <module>
    main()
  File "/Users/Sandro/projects/dummy/main.py", line 27, in main
    connection.execute("INSERT INTO mytable (user_id) VALUES (:p1)", p1="user1")
  File "/Users/Sandro/anaconda3/envs/dummy/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 976, in execute
    return self._execute_text(object_, multiparams, params)
  File "/Users/Sandro/anaconda3/envs/dummy/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1151, in _execute_text
    parameters,
  File "/Users/Sandro/anaconda3/envs/dummy/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1288, in _execute_context
    e, statement, parameters, cursor, context
  File "/Users/Sandro/anaconda3/envs/dummy/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1482, in _handle_dbapi_exception
    sqlalchemy_exception, with_traceback=exc_info[2], from_=e
  File "/Users/Sandro/anaconda3/envs/dummy/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 178, in raise_
    raise exception
  File "/Users/Sandro/anaconda3/envs/dummy/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1248, in _execute_context
    cursor, statement, parameters, context
  File "/Users/Sandro/anaconda3/envs/dummy/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 590, in do_execute
    cursor.execute(statement, parameters)
  File "/Users/Sandro/anaconda3/envs/dummy/lib/python3.6/site-packages/pg8000/core.py", line 865, in execute
    self._c.execute(self, operation, args)
  File "/Users/Sandro/anaconda3/envs/dummy/lib/python3.6/site-packages/pg8000/core.py", line 1849, in execute
    self.handle_messages(cursor)
  File "/Users/Sandro/anaconda3/envs/dummy/lib/python3.6/site-packages/pg8000/core.py", line 1988, in handle_messages
    raise self.error
sqlalchemy.exc.ProgrammingError: (pg8000.core.ProgrammingError) {'S': 'ERROR', 'V': 'ERROR', 'C': '42601', 'M': 'syntax error at or near ":"', 'P': '39', 'F': 'scan.l', 'L': '1128', 'R': 'scanner_yyerror'}
[SQL: INSERT INTO mytable (user_id) VALUES (:p1)]
[parameters: {'p1': 'user1'}]
(Background on this error at: http://sqlalche.me/e/f405)

Versions.

• OS: Mac
• Python: 3.6
• SQLAlchemy: 1.3.16
• Database: Postgres:10.7
• DBAPI: pg8000:1.15.2

[gordthompson]

Try wrapping your SQL command in a SQLAlchemy text object, e.g.,

from sqlalchemy import text

# ...

with engine.connect() as connection:
    connection.execute(text("INSERT INTO mytable (user_id) VALUES (:p1)"), p1="user1")

[zzzeek]

The DBAPI does not define any attribute "cursor.paramstyle": https://www.python.org/dev/peps/pep-0249/#cursor-attributes so SQLAlchemy knows nothing about this. SQLAlchemy's "paramstyle" indicates the paramstyle that SQLAlchemy should render and that's all.

if pg8000 accepts this attribute on the connection itself as well, you could set it using a connect event:

https://docs.sqlalchemy.org/en/13/core/events.html?highlight=event%20connect#sqlalchemy.events.PoolEvents.connect

or you could set it at the pg8000 module level. the module-level paramstyle is also not documented as writable in the DBAPI.

[sandro-felicioni]

@gordthompson surrounding the query with text does not help.

@zzzeek what I meant was that pg8000's cursor contains the paramstyle attribute and could be passed in as constructor argument. Cursor extract from pg8000/core.py:

class Cursor():
 def __init__(self, connection, paramstyle=None):
        self._c = connection
        self.arraysize = 1
        self.ps = None
        self._row_count = -1
        self._cached_rows = deque()
        if paramstyle is None:
            self.paramstyle = pg8000.paramstyle
        else:
            self.paramstyle = paramstyle

As you suggested I could make it work by overriding pg8000.paramstyle myself like this:

engine = create_engine(f"postgresql+pg8000://{username}:{password}@{host}:{port}/{database}",  paramstyle="named")
pg8000.paramstyle = "named"
with engine.connect() as connection:
    connection.execute("INSERT INTO mytable (user_id) VALUES (:p1)", p1="user1")

However I find it confusing that in the official sqlalchemy doc here I see many code snippets like the following but apparently they only work if either the default paramstyle of the DPAPI is the one you use or you set the module's paramstyle manually in some other way.

conn.execute(
    table.insert(),
    {"id":1, "value":"v1"},
    {"id":2, "value":"v2"}
)

I have two suggestions so that others don't fall into the same trap:

• Add an additional comment when you explain paramstyle here e.g. that you need to propagate it manually to the module.

• Add a working example for pg8000 (e.g. above solution) to the SQLAlchemy doc here.

Anyway thanks for clarifying things up!