where in clause for sqlite

[allanwakes]

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.

[bendog]

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

[allanwakes]

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.

[bendog]

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]

[ghost]

I'm having a similar issue with asyncpg

[conservative-dude]

It seems where in clause is not supported by asyncpg as per https://github.com/MagicStack/asyncpg/issues/94