0

I'll try to be as a clear as possible. I'm new with Db.

OS = Windows 7
Python ver = 2.7.10
SQLite ver = 3.8.11.1

I have the following table in a SQLite DB:

CREATE TABLE job_logs(ID INTEGER PRIMARY KEY, eventcount INTEGER NOT NULL,
                      content TEXT, timestamp CHAR(25) NO NULL);

The table looks like this:

1 | 1 | blabblablabla..blab | Mon May 15 07:22:59 2006
1 | 2 | blabblablabla..blab | Mon May 15 09:22:30 2006
...
2 | 1 | blabblablabla..blab | Mon May 20 07:22:59 2006
2 | 2 | blabblablabla..blab | Mon May 21 09:26:00 2006
..

I want to retrieve the timestamps for the first and last eventcount with ID=301.

In sqlite3 shell, I typed the following queries and I got the correct values:

sqlite>SELECT ID, min(eventcount), timestamp FROM job_logs WHERE ID = 301;
301|1| Mon May 15 07:22:59 2006
sqlite>SELECT ID, max(eventcount), timestamp FROM job_logs WHERE ID = 301;
301|52| Mon Jun  5 16:04:12 2006

Ok. So I wrote the following python script, but I didn't get the same result:

import sqlite3
db_dir = <db location>
db = sqlite3.connect(db_dir)

cursor = db.cursor()
cursor.execute("SELECT ID, min(eventcount), timestamp FROM job_logs WHERE ID = 301;")
min = cursor.fetchone()
print min
(301, 1, u'Mon Jun  5 16:04:12 2006')

cursor.execute("SELECT ID, max(eventcount), timestamp FROM job_logs WHERE ID = 301;")
max = cursor.fetchone()
print max
(301, 52, u'Mon Jun  5 16:04:12 2006')

As you can see both queries return have the timestamp value of max(eventcount) and this is the problem.

This is the first time that I see this kind of behavior. Note that the eventcount value is correct, only the timestamp value is wrong. I verified the database and everything seems fine and the db gave me the correct results if I ran the query on the SQLite shell.

Also, I tried this query but I got the same result:

cursor.execute("SELECT ID, min(eventcount), timestamp FROM job_logs WHERE ID = 301 GROUP BY ID;")
Ed Rivera
  • 59
  • 7
  • 1
    The SQLite library used by your Python is older than 3.7.11. – CL. May 11 '16 at 08:50
  • @CL Thanks for letting me know how that I need a newer python version. But I don't see how this question is a duplicate of "How can I select the set of rows...". My question wasn't about how to make the query, but about the discrepancy of both returns... Anyway thanks. – Ed Rivera May 11 '16 at 15:11
  • I installed python 3.4.4 and ran the script. It worked as expected. – Ed Rivera May 11 '16 at 15:58

0 Answers0