Issue with str_to_date() function with Empty field

Question

In python, I am trying to insert some data into my table;

   try:
        cursor.execute("INSERT INTO covid_testtable (pid, age, state, city, notes, backnotes, type, nationality, status, announced, changed) " +
                "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, STR_TO_DATE(%s, '%%d/%%m/%%Y'), STR_TO_DATE(%s, '%%d/%%m/%%Y'))",
            (pid, age, item["detectedstate"], item["detectedcity"], item["notes"], item["backupnotes"], item["typeoftransmission"],
                item["nationality"], item["currentstatus"], dateannounced, statuschanged));
    except ValueError as verr:
        print(item["dateannounced"], verr);
        break;

This works till a point where one of the date columns have a empty entry.

pymysql.err.InternalError: (1411, "Incorrect datetime value: '' for function str_to_date"

I tried to change the value of the empty date to "00/00/0000" however it seems the issue is with the str_to_date() function. I am fine with inserting NULL value however, not managing to insert.

The valid values which are succesfully inserted are of this format "30/01/2020" -> "%d/%m/%Y"

My table schema is as below:

create table covid_testtable ( pid int NOT NULL, age int, state VARCHAR(255), 
city VARCHAR(255), notes VARCHAR(255), backnotes VARCHAR(255), type VARCHAR(255), 
nationality VARCHAR(255), status VARCHAR(255), announced DATE default NULL, 
changed DATE default NULL,  PRIMARY KEY (pid));

EDIT 2: Selected Answer solved this issue:

def validate_date(val):
    try:
        return datetime.strptime(val, '%d/%m/%Y').strftime('%Y-%m-%d');
    except ValueError as verr:
        return None;

    dateannounced = validate_date(item["dateannounced"]);
    statuschanged = validate_date(item["statuschangedate"]);
    try:
        cursor.execute("INSERT INTO covid_testtable (pid, age, state, city, notes, backnotes, type, nationality, status, announced, changed) " +
                "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
            (pid, age, item["detectedstate"], item["detectedcity"],
                item["notes"], item["backupnotes"], item["typeoftransmission"],
                item["nationality"], item["currentstatus"], dateannounced,
                statuschanged));

Barmar · Answer 1 · 2020-04-18T17:11:01.080

1

Check whether the string is empty with if().

cursor.execute("""
    INSERT INTO covid_testtable (pid, age, state, city, notes, backnotes, type, nationality, status, announced, changed)
    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, 
            IF(%s = '', NULL, STR_TO_DATE(%s, '%%d/%%m/%%Y')), 
            IF(%s = '', NULL, STR_TO_DATE(%s, '%%d/%%m/%%Y')))""",
    (pid, age, item["detectedstate"], item["detectedcity"], item["notes"], item["backupnotes"], item["typeoftransmission"],
        item["nationality"], item["currentstatus"], dateannounced, dateannounced, statuschanged, statuschanged));

Note that I had to repeat dateannounced and statuschanged in the parameter list, since I'm substituting them into the IF functions in addition to the STR_TO_DATE functions.

edited Apr 18 '20 at 17:11

answered Apr 17 '20 at 06:22

Barmar

741,623
53
500
612

When you tried to copy my answer, you forgot to put `dateannounced` and `statuschanged` twice in the parameter list. – Barmar Apr 17 '20 at 18:06
Oh, I just took the IF statements from your answer, My bad. Will update the edit. – RootPhoenix Apr 18 '20 at 15:52
I take partial blame, I should have explained what I was doing more fully. – Barmar Apr 18 '20 at 17:11

score 1 · Accepted Answer · answered Apr 17 '20 at 06:30

Rather than processing the dates in the query, you could pre-process them in your python code. For example:

from datetime import datetime

dstr = ''
try:
    dateannounced = datetime.strptime(dstr, '%d/%m/%Y').strftime('%Y-%m-%d')
except ValueError:
    dateannounced = None

Then your query simply becomes

cursor.execute("INSERT INTO covid_testtable (pid, age, state, city, notes, backnotes, type, nationality, status, announced, changed) " +
               "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
              (pid, age, item["detectedstate"], item["detectedcity"], 
               item["notes"], item["backupnotes"], item["typeoftransmission"],
               item["nationality"], item["currentstatus"], dateannounced, 
               statuschanged));

Issue with str_to_date() function with Empty field

2 Answers2