I'm trying to retrieve a boolean state from my database from the column name which is the same as the variable reward_number and the userid is the same as user_id[0]. However, every time I do this, I get this error:
sqlite3.OperationalError: near "1": syntax error
Code:
cursor.execute("SELECT ({}) FROM rewards WHERE userId=?".format(reward_number),
(user_id[0],))
state = cursor.fetchone[0]
In case of the value of reward_number is Reward 1 without any additional quotation marks, the database cannot parse your query: it cannot guess that this should be a single token Reward 1, not two different tokens Reward and 1, because it has no any external data, just plain sql text. So this leads to parsing error.
To make this work you have two possibilities:
This is how they can be quoted. Unfortunately, it is not described directly and clearly about cases different from keywords, but it works the same. Also you can see that identifiers are case-insensitive regardless if they are quoted or not:
create table t as select 1 as `column name with space`✓
select `column name with space` from t| column name with space | | ---------------------: | | 1 |
select `Column name with space` from t| column name with space | | ---------------------: | | 1 |
select [column name with space] from t| column name with space | | ---------------------: | | 1 |
select "Column name with space" from t| column name with space | | ---------------------: | | 1 |
db<>fiddle here
So the code should be:
cursor.execute("SELECT (`{}`) FROM rewards WHERE userId=?".format(reward_number),
(user_id[0],))
reward_number?