Error in SQL on date caused by value not being inserted

Question

I am inserting a row into the Issues table with the function below. The table is currently entirely empty.

 def insertIssue(self, choice, doc_id):
        date = datetime.today() + timedelta(days=14)
        returndate = str(date.strftime('%Y-%m-%d'))
        issuedate = str(datetime.today().strftime('%Y-%m-%d'))
        sql = dbcfg.sql['insertIssues'].replace('{_ttl}','"' + choice[2] + '"').replace('{_date}',issuedate).replace(
            '{_due}', returndate).replace("{_docid}", str(doc_id))
        logger.info("Issues SQL Insert: " + sql)
        try:
            mycursor.execute(sql)
            mycursor.fetchall()

        except Exception as e:
            logger.error("Error in Issues SQL: " + str(e) + traceback.format_exc())
            sys.exit(-1)

For the above,the variables are as follows:

choice = (3, 3, 'Return of the King', '2', 'Lord, rings, king, return', 'High Fantasy', 'JRR Tolkein', datetime.date(2000, 5, 20))
date = {datetime} 2023-02-08 23:59:50.303823
doc_id = {int} 3
issuedate = {str} '2023-01-25'
returndate = {str} '2023-02-08'
sql = 'INSERT INTO Librarian.Issues(Title, Date_Issued, Date_Due, Doc_id) VALUES("Return of the King",2023-01-25,2023-02-08, 3);'

(via debugger)

The error message I get is :

Traceback (most recent call last):
  File "C:\Users\admin\Desktop\Projects\IntegratedLibrarySystem\app.py", line 276, in insertIssue
    mycursor.execute(sql)
  File "C:\Users\admin\AppData\Local\Programs\Python\Python311\Lib\site-packages\mysql\connector\cursor_cext.py", line 279, in execute
    result = self._cnx.cmd_query(
             ^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\admin\AppData\Local\Programs\Python\Python311\Lib\site-packages\mysql\connector\connection_cext.py", line 573, in cmd_query
    raise get_mysql_exception(
mysql.connector.errors.DataError: 1292 (22007): Incorrect date value: '1997' for column 'Date_Issued' at row 1

The Incorrect date value for 1997 confused me, since the table is currently empty and the value being inserted does not have the 1997 date in it. The query held in the variable sql has the right syntax too.

Thanks for the help!

Answer 1

From your question I can see that, dates are strings.

issuedate = {str} '2023-01-25'
returndate = {str} '2023-02-08'

The dates should have single quotes (') or double quotes (") if you are storing those as Varchar (Strings) in Database.

' (Single Quote) OR " (Double Quote) or are used for enclosing string-like values "26-01-2023 00:00:00" or '26-01-2023 00:00:00' in mysql But single quotes are more widely accepted by other RDBMS.

INSERT INTO Librarian.Issues(Title, Date_Issued, Date_Due, Doc_id) VALUES('Return of the King','2023-01-25','2023-02-08', 3);