Creating a SQL Server Database from Python

Question

I am using Python with pywin32 adodbapi to write a script to create a SQL Server database and all related tables, views and procedures. The problem is that Python DBAPI requires cursor.execute () to be wrapped in a transaction that is executed only with cursor.commit (), and you cannot run the drop or create database command in a user transaction. Any ideas on how to get around this?

EDIT:

Nothing seems like the autocommit parameter to the adodbapi connect () method or its cursor () method. I would be glad to use pymssql instead of adodbapi, except that it truncates char and varchar datatypes with 255 characters.

I tried this before posting; here's the trace.

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

adodbapi conn , . DB-API , autocommit , API- , adodbapi.

, conn.adoConn, , ADO api DB-API, . , :

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

adodbapi commit().

Answer 2

" , PAP-DBAPI , cursor.execute() , cursor.commit()"

", drop create database ."

, DBAPI.

, , ADODBAPI. ? , ?

" ". autocommit=True, DDL.

, DDL.

http://pymssql.sourceforge.net/, , , DDL .

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 3

db . python, , create db. adodbapi .. .

Answer 4

adodbapi (, DBCC CHECKDB...), joeforker . , , , adodbapi , - .

adodbapi commit :

self.conn = adodbapi.connect(conn_str)
# rollback the transaction that was started in Connection.__init__()
self.conn.adoConn.RollbackTrans() 
# prevent adodbapi from trying to rollback a transaction in Connection.close()
self.conn.supportsTransactions = False

As far as I can tell, this allows you to reuse the standard SQL Server auto-commit features, i.e. every SQL statement is automatically committed. The downside is that I don’t have the ability to re-activate the transaction (at the moment) if I don’t start something in the transaction, because Connection.commit()it won’t do anything when supportsTransactions == False.