将Python datetime.datetime对象插入MySQL

问题:将Python datetime.datetime对象插入MySQL

我在MySQL表中有一个日期列。我想在datetime.datetime()此列中插入一个对象。我应该在execute语句中使用什么?

我努力了:

now = datetime.datetime(2009,5,5)

cursor.execute("INSERT INTO table
(name, id, datecolumn) VALUES (%s, %s
, %s)",("name", 4,now))

我收到以下错误消息:"TypeError: not all arguments converted during string formatting" 应该用什么代替%s

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?


回答 0

对于时间字段,请使用:

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

我认为strftime也适用于日期时间。

For a time field, use:

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

I think strftime also applies to datetime.


回答 1

您最有可能收到TypeError,因为您需要在datecolumn值前后加上引号。

尝试:

now = datetime.datetime(2009, 5, 5)

cursor.execute("INSERT INTO table (name, id, datecolumn) VALUES (%s, %s, '%s')",
               ("name", 4, now))

关于格式,我成功使用了上面的命令(包括毫秒)和以下命令:

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

希望这可以帮助。

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.


回答 2

尝试使用now.date()获取Date对象而不是获取对象DateTime

如果那不起作用,那么将其转换为字符串应该起作用:

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

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

回答 3

使用Python方法datetime.strftime(format),其中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')))

时区

如果需要考虑时区,则可以按如下所示为UTC设置MySQL时区:

cursor.execute("SET time_zone = '+00:00'")

时区可以在Python中设置:

now = datetime.datetime.utcnow().replace(tzinfo=datetime.timezone.utc)

MySQL文档

MySQL可以使用以下格式识别DATETIME和TIMESTAMP值:

作为任一字符串 ‘YYYY-MM-DD HH:MM:SS’‘YY-MM-DD HH:MM:SS’ 格式。这里也允许使用“宽松”语法:任何标点符号都可以用作日期部分或时间部分之间的分隔符。例如,“ 2012-12-31 11:30:45”,“ 2012 ^ 12 ^ 31 11 + 30 + 45”,“ 2012/12/31 11 * 30 * 45”和“ 2012 @ 12 @ 31 11” ^ 30 ^ 45’是等效的。

在日期和时间部分与小数秒部分之间唯一识别的分隔符是小数点。

日期和时间部分可以用T分隔而不是空格。例如,“ 2012-12-31 11:30:45”与“ 2012-12-31T11:30:45”等效。

如果字符串不带分隔符,则格式为“ YYYYMMDDHHMMSS”或“ YYMMDDHHMMSS”,但前提是该字符串应作为日期使用。例如,“ 20070523091528”和“ 070523091528”被解释为“ 2007-05-23 09:15:28”,但“ 071122129015”是非法的(具有无意义的分钟部分),并变为“ 0000-00-00 00”: 00:00’。

如果数字是日期,则以YYYYMMDDHHMMSS或YYMMDDHHMMSS格式表示。例如,将19830905132800和830905132800解释为“ 1983-09-05 13:28:00”。

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’.


回答 4

您要连接到哪个数据库?我知道Oracle对日期格式可能很挑剔,并且喜欢ISO 8601格式。

**注意:糟糕,我刚读过您在MySQL上。只需格式化日期并尝试将其作为单独的直接SQL调用进行测试即可。

在Python中,您可以获得一个ISO日期,例如

now.isoformat()

例如,Oracle喜欢日期,例如

insert into x values(99, '31-may-09');

根据您的数据库,如果是Oracle,则可能需要TO_DATE它:

insert into x
values(99, to_date('2009/05/31:12:00:00AM', 'yyyy/mm/dd:hh:mi:ssam'));

TO_DATE的一般用法是:

TO_DATE(<string>, '<format>')

如果使用另一个数据库(我看到了光标并认为是Oracle;可能是错误的),请检查其日期格式工具。对于MySQL,它是DATE_FORMAT(),对于SQL Server,它是CONVERT。

另外,使用SQLAlchemy之类的工具将消除此类差异,并使您的生活变得轻松。

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.


回答 5

如果您仅使用python datetime.date(而不是完整的datetime.datetime),则将日期转换为字符串。这非常简单,对我有用(mysql,python 2.7,Ubuntu)。该列published_date是一个MySQL日期字段,python变量publish_datedatetime.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:
    ...

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:
    ...

回答 6

当烦恼到T-SQL

这失败了:

select CONVERT(datetime,'2019-09-13 09:04:35.823312',21)

这有效:

select CONVERT(datetime,'2019-09-13 09:04:35.823',21)

简单的方法:

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)

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)