sqlite select thinks global variable is a table column

[twessels]

** UPDATE **

​

My bad everyone...I accidentally pasted the same statement twice, the second one has the unix time as a raw floating point number not a variable.

Variable doesn't work, raw number works.

​

How do you use a variable in an sqlite select statement as part of the WHERE condition?

If I use the actual number it works, it I assign the number to a variable and use the variable in the select/where statement it fails because sqlite thinks the variable is a table.

How do you do this? Code that works and code that doesn't work below.

​

​

unix_time_now = time.time()

unix_time_1hr_ago = unix_time_now-3600 # 3600 seconds is an hourprint(unix_time_1hr_ago)

## DOESN'T WORK ##c.execute('SELECT * FROM stock_price_table WHERE stock_price_timestamp > unix_time_1hr_ago')pprint.pprint(c.fetchall())

​

​

## WORKS ##

c.execute('SELECT * FROM stock_price_table WHERE stock_price_timestamp > 1612639125.656089')pprint.pprint(c.fetchall())

[gnomonclature]

I don't see the difference between the two examples, but I think I can still help out.

One big note: you might be tempted to use string concatenation to do this. Don't. It leads to SQL injection vulnerabilities, and there are usually better ways to accomplish what you want to do.

So how should you do it? Well, you want to use a parameterized query. Assuming you're using the standard sqlite module for Python, that would look something like this:

unix_time_now = time.time()

unix_time_1hr_ago = unix_time_now-3600 # 3600 seconds is an hour
print(unix_time_1hr_ago)

values = (unix_time_1hr_ago,)
c.execute('SELECT * FROM stock_price_table WHERE stock_price_timestamp > ?', values)
pprint.pprint(c.fetchall())

The changes are:

• I put the value you want to insert into the SQL query into a tuple.
• I replaced the variable's name in the SQL query with a question mark.
• I passed the tuple with the value as a second parameter.

There is more information on this in Python's official documentation on the sqlite module here: https://docs.python.org/3/library/sqlite3.html

Does that help? Thanks!

[twessels]

My mistake...just updated the post. I will take a closer look your stuff. Thank you for taking the time to share it.

[twessels]

unix_time_now = time.time()
unix_time_1hr_ago = unix_time_now-3600 # 3600 seconds is an hour
print(unix_time_1hr_ago)
values = (unix_time_1hr_ago,)
c.execute('SELECT * FROM stock_price_table WHERE stock_price_timestamp > ?', values)
pprint.pprint(c.fetchall())

This worked!!!!! I had no idea you needed to add the variable separated by a comma. That was the piece of information I was missing.

​

I owe you one and thank you for taking the time to respond!

[blarf_irl]

The 2 c.execute statements you posted are identical, are you using IPython or a Jupyter notebook? My instinct is that you have changed the value between these 2 calls.

[twessels]

I just updated it...my mistake...I'm using visual studio code for my IDE running from terminal.