PostgreSQL CREATE DATABASE cannot run inside a transaction block

Question

I'm trying to create dataBase in Postgre SQL Python, i got an error: CREATE DATABASE cannot run inside a transaction block. I have read that u need autocommit, but i already have it. What's the problem

try:
    with psycopg2.connect(
        host=host,
        user=rootUser,
        password=rootPassword,
    ) as connection:
        connection.autocommit = True
        with connection.cursor() as cursor:
            cursor.execute(
                ("CREATE DATABASE " + db_name + """ WITH
                OWNER = """ + user + """ ENCODING = 'UTF8'
                CONNECTION LIMIT = -1
                IS_TEMPLATE = False;""")
            )

Not able to test currently but I would check the use of triple quotes on the create database part. Also I would normally format string to inject parameters e.g. q =””” abc {} ghi”””. Then q.format(‘def’). stackoverflow.com/a/3877647/12406828 I’m wondering if it’s not resolving properly and creating two statements in the view of Postgres. — Stickleback
– Stickleback, Commented Sep 22, 2023 at 20:04
For reasons I don't understand, the with psycopg2.connect(...) as connection: construct seems to render the autocommit setting non-functional. Defining and populating the "connection" variable the usual way does work. — jjanes
– jjanes, Commented Sep 22, 2023 at 20:16

snakecharmerb · Accepted Answer · 2023-09-23 07:08:07Z

0

Since v2.9, with conn starts a transaction (GitHub issue), so it can't be used for actions like executing CREATE DATABASE which must be issued outside a transaction.

You can do it without using the context manager:

try:
    conn = psycopg2.connect(dbname='postgres')
    conn.autocommit = True
    with conn.cursor() as cur:
        cur.execute('CREATE DATABASE foo')
finally:
    conn.close()

or like this

import contextlib

with contextlib.closing(psycopg2.connect(dbname='postgres')) as conn:
    conn.autocommit = True
    with conn.cursor() as cur:
        cur.execute('CREATE DATABASE foo')

answered Sep 23, 2023 at 7:08

snakecharmerb

57.1k13 gold badges136 silver badges200 bronze badges

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

Comments

Nnaemeka Daniel John · Accepted Answer · 2023-09-22 19:56:01Z

-1

First of all you have to ensure that you are using a psycopg version that supports autocommit, i.e version 2.73 or later. Also you have to set autocommit to True before creating the cursor by modifying your code;

try:
    with psycopg2.connect(
        host=host,
        user=rootUser,
        password=rootPassword,
        autocommit=True,
    ) as connection:
        with connection.cursor() as cursor:
            cursor.execute(
                ("CREATE DATABASE " + db_name + """ WITH
                OWNER = """ + user + """ ENCODING = 'UTF8'
                CONNECTION LIMIT = -1
                IS_TEMPLATE = False;""")
            )
except psycopg2.Error as e:
    print("Error:", e)

answered Sep 22, 2023 at 19:56

Nnaemeka Daniel John

4843 silver badges9 bronze badges

1 Comment

jjanes Over a year ago

Using autocommit as a connection parameter is not supported (not in 2.9.3 anyway)

Collectives™ on Stack Overflow

PostgreSQL CREATE DATABASE cannot run inside a transaction block

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related