Inserting a Python datetime.datetime object into MySQL
PythonMysqlDatetimePython Problem Overview
I have a date column in a MySQL table. I want to insert a datetime.datetime()
object into this column. What should I be using in the execute statement?
I have tried:
now = datetime.datetime(2009,5,5)
cursor.execute("INSERT INTO table
(name, id, datecolumn) VALUES (%s, %s
, %s)",("name", 4,now))
I am getting an error as: "TypeError: not all arguments converted during string formatting"
What should I use instead of %s
?
Python Solutions
Solution 1 - Python
For a time field, use:
import time
time.strftime('%Y-%m-%d %H:%M:%S')
I think strftime
also applies to datetime
.
Solution 2 - Python
You are most likely getting the TypeError because you need quotes around the datecolumn value.
Try:
now = datetime.datetime(2009, 5, 5)
cursor.execute("INSERT INTO table (name, id, datecolumn) VALUES (%s, %s, '%s')",
("name", 4, now))
With regards to the format, I had success with the above command (which includes the milliseconds) and with:
now.strftime('%Y-%m-%d %H:%M:%S')
Hope this helps.
Solution 3 - Python
Try using now.date()
to get a Date
object rather than a DateTime
.
If that doesn't work, then converting that to a string should work:
now = datetime.datetime(2009,5,5)
str_now = now.date().isoformat()
cursor.execute('INSERT INTO table (name, id, datecolumn) VALUES (%s,%s,%s)', ('name',4,str_now))
Solution 4 - Python
Use Python method datetime.strftime(format)
, where format = '%Y-%m-%d %H:%M:%S'
.
import datetime
now = datetime.datetime.utcnow()
cursor.execute("INSERT INTO table (name, id, datecolumn) VALUES (%s, %s, %s)",
("name", 4, now.strftime('%Y-%m-%d %H:%M:%S')))
Timezones
If timezones are a concern, the MySQL timezone can be set for UTC as follows:
cursor.execute("SET time_zone = '+00:00'")
And the timezone can be set in Python:
now = datetime.datetime.utcnow().replace(tzinfo=datetime.timezone.utc)
MySQL Documentation
> MySQL recognizes DATETIME and TIMESTAMP values in these formats: > > As a string in either 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS' > format. A “relaxed” syntax is permitted here, too: Any punctuation > character may be used as the delimiter between date parts or time > parts. For example, '2012-12-31 11:30:45', '2012^12^31 11+30+45', > '2012/12/31 113045', and '2012@12@31 11^30^45' are equivalent. > > The only delimiter recognized between a date and time part and a > fractional seconds part is the decimal point. > > The date and time parts can be separated by T rather than a space. For > example, '2012-12-31 11:30:45' '2012-12-31T11:30:45' are equivalent. > > As a string with no delimiters in either 'YYYYMMDDHHMMSS' or > 'YYMMDDHHMMSS' format, provided that the string makes sense as a date. > For example, '20070523091528' and '070523091528' are interpreted as > '2007-05-23 09:15:28', but '071122129015' is illegal (it has a > nonsensical minute part) and becomes '0000-00-00 00:00:00'. > > As a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS format, provided > that the number makes sense as a date. For example, 19830905132800 and > 830905132800 are interpreted as '1983-09-05 13:28:00'.
Solution 5 - Python
What database are you connecting to? I know Oracle can be picky about date formats and likes ISO 8601 format.
**Note: Oops, I just read you are on MySQL. Just format the date and try it as a separate direct SQL call to test.
In Python, you can get an ISO date like
now.isoformat()
For instance, Oracle likes dates like
insert into x values(99, '31-may-09');
Depending on your database, if it is Oracle you might need to TO_DATE it:
insert into x
values(99, to_date('2009/05/31:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'));
The general usage of TO_DATE is:
TO_DATE(<string>, '<format>')
If using another database (I saw the cursor and thought Oracle; I could be wrong) then check their date format tools. For MySQL it is DATE_FORMAT() and SQL Server it is CONVERT.
Also using a tool like SQLAlchemy will remove differences like these and make your life easy.
Solution 6 - Python
If you're just using a python datetime.date (not a full datetime.datetime), just cast the date as a string. This is very simple and works for me (mysql, python 2.7, Ubuntu). The column published_date
is a MySQL date field, the python variable publish_date
is datetime.date
.
# make the record for the passed link info
sql_stmt = "INSERT INTO snippet_links (" + \
"link_headline, link_url, published_date, author, source, coco_id, link_id)" + \
"VALUES(%s, %s, %s, %s, %s, %s, %s) ;"
sql_data = ( title, link, str(publish_date), \
author, posted_by, \
str(coco_id), str(link_id) )
try:
dbc.execute(sql_stmt, sql_data )
except Exception, e:
...
Solution 7 - Python
dt= datetime.now()
query = """INSERT INTO table1(python_Date_col)
VALUES (%s)
"""
conn = ...... # Connection creating process
cur = conn.cursor()
cur.execute(query,(dt))
Above code will fail as "datetime.now()" produces "datetime.datetime(2014, 2, 11, 1, 16)" as a parameter value to insert statement.
Use the following method to capture the datetime which gives string value.
dt= datetime.now().strftime("%Y%m%d%H%M%S")
I was able to successfully run the code after the change...
Solution 8 - Python
when iserting into t-sql
this fails:
select CONVERT(datetime,'2019-09-13 09:04:35.823312',21)
this works:
select CONVERT(datetime,'2019-09-13 09:04:35.823',21)
easy way:
regexp = re.compile(r'\.(\d{6})')
def to_splunk_iso(dt):
"""Converts the datetime object to Splunk isoformat string."""
# 6-digits string.
microseconds = regexp.search(dt).group(1)
return regexp.sub('.%d' % round(float(microseconds) / 1000), dt)