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}

marc_s · Accepted Answer · 2020-10-25 20:21:28Z

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

edited Oct 25, 2020 at 20:21

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Oct 23, 2020 at 6:33

nacho

5,3772 gold badges28 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gokul nath Over a year ago

Getting TypeError: not enough arguments for format string

Takman · Accepted Answer · 2020-10-23 10:04:03Z

1

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

answered Oct 23, 2020 at 10:04

Takman

1,08610 silver badges13 bronze badges

Collectives™ on Stack Overflow

How to handle null values while inserting to mysql with python

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related