CODEWITHSUNDEEP
pythonmysql
Lostsoul
Lostsoul

Reputation: 26067

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

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?

Upvotes: 72

Views: 166293

Answers (8)

mwolfe 11
mwolfe 11

Reputation: 50

In addition to the other answers, the mycursor.execute() line must be joined with a , like so: mycursor.execute(queryString, tupleOfValues)

Joining the arguments with a % for string formatting (mycursor.execute(queryString % tupleOfValues)) will turn the None into a string, and it will be evaluated by MySQL as a column title, resulting in *** mysql.connector.errors.ProgrammingError: 1054 (42S22): Unknown column 'None' in 'field list'.

Upvotes: 0

Akarsh Saransi
Akarsh Saransi

Reputation: 1

I had faced this same issue to make numeric and dates fields null. we can make varchar fields null using 'NULL' as string in python but its a wrong method. Solution for this is to use PyMySql library and importing NULL keyword it will set NULL to any datatype field.

Upvotes: 0

Yash Sharma
Yash Sharma

Reputation: 181

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

Upvotes: -1

Ken Stulce
Ken Stulce

Reputation: 23

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

Upvotes: 1

buxizhizhoum
buxizhizhoum

Reputation: 1929

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

Upvotes: 0

j c
j c

Reputation: 2139

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

Upvotes: 211

Chris
Chris

Reputation: 435

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)

Upvotes: -3

phaxian
phaxian

Reputation: 1078

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

Upvotes: -26

Related Questions