Inserting a Python Datetime.Datetime Object into MySQL

Inserting datetime into MySql db

You are now passing in a time.struct_time object, something MySQL knows nothing about. You'll need to format the timestamp to a format MySQL understands. Unfortunately the MySQLdb library doesn't do this for you.

It'll be easiest using the datetime module, but you can do this with the time module too:

import datetime

a = datetime.datetime.strptime('my date', "%b %d %Y %H:%M")

cursor.execute('INSERT INTO myTable (Date) VALUES(%s)', (a.strftime('%Y-%m-%d %H:%M:%S'),))

The .strftime() method call on the datetime.datetime object formats the information in such a way that MySQL will accept.

Doing the same task with just the time module:

import time

a = time.strptime('my date', "%b %d %Y %H:%M")

cursor.execute('INSERT INTO myTable (Date) VALUES(%s)', (time.strftime('%Y-%m-%d %H:%M:%S', a),))

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

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.

Insert datetime.datetime object in MySQL

Try to insert a space after your table name and to put your text inside "", like this:

cursor.execute('INSERT INTO tweets (created_at) VALUES ("{created_at}")'.format(created_at=t)) 

Issue with python inserting record containing datetime object into mysql db

you need to commit after execute

# Make sure data is committed to the database
 conn.commit()

 cur.close()
 conn.close()

How to neglect timestamps when inserting date to MySQL database

Obviously the column Date has data type DATETIME or TIMESTAMP.

If you don't need the time part of the column, you should change the data type to DATE:

ALTER TABLE dates MODIFY Date DATE;

Insert date object from python to mysql

I believe you have to format your date in YYYY-MM-DD format.

---

Related Topics