pythonmysqlmysql-pythonpymysqlstr-to-date

Issue with str_to_date() function with Empty field


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

Solution

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