How to handle null values while inserting to mysql with python

Question

I have a dictionary where some of the values might be None. How to insert to mysql data base. I have tried with below,

dict = {'column1': 'test', 'column2': 'test subject', 'Address': None}
query = """insert into tablename (column1,column2,column3) values ('%s', '%s', '%s')""" % (dict['column1'], dict['column1'], dict['column3'])
db_con.execute(query)

But, it's converting None to string and updating in DB. But, I want that field to be null in DB. How to handle to string and None because the values in dictionary either string or None.

Basically, i want to handle both below dicts,

dict = {'column1': 'test', 'column2': 'test subject', 'Address': 'test'}
dict = {'column1': 'test', 'column2': 'test subject', 'Address': None}

Answer 1

Just use prepared statements and you will solve it and you will avoid SQL injections too:

dict = {'column1': 'test', 'column2': 'test subject', 'Address': None} 
query = """insert into tablename (column1, column2, column3) values (%s, %s, %s)""" 
db_con.execute(query, (dict['column1'], dict['column2'], dict['Address']))

EDIT Sorry I didn't see the ' I have modified the code.

Also, in your dict the name of the elements are column1, column2 and Address and in your query are column1, column2, column3 so you will have to modify one of them

• Gokul nath: Getting TypeError: not enough arguments for format string

Answer 2

Remove single quotes surrounding %s. None should translate to native SQL NULL.

dict = {'column1': 'test', 'column2': 'test subject', 'Address': None}
query = """
 INSERT INTO tablename (column1, column2, column3)
 VALUES (%s, %s, %s)
"""
db_con.execute(query, (dict['column1'], dict['column1'], dict['Address']))