inserting null dates in MySql with pymysql

Question

I am trying to insert dates into a MySql database using pymysql and parameters. Some of the rows have a date field but for some others that particular date field is missing. The empty rows give an error of the type "pymysql.err.InternalError: (1292, "Incorrect date value:". Below is a piece of code that reproduces the error:

import pymysql
db=pymysql.connect("localhost","testuser","test1234","TESTDB")
cursor = db.cursor()
cursor.execute("SELECT VERSION()")
data = cursor.fetchone()
print ("Database version : %s " % data)

query = "DROP TABLE IF EXISTS birthdays"
cursor.execute(query)

query="CREATE TABLE birthdays(id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,\
name VARCHAR(20),\
birthday DATE NULL DEFAULT NULL)"
cursor.execute(query)
db.commit()

birthdays={'David':'2014-05-22','Larry':'014-05-22', 'Barry':''}

for k,v in birthdays.items():
    print(k,v)
    query="INSERT INTO birthdays (name,birthday) VALUES ('%s','%s')"%(k,v)
    cursor.execute(query)
    db.commit()

db.close()

The problem is with Barry and its empty date. I have tried setting Barry's date to None but it is not working. If I set it up to "NULL" and remove the quotes from the date parameter (('%s',%s) instead of ('%s','%s')) it works for Barry but note for the others.

thank you very much in advance,

Gabriel Vidal

score 0 · Answer 1 · answered Sep 14 '18 at 10:25

0

You can use below code i have just change in your array and set 'Barry':None because When using mysqldb and cursor.execute(), pass the value None:

birthdays={'David':'2014-05-22','Larry':'014-05-22', 'Barry':None}

for k,v in birthdays.items():
    print(k,v)
    query="INSERT INTO birthdays (name,birthday) VALUES ('%s','%s')"%(k,v)
    cursor.execute(query)
    db.commit()

db.close()

For further detail here

answered Sep 14 '18 at 10:25

Yagnesh Makwana

301
2
11

1

Hi, thank you, however it didn't work for me. What has worked is passing the values as a tuple as arguments in cursor.execute(query,args) – user1862963 Sep 14 '18 at 10:33
Hello @user1862963 Can you Please try: cursor.execute("INSERT INTO birthdays (name,birthday) VALUES ('%s','%s')", (k,v)) – Yagnesh Makwana Sep 14 '18 at 10:38
Hi, yes, that's what I meant in my first comment, however you have to remove the quotes from the types or values, like this: cursor.execute("INSERT INTO birthdays (name,birthday) VALUES (%s,%s)", (k,v)). Thanks – user1862963 Sep 14 '18 at 11:15

score 0 · Answer 2 · answered Nov 29 '19 at 22:50

In case someone drops by:

The following code snippet solves the problem you had. Note that you should keep your execute statements outside of the for loop to minimize the number of connections you do with the database.

birthdays = {'David':'2014-05-22','Larry':'2014-05-22', 'Barry':'NULL'}
values = ""
for k,v in birthdays.items():
    values += "('%s', '%s')," % (k,v)

values = values[:-1].replace("'NULL'", "NULL")  # Drop the final comma and the quotes of NULL values
query = f"INSERT INTO birthdays (name,birthday) VALUES {values}"  # >= 3.6
cursor.execute(query)  # Interaction with the database occurs only once
db.commit()

This generates the following statement:

INSERT INTO birthdays (name,birthday) VALUES ('David', '2014-05-22'),('Larry', '2014-05-22'),('Barry', NULL)

which is a valid sql statement

inserting null dates in MySql with pymysql

2 Answers2