Passing a list to an IN clause for SQLite

[arieroos]

How would I go about setting up a query like this?

ids = [1, 17, 202]
result = commands.query(
        """
            SELECT * FROM template WHERE id IN ?ids?
        """,
        model=Template,
        param={
            'ids': ids
        }

Of course this won't work for Sqlite, because sqlite doesn't support list bindings.

So I referred to this stack overflow thread: https://stackoverflow.com/questions/1309989/parameter-substitution-for-a-sqlite-in-clause

And then tried to do this:

ids = [1, 17, 202]
result = commands.query(
        f"""
            SELECT * FROM template WHERE id IN ({','.join('?'*len(ids))}) 
        """,
        model=Template,
        param=ids
    )

Still no luck.

What should I do here?

[arieroos]

Here is what I did:

ids = [1, 17, 202]

param_names = [f'?id_{id}?' for id in ids]
result = commands.query(
        f"""
            SELECT * FROM template WHERE id IN ({','.join(param_names)}) 
        """,
       model=Template,
        param={f'id_{id}': id for id in ids}
    )

This seems to work

[zschumacher]

If ids is a tuple and you bind the params, does that work for you? I'd be surprised if sqlite can't handle binding a tuple

[arieroos]

No ,SQLite doesn't allow tuple binding either: sqlite3.ProgrammingError: Error binding parameter 1: type 'tuple' is not supported

There is just no direct parameter substitution for collections in the SQLite C library. That lack of binding parameter then just boils up to the Python implementation. I'm not sure how Dapper handles this in C#, I couldn't find anything about it through Google.

[zschumacher]

Hmmm okay. I'm looking at 1.0ing this lib this year so additional type binding is something I can think about