无法使用存储过程pyodbc SQL SERVER创建数据库 [英] Not able to create database using stored procedure pyodbc SQL SERVER

问题描述

我试图调用一个存储过程，该存储过程从pyodbc创建数据库

以下是代码的最小示例

 导入pyodbc 
 conn = pyodbc.connect（ Driver = {SQL Server}; Server = SERVERNAME; Trusted_Connection = yes;）
游标= conn.cursor（）
 cursor.execute（ \ 
 DECLARE @RC int 
 DECLARE @ClientName varchar（255）
 SET @ClientName ='Test'
 EXECUTE @RC = [DB_NAME]。[SCHEMA]。[sp_NAME] @ClientName 
）
 conn.commit（）
  

理想情况下，代码应在SQL Server实例中创建一个名为 Test 的数据库。这不会产生任何错误。

运行

  DECLARE @RC int 
宣告@ClientName varchar（255）
 SET @ClientName ='Test'
 EXECUTE @RC = [DB_NAME]。[SCHEMA]。[sp_NAME] @ClientName 
  

从SSMS似乎可以创建数据库。已经提到了几个问题，包括这个问题。

解决方案

Python DB API 2.0 指定默认情况下，应在禁用自动提交的情况下打开连接。但是，许多数据库要求DDL语句不在事务内执行。尝试在禁用自动提交的情况下执行DDL语句可能会导致错误或意外结果。

因此，确保在启用了自动提交的连接上执行DDL语句更为安全。 。这可以通过在打开连接时设置 autocommit = True 来实现...

< pre class = lang-python prettyprint-override> cnxn = pyodbc.connect（connection_string，autocommit = True）


...或通过在现有连接上启用 autocommit ...

  cnxn = pyodbc.connect（connection_string）
＃... 
 cnxn.autocommit =真
  

I am trying to call a stored procedure that creates a Database from pyodbc

The Following is a minimal example of the code

import pyodbc
conn = pyodbc.connect("Driver={SQL Server};Server=SERVERNAME;Trusted_Connection=yes;")
cursor=conn.cursor()
cursor.execute("""\
    DECLARE @RC int
    DECLARE @ClientName varchar(255)
    SET @ClientName = 'Test'
    EXECUTE @RC = [DB_NAME].[SCHEMA].[sp_NAME] @ClientName
""")
conn.commit()

The code should ideally create a Database named 'Test' in the SQL Server Instance. This does not produce any error. But the database is not created.

Running

DECLARE @RC int
DECLARE @ClientName varchar(255)
SET @ClientName = 'Test'
EXECUTE @RC = [DB_NAME].[SCHEMA].[sp_NAME] @ClientName

From SSMS seems to create the database. Already referred a couple of questions including this one.

解决方案

The Python DB API 2.0 specifies that, by default, connections should be opened with autocommit disabled. However, many databases require that DDL statements not be executed within a transaction. Attempts to execute DDL statements with autocommit disabled can lead to errors or unexpected results.

Therefore, it is safer to ensure that DDL statements are executed on a connection where autocommit is enabled. That can be accomplished by setting autocommit=True when opening the connection ...

cnxn = pyodbc.connect(connection_string, autocommit=True)

... or by enabling autocommit on an existing connection ...

cnxn = pyodbc.connect(connection_string)
# ...
cnxn.autocommit = True

这篇关于无法使用存储过程pyodbc SQL SERVER创建数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！