python - Insert Null value with pyodbc

Question

I have this code

import pyodbc 
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=RONSQLEXPRESS;'
                      '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()

That insert NULL value not realy NULL , I must put '{}' because if I want to set value in city variable it must be between '

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

So how can I handle that ? put NULL or value when the var is not NULL ?

question from:https://stackoverflow.com/questions/66049230/insert-null-value-with-pyodbc

Answer 1

Use parameters to the query, not string formatting

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