Question 250985 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL CREATE DATABASE cannot run inside a transaction block

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;""")
            )
Tags:

2

Answers


  1. 0

    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)
    Login or Signup to reply.
  2. 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')
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search