SQLite 日期存储和转换

如果您的用户输入各种格式的日期数据，请查看 dateutil.parser.parse.它或许可以帮助您将这些各种字符串转换为 datetime.datetime 对象.I am having design problems with date storage/retrieval using Python and SQLite.I understand that a SQLite date column stores dates as text in ISO format(ie. '2010-05-25'). So when I display a British date (eg. on a web-page) Iconvert the date usingdatetime.datetime.strptime(mydate,'%Y-%m-%d').strftime('%d/%m/%Y')However, when it comes to writing-back data to the table, SQLite is veryforgiving and is quite happy to store '25/06/2003' in a date field, but thisis not ideal becauseI could be left with a mixture of date formats in the samecolumn,SQLite's date functions only work with ISO format.Therefore I need to convert the date string back to ISO format beforecommitting, but then I would need a generic function which checks data about tobe written in all date fields and converts to ISO if necessary. That sounds abit tedious to me, but maybe it is inevitable.Are there simpler solutions? Would it be easier to change the date field to a10-character field and store 'dd/mm/yyyy' throughout the table? This way noconversion is required when reading or writing from the table, and I could usedatetime() functions if I needed to perform any date-arithmetic.How have other developers overcome this problem? Any help would be appreciated.For the record, I am using SQLite3 with Python 3.1. 解决方案 If you set detect_types=sqlite3.PARSE_DECLTYPES in sqlite3.connect,then the connection will try to convert sqlite data types to Python data typeswhen you draw data out of the database.This is a very good thing since its much nicer to work with datetime objects thanrandom date-like strings which you then have to parse withdatetime.datetime.strptime or dateutil.parser.parse.Unfortunately, using detect_types does not stop sqlite from acceptingstrings as DATE data, but you will get an error when you try todraw the data out of the database (if it was inserted in some format other than YYYY-MM-DD)because the connection will fail to convert it to a datetime.date object:conn=sqlite3.connect(':memory:',detect_types=sqlite3.PARSE_DECLTYPES) cur=conn.cursor()cur.execute('CREATE TABLE foo(bar DATE)')# Unfortunately, this is still accepted by sqlitecur.execute("INSERT INTO foo(bar) VALUES (?)",('25/06/2003',))# But you won't be able to draw the data out later because parsing will failtry: cur.execute("SELECT * FROM foo")except ValueError as err: print(err) # invalid literal for int() with base 10: '25/06/2003' conn.rollback()But at least the error will alert you to the fact that you've inserteda string for a DATE when you really should be inserting datetime.date objects:cur.execute("INSERT INTO foo(bar) VALUES (?)",(datetime.date(2003,6,25),))cur.execute("SELECT ALL * FROM foo")data=cur.fetchall()data=zip(*data)[0]print(data)# (datetime.date(2003, 6, 25),)You may also insert strings as DATE data as long as you use the YYYY-MM-DD format. Notice that although you inserted a string, it comes back out as a datetime.date object:cur.execute("INSERT INTO foo(bar) VALUES (?)",('2003-06-25',))cur.execute("SELECT ALL * FROM foo")data=cur.fetchall()data=zip(*data)[0]print(data)# (datetime.date(2003, 6, 25), datetime.date(2003, 6, 25))So if you are disciplined about inserting only datetime.date objects into the DATE field, then you'll have no problems later when drawing the data out.If your users are input-ing date data in various formats, check out dateutil.parser.parse. It may be able to help you convert those various strings into datetime.datetime objects. 这篇关于SQLite 日期存储和转换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！