Python SQLITE substituted query returns nothing from database

Question

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?

Are you sure SQLite can understand those date formats? They don't match the format used in your raw query. — dirn
– dirn, Commented Sep 16, 2016 at 0:52
Well...that's an embarrassment. Thanks for catching that. You should go ahead and post an answer. Related question: Is there a way to view the raw query that is generated from the cursor.execute method? Like SQL's .last_executed, for instance. — Toni_Entranced
– Toni_Entranced, Commented Sep 16, 2016 at 0:57
I'm not entirely sure what your related question is about. Your query is contained in your query variable. Are you looking for something more than that? — dirn
– dirn, Commented Sep 16, 2016 at 3:01
I was just worried that cursor.execute may have done something to the final string that it will send to SQLITE. I want to see what that is (for debugging purposes). — Toni_Entranced
– Toni_Entranced, Commented Sep 16, 2016 at 3:03

dirn · Accepted Answer · 2016-09-16 03:03:39Z

1

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

answered Sep 16, 2016 at 3:03

dirn

21k7 gold badges75 silver badges76 bronze badges

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

Collectives™ on Stack Overflow

Python SQLITE substituted query returns nothing from database

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related