使用 pyodbc 时 SQL 查询失败，但在 SQL 中有效 [英] SQL query fails when using pyodbc, but works in SQL

问题描述

我在编写一个非常简单的脚本时遇到了一些问题.我只是想使用 Python pyodbc 模块创建一个新的 SQL Server 数据库.当我在 SQL Server 2012 中执行它时，我试图传递的sqlcommand"参数工作得很好，但它在这个 python 脚本中失败了.不知道出了什么问题，有人有任何想法吗?

导入pyodbc, osdef create_db(文件夹，db_name):unc = r'\\arcsql\SDE\{0}'.format(文件夹)如果不是 os.path.exists(unc):os.makedirs(unc)full_name = os.path.join(r'E:\SDE', 文件夹, db_name)conn = pyodbc.connect("driver={SQL Server}; server=ArcSQL; database=master; Trusted_Connection=yes", automcommit=True)游标 = conn.cursor()sqlcommand = """USE [大师]去在主数据库上创建数据库 [{0}]( NAME = N'{0}', FILENAME = N'{1}.mdf', SIZE = 4MB , MAXSIZE = 10MB, FILEGROWTH = 1MB )登录( NAME = N'{0}_log', FILENAME = N'{1}_log.ldf', SIZE = 4MB , MAXSIZE = 10MB, FILEGROWTH = 10%)去使用 [{0}]GO""".format(db_name, full_name)打印 sql 命令cursor.execute(sqlcommand)打印 '创建{0}"'.format(db_name)如果 __name__ == '__main__':#测试create_db('_test', 'py_db_test')

和错误:

<预><代码>>>>使用 [大师]去在 PRIMARY 上创建数据库 [py_db_test2]( NAME = N'py_db_test2', FILENAME = N'E:\SDE\_test\py_db_test2.mdf', SIZE = 4MB , MAXSIZE = 10MB, FILEGROWTH = 1MB )登录( NAME = N'py_db_test2_log', FILENAME = N'E:\SDE\_test\py_db_test2_log.ldf', SIZE = 4MB, MAXSIZE = 10MB, FILEGROWTH = 10%)去使用 [py_db_test2]去回溯(最近一次调用最后一次): 中的文件C:/Users/calebma/Desktop/create_sql_db.py"，第 40 行create_db('_test', 'py_db_test2')文件C:/Users/calebma/Desktop/create_sql_db.py"，第 35 行，在 create_db 中cursor.execute(sqlcommand)错误: ('08004', "[08004] [Microsoft][ODBC SQL Server 驱动程序][SQL Server]数据库 'py_db_test2' 不存在.确保名称输入正确.(911) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server 驱动程序][SQL Server]'GO' 附近的语法不正确.(102); [42000] [Microsoft][ODBC SQL Server 驱动程序][SQL Server]'GO'附近的语法不正确.(102))")>>>

解决方案

GO 是一个批处理终止符SQL Server 管理工作室.在 pyodbc 中没有任何意义.相反，从您的脚本发出单独的命令.

更新代码:

导入pyodbc, osdef create_db(文件夹，db_name):unc = r'\\arcsql\SDE\{0}'.format(文件夹)如果不是 os.path.exists(unc):os.makedirs(unc)full_name = os.path.join(r'E:\SDE', 文件夹, db_name)conn = pyodbc.connect("driver={SQL Server}; server=ArcSQL; database=master; Trusted_Connection=yes", automcommit=True)游标 = conn.cursor()sqlcommand = """在主数据库上创建数据库 [{0}]( NAME = N'{0}', FILENAME = N'{1}.mdf', SIZE = 4MB , MAXSIZE = 10MB, FILEGROWTH = 1MB )登录(NAME = N'{0}_log', FILENAME = N'{1}_log.ldf', SIZE = 4MB, MAXSIZE = 10MB, FILEGROWTH = 10%)""".format(db_name, full_name)打印 sql 命令cursor.execute(sqlcommand)打印 '创建{0}"'.format(db_name)# 在新数据库中做一些事情conn = pyodbc.connect("driver={SQL Server}; server=ArcSQL; database={0}; Trusted_Connection=yes".format(db_name), automcommit=True)如果 __name__ == '__main__':#测试create_db('_test', 'py_db_test')

I am having some trouble with what should be a very simple script. I am just trying to create a new SQL Server database using the Python pyodbc module. The "sqlcommand" parameter I am attempting to pass in works perfectly when I execute it in SQL Server 2012, but it is failing from this python script. Not sure what is going wrong, anyone have any ideas?

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')

and the errors:

>>> 
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)")
>>> 

解决方案

GO is a batch terminator in SQL Server Management Studio. It doesn't make any sense in pyodbc. Instead, issue separate commands from your script.

Updated Code:

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')

这篇关于使用 pyodbc 时 SQL 查询失败，但在 SQL 中有效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！