将日期时间插入MySql数据库

问题描述

我有一个由strptime函数设置的datetime值

I have a datetime value which is made by strptime function

import MySQLdb
a = time.strptime('my date', "%b %d %Y %H:%M")

在MySql db中有一个DATETIME类型的列.当我尝试将此值插入db时，很明显我得到

There is a column in MySql db of type DATETIME. When I try to insert this value into db, I, obviously, get the error of

mysql_exceptions.OperationalError: (1305, 'FUNCTION time.struct_time does not exist')

INSERT INTO myTable(Date......) VALUES(time.struct_time(tm_year=2222, tm_mon=4, tm_mday=1, tm_hour=1, tm_min=2, tm_sec=4, tm_wday=1, tm_yday=118, tm_isdst=-1), ......)

如何将该值插入db?

推荐答案

您现在正在传递time.struct_time对象，MySQL对此一无所知.您需要将时间戳记格式化为MySQL可以理解的格式.不幸的是，MySQLdb库无法为您完成此操作.

You are now passing in a time.struct_time object, something MySQL knows nothing about. You'll need to format the timestamp to a format MySQL understands. Unfortunately the MySQLdb library doesn't do this for you.

使用datetime模块将是最简单的方法，但是您也可以使用time模块进行此操作:

It'll be easiest using the datetime module, but you can do this with the time module too:

import datetime

a = datetime.datetime.strptime('my date', "%b %d %Y %H:%M")

cursor.execute('INSERT INTO myTable (Date) VALUES(%s)', (a.strftime('%Y-%m-%d %H:%M:%S'),))

datetime.datetime对象上的.strftime()方法调用以MySQL可接受的方式格式化信息.

The .strftime() method call on the datetime.datetime object formats the information in such a way that MySQL will accept.

仅使用time模块执行同一任务:

Doing the same task with just the time module:

import time

a = time.strptime('my date', "%b %d %Y %H:%M")

cursor.execute('INSERT INTO myTable (Date) VALUES(%s)', (time.strftime('%Y-%m-%d %H:%M:%S', a),))

这篇关于将日期时间插入MySql数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！