How to insert value into DATE column in Mysql table from Python 3

Question

I want to update a DATE type column called file_date in mysql table from python code. I have a string date like this a='2020-02-20'. When I tried in multiple ways, always I'm getting similar to this below error,

 mysql.connector.errors.DatabaseError: 1411 (HY000): Incorrect datetime value: '1998' for function str_to_date

I don't know from where this 1998 coming from. My sample code is given below

import mysql.connector
a='2020-02-20'
insert_qry="insert into table_name(file_date) values(STR_TO_DATE({},'%Y-%m-%d'))".format(a)
#assume I have a db_cursor created to connect mysql server
db_cursor.execute(insert_qry)

Point me where it went wrong. Thanks in advance.

Tim Biegeleisen · Accepted Answer · 2020-03-19 07:22:52Z

5

One option is to form a Python datetime at the date you want, then bind it to a %s placeholder in your prepared statement:

import mysql.connector
a = datetime.datetime(2020, 2, 20)
insert_qry = "INSERT INTO table_name (file_date) VALUES (%s)"
db_cursor.execute(insert_qry, (a,))

Note that the following approach should also work:

a = '2020-02-20'
insert_qry = "INSERT INTO table_name (file_date) VALUES (%s)"
db_cursor.execute(insert_qry, (a,))

This should also work because the string literal '2020-02-20' is also a valid date literal in MySQL. But, it is best to use a date object in Python and bind that to a placeholder, letting the prepared statement API worry about conversions.

answered Mar 19, 2020 at 7:22

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vanjith Over a year ago

Thanks for the answer. Your second option works, but What I'm looking for is string formatting. I can pass tuple to execute method thats ok fine. But formatting a string for insert query with date column filled is not possible in python it seems then.

Tim Biegeleisen Over a year ago

You should not be using format in this way; it is open to SQL injection as far as I know.

Ajay Tom George · Accepted Answer · 2020-03-19 07:37:40Z

1

You should not insert date value like this, first store the value to a python datetime variable and then change it the format required and push it

from datetime import datetime
a='2020-02-20'
x= datetime.datetime.strptime(a, '%Y-%m-%d')
formatted_date = x.strftime('%Y-%m-%d %H:%M:%S')
cursor.execute('insert into table(file_date) values(%s)', (formatted_date))

answered Mar 19, 2020 at 7:37

Ajay Tom George

2,3672 gold badges19 silver badges29 bronze badges

Collectives™ on Stack Overflow

How to insert value into DATE column in Mysql table from Python 3

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related