How to handle idle in transaction in python for Postgres?

Question

Hello everyone I have the following issue,

I am trying to run a simple UPDATE query using sqlalchemy and psycopg2 for Postgres.

the query is

update = f"""
             UPDATE {requests_table_name}
             SET status = '{status}', {column_db_table} = '{dt.datetime.now()}'
             WHERE request_id = '{request_id}'
            """

and then commit the changes using

cursor.execute(update).commit()

But it throws an error that AttributeError: 'NoneType' object has no attribute 'commit'

My connection string is

engine = create_engine(
            f'postgresql://{self.params["user"]}:{self.params["password"]}@{self.params["host"]}:{self.params["port"]}/{self.params["database"]}')
        
conn = engine.connect().connection
cursor = conn.cursor()

The other thing is that cursor is always closed <cursor object at 0x00000253827D6D60; closed: 0>

The connection with the database is ok, I can etch tables and update them using pandas pd_to_sql method, but with commmiting using cursor it does not work. It works perfect with sql server but not with postgres. In postgres, however, it creates a PID with the status "idle in transaction" and Client: ClientRead, every time I run cursor.execute(update).commit().

I connot get where is the problem, in the code or in the database. I tried to use different methods to initiate a cursor, like raw_connection(), but without a result.

I checked for Client: ClientRead with idle in transaction but am not sure how to overcome it.

Regarding f-strings, don't use them (or any string formatting) for passing values to SQL. It is error prone and can lead to SQL injection. The libraries provide their own placeholder mechanisms for passing values. Since you're using SQLAlchemy, you should not need to access the underlying DB-API connection (and cursors), unless doing something more exotic (like COPY). — Ilja Everilä
– Ilja Everilä, Commented Sep 9, 2020 at 7:04

Laurenz Albe · Accepted Answer · 2020-09-09 07:25:52Z

1

You have to call commit() on the connection object.

According to the documentation, execute() returns None.

answered Sep 9, 2020 at 7:25

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Comments

Sander Vinkesteijn · Accepted Answer · 2021-10-28 08:13:45Z

1

Note that even if you use a context manager like this:

with my_connection.cursor() as cur:
  cur.execute('INSERT INTO ..')

You may find your database processes still getting stuck in the idle in transaction state. The COMMIT is handled at the connection level, like @laurenz-albe said, so you need to wrap that too:

with my_connection as conn:
  with conn.cursor() as cur:
    cur.execute('INSERT INTO ..')

It's spelled out clearly in the documentation, but I still managed to overlook it.

answered Oct 28, 2021 at 8:13

Sander Vinkesteijn

111 bronze badge

Collectives™ on Stack Overflow

How to handle idle in transaction in python for Postgres?

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