Posts Tagged ‘python’

Admin Scripts in pgdb

Posted: February 6, 2008 in FOSS, Techstuff
Tags: , ,

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:

import pgdb
conn=pgdb.connect(‘localhost:postgres:postgres:password’)
cursor=conn.cursor()
cursor.execute(‘create database test’)
cursor.close()
conn.close()

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

cursor.execute(‘COMMIT’)

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.

Advertisements