Search code examples
pythondatabasesqliteexecute

SQLite Python AND statement not working

I'm attempting to pull a list of rows from an SQLite database that are on a given day of the week and a certain hour of that day. When I open my sqlite browser and type the SQL:

SELECT * FROM points
WHERE strftime('%H', checkintime) == '12'
  AND strftime('%w', checkintime) == '1'

It works and I receive all of the rows I should be receiving. However when I attempt to do this in python:

_points.execute("""SELECT * FROM points WHERE strftime('%H', checkintime) == ? AND strftime('%w', checkintime) == ?""", (time, day))

Where time is 12 and day is 1 I get zero results returned. What's even more strange is when I change the execute statement to use hardcoded values, it works.

I've attempted to typecast my vars to both strings and ints but its still a no go. :(

Any suggestions?

Solution

  • How about you handle the string formatting outside of the execute call? For example, 

    sql_statement = """SELECT * FROM points WHERE strftime('%s', checkintime) == %s AND strftime('%s', checkintime) == %s""" % ('%H', time, '%w', day)

_points.execute(sql_statement)

    This should avoid the string mapping that the execute statement is unable to accomplish.

    Best of luck!