Reputation: 1053
I am using Python and have built a table in sqlite. I didn't realize Python has a timestamp field type for sqlite, so I stored it as text.
Below is my code and the result.. the only dates shown in the tables are for 2019-06-18
but the query returns for 2019-06-19
... no idea why
import sqlite3
# Connecting to the database file
sqlite_file = 'insta.sqlite'
conn = sqlite3.connect(sqlite_file)
c = conn.cursor()
# Get all rows
c.execute("SELECT * FROM daily_counts")
count_row = c.fetchall()
for row in count_row:
print(row)
# Get Daily Count
c.execute('SELECT count FROM daily_counts where current_date = ? and client_id = ? and metric = ?',
('2019-06-18', 0, 'C'))
count_row = c.fetchall()
print(count_row)
# Get Daily Count
c.execute('SELECT count FROM daily_counts where current_date = ? and client_id = ? and metric = ?',
('2019-06-19', 0, 'C'))
count_row = c.fetchall()
print(count_row)
# Get Daily Count
c.execute('SELECT count FROM daily_counts where current_date = ? and client_id = ? and metric = ?',
('2019-06-20', 0, 'C'))
count_row = c.fetchall()
print(count_row)
Results: (note that when the condition is the 19th it returns the results for the 18th, and the 18th returns no results..)
(0, u'2019-06-18', u'A', 2)
(0, u'2019-06-18', u'B', 6)
(1, u'2019-06-18', u'C', 180)
(1, u'2019-06-18', u'D', 258)
(1, u'2019-06-18', u'E', 111)
(0, u'2019-06-18', u'C', 180)
(1, u'2019-06-18', u'F', 3)
[]
[(180,)]
[]
I assume I should be using the timestamp field type instead of text? But I would still expect this to work
Upvotes: 0
Views: 68
Reputation: 6520
current_date
is a sqlite reserved word.
Add double-quotes or backticks to the column name (eg "current_date"
) in the sql strings and you should get the expected result.
Upvotes: 1