CODEWITHSUNDEEP
pythonsqlite
Alan Harris-Reid
Alan Harris-Reid

Reputation: 2851

SQLite date storage and conversion

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) I convert the date using

datetime.datetime.strptime(mydate,'%Y-%m-%d').strftime('%d/%m/%Y')

However, when it comes to writing-back data to the table, SQLite is very forgiving and is quite happy to store '25/06/2003' in a date field, but this is not ideal because

Therefore I need to convert the date string back to ISO format before committing, but then I would need a generic function which checks data about to be written in all date fields and converts to ISO if necessary. That sounds a bit tedious to me, but maybe it is inevitable.

Are there simpler solutions? Would it be easier to change the date field to a 10-character field and store 'dd/mm/yyyy' throughout the table? This way no conversion is required when reading or writing from the table, and I could use datetime() 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.

Upvotes: 13

Views: 22214

Answers (2)

Roger Binns
Roger Binns

Reputation: 3348

Note that SQLite itself does not have a native date/time type. As @unutbu answered, you can make the pysqlite/sqlite3 module try to guess (and note that it really is a guess) which columns/values are dates/times. SQL expressions will easily confuse it.

SQLite does have a variety of date time functions and can work with various strings, numbers in both unixepoch and julian format, and can do transformations. See the documentation:

http://www.sqlite.org/lang_datefunc.html

You may find it more convenient to get SQLite to do the date/time work you need instead of importing the values into Python and using Python libraries to do it. Note that you can put constraints in the SQL table definition for example requiring that string value be present, be a certain length etc.

Upvotes: 5

unutbu
unutbu

Reputation: 880797

If you set detect_types=sqlite3.PARSE_DECLTYPES in sqlite3.connect, then the connection will try to convert sqlite data types to Python data types when you draw data out of the database.

This is a very good thing since its much nicer to work with datetime objects than random date-like strings which you then have to parse with datetime.datetime.strptime or dateutil.parser.parse.

Unfortunately, using detect_types does not stop sqlite from accepting strings as DATE data, but you will get an error when you try to draw 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 sqlite
cur.execute("INSERT INTO foo(bar) VALUES (?)",('25/06/2003',))

# But you won't be able to draw the data out later because parsing will fail
try:
    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 inserted a 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.

Upvotes: 17

Related Questions