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

Quick note about using parameters in SQL statements with Python. See the RealPython article on this topic - Preventing SQL Injection Attacks With Python. Here's another good article from TowardsDataScience.com - A Simple Approach To Templated SQL Queries In Python. These helped me with same None/NULL issue.

Also, I found that if I put "NULL" (without quotes) directly into the INSERT query in VALUES, it was interpreted appropriately in the SQL Server DB. The translation problem only exists if needing to conditionally add NULL or a value via string interpolation.

Examples:

cursor.execute("SELECT admin FROM users WHERE username = %s'", (username, ));

cursor.execute("SELECT admin FROM users WHERE username = %(username)s", {'username': username});

UPDATE: This StackOverflow discussion is more in line with what I'm trying to do and may help someone else.

Example:

import pypyodbc
myData = [
    (1, 'foo'),
    (2, None),
    (3, 'bar'),
]
connStr = """
DSN=myDb_SQLEXPRESS;
"""
cnxn = pypyodbc.connect(connStr)
crsr = cnxn.cursor()
sql = """
INSERT INTO myTable VALUES (?, ?)
"""
for dataRow in myData:
    print(dataRow)
    crsr.execute(sql, dataRow)
cnxn.commit()
crsr.close()
cnxn.close()

Answer 3

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

Answer 4

Based on above answers I wrote a wrapper function for my use case, you can try and change the function according to your need.

def sanitizeData(value):
    if value in ('', None):
        return "NULL"

    # This case handles the case where value already has ' in it (ex: O'Brien). This is how SQL skils single quotes
    if type(value) is str: 
        return "'{}'".format(value.replace("'", "''"))

    return value

Now call the sql query like so,

"INSERT INTO %s (Name, Email) VALUES (%s, %s)"%(table_name, sanitizeData(actual_name), sanitizeData(actual_email))

Answer 5

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)

Answer 6

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)
...