How to Properly Insert Python `datetime.datetime` Objects into MySQL Date Columns?

datetime.datetime Objects in MySQL: Insertion Considerations

When working with MySQL databases and Python, one may encounter a specific need: inserting a datetime.datetime object into a date column. This query aims to address the potential challenges and provide a solution.

Challenge:

When executing an insertion statement with a datetime.datetime object formatted as "%s," MySQL encounters an error due to the inability to convert the object during string formatting.

Solution:

Instead of the " %s " placeholder, the query should use the following string format for inserting a date or time field:

 import time
 time.strftime('%Y-%m-%d %H:%M:%S')

This function returns the current date or time in a string format.

Example:

import time

now = datetime.datetime.now()

cursor.execute("INSERT INTO table
(name, id, datecolumn) VALUES (%s, %s
, time.strftime('%Y-%m-%d %H:%M:%S'))", ("name", 4, now))

commit()

By leveraging this method, Python can effectively insert datetime.datetime objects into date columns within MySQL tables, ensuring compatibility and accurate data representation.

The above is the detailed content of How to Properly Insert Python `datetime.datetime` Objects into MySQL Date Columns?. For more information, please follow other related articles on the PHP Chinese website!