In my project there was need for writing few admin scripts in python to create database account and database for a user.
In case of MySQL we connected to mysql database (admin database) and then performed the DDL queries like create database.The connection string we used is
conn=mysql.connect(host=”localhost”,user=”username”,passwd=” “,db=”mysql” ).
It worked well.In the same way we wrote code for creating database in postgres is as follows:
cursor.execute(‘create database test’)
here postgres is the admin database.
But the create database didn’t work. The following error was thrown.
ERROR: CREATE DATABASE: May not be called in a transaction block
ie., we are not allowed to create database in the transaction block. Later found that doing a commit will end or close the current transaction and hence after a commit we performed create database.It worked then. In order to do that we just included a line
before the line cursor.execute(‘create database test’)
Thus we can just do a commit before issuing any statements that PostgreSQL cannot execute within a transaction and perform such statements outside the transaction block.