Create a Postgres database using python

Question

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.

From the doc: 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
– Alberto Chiusole, Commented Mar 23, 2022 at 20:00

iamyojimbo · Accepted Answer · 2022-11-29 11:36:20Z

116

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))
    )

edited Nov 29, 2022 at 11:36

iamyojimbo

4,7697 gold badges38 silver badges45 bronze badges

answered Dec 27, 2015 at 19:47

Tom-db

6,9584 gold badges33 silver badges51 bronze badges

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

6 Comments

JL Peyret Over a year ago

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.

András Aszódi Over a year ago

@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... :-)

AlDante Feb 8 at 11:50

@AndrásAszódi Null is always 0 in C. See section 6.3.2.3 open-std.org/jtc1/sc22/wg14/www/docs/n1256.pdf

András Aszódi Feb 11 at 8:17

@AlDante Thank you for the C standard document. In Section §7.17(3), Page 254 it says "The macros are NULL which expands to an implementation-defined null pointer constant" [emphasis mine]. I have never seen an implementation that defined a NULL different from 0, but the standard does allow it :-)

AlDante Feb 12 at 18:36

@AndrásAszódi stddef does define an implementation dependent NULL constant. But you were discussing pointers, specifically the legitimacy of assigning 0 to them. The C standard guarantees that 0 pointers are null pointers and vice versa. Whether they are equal to the NULL macro is, as you point out, implementation defined. NB I agree that it’s better to hide magic numbers behind a macro or a keyword. Just saying that a null pointer is always 0 in C, not just “in all likelihood”.

|

Álvaro · Accepted Answer · 2020-08-02 10:30:08Z

52

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)))

edited Aug 2, 2020 at 10:30

answered Apr 26, 2017 at 12:58

Álvaro

2,06918 silver badges29 bronze badges

2 Comments

quapka Over a year ago

Do we just see another SQL injection through the value of self.db_name? self.db_name = "arbitrary; DROP TABLE admin"

lava Over a year ago

con.autocommit = True. fixed

renato · Accepted Answer · 2022-11-11 14:53:51Z

3

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

edited Nov 11, 2022 at 14:53

answered Nov 11, 2022 at 14:52

renato

394 bronze badges

1 Comment

Thismatters Feb 12 at 20:32

This doesn't actually create the database. It raises an error when the dbname doesn't exist.

Collectives™ on Stack Overflow

Create a Postgres database using python

3 Answers 3

6 Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related