How can I insert NULL data into MySQL database with Python?

Question

I'm getting a weird error when inserting some data from a Python script to MySQL. It's basically related to a variable being blank that I am inserting. I take it that MySQL does not like blank variables but is there something else I can change it to so it works with my insert statement?

I can successfully use an IF statement to turn it to 0 if its blank but this may mess up some of the data analytics I plan to do in MySQL later. Is there a way to convert it to NULL or something so MySQL accepts it but doesn't add anything?

Answer 1

When using mysqldb and cursor.execute(), pass the value None, not "NULL":

value = None cursor.execute("INSERT INTO table (`column1`) VALUES (%s)", (value,)) 

Found the answer here

Answer 2

if the col1 is char, col2 is int, a trick could be:

insert into table (col1, col2) values (%s, %s) % ("'{}'".format(val1) if val1 else "NULL", val2 if val2 else "NULL"); 

you do not need to add ' ' to %s, it could be processed before pass value to sql.

this method works when execute sql with session of sqlalchemy, for example session.execute(text(sql))

ps: sql is not tested yet