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

Question

我在应该是一个非常简单的脚本方面遇到了麻烦。我只是想使用Python pyodbc模块创建一个新的SQL Server数据库。当我在SQL Server2012中执行该参数时，我尝试传递的“ sqlcommand”参数完美运行，但是此python脚本失败了。不知道出了什么问题，有人有什么想法吗？

import pyodbc, os

def create_db(folder, db_name):
    unc = r'\\arcsql\SDE\{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\_test\py_db_test2.mdf', SIZE = 4MB , MAXSIZE = 10MB, FILEGROWTH = 1MB )
LOG ON
( NAME = N'py_db_test2_log', FILENAME = N'E:\SDE\_test\py_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 ManagementStudio中的批处理终止符。在中没有任何意义pyodbc。而是从脚本中发出单独的命令。

更新的代码：

import pyodbc, os

def create_db(folder, db_name):
    unc = r'\\arcsql\SDE\{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')