使用 pyodbc 时 SQL 查询失败，但在 SQL 中工作

Question

我在处理应该非常简单的脚本时遇到了一些麻烦。 我只是尝试使用 Python pyodbc 模块创建一个新的 SQL Server 数据库。 当我在SQL Server 2012中执行它时，我尝试传入的"sqlcommand"参数可以完美运行，但是这个python脚本失败了。 不知道出了什么问题，有人有什么想法吗？

import pyodbc, os
def create_db(folder, db_name):
    unc = r'\arcsqlSDE{0}'.format(folder)
    if not os.path.exists(unc):
        os.makedirs(unc)
    full_name = os.path.join(r'E:SDE', folder, db_name)
    conn = pyodbc.connect("driver={SQL Server}; server=ArcSQL; database=master; Trusted_Connection=yes", automcommit=True) 
    cursor = conn.cursor()
    sqlcommand = """USE [master]
GO
CREATE DATABASE [{0}] ON PRIMARY
( NAME = N'{0}', FILENAME = N'{1}.mdf', SIZE = 4MB , MAXSIZE = 10MB, FILEGROWTH = 1MB )
LOG ON
( NAME = N'{0}_log', FILENAME = N'{1}_log.ldf', SIZE = 4MB , MAXSIZE = 10MB, FILEGROWTH = 10%)
GO
USE [{0}]
GO""".format(db_name, full_name)
    print sqlcommand
    cursor.execute(sqlcommand)
    print 'Created "{0}"'.format(db_name)
if __name__ == '__main__':
    #test
    create_db('_test', 'py_db_test')

和错误：

>>> 
USE [master]
GO
CREATE DATABASE [py_db_test2] ON PRIMARY
( NAME = N'py_db_test2', FILENAME = N'E:SDE_testpy_db_test2.mdf', SIZE = 4MB , MAXSIZE = 10MB, FILEGROWTH = 1MB )
LOG ON
( NAME = N'py_db_test2_log', FILENAME = N'E:SDE_testpy_db_test2_log.ldf', SIZE = 4MB , MAXSIZE = 10MB, FILEGROWTH = 10%)
GO
USE [py_db_test2]
GO
Traceback (most recent call last):
  File "C:/Users/calebma/Desktop/create_sql_db.py", line 40, in <module>
    create_db('_test', 'py_db_test2')
  File "C:/Users/calebma/Desktop/create_sql_db.py", line 35, in create_db
    cursor.execute(sqlcommand)
Error: ('08004', "[08004] [Microsoft][ODBC SQL Server Driver][SQL Server]Database 'py_db_test2' does not exist. Make sure that the name is entered correctly. (911) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'GO'. (102); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'GO'. (102)")
>>> 

Answer 1

GO是 SQL Server Management Studio 中的批处理终止符。这在pyodbc中没有任何意义.相反，请从脚本中发出单独的命令。

更新的代码：

import pyodbc, os
def create_db(folder, db_name):
    unc = r'\arcsqlSDE{0}'.format(folder)
    if not os.path.exists(unc):
        os.makedirs(unc)
    full_name = os.path.join(r'E:SDE', folder, db_name)
    conn = pyodbc.connect("driver={SQL Server}; server=ArcSQL; database=master; Trusted_Connection=yes", automcommit=True) 
    cursor = conn.cursor()
    sqlcommand = """
CREATE DATABASE [{0}] ON PRIMARY
( NAME = N'{0}', FILENAME = N'{1}.mdf', SIZE = 4MB , MAXSIZE = 10MB, FILEGROWTH = 1MB )
LOG ON
( NAME = N'{0}_log', FILENAME = N'{1}_log.ldf', SIZE = 4MB , MAXSIZE = 10MB, FILEGROWTH = 10%)
""".format(db_name, full_name)
    print sqlcommand
    cursor.execute(sqlcommand)
    print 'Created "{0}"'.format(db_name)
    # Do stuff in the new database
    conn = pyodbc.connect("driver={SQL Server}; server=ArcSQL; database={0}; Trusted_Connection=yes".format(db_name), automcommit=True) 
if __name__ == '__main__':
    #test
    create_db('_test', 'py_db_test')

Answer 2

对于其他有类似问题的人，您也缺少提交。

因此，您可以在开始时使用 conn.autocommit = True，或者在 cursor.execute 命令后使用 conn.commit（）

我

挣扎了很长时间，试图弄清楚为什么我存储的proc不会运行但不会出错 - 最终我正在做一个cursor.commit（）而不是conn.commit（）

Answer 3

适用于脚本中有 GO 并希望单独运行每个脚本的其他人。

sqlCommands = sqlFile.split('GO;')
for command in sqlCommands:
    cursor.execute(command)