blocks|key|3829420|text|对于时间字段，请使用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3829421|import+time++++
time.strftime('%25Y-%25m-%25d+%25H:%25M:%25S')|code-block|syntax|javascript|3829422|我认为strftime也适用于datetime。|offset|length|style|CODE|3829423|entityMap^0|0|0|3|8|F|8|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]|$I|T|J|U|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|N|$]]

For a time field, use:
<pre><code>import time 
time.strftime('%Y-%m-%d %H:%M:%S')
</code></pre>
I think <code>strftime</code> also applies to <code>datetime</code>.

blocks|key|1719123|text|您最有可能获得TypeError，因为您需要用引号将日期列值括起来。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1719124|尝试：|1719125|now+=+datetime.datetime(2009,+5,+5)

cursor.execute("INSERT+INTO+table+(name,+id,+datecolumn)+VALUES+(%25s,+%25s,+'%25s')",
+++++++++++++++("name",+4,+now))|code-block|syntax|javascript|1719126|关于格式，我成功地使用了上面的命令(包括毫秒)和：|1719127|now.strftime('%25Y-%25m-%25d+%25H:%25M:%25S')|1719128|希望这能有所帮助。|1719129|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|T|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

You are most likely getting the TypeError because you need quotes around the datecolumn value.

Try:

<pre><code>now = datetime.datetime(2009, 5, 5)

cursor.execute("INSERT INTO table (name, id, datecolumn) VALUES (%s, %s, '%s')",
 ("name", 4, now))
</code></pre>

With regards to the format, I had success with the above command (which includes the milliseconds) and with:

<pre><code>now.strftime('%Y-%m-%d %H:%M:%S')
</code></pre>

Hope this helps.

blocks|key|3829381|text|尝试使用now.date()获取Date对象，而不是DateTime。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3829382|如果这不起作用，那么将其转换为字符串应该是有效的：|3829383|now+=+datetime.datetime(2009,5,5)
str_now+=+now.date().isoformat()
cursor.execute('INSERT+INTO+table+(name,+id,+datecolumn)+VALUES+(%25s,%25s,%25s)',+('name',4,str_now))|code-block|syntax|javascript|3829384|entityMap^0|4|A|G|4|Q|8|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@$9|P|A|Q|B|C]|$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|V|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|W|8|@]|D|@]|E|$K|L]]|$1|M|3|-4|5|6|7|X|8|@]|D|@]|E|$]]]|N|$]]

Try using <code>now.date()</code> to get a <code>Date</code> object rather than a <code>DateTime</code>.

If that doesn't work, then converting that to a string should work:

<pre><code>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))
</code></pre>

blocks|key|5246849|text|使用Python方法格式，其中datetime.strftime(format)+=+'%25Y-%25m-%25d+%25H:%25M:%25S'。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|5246850|import+datetime

now+=+datetime.datetime.utcnow()

cursor.execute("INSERT+INTO+table+(name,+id,+datecolumn)+VALUES+(%25s,+%25s,+%25s)",
+++++++++++++++("name",+4,+now.strftime('%25Y-%25m-%25d+%25H:%25M:%25S')))|code-block|syntax|javascript|5246851|时区|5246852|如果需要考虑时区，可以按如下方式为协调世界时设置MySQL时区：|5246853|cursor.execute("SET+time_zone+=+'%2B00:00'")|5246854|并且可以在Python中设置时区：|5246855|now+=+datetime.datetime.utcnow().replace(tzinfo=datetime.timezone.utc)|5246856|5246857|MySQL+Documentation|5246858|5246859|+MySQL可以识别以下格式的日期时间和时间戳值：|blockquote|5246860|5246861|'YYYY-MM-DD+HH:MM:SS'或'YY-MM-DD+HH:MM:SS'格式的字符串。这里也允许使用“宽松”语法:任何标点符号都可以用作日期部分或时间部分之间的分隔符。例如，'2012-12-31+11:30:45‘、'2012%5E12%5E31+11%2B30%2B45’、'2012/12/31+11*30*45‘和'2012@12@31+11%5E30%5E45’是等效的。|BOLD|5246862|日期和时间部分与小数秒部分之间唯一可识别的分隔符是小数点。|5246863|日期和时间部分可以用T分隔，而不是用空格分隔。例如，'2012-12-31+11:30:45‘'2012-12-31T11:30:45’是等效的。|5246864|作为不带分隔符的字符串，格式为'YYYYMMDDHHMMSS‘或'YYMMDDHHMMSS’，前提是该字符串作为日期有意义。例如，'20070523091528‘和'070523091528’被解释为'2007-05-23+09:15:28'，但是'071122129015‘是非法的(它有一个无意义的分钟部分)，变成了'0000-00-+00+:00:00’。|5246865|作为YYYYMMDDHHMMSS或YYMMDDHHMMSS格式的数字，前提是该数字作为日期有意义。例如，19830905132800和830905132800被解释为'1983-09-05+13:28:00‘。|5246866|entityMap|0|LINK|mutability|MUTABLE|url|https://docs.python.org/3/library/datetime.html#datetime.datetime.strftime|1|https://dev.mysql.com/doc/refman/8.0/en/date-and-time-literals.html^0|F|P|17|J|F|P|0|0|0|0|0|0|0|0|0|0|J|1|0|0|0|0|0|L|M|J|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1N|8|@$9|1O|A|1P|B|C]|$9|1Q|A|1R|B|C]]|D|@$9|1S|A|1T|1|1U]]|E|$]]|$1|F|3|G|5|H|7|1V|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|1W|8|@]|D|@]|E|$]]|$1|M|3|N|5|6|7|1X|8|@]|D|@]|E|$]]|$1|O|3|P|5|H|7|1Y|8|@]|D|@]|E|$I|J]]|$1|Q|3|R|5|6|7|1Z|8|@]|D|@]|E|$]]|$1|S|3|T|5|H|7|20|8|@]|D|@]|E|$I|J]]|$1|U|3|-4|5|6|7|21|8|@]|D|@]|E|$]]|$1|V|3|W|5|6|7|22|8|@]|D|@$9|23|A|24|1|25]]|E|$]]|$1|X|3|-4|5|6|7|26|8|@]|D|@]|E|$]]|$1|Y|3|Z|5|10|7|27|8|@]|D|@]|E|$]]|$1|11|3|-4|5|6|7|28|8|@]|D|@]|E|$]]|$1|12|3|13|5|6|7|29|8|@$9|2A|A|2B|B|14]|$9|2C|A|2D|B|14]]|D|@]|E|$]]|$1|15|3|16|5|6|7|2E|8|@]|D|@]|E|$]]|$1|17|3|18|5|6|7|2F|8|@]|D|@]|E|$]]|$1|19|3|1A|5|6|7|2G|8|@]|D|@]|E|$]]|$1|1B|3|1C|5|6|7|2H|8|@]|D|@]|E|$]]|$1|1D|3|-4|5|6|7|2I|8|@]|D|@]|E|$]]]|1E|$1F|$5|1G|1H|1I|E|$1J|1K]]|1L|$5|1G|1H|1I|E|$1J|1M]]]]

Use Python method <a href="https://docs.python.org/3/library/datetime.html#datetime.datetime.strftime" rel="noreferrer"><code>datetime.strftime(format)</code></a>, where format = <code>'%Y-%m-%d %H:%M:%S'</code>.

<pre><code>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')))
</code></pre>

<h2>Timezones</h2>

If timezones are a concern, the MySQL timezone can be set for UTC as follows:

<pre><code>cursor.execute("SET time_zone = '+00:00'")
</code></pre>

And the timezone can be set in Python:

<pre><code>now = datetime.datetime.utcnow().replace(tzinfo=datetime.timezone.utc)
</code></pre>

<hr>

<h2><a href="https://dev.mysql.com/doc/refman/8.0/en/date-and-time-literals.html" rel="noreferrer">MySQL Documentation</a></h2>

<blockquote>
 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'.
</blockquote>

blocks|key|1719049|text|您要连接到哪个数据库？我知道甲骨文对日期格式很挑剔，喜欢ISO 8601格式。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1719050|**注意:糟糕，我刚看到你在用MySQL。只需格式化日期并将其作为单独的直接SQL调用进行测试即可。|1719051|在Python中，您可以获得如下所示的ISO日期|1719052|now.isoformat()|code-block|syntax|javascript|1719053|例如，Oracle喜欢这样的日期|1719054|insert+into+x+values(99,+'31-may-09');|1719055|根据您的数据库，如果是Oracle，您可能需要对其执行TO_DATE：|1719056|insert+into+x
values(99,+to_date('2009/05/31:12:00:00AM',+'yyyy/mm/dd:hh:mi:ssam'));|1719057|TO_DATE的一般用法是：|1719058|TO_DATE(<string>,+'<format>')|1719059|如果使用另一个数据库(我看到了光标并认为是Oracle；我可能是错的)，那么请检查他们的日期格式工具。对于MySQL，它是DATE_FORMAT()，对于Server，它是CONVERT。|1719060|此外，使用像SQLAlchemy这样的工具将消除这些差异，并使您的生活变得轻松。|1719061|entityMap|0|LINK|mutability|MUTABLE|url|http://en.wikipedia.org/wiki/ISO_8601|1|https://en.wikipedia.org/wiki/SQLAlchemy^0|S|8|0|0|0|0|0|0|0|0|0|0|0|0|6|A|1|0^^$0|@$1|2|3|4|5|6|7|1C|8|@]|9|@$A|1D|B|1E|1|1F]]|C|$]]|$1|D|3|E|5|6|7|1G|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|1H|8|@]|9|@]|C|$]]|$1|H|3|I|5|J|7|1I|8|@]|9|@]|C|$K|L]]|$1|M|3|N|5|6|7|1J|8|@]|9|@]|C|$]]|$1|O|3|P|5|J|7|1K|8|@]|9|@]|C|$K|L]]|$1|Q|3|R|5|6|7|1L|8|@]|9|@]|C|$]]|$1|S|3|T|5|J|7|1M|8|@]|9|@]|C|$K|L]]|$1|U|3|V|5|6|7|1N|8|@]|9|@]|C|$]]|$1|W|3|X|5|J|7|1O|8|@]|9|@]|C|$K|L]]|$1|Y|3|Z|5|6|7|1P|8|@]|9|@]|C|$]]|$1|10|3|11|5|6|7|1Q|8|@]|9|@$A|1R|B|1S|1|1T]]|C|$]]|$1|12|3|-4|5|6|7|1U|8|@]|9|@]|C|$]]]|13|$14|$5|15|16|17|C|$18|19]]|1A|$5|15|16|17|C|$18|1B]]]]

What database are you connecting to? I know Oracle can be picky about date formats and likes <a href="http://en.wikipedia.org/wiki/ISO_8601" rel="nofollow noreferrer">ISO&nbsp;8601</a> 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

<pre><code>now.isoformat()
</code></pre>

For instance, Oracle likes dates like

<pre><code>insert into x values(99, '31-may-09');
</code></pre>

Depending on your database, if it is Oracle you might need to TO_DATE it:

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

The general usage of TO_DATE is:

<pre><code>TO_DATE(&lt;string&gt;, '&lt;format&gt;')
</code></pre>

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&nbsp;Server it is CONVERT.

Also using a tool like <a href="https://en.wikipedia.org/wiki/SQLAlchemy" rel="nofollow noreferrer">SQLAlchemy</a> will remove differences like these and make your life easy.

blocks|key|1482013|text|如果您只是使用python+(不是完整的datetime.datetime)，那么只需将datetime.date转换为字符串。这非常简单，适用于我(mysql，Python2.7，Ubuntu)。列published_date是一个MySQL日期字段，python变量publish_date是datetime.date。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1482014|#+make+the+record+for+the+passed+link+info
sql_stmt+=+"INSERT+INTO+snippet_links+("+%2B+\
++++"link_headline,+link_url,+published_date,+author,+source,+coco_id,+link_id)"+%2B+\
++++"VALUES(%25s,+%25s,+%25s,+%25s,+%25s,+%25s,+%25s)+;"

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:
++++...|code-block|syntax|javascript|1482015|entityMap^0|2S|E|3R|C|44|D|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|U|8|@]|D|@]|E|$]]]|L|$]]

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 <code>published_date</code> is a MySQL date field, the python variable <code>publish_date</code> is <code>datetime.date</code>. 

<pre><code># 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:
 ...
</code></pre>

blocks|key|1719289|text|++++dt=+datetime.now()

++++query+=+"""INSERT+INTO+table1(python_Date_col)
+++++++++++++++VALUES+(%25s)
++++++++++++"""
++++conn+=+......+#+Connection+creating+process
++++cur+=+conn.cursor()
++++cur.execute(query,(dt))|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|1719290|上面的代码将失败，因为"datetime.now()“会生成"datetime.datetime(2014，2，11，1，16)”作为insert语句的参数值。|unstyled|1719291|使用以下方法捕获给出字符串值的datetime。|1719292|++++dt=+datetime.now().strftime("%25Y%25m%25d%25H%25M%25S")|1719293|在更改之后，我能够成功地运行代码...|1719294|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|P|8|@]|9|@]|A|$]]|$1|G|3|H|5|F|7|Q|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$B|C]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$]]|$1|M|3|-4|5|F|7|T|8|@]|9|@]|A|$]]]|N|$]]

<pre><code> dt= datetime.now()

 query = &quot;&quot;&quot;INSERT INTO table1(python_Date_col)
 VALUES (%s)
 &quot;&quot;&quot;
 conn = ...... # Connection creating process
 cur = conn.cursor()
 cur.execute(query,(dt))
</code></pre>
Above code will fail as &quot;datetime.now()&quot; produces &quot;datetime.datetime(2014, 2, 11, 1, 16)&quot; as a parameter value to insert statement.
Use the following method to capture the datetime which gives string value.
<pre><code> dt= datetime.now().strftime(&quot;%Y%m%d%H%M%S&quot;)
</code></pre>
I was able to successfully run the code after the change...

blocks|key|1482069|text|当iserting到t-sql中时|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1482070|这将失败：|1482071|select+CONVERT(datetime,'2019-09-13+09:04:35.823312',21)|code-block|syntax|javascript|1482072|这是可行的：|1482073|select+CONVERT(datetime,'2019-09-13+09:04:35.823',21)|1482074|简单的方法：|1482075|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('.%25d'+%25+round(float(microseconds)+/+1000),+dt)|1482076|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|V|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|W|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|F|7|Y|8|@]|9|@]|A|$G|H]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

when iserting into t-sql

this fails:

<pre><code>select CONVERT(datetime,'2019-09-13 09:04:35.823312',21)
</code></pre>

this works:

<pre><code>select CONVERT(datetime,'2019-09-13 09:04:35.823',21)
</code></pre>

easy way:

<pre><code>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)
</code></pre>

I have a date column in a MySQL table. I want to insert a <code>datetime.datetime()</code> object into this column. What should I be using in the execute statement?

I have tried:

<pre><code>now = datetime.datetime(2009,5,5)

cursor.execute("INSERT INTO table
(name, id, datecolumn) VALUES (%s, %s
, %s)",("name", 4,now))
</code></pre>

I am getting an error as: <code>"TypeError: not all arguments converted during string formatting"</code>
What should I use instead of <code>%s</code>?

Inserting a Python datetime.datetime object into MySQL

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我在MySQL表中有一个date列。我想在这个列中插入一个datetime.datetime()对象。我应该在execute语句中使用什么？我试过了：now = datetime.datetime(2009,5,5)cursor.execute("INSERT INTO table(name, id, datecolumn) VALUES (%s, %s, %s)",("name", 4,now)

问在MySQL中插入Python datetime.datetime对象
EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在MySQL中插入Python datetime.datetime对象EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在MySQL中插入Python datetime.datetime对象
EN