Create a Postgres database using python
Use ISOLATION_LEVEL_AUTOCOMMIT, a psycopg2 extensions:
No transaction is started when command are issued and no commit() or rollback() is required.
import psycopg2from psycopg2 import sqlfrom psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT # <-- ADD THIS LINEcon = psycopg2.connect(dbname='postgres', user=self.user_name, host='', password=self.password)con.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT) # <-- ADD THIS LINEcur = 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)) )
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 psycopg2from psycopg2 import sqlcon = psycopg2.connect(...)con.autocommit = Truecur = 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)))