创建数据库(如果已经存在定制名称)

Question

conn = pyodbc.connect("DRIVER={SQL Server};"
    "SERVER="+server+";"
    "UID="+username+";"
    "PWD="+password,
    autocommit=True)
cursor = conn.cursor()
database= "abcd"
sql_create = (
        "DECLARE @sql AS NVARCHAR(MAX);"
        "SET @sql = 'if not exists(select * from sys.databases where name = ' + QUOTENAME(?) + ')'  + ' CREATE DATABASE ' + QUOTENAME(?);"
        "EXEC sp_executesql @sql")
cursor.execute(sql_create,database,database)  

获取错误msg，例如pyodbc.programmingerror ：（'42S22'，u" [42S22] [Microsoft] [Microsoft] [SQL Server for SQL Server] [SQL Server] [SQL Server] [SQL Server]无效列列名称'abcd'。）"

Answer 1

不要将QUOTENAME和串联用于WHERE子句参数。另外，避免使用带有Windows的传统SQL Server ODBC驱动程序访问Azure SQL数据库。而是下载并使用较新的ODBC驱动程序。以下是这些更改的示例。

conn = pyodbc.connect("DRIVER={ODBC Driver 17 for SQL Server};"
    "SERVER="+server+";"
    "UID="+username+";"
    "PWD="+password,
    autocommit=True)
cursor = conn.cursor()
database= "abcd"
sql_create = (
        "DECLARE @sql AS NVARCHAR(MAX);"
        "SET @sql = N'if not exists(select * from sys.databases where name = @DatabaseName)'  + N' CREATE DATABASE ' + QUOTENAME(?) + N';';"
        "EXEC sp_executesql @sql, N'@DatabaseName sysname', @DatabaseName = ?;")
cursor.execute(sql_create,database,database)  

您还可以声明数据库名称的T-SQL变量并将其分配给参数值，以便您只需要传递一个参数：

sql_create = (
        "DECLARE @sql AS NVARCHAR(MAX);"
        "DECLARE @DatabaseName sysname = ?;"
        "SET @sql = N'if not exists(select * from sys.databases where name = @DatabaseName)'  + N' CREATE DATABASE ' + QUOTENAME(@DatabaseName) + N';';"
        "EXEC sp_executesql @sql, N'@DatabaseName sysname', @DatabaseName = @DatabaseName;")
cursor.execute(sql_create,database)