SQLAlchemy ProgrammingError when inserting into postgresql 11 table

Question

I am unable to insert data using SQLAlchemy (v1.3.10) into a postgresql 11 table. I first noticed the error when running some old code that had been previously working with SQLAlchemy (v1.2.10) and posgresql 11. I updated both versions in my project some time ago, but hadn't tested the database part of my codebase until now (my mistake). The code base is relatively large, so unfortunately, I'm not exactly sure what else could have changed to cause this problem. I have tried downgrading sqlalchemy and psycopg2 to the previous versions, but that did not resolve the issue. Unfortunately, reverting back to postgresql 10 is more challenging so I would like to avoid that if possible.

The error first occurred when trying to insert a ORM model object, but I get the same error when trying to insert using the expression API and even with raw sql statements. I set up a very simple table:

create table test_table(
  id integer not null primary key;
);

and created a session using the Getting a Session recipe with a context manager (lower on the page). When executing this code:

stmt = text("insert into nba.test_table(id) values (:id)")
values = {'id': 3}
session.execute(stmt, values)

I get the following stack trace:

Traceback (most recent call last):
  File "/usr/lib64/python3.6/unittest/case.py", line 59, in testPartExecutor
    yield
  File "/usr/lib64/python3.6/unittest/case.py", line 605, in run
    testMethod()
  File "/home/reid/git/worthy/test/test_db.py", line 32, in test_insert
    session.execute(stmt, values)
  File "/home/reid/git/worthy/.venv/lib64/python3.6/site-packages/sqlalchemy/orm/session.py", line 1269, in execute
    clause, params or {}
  File "/home/reid/git/worthy/.venv/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 988, in execute
    return meth(self, multiparams, params)
  File "/home/reid/git/worthy/.venv/lib64/python3.6/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/reid/git/worthy/.venv/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1107, in _execute_clauseelement
    distilled_params,
  File "/home/reid/git/worthy/.venv/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1253, in _execute_context
    e, statement, parameters, cursor, context
  File "/home/reid/git/worthy/.venv/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1473, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "/home/reid/git/worthy/.venv/lib64/python3.6/site-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/home/reid/git/worthy/.venv/lib64/python3.6/site-packages/sqlalchemy/util/compat.py", line 152, in reraise
    raise value.with_traceback(tb)
  File "/home/reid/git/worthy/.venv/lib64/python3.6/site-packages/sqlalchemy/engine/base.py", line 1249, in _execute_context
    cursor, statement, parameters, context
  File "/home/reid/git/worthy/.venv/lib64/python3.6/site-packages/sqlalchemy/engine/default.py", line 580, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "insert"
LINE 1: ...ECLARE "c_7fbd3ae7c8d0_1" CURSOR WITHOUT HOLD FOR insert int...
                                                             ^

[SQL: insert into nba.test_table(id) values (%(id)s)]
[parameters: {'id': 3}]
(Background on this error at: http://sqlalche.me/e/f405)

The stack trace is nearly identical whether I use a raw sql query, a query built using the sqlalchemy expression api, or using a model class mapped with the ORM api.

I can query tables just fine and if I run an equivalent query using the psycopg2 module directly, I have no problems inserting the data.

Reid Swanson · Accepted Answer · 2019-10-11 20:04:47Z

1

There is a slight difference in the way I created my engine than the recipe provided in the link above.

From the link it is:

some_engine = create_engine('postgresql://scott:tiger@localhost/')

Where as I had included an extra argument

some_engine = create_engine('postgresql://scott:tiger@localhost/', execution_options={'stream_results': True})

When removing the execution_options argument everything works again.

answered Oct 11, 2019 at 20:04

Reid Swanson

731 silver badge8 bronze badges

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

Comments

Danielle · Accepted Answer · 2021-10-29 17:04:38Z

1

connection().execute(...) is for grabbing query data. For everything else try getting the cursor to run your db commands.

Here is an example that works:

conn = create_engine('your_connection_string',
                     execution_options={'stream_results': True}).raw_connection()
cur = conn.cursor()
cur.execute(f'SET search_path = public, {other_search_path}')

dfs = []
for chunk in pd.read_sql(f'''
    SELECT *
    FROM table
    WHERE unique_id = 7777
    ''', conn, chunksize=1000000):

    dfs.append(chunk)

return pd.concat(dfs)

edited Oct 29, 2021 at 17:04

answered Oct 29, 2021 at 16:50

Danielle

1411 gold badge1 silver badge7 bronze badges

Collectives™ on Stack Overflow

SQLAlchemy ProgrammingError when inserting into postgresql 11 table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related