encode
where in clause for sqlite
Awesome project!
I want to use something like
select * from tables where col in ('dog', 'cat') in sqlite3
but I don't know how to use it in raw query in databases.
I tried with
query = "select * from tables where col in :values"
query_value = {"values": address_list}
all_rows = await database.fetch_all(query=query, values=query_value)
But it said
sqlite3.OperationalError: near "?": syntax error
Looked up on the web, not too much clue. Appreciate any help.
i used
query = "select * from tables where col in (:values)"
query_value = {"values": ", ".join(address_list)}
which worked on mysql, but when i have multiple items it seems to fail on sqlite
my assumption is that it's actually inserting 'dog, cat' into the brackets, so it won't work.
trying to pass the value as a list is giving a sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type. error
i used
query = "select * from tables where col in (:values)" query_value = {"values": ", ".join(address_list)}which worked on mysql, but when i have multiple items it seems to fail on sqlite
my assumption is that it's actually inserting 'dog, cat' into the brackets, so it won't work. trying to pass the value as a list is giving a
sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.error
Yep, I tried your way, no more complaining about syntax, but actual search failed on sqlite.
here's how i ended up solving it.
# patching list properties here because it doesn't work very well right now
# i hope that in the future it will work a bit better from the package
# however for now it needs to be handled in here.
for list_value_name in [x for x in query_values.keys()]:
# for each item in the query_values dict
# check to see if they are a list like type
# and if the name exists in the query as a param
if isinstance(query_values[list_value_name], (tuple, set, list)) and f":{list_value_name}" in query:
# replace the param with hard coded values (this will only work with numbers
# query values need to be represented as comma sep strings, so [str(x) for x in list] will convert to strings
query = query.replace(f":{list_value_name}", f"({','.join([str(x) for x in query_values[list_value_name]])})")
# then remove the values from the value list
del query_values[list_value_name]
It seems where in clause is not supported by asyncpg as per https://github.com/MagicStack/asyncpg/issues/94
