Как извлечь правильные данные из базы данных Sqlite с помощью Python?

У меня есть база данных имен людей и их дней рождения. Формат дня рождения – mm/dd/yyyy , например «3/13/1960».

Я хочу извлечь список людей, родившихся после определенной даты. Я назвал эту дату «базой».

Программа, которую вы видите ниже, сначала создает БД людей (для имитации реальной БД, с которой я хочу работать), а затем извлекает требуемый список. Проблема в том, что результат не такой, как я ожидаю:

 import datetime as dt import peewee as pw db = pw.SqliteDatabase('people1.db') class Person(pw.Model): name = pw.CharField() birthday = pw.DateField(formats=['%m/%d/%Y']) class Meta: database = db # This model uses the "people.db" database. db.create_tables([Person]) bob0 = Person(name='Bob0', birthday='4/13/1940') bob1 = Person(name='Bob1', birthday='5/13/1950') bob2 = Person(name='Bob2', birthday='3/13/1960') bob3 = Person(name='Bob3', birthday='3/13/1970') bob4 = Person(name='Bob4', birthday='3/13/1980') bob5 = Person(name='Bob5', birthday='3/13/1990') base = Person(name="base", birthday='3/13/1960') bob0.save() bob1.save() bob2.save() bob3.save() bob4.save() bob5.save() base.save() for item in Person.select().where(Person.birthday > base.birthday): print item.name , item.birthday 

Вывод:

 >>> ================================ RESTART ================================ >>> Bob0 1940-04-13 Bob1 1950-05-13 Bob3 1970-03-13 Bob4 1980-03-13 Bob5 1990-03-13 >>> 

Как вы видите выше, base = 3/13/1960 . Поэтому у меня не должно быть Bob0 и Bob1 на выходе! Как я могу справиться с этим?

Обратите внимание, что я не хочу менять формат дней рождения в базе данных. Я также не хочу брать все строки и проверять их позже! Я хочу получить только нужные строки.

Вы можете использовать sqlite3.Connection.create_function, чтобы указать свою собственную функцию sqlite, которая преобразует ваши даты во что-то, что может быть лексикографически отсортировано:

 import datetime as dt import peewee as pw # custom sqlite function to reformat our date string def _sqlite_reformat_date(unfortunate_date_string): return dt.datetime \ .strptime(unfortunate_date_string,'%m/%d/%Y') \ .strftime('%Y-%m-%d') # Subclass pw.SqliteDatabase to add our custom sqlite function class MySqliteDatabase(pw.SqliteDatabase): def __init__(self, *args, **kwargs): super(MySqliteDatabase, self).__init__(*args, **kwargs) def _add_conn_hooks(self, conn): conn.create_function('reformat_date', 1, _sqlite_reformat_date) super(MySqliteDatabase, self)._add_conn_hooks(conn) db = MySqliteDatabase('people1.db') # ... # Your model definition and data inserts from your example above # ... rd = pw.fn.reformat_date # Use our custom sqlite function for item in Person.select().where(rd(Person.birthday) > rd(base.birthday)): print item.name , item.birthday 

Хотя этот подход будет «получать только нужные строки», он все равно будет запускать эту функцию python для каждой строки! Это немного лучше, чем просто выборка всех строк при сравнении дат в python, это может быть даже медленнее!

Однако функция _sqlite_reformat_date может быть реорганизована намного быстрее, и ее приятно знать, как легко добавить пользовательскую функцию в sqlite.

SQlite хранит дату-время как строки. Так, как другие предложили в комментариях и других ответах, вы должны использовать другой формат для хранения дат, чтобы «упорядочение дат и лексическое упорядочение работали одинаково»:

 import datetime as dt import peewee as pw db = pw.SqliteDatabase('people1.db') class Person(pw.Model): name = pw.CharField() birthday = pw.DateField(formats=['%Y-%m-%d']) class Meta: database = db # This model uses the "people.db" database. db.create_tables([Person]) Person.create(name='Bob0', birthday=dt.date(1940, 4, 13)) Person.create(name='Bob1', birthday=dt.date(1950, 5, 13)) Person.create(name='Bob2', birthday=dt.date(1960, 3, 13)) Person.create(name='Bob3', birthday=dt.date(1970, 3, 13)) Person.create(name='Bob4', birthday=dt.date(1980, 3, 13)) Person.create(name='Bob5', birthday=dt.date(1990, 3, 13)) base = Person.create(name="base", birthday=dt.date(1960, 3, 13)) for item in Person.select().where(Person.birthday > base.birthday): print item.name , item.birthday 

Это дает:

 Bob3 1970-03-13 Bob4 1980-03-13 Bob5 1990-03-13 

ОБНОВИТЬ

Я не заметил вашего комментария, что вы не хотите менять базу данных.

Вот сумасшедший способ извлечь часть даты:

 SELECT birthday, CAST(substr(birthday, 1, instr(birthday, '/') - 1) AS integer), CAST(substr(substr(birthday, instr(birthday, '/') + 1), 1, instr(substr(birthday, instr(birthday, '/') + 1), '/') - 1) AS integer), CAST(substr(birthday, instr(birthday, '/') + instr(substr(birthday, instr(birthday, '/') + 1), '/') + 1) AS integer) FROM person 

который по моим данным теста дает:

 4/13/1940 4 13 1940 12/13/1950 12 13 1950 3/3/1960 3 3 1960 3/25/1970 3 25 1970 3/13/1980 3 13 1980 3/13/1990 3 13 1990 3/13/1960 3 13 1960 

Вы можете использовать эти выражения для сравнения их с частями данной даты:

 query = """ SELECT * FROM person WHERE ( substr('0000' || CAST(substr(birthday, instr(birthday, '/') + instr(substr(birthday, instr(birthday, '/') + 1), '/') + 1) AS integer), -4, 4) || '-' || -- year substr('00' || CAST(substr(birthday, 1, instr(birthday, '/') - 1) AS integer), -2, 2) || '-' || -- month substr('00' || CAST(substr(substr(birthday, instr(birthday, '/') + 1), 1, instr(substr(birthday, instr(birthday, '/') + 1), '/') - 1) AS integer), -2, 2) -- day ) > '1960-03-03' """ for item in Person.raw(query): print item.name, item.birthday 

Я реконструирую дату ISO здесь и использую ее для сравнения.

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

 from datetime import datetime import peewee as pw db = pw.SqliteDatabase('people1.db') class Person(pw.Model): name = pw.CharField() birthday = pw.DateField(formats=['%m/%d/%Y']) class Meta: database = db # This model uses the "people.db" database. db.create_tables([Person]) bob0 = Person(name='Bob0', birthday='4/13/1940') bob1 = Person(name='Bob1', birthday='5/13/1950') bob2 = Person(name='Bob2', birthday='3/13/1960') bob3 = Person(name='Bob3', birthday='3/13/1970') bob4 = Person(name='Bob4', birthday='3/13/1980') bob5 = Person(name='Bob5', birthday='3/13/1990') bob6 = Person(name='Bob6', birthday='12/1/1990') base = Person(name="base", birthday='3/13/1960') bob0.save() bob1.save() bob2.save() bob3.save() bob4.save() bob5.save() bob6.save() base.save() month = 'substr(birthday,1,instr(birthday,"/")-1)' iso_month = 'case when length({month}) = 1 then "0" || {month} else {month} end'.format(month=month) day = 'trim(trim(birthday,"0123456789"),"/")' iso_day = 'case when length({day}) = 1 then "0" || {day} else {day} end'.format(day=day) year = 'substr(ltrim(ltrim(birthday,"0123456789"),"/"),instr(ltrim(ltrim(birthday,"0123456789"),"/"),"/")+1)' iso_date = 'replace(replace(replace("yyyy-mm-dd","yyyy",{year}),"mm",{iso_month}),"dd",{iso_day})'.format(year=year,iso_month=iso_month,iso_day=iso_day) iso_base = datetime.strptime(base.birthday,'%m/%d/%Y').date().isoformat() if __name__ == '__main__': for item in Person.select().where(pw.SQL(iso_date) > iso_base): print item.name , item.birthday #output #Bob3 1970-03-13 #Bob4 1980-03-13 #Bob5 1990-03-13 #Bob6 1990-12-01 

SQLite хранит даты как строки. По этой причине они должны храниться как YYYY-MM-DD, это гарантирует, что они будут отсортированы правильно. Честно говоря, нет причин делать иначе.

Если вы посмотрите на документы sqlite, он даже не распознает даты в используемом вами формате:

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

Итак, мой совет – обновить способ хранения дат.

В противном случае создайте пользовательскую функцию, которая делает правильную вещь, используя strptime (предполагается использование playhouse.sqlite_ext.SqliteExtDatabase ):

 @db.func() def fix_time(s): return datetime.datetime.strptime(s, '%m/%d/%Y').strftime('%Y-%m-%d') 

Если вы хотите придерживаться обычной старой SqliteDatabase , вы можете вызвать метод sqlite3 connection.create_function .