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

56
votes

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?

4
pythonmysql
Does the column you're attempting to insert this NULL value into allow for NULLs? If not, what exactly is the error? - Philip Southam
yes it does allow for null. I see if I don't insert anything, it says the column is null the problem is my sql statements are constructed already. Basically I'm pulling a bunch of prices from a file, and sometimes the price is blank, it means no price is avail. I still want the details inserted but not the price. - Lostsoul
my first idea was to turn them all to zero, but it'll mess me up later on when I do some analytics and find free products shifting the average price of the products, etc.. - Lostsoul
What EXACTLY is the "weird error" that you are getting? What does "blank" mean? Do print repr(socalled_blank_value) and show us the answer. What is the SQL type of the corresponding column? - John Machin
sorry about that John. The error is "Data truncated for column 'price' at row 1 [SQLCode: 1265], [SQLState: 01000]" when I print the value of the variable its totally blank..i don't think there's a space in even because if i put "if variable == "":" that actually matches the value of this variable. - Lostsoul

4 Answers

-26
votes

Do a quick check for blank, and if it is, set it equal to NULL:

if(!variable_to_insert)
    variable_to_insert = "NULL"

...then make sure that the inserted variable is not in quotes for the insert statement, like: 

insert = "INSERT INTO table (var) VALUES (%s)" % (variable_to_insert)
...

not like:

insert = "INSERT INTO table (var) VALUES ('%s')" % (variable_to_insert)
...
188
votes

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

1
votes

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

-3
votes

Why not set the variable equal to some string like 'no price' and then filter this out later when you want to do math on the numbers?

filter(lambda x: x != 'no price',list_of_data_from_database)