CODEWITHSUNDEEP
python-3.xpsycopg2
Bjarke Moholt
Bjarke Moholt

Reputation: 323

How to call non-transaction sql using psycopg2?

I am using psycopg2 with python3.10.4 to connect to a database, like this:

import os
import psycopg2
import psycopg2.extras
from psycopg2._psycopg import connection as pgconnection

class DbConnection:
    con : pgconnection = None

    def connect(self):
        dbname = os.getenv('POSTGRES_DB')
        user = os.getenv('POSTGRES_USER')
        self.con = psycopg2.connect(
            dbname=dbname,
            user=user,
            cursor_factory=psycopg2.extras.RealDictCursor)

    def __execute(self, fetch: bool, query, vars=...) -> list[tuple[Any]]:
        if not self.con or self.con.closed != 0:
            self.connect()
        with self.con as con:
            with con.cursor() as cur:
                cur.execute(query, vars)
                if fetch:
                    return cur.fetchall()
        return None

I use this to execute a set of migration scripts, one of them being

ALTER SYSTEM SET max_connections TO '100';

When I do this, I get ERROR:  ALTER SYSTEM cannot run inside a transaction block

From my understanding, psycopg2 executes in transactions and fails to perform the ALTER SYSTEM statement because it is a non-transactional method. So the question is:

How do I execute a non-transactional statement like ALTER SYSTEM using psycopg2?

Upvotes: 0

Views: 697

Answers (1)

Adrian Klaver
Adrian Klaver

Reputation: 19664

Use autocommit per Transactions control.

con.close()
con = psycopg2.connect(dbname="test", host='localhost', user='postgres', port=5432)
con.autocommit=True
cur = con.cursor()
cur.execute("ALTER SYSTEM SET max_connections TO '100'")

Though this will only take hold after server is restarted.

If you have already started a connection session by running some queries you will need to start a new session or close/reopen connection.

con.close()
con = psycopg2.connect(dbname="test", host='localhost', user='postgres', port=5432)
cur = con.cursor()
cur.execute("SHOW all")
con.autocommit = True 
ProgrammingError: set_session cannot be used inside a transaction

con.rollback()
con.autocommit = True

Upvotes: 1

Related Questions