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?
asked Jul 16, 2009 at 9:29
1
For a time field, use:
import time
time.strftime['%Y-%m-%d %H:%M:%S']
I think strftime also applies to datetime.
FObersteiner
18.2k5 gold badges29 silver badges57 bronze badges
answered Dec 22, 2010 at 11:56
7
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.
Martin Thoma
113k148 gold badges570 silver badges876 bronze badges
answered Aug 30, 2012 at 6:59
EdvinasEdvinas
6956 silver badges8 bronze badges
2
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]]
answered Jul 16, 2009 at 9:39
WoganWogan
66.1k5 gold badges33 silver badges35 bronze badges
1
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 11*30*45', 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'.
answered Apr 14, 2019 at 18:00
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[, '']
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.
answered Jul 16, 2009 at 15:08
Ryan ChristensenRyan Christensen
7,7851 gold badge26 silver badges25 bronze badges
1
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:
...
Phillip
2,1553 gold badges25 silver badges40 bronze badges
answered May 16, 2017 at 0:08
jbartasjbartas
3082 silver badges6 bronze badges
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...
answered Dec 22, 2021 at 2:18
SunilSunil
563 bronze badges
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]
ivcubr
1,7789 gold badges19 silver badges25 bronze badges
answered Oct 4, 2019 at 10:54