Сохранение и преобразование даты SQLite

У меня проблемы с дизайном хранения/поиска даты с использованием Python и SQLite.

Я понимаю, что столбец даты SQLite сохраняет даты как текст в формате ISO (т.е. '2010-05-25'). Поэтому, когда я показываю британскую дату (например, на веб-странице), я конвертируйте дату, используя

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

Однако, когда дело доходит до записи данных в таблицу, SQLite очень прощающ и вполне счастлив хранить '25/06/2003' в поле даты, но это не является идеальным, потому что

Я мог бы оставить смесь форматов даты в том же колонка,
Функции даты SQLite работают только с форматом ISO.

Поэтому мне нужно преобразовать строку даты обратно в формат ISO до но тогда мне понадобится общая функция, которая проверяет данные в записываться во все поля даты и при необходимости преобразуется в ISO. Это звучит немного утомительно для меня, но, возможно, это неизбежно.

Существуют ли более простые решения? Было бы проще изменить поле даты на 10-символьное поле и сохранить 'dd/mm/yyyy' по всей таблице? Таким образом, нет преобразование требуется при чтении или записи из таблицы, и я мог бы использовать datetime(), если мне нужно выполнить любую арифметику даты.

Как другие разработчики могут решить эту проблему? Любая помощь будет оценена по достоинству. Для записи я использую SQLite3 с Python 3.1.

Ответ 1

Если вы установили detect_types=sqlite3.PARSE_DECLTYPES в sqlite3.connect, то соединение попытается преобразовать типы данных sqlite в типы данных Python когда вы извлекаете данные из базы данных.

Это очень хорошо, так как гораздо приятнее работать с объектами datetime, чем случайные строки, относящиеся к дате, которые вам нужно проанализировать с помощью datetime.datetime.strptime или dateutil.parser.parse.

К сожалению, использование detect_types не останавливает принятие sqlite строки как данные DATE, но вы получите сообщение об ошибке при попытке извлекать данные из базы данных (если она была вставлена в каком-либо формате, отличном от ГГГГ-ММ-ДД) потому что соединение не сможет преобразовать его в объект datetime.date:

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()

Но, по крайней мере, ошибка предупредит вас о том, что вы вставили строка для DATE, когда вы действительно должны вставлять объекты datetime.date:

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),)

Вы также можете вставлять строки как данные DATE, если вы используете формат YYYY-MM-DD. Обратите внимание, что хотя вы вставляете строку, она возвращается в виде объекта datetime.date:

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))

Итак, если вы дисциплинированно вставляете только теги datetime.date в поле DATE, то при извлечении данных у вас не будет проблем.

Если ваши пользователи вводят данные даты в различных форматах, посмотрите dateutil.parser.parse. Он может помочь вам преобразовать эти различные строки в объекты datetime.datetime.

Ответ 2

Обратите внимание, что сам SQLite не имеет собственного типа даты/времени. Как ответил @unutbu, вы можете заставить модуль pysqlite/sqlite3 угадать (и отметить, что это действительно догадка), в столбцах/значениях указаны даты/времена. SQL-выражения легко смутят его.

SQLite имеет множество функций времени по дате и может работать с различными строками, числами как в формате unixepoch, так и в формате julian, и может делать преобразования. См. Документацию:

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

Вам может быть удобнее получить SQLite для выполнения требуемой работы по дате/времени, а не импортировать значения в Python и использовать библиотеки Python для этого. Обратите внимание, что вы можете помещать ограничения в определение таблицы SQL, например, требуя наличия значения строки, определенной длины и т.д.