python sql where using dates

Question

I am trying to get all records that are within the current week(monday to sunday) and then I was planning to insert them back into the database but with the date of lesson being increased by 7. However I get the following error:

sqlite3.OperationalError: near ">=": syntax error

I may be wrong but I think that this is due to how python stores dates is there a way around this, if not I can always get all records in table into array and filter that array in python. The code for the sql is underneath:

with sqlite3.connect("GuitarLessons.db") as db:
        cursor = db.cursor()
        cursor.row_factory = sqlite3.Row
        sql = "select *"\
              "from tblBookings"\
              "where DateOfLesson >=  ?"\
              "and DateOfLesson <= ?"
        cursor.execute(sql,(startweekd,endweekd))
        BookingList = cursor.fetchall()
        print(BookingList)

The rest of my code is just calculating the start and end date for that week.

import datetime
from datetime import date, timedelta
import sqlite3

tdate = datetime.datetime.today()
tday = datetime.datetime.today().weekday()
tdadd = 7 - (tday+1)
endweekd = date.today() + timedelta(days=tdadd)
startweekd = endweekd - timedelta(days=7)
endweekd = endweekd.strftime("%d/%m/%y")
startweekd = startweekd.strftime("%d/%m/%y")
print(startweekd)
print(endweekd)

How are the dates stored in the table? Can you show some rows, preferably using the command-line client, so we know what is actually stored? — Jan Hudec
– Jan Hudec, Commented Jan 14, 2016 at 13:50

Jan Hudec · Accepted Answer · 2016-01-14 15:44:14Z

1

SQLite (nowhere, not just in Python) does not support dates.

So you have to convert the dates, on query, but also on storage, to some format it will understand. There are two options:

Number of seconds since some epoch, e.g. unix time, possibly fractional.
Strings.

To make comparison of strings work, the dates must be stored in the ISO 8601 format (or at least that order). ISO 8601 timestamp has format specification "%FT%T" (or on systems that don't understand %F or %T "%Y-%m-%dT%H:%M:%S"). Or just dates as "%F"/"%Y-%m-%d". You can use different separators, but the only thing that will gain you is some confusion. Also SQLite has some built-in functions to work with date in ISO 8601 format.

I believe you can define the conversion somewhere so it will then be used automatically when binding query parameters, but I don't remember where. Manually is guaranteed to work.

edited Jan 14, 2016 at 15:44

answered Jan 14, 2016 at 13:56

Jan Hudec

76.8k13 gold badges138 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ryanmoir Over a year ago

okay so I need to first go through my database and change my dates and then come back to my code and put the dates in that format. Is there anything else I would need to do?(asking instead of doing right now as got lesson so don't have time)

Jan Hudec Over a year ago

@user2711309, I can't think of anything critical. Just keep in mind that SQLite is dynamically typed like python, so the entries you insert will be of the type you bind even if the schema says something else. And watch out for the automatic transactions in the python binding.

giograno · Accepted Answer · 2016-01-14 13:57:21Z

1

sqllite requires date to be in YYYY:MM:DD format. You probably should use strftime with the following parameters:

endweekd   = endweekd.strftime("%Y:%m:%d")
startweekd = startweekd.strftime("%Y:%m:%d")

answered Jan 14, 2016 at 13:57

giograno

1,8294 gold badges21 silver badges30 bronze badges

1 Comment

Jan Hudec Over a year ago

The SQLite functions for date manipulation expect -, not : between date components as per ISO 8601.

Collectives™ on Stack Overflow

python sql where using dates

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related