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
Comments
  1. Nishvanth.. says:

    Create Database can be created inisde a transaction block..//

  2. Geethapriya says:

    Creating database within the transaction block in mysql using a python script worked for me. But doing the same in postgres didnt work. So i had to end the transaction block by a commit and then create the database out of it.

  3. Geethapriya says:

    @Nishvanth Is there any other way to do this in postgres?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s