SQLITE3 How to quickly and efficiently query this?

Question

I'm trying to compare a local variable

currentTime = time.time()

with data in my table, under the AuthorID column, to see which value is bigger. Then, I will proceed to update the column 'Reminded' to True. My table is called reminderFormat.

This is how the Database looks: http://prntscr.com/gb9ay1

What is the best way to go about this? I believe I could specifically fetch FutureTime and store it as a variable, then compare it to currentTime. However, would there be a better option for this which is quicker and more efficient using a query?

Thanks

See [How to format SQL tables in a Stack Overflow post?](https://meta.stackexchange.com/q/96125) — CL., Aug 21 '17 at 16:35

Laurent LAPORTE · Accepted Answer · 2017-08-21T16:49:33.780

1

I think, the best practice is to use a single SQL statement to update the records.

Since, SQLite has no boolean value, you need to set the value 1 (instead of True).

For instance, the statement could be:

import time

st = "UPDATE reminderFormat SET Reminded = 1 " \
     "WHERE FutureTime <= {currentTime}".format(currentTime=time.time())

EDIT: Demo

Here is a demo:

import time
import sqlite3

records = [
    (20183740995, 1503330725.0, "testtt", 0),
    (20183740995, 1503330732.0, "testtt", 0),
    (20183740995, 1503331334.0, "testtt", 0),
    (20183740995, 1509999999.0, "testtt", 0),
    ]

con = sqlite3.connect(":memory:")

# Create the table
con.execute("create table reminderFormat(AuthorID, FutureTime, Message, Reminded)")

# Fill the table
con.executemany("INSERT INTO reminderFormat(AuthorID, FutureTime, Message, Reminded) VALUES (?, ?, ?, ?)", records)

curr_time = time.time()
con.execute("UPDATE reminderFormat SET Reminded = 1 WHERE FutureTime <= ?", (curr_time,))

# Print the table contents
for row in con.execute("SELECT AuthorID, FutureTime, Reminded FROM reminderFormat"):
    print(row)

You get:

(20183740995, 1503330725.0, 1)
(20183740995, 1503330732.0, 1)
(20183740995, 1503331334.0, 1)
(20183740995, 1509999999.0, 0)

edited Aug 21 '17 at 16:49

answered Aug 21 '17 at 16:34

Laurent LAPORTE

21,958
6
58
103

See [How to use variables in SQL statement in Python?](https://stackoverflow.com/questions/902408/how-to-use-variables-in-sql-statement-in-python) – CL. Aug 21 '17 at 16:37
@CL: Agree, but I don't know which client library used the OP, so… – Laurent LAPORTE Aug 21 '17 at 16:39
Worked very well - thank you. For some reason, If I remove the , in (curr_time,) I get an error. Also, I needed to commit to the flle to get it to save. Thanks – smye Aug 21 '17 at 17:41
@smye parameters are passed by `tuple`. So, the trailing comma means this is a `tuple` singleton. Without a comma, it is a scalar. – Laurent LAPORTE Aug 21 '17 at 19:10
@smye Anything else? – Laurent LAPORTE Aug 23 '17 at 17:15

score -2 · Answer 2 · edited Aug 24 '17 at 17:50

-2

In sqlite3 you can use a Case statement in query to check if your current time is less than or greater than the AuthorID for this your query in sqlite3 would be something like this:

select case AuthorID<Futuretime then 1 else 0 end from table;

This should provide you with 1 and 0 in the column without having to fetch time to python.

edited Aug 24 '17 at 17:50

Joe Mayo

7,501
7
41
60

answered Aug 21 '17 at 16:33

Daniyal Ahmed

715
5
11

SQLITE3 How to quickly and efficiently query this?

2 Answers2