I'm tryng to do something like this..
con = sqlite3.connect('tables.db')
cur = con.cursor()
max_tyme = '2022-06-22 17:14:01.048'
cur.execute(f'''SELECT A.ID, B, A, tyme
FROM A, BD
WHERE A.ID = BD.ID
AND tyme > {max_tyme}''')
Resulting in this error: Traceback (most recent call last): File "d:\MEGA\test\test.py", line 11, in cur.execute(f'''SELECT A.ID, B, A, tyme sqlite3.OperationalError: near "17": syntax error
When I try..
con = sqlite3.connect('tables.db')
cur = con.cursor()
max_tyme = '2022-06-22 17:14:01.048'
cur.execute(f'''SELECT A.ID, B, A, tyme
FROM A, BD
WHERE A.ID = BD.ID
AND tyme > '2022-06-22 17:14:01.048' ''')
It works as it's supposed to, but I really need to use a variable. As I can see, the problem is whitespace, but I can't change the format of datetime.
Please help! Thanks!
This is possible, but not in the way you have implemented right now.
The first thing to get out of the way is to never, ever, ever use fstrings for database queries. This opens you up to a form of attack called SQL Injection (read more here)
So what's happening here?
Your current code using fstrings takes the value of the variable and stitches it into the string that is sent to the cur.execute function. This means that the string being sent to the database is:
'''SELECT A.ID, B, A, tyme
FROM A, BD
WHERE A.ID = BD.ID
AND tyme > 2022-06-22 17:14:01.048'''
(note the absence of quotes around the time representation). This is then not recognised by the database and leads to the error you are seeing.
How do you fix it? By using input sanitisation. sqlite3 uses the '?' symbol to represent it's sanitised variables, so the code you would need to use is:
con = sqlite3.connect('tables.db')
cur = con.cursor()
max_tyme = '2022-06-22 17:14:01.048'
cur.execute(f'''SELECT A.ID, B, A, tyme
FROM A, BD
WHERE A.ID = BD.ID
AND tyme > ?''', (max_tyme,))
This should give the same output as when you made the db call manually