Create a Postgres database using python
PythonPostgresqlPsycopg2Python Problem Overview
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.
Python Solutions
Solution 1 - Python
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 attacs.
cur.execute(sql.SQL("CREATE DATABASE {}").format(
sql.Identifier(self.db_name))
)
Solution 2 - Python
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)))