CODEWITHSUNDEEP
mysqlpython-3.xstring-formatting
Vanjith
Vanjith

Reputation: 540

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

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.

Upvotes: 1

Views: 4181

Answers (2)

Ajay Tom George
Ajay Tom George

Reputation: 2279

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

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

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.

Upvotes: 5

Related Questions