Creating a SQL Server database from Python

Question

I'm using Python with pywin32's adodbapi to write a script to create a SQL Server database and all its associated tables, views, and procedures. The problem is that Python's DBAPI requires that cursor.execute() be wrapped in a transaction that is only committed by cursor.commit(), and you can't execute a drop or create database statement in a user transaction. Any ideas on how to get around that?

EDIT:

There does not seem to be anything analogous to an autocommit parameter to either the connect() method of adodbapi or its cursor() method. I'd be happy to use pymssql instead of adodbapi, except that it truncates char and varchar datatypes at 255 characters.

I did try this before posting; here's the traceback.

Traceback (most recent call last):
  File "demo.py", line 39, in <module>
    cur.execute("create database dummydatabase")
  File "C:\Python26\lib\site-packages\adodbapi\adodbapi.py", line 713, in execute
    self._executeHelper(operation,False,parameters)
  File "C:\Python26\lib\site-packages\adodbapi\adodbapi.py", line 664, in _executeHelper
    self._raiseCursorError(DatabaseError,tracebackhistory)
  File "C:\Python26\lib\site-packages\adodbapi\adodbapi.py", line 474, in _raiseCursorError
    eh(self.conn,self,errorclass,errorvalue)
  File "C:\Python26\lib\site-packages\adodbapi\adodbapi.py", line 60, in standardErrorHandler
    raise errorclass(errorvalue)
adodbapi.adodbapi.DatabaseError: 
--ADODBAPI
Traceback (most recent call last):
   File "C:\Python26\lib\site-packages\adodbapi\adodbapi.py", line 650, in _executeHelper
    adoRetVal=self.cmd.Execute()
   File "<COMObject ADODB.Command>", line 3, in Execute
   File "C:\Python26\lib\site-packages\win32com\client\dynamic.py", line 258, in _ApplyTypes_
    result = self._oleobj_.InvokeTypes(*(dispid, LCID, wFlags, retType, argTypes) + args)
 com_error: (-2147352567, 'Exception occurred.', (0, u'Microsoft SQL Native Client', u'CREATE DATABASE statement not allowed within multi-statement transaction.', None, 0, -2147217900), None)
-- on command: "create database dummydatabase"
-- with parameters: None

Answer 1

"The problem is that Python's DBAPI requires that cursor.execute() be wrapped in a transaction that is only committed by cursor.commit()"

"and you can't execute a drop or create database statement in a user transaction."

I'm not sure all of this is actually true for all DBAPI interfaces.

Since you don't show the error messages, it may turn out that this is not true for ADODBAPI interface. Have you actually tried it? If so, what error message are you getting?

A connection may not always be creating a "user transaction". You can often open connections with autocommit=True to get DDL-style autocommit.

Also, you might want to consider using a different connection to do run DDL.

http://pymssql.sourceforge.net/ for example, shows DDL being executed like this.

import pymssql
conn = pymssql.connect(host='SQL01', user='user', password='password', database='mydatabase')
cur = conn.cursor()
cur.execute('CREATE TABLE persons(id INT, name VARCHAR(100))')

Answer 2

The adodbapi connection object conn does automatically start a new transaction after every commit if the database supports transactions. DB-API requires autocommit to be turned off by default and it allows an API method to turn it back on, but I don't see one in adodbapi.

You might be able to use the conn.adoConn property to hack around this, using the ADO api instead of DB-API to take you out of any transaction. Let me know if this works:

conn.adoConn.CommitTrans()
cursor.execute('CREATE DATABASE ...')
conn.adoConn.BeginTrans()

Here's the source for the adodbapi commit() method.