blocks|key|1288358|text|对于时间字段，请使用：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1288359|import+time++++
time.strftime('%25Y-%25m-%25d+%25H:%25M:%25S')|code-block|syntax|javascript|1288360|我认为strftime也适用于datetime。|offset|length|style|CODE|1288361|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|1288412|text|您最有可能获得TypeError，因为您需要用引号将日期列值括起来。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1288413|尝试：|1288414|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|1288415|关于格式，我成功地使用了上面的命令(包括毫秒)和：|1288416|now.strftime('%25Y-%25m-%25d+%25H:%25M:%25S')|1288417|希望这能有所帮助。|1288418|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|1503088|text|尝试使用now.date()获取Date对象，而不是DateTime。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1503089|如果这不起作用，那么将其转换为字符串应该是有效的：|1503090|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|1503091|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|1288319|text|您要连接到哪个数据库？我知道甲骨文对日期格式很挑剔，喜欢ISO 8601格式。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1288320|**注意:糟糕，我刚看到你在用MySQL。只需格式化日期并将其作为单独的直接SQL调用进行测试即可。|1288321|在Python中，您可以获得如下所示的ISO日期|1288322|now.isoformat()|code-block|syntax|javascript|1288323|例如，Oracle喜欢这样的日期|1288324|insert+into+x+values(99,+'31-may-09');|1288325|根据您的数据库，如果是Oracle，您可能需要对其执行TO_DATE：|1288326|insert+into+x
values(99,+to_date('2009/05/31:12:00:00AM',+'yyyy/mm/dd:hh:mi:ssam'));|1288327|TO_DATE的一般用法是：|1288328|TO_DATE(<string>,+'<format>')|1288329|如果使用另一个数据库(我看到了光标并认为是Oracle；我可能是错的)，那么请检查他们的日期格式工具。对于MySQL，它是DATE_FORMAT()，对于Server，它是CONVERT。|1288330|此外，使用像SQLAlchemy这样的工具将消除这些差异，并使您的生活变得轻松。|1288331|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|1503291|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|1503292|#+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|1503293|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|1503382|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|1503383|上面的代码将失败，因为"datetime.now()“会生成"datetime.datetime(2014，2，11，1，16)”作为insert语句的参数值。|unstyled|1503384|使用以下方法捕获给出字符串值的datetime。|1503385|++++dt=+datetime.now().strftime("%25Y%25m%25d%25H%25M%25S")|1503386|在更改之后，我能够成功地运行代码...|1503387|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|1288550|text|当iserting到t-sql中时|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1288551|这将失败：|1288552|select+CONVERT(datetime,'2019-09-13+09:04:35.823312',21)|code-block|syntax|javascript|1288553|这是可行的：|1288554|select+CONVERT(datetime,'2019-09-13+09:04:35.823',21)|1288555|简单的方法：|1288556|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)|1288557|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

数据库

Python

Ubuntu

MySQL

我在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

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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