Toni_Entranced Toni_Entranced - 2 months ago 13
Python Question

Python SQLITE substituted query returns nothing from database

I am trying to execute the following query:

sqlite> select * from history where timeStamp >= "2016-09-15 13:05:00" and timeStamp < "2016-09-15 13:06:00";
timeStamp isOpen
------------------- ----------
2016-09-15 13:05:04 0
2016-09-15 13:05:09 0
2016-09-15 13:05:14 1
2016-09-15 13:05:19 1
2016-09-15 13:05:24 1
2016-09-15 13:05:29 1
2016-09-15 13:05:34 1
2016-09-15 13:05:39 0
2016-09-15 13:05:44 1
2016-09-15 13:05:49 1
2016-09-15 13:05:54 1
2016-09-15 13:05:59 0


From Postman I run:
{{origin}}:{{port}}/logs?from=201609151305&to=201609151306


In Python, I translate those values to:
2016/09/15 13:05:00
and

2016/09/15 13:06:00
, which are passed to my helper method:

vals = (lowStr, upperStr)
query = 'select * from history where timeStamp >= ? and timeStamp < ?'
returnList = accessDB('SELECT',query,vals)


AccessDB then does the following:

def accessDB(operation, query, vals):
con = None
try:
con = sqlite3.connect('logs.db')
cur = con.cursor()
cur.execute(query, vals)
if operation == 'SELECT':
return cur.fetchall()
if operation == 'INSERT':
con.commit()
except sqlite3.Error as e:
print("Error %s:" % e.args[0])
sys.exit(1)
finally:
if con:
con.close()


Nothing is being returned in the results, however. The return list is empty. What am I doing wrong?

Answer

In your example SQL, you are using datetimes with the format

YYYY-MM-DD HH:MM:SS

In your Python example, however, you are using datetimes with the format

YYYY/MM/DD HH:MM:SS

SQLite isn't seeing this as a valid date format. Change your /s to -s (how to do that depends on how you're doing the formatting).