使用pyodbc插入空值

Question

我有这个密码

import pyodbc 
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=RON\SQLEXPRESS;'
                      'Database=TestDB;'
                      'Trusted_Connection=yes;')

cursor = conn.cursor()
cursor.execute('SELECT * FROM TestDB.dbo.Person')
city = 'NULL'
cursor.execute(" INSERT INTO TestDB.dbo.Person (Name, Age, City) VALUES ('Bob',55,'{}')".format(city))
conn.commit()

插入NULL值不是真正的NULL，我必须把'{}'放进去，因为如果我想在city变量中设置值，它必须在'之间

city = 'NEW YORK'
cursor.execute(" INSERT INTO TestDB.dbo.Person (Name, Age, City) VALUES ('Bob',55,'{}')".format(city))

那我怎么处理呢？当变量不为NULL时，是否将其设置为NULL或value

Answer 1

对查询使用参数，而不是字符串格式

city = None
cursor.execute(" INSERT INTO TestDB.dbo.Person (Name, Age, City) VALUES ('Bob',55,?)", (city, ))
conn.commit()

Answer 2

您可以使用参数

Name, Age, City = 'Bob', 55, None
cursor.execute("INSERT INTO TestDB.dbo.Person (Name, Age, City) VALUES (?, ?, ?)", (Name, Age, City))