使用Python创建SQL Server数据库

Question

我正在使用Python和pywin32的adodbapi编写脚本来创建SQL Server数据库及其所有相关表，视图和存储过程。问题在于Python的DBAPI要求将cursor.execute()包装在只有通过cursor.commit()才能提交的事务中，而用户事务中无法执行删除或创建数据库语句。有什么想法可以解决这个问题吗？

编辑：似乎adodbapi的connect()方法或其cursor()方法都没有类似于自动提交参数的东西。如果可以的话，我很乐意使用pymssql代替adodbapi，但它会将char和varchar数据类型截断为255个字符。

在发布之前，我尝试过这个方法；以下是回溯信息。

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

- JasonFruit

Answer 1

“问题在于Python的DBAPI要求将cursor.execute()包装在一个事务中，只有通过cursor.commit()提交才能完成。”

“而且你不能在用户事务中执行drop或create数据库语句。”

“我不确定这是否适用于所有DBAPI接口。”

“由于你没有显示错误信息，可能会发现这对于ADODBAPI接口并非如此。你真的尝试过吗？如果是这样，你得到了什么错误消息？”

“连接可能不总是创建一个“用户事务”。你可以经常使用autocommit=True打开连接以获得DDL-style autocommit。”

“此外，您可能希望考虑使用不同的连接来运行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))')

- S.Lott

---

你说得对 - 这并不适用于所有的DBAPI接口，但当提供者允许时，这是首选的方法。我已经更新了我的问题，并提供了与你的回答相关的更多信息。谢谢你的建议！ - JasonFruit

Answer 2

adodbapi连接对象conn如果数据库支持事务，则在每次提交后会自动启动新的事务。DB-API要求默认关闭自动提交，并允许API方法将其重新打开，但我在adodbapi中没有看到这样的方法。

您可以尝试使用conn.adoConn属性绕过此问题，使用ADO API而不是DB-API将您从任何事务中取出。如果这起作用，请告诉我：

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

这里是adodbapi commit()方法的源代码。

- joeforker

---

那很接近了。最终起作用的是：conn.adoConn.Execute("create database dummydatabase")谢谢！我可能永远也想不出来。 - JasonFruit

Answer 3

我在尝试运行adodbapi命令（例如DBCC CHECKDB...）时遇到了同样的问题，joeforker的建议有所帮助。但我仍然面临一个问题，那就是adodbapi会自动启动一个事务，因此无法在事务之外执行任何操作。

最终，我通过以下方式禁用了adodbapi的提交行为：

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

据我所知，这将重新启用标准的SQL Server自动提交功能，即每个SQL语句都会自动提交。缺点是，如果我想在事务中运行某些内容，目前没有办法再次启用事务，因为当supportsTransactions == False时，Connection.commit()不会执行任何操作。

- liwp

Answer 4

在事务之外创建实际的数据库。我不熟悉Python，但必须有一种方法可以执行用户提供的字符串到数据库中，将其与实际的创建数据库命令一起使用。然后使用adodbapi来完成所有表格等操作，并提交该事务。

- KM.

---

是的，这是问题的一个很好的表述。你有任何想法如何完成它吗？ Adodbapi文档大部分都没有记录，但是我通过其单元测试进行了深入挖掘，没有测试涉及在事务之外运行SQL命令。 - JasonFruit

尝试除adodbapi之外的其他方法来发出创建命令。 - KM.