“CREATE ... 语句不允许在多语句事务中"使用pyodbc时 [英] "CREATE ... statement not allowed within multi-statement transaction" when using pyodbc
问题描述
我正在尝试使用 pyodbc 创建 SQL Server 数据库.
I'm trying to create a SQL Server database using pyodbc.
import pyodbc
server = 'AMR112\NAMED1'
database = 'msdb'
username = ''
password = 'mypassword'
abcd='yes'
ghi='False'
#driver = '{/usr/local/lib/libtdsodbc.so}' #for linux of windows
driver= '{ODBC Driver 13 for SQL Server}'
cnxn = pyodbc.connect('DRIVER='+driver+';PORT=1433;SERVER='+server+??';PORT=1443;DATABASE??='+database+';UID='+??username+';PWD='+ password+';trusted_connection='+ abcd+'; autocommit='+ ghi) cursor = cnxn.cursor()
cursor.execute("create database dbafgh")
row = cursor.fetchone()
if row:
print row
cursor.close()
它因这个错误而失败
在多语句事务中不允许使用 CREATE DATABASE 语句
CREATE DATABASE statement not allowed within multi-statement transaction
失败是因为 .execute
方法启动了一个事务并且 CREATE DATABASE
不能在一个事务中运行.
It fails because the .execute
method starts a transaction and CREATE DATABASE
cannot be run within a transaction.
那么还有其他方法可以使用 python 执行 CREATE DATABASE
命令吗?
So is there any other way to execute a CREATE DATABASE
command using python?
推荐答案
在建立连接时,pyodbc 根据 Python 的 DB-API 规范默认为 autocommit=False
.因此,当第一条 SQL 语句被执行时,ODBC 开始一个数据库事务,该事务一直有效,直到 Python 代码执行 .commit()
或 .rollback()
连接.
When establishing a connection, pyodbc defaults to autocommit=False
in accordance with Python's DB-API spec. Therefore when the first SQL statement is executed, ODBC begins a database transaction that remains in effect until the Python code does a .commit()
or a .rollback()
on the connection.
SQL Server 不允许在这样的事务中执行 CREATE DATABASE
,因此在发出这样的语句之前,我们需要在 autocommit
模式下进行连接.这可以在打开连接时完成...
SQL Server does not allow CREATE DATABASE
to be executed within such a transaction, so we need to have the connection in autocommit
mode before issuing such statements. That can be accomplished when the connection is opened ...
conn = pyodbc.connect(conn_str, autocommit=True)
... 或者如果连接已经建立,则切换到 autocommit
模式:
... or by switching to autocommit
mode if the connection is already established:
conn = pyodbc.connect(conn_str) # autocommit=False by default
# ...
conn.autocommit = True
conn.execute("CREATE DATABASE MyNewDatabase")
这篇关于“CREATE ... 语句不允许在多语句事务中"使用pyodbc时的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!