Sqlite where query in Python on integer value field not working

Question

I am trying to query a SQLite database for the rows that satisfy the condition cur.execute("SELECT * FROM data WHERE 'FirstPacketTime' > 1570680300").

I have looked through multiple stackoverflow questions and other online resources. In this case the FirstPacketTime db field is defined as an integer datatype (datetime in seconds). The row results are also coming back as integers, however the row data contains rows where the FirstPacketTime values are clearly less than the value shown above. Changing the greater than to an equal or less than ends up with no query results. What am I missing here? I have done queries before with python against MySQL databases with no issue.

Database schema

(0, 'FirstPacketTime', 'integer', 0, None, 0)
(1, 'SourceIP', 'text', 0, None, 0)
(2, 'SourcePort', 'integer', 0, None, 0)
(3, 'DestinationIP', 'text', 0, None, 0)
(4, 'DestinationPort', 'integer', 0, None, 0)
(5, 'Protocol', 'text', 0, None, 0)
(6, 'TotalBytes', 'integer', 0, None, 0)
(7, 'TotalPackets', 'integer', 0, None, 0)

Query results - first 10 records

(1570676279, '19.116.151.212', 9876, '19.116.0.157', 53299, 'tcp_ip', 56, 1)
(1570676279, '19.116.151.212', 9876, '19.116.0.157', 53301, 'tcp_ip', 56, 1)
(1570650779, '19.116.1.36', 53497, '19.116.160.133', 102, 'tcp_ip', 67799, 696)
(1570676339, '19.116.89.20', 3139, '19.116.29.147', 445, 'tcp_ip', 96, 2)
(1570676339, '19.116.89.20', 3479, '19.116.29.189', 445, 'tcp_ip', 96, 2)
(1570676339, '19.116.89.17', 3843, '19.116.29.33', 445, 'tcp_ip', 96, 2)
(1570676339, '19.116.89.24', 2398, '19.116.29.6', 445, 'tcp_ip', 96, 2)
(1570676339, '19.116.89.20', 3206, '19.116.29.159', 445, 'tcp_ip', 96, 2)
(1570676339, '19.116.89.20', 3161, '19.116.29.151', 445, 'tcp_ip', 96, 2)
(1570676339, '19.116.89.15', 1082, '19.116.0.16', 445, 'tcp_ip', 96, 2)

Code

cur.execute("SELECT * FROM data WHERE 'FirstPacketTime' > 1570680300")
rows = cur.fetchmany(10)

Remove the single quotes: SELECT * FROM data WHERE FirstPacketTime > 1570680300 — forpas
– forpas, Commented Oct 31, 2019 at 18:31
To add to forpas's comment, see sqlite.org/lang_keywords.html. Single quotes delimit strings, not database object names. — C Perkins
– C Perkins, Commented Oct 31, 2019 at 18:36
I do have data that I expect to be returned. I have also tried setting the criteria to return the FirstPacketTime equal to the value 1570676279 (first one shown above in the query results) and get nothing back. removing the single quotes from the fieldname results in nothing coming back. — rjs
– rjs, Commented Oct 31, 2019 at 18:40

roshan ok · Accepted Answer · 2019-10-31 19:03:17Z

2

A sample db created

Below query was executed, and was able to get the records

import sqlite3
conn = sqlite3.connect('test.db')
c = conn.cursor()
print (c.execute("SELECT * FROM test WHERE FirstPacketTime < 1570676280").fetchall())
print (c.execute("SELECT * FROM test WHERE FirstPacketTime = 1570676279").fetchall())
print (c.execute("SELECT * FROM test WHERE FirstPacketTime = 1570676339").fetchall())

[(1570676279, '19.116.151.212', 9876)]
[(1570676279, '19.116.151.212', 9876)]
[(1570676339, '19.116.89.20', 3139)]

What i did is Removed the single quotes.

Also , initially it didnt appear for me , because i had not committed the data to database.

Once the data was commited to the database , i was able to get the output

answered Oct 31, 2019 at 19:03

roshan ok

3831 silver badge6 bronze badges

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

Collectives™ on Stack Overflow

Sqlite where query in Python on integer value field not working

Database schema

Query results - first 10 records

Code

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Database schema

Query results - first 10 records

Code

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related