77

I want to create Postgres database using Python.

con = psql.connect(dbname='postgres',
      user=self.user_name, host='',
      password=self.password)

cur = con.cursor()
cur.execute("CREATE DATABASE %s  ;" % self.db_name)

I am getting the following error:

InternalError: CREATE DATABASE cannot run inside a transaction block

I am using psycopg2 to connect. I don't understand what's the problem. What am I trying to do is to connect to database (Postgres):

psql -postgres -U UserName

And then create another database:

create database test;

This is what I usually do and I want to automate this by creating Python script.

Muhamed Huseinbašić
  • 552
  • 2
  • 7
  • 25
kiran6
  • 1,247
  • 2
  • 13
  • 19
  • From the [doc](https://www.psycopg.org/docs/usage.html#the-problem-with-the-query-parameters): Warning - Never, never, NEVER use Python string concatenation (+) or string parameters interpolation (%) to pass variables to a SQL query string. Not even at gunpoint. – Alberto Chiusole Mar 23 '22 at 20:00

3 Answers3

111

Use ISOLATION_LEVEL_AUTOCOMMIT, a psycopg2 extensions:

No transaction is started when command are issued and no commit() or rollback() is required.

import psycopg2
from psycopg2 import sql
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT # <-- ADD THIS LINE

con = psycopg2.connect(dbname='postgres',
      user=self.user_name, host='',
      password=self.password)

con.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT) # <-- ADD THIS LINE

cur = con.cursor()

# Use the psycopg2.sql module instead of string concatenation 
# in order to avoid sql injection attacks.
cur.execute(sql.SQL("CREATE DATABASE {}").format(
        sql.Identifier(self.db_name))
    )
iamyojimbo
  • 4,233
  • 6
  • 32
  • 39
Tom-db
  • 6,528
  • 3
  • 30
  • 44
  • worked for me. just FYI all that you are importing is **0** the integer. so could just pass it in without bothering w extensions. and also FYI you need a separate execute call if you have to DROP DATABASE first. – JL Peyret Aug 30 '19 at 01:57
  • 2
    @JLPeyret "all that you are importing is 0 the integer. so could just pass it in without bothering w extensions" -- true, but one can never be sure that this stays so forever. Code such as `con.set_isolation_level(0)` is less readable and less future-proof as named constants. Cf. the issue of C's `NULL` pointer. It almost always has the numerical value `0`, but it would be very bad practice to say `int *x = 0;`. In C++11 and above, there's even a keyword `nullptr` for this. Although everyone "knows" that it's just `0` in all likelihood... :-) – András Aszódi Mar 11 '20 at 10:37
48

As shown in the other answer the connection must be in autocommit mode. Another way of setting it using psycopg2 is through the autocommit attribute:

import psycopg2
from psycopg2 import sql

con = psycopg2.connect(...)
con.autocommit = True

cur = con.cursor()
# sql.SQL and sql.Identifier are needed to avoid SQL injection attacks.
cur.execute(sql.SQL('CREATE DATABASE {};').format(
    sql.Identifier(self.db_name)))
Álvaro Marco
  • 2,029
  • 17
  • 29
1

A better and simple solution:

import psycopg # this uses psycopg version 3

def conection()
    config = {'user':'postgres',
          'password':'password_string',
              'host':'127.0.0.1',
              'port':'5432',
            'dbname':'postgres',
        'autocommit':True} #this resolve the problem "InternalError: CREATE DATABASE cannot run inside a transaction block"
    try:
        cnx = psycopg.connect(**config)
    except psycopg.Error as err:
        print(err)
        exit(1)
    else:
        return cnx
renato
  • 11
  • 3