Bind tuples to parameterized queries with SQLite on Python

sequence_binder.py

"""
Cross-database compatbile SQLite work-arounds for binding sequences to parameterized queries.

Usage:

    bind_pattern, bind_dict = sequence_for_binding( {1, 2, 3} )
    query = f"SELECT * FROM mytable WHERE id IN {bind_pattern}"
    execute(query, bind_dict)

or, for nested sequences:

    bind_pattern, bind_dict = arbitrary_sequence_for_binding( {(1, 2), (3, 4), (5, 6)} )
    query = f"SELECT * FROM mytable WHERE (id1, id2) IN (VALUES {bind_pattern})"
    execute(query, bind_dict)

You can use the arbitrary binder also for flat sequences, with identical results.

Since we control the bind keys and the values are only used with binding, this
should be safe against SQL Injection.

The bind keys are in the :key format (DB-API 'named' bind type), that is
understood both by SQLAlchemy's text() function and by most native Python
database drivers.

Typical usecase:

If you are plauged by errors like this:
    (sqlite3.OperationalError) near "?": syntax error
    (sqlite3.InterfaceError) Error binding parameter 0 - probably unsupported type.

When doing something like this:
    query = "SELECT * FROM mytable WHERE id IN :ids"
    execute(query, ids=(1, 2, 3))

"""

from typing import Dict, Tuple, Any, Sequence


def sequence_for_binding(seq: Sequence[Any], key: str = "i") -> Tuple[str, Dict[str, Any]]:
    """ Map a flat sequence to a bind pattern and a parameter dict. """
    assert key.isalnum() and len(key) < 20, "Rudimentary sanity enforcement for bind-key"
    seq = tuple(seq)  # If seq is some weird type, make sure it doesn't change order as we iterate
    bind_pattern = ",".join(f":{key}" + str(idx) for idx, _ in enumerate(seq))
    bind_dict = {f"{key}" + str(idx): i for idx, i in enumerate(seq)}
    return bind_pattern, bind_dict


def arbitrary_sequence_for_binding(seq: Sequence[Any], key: str = "i", key_idx: int=0) -> Tuple[str, Dict[str, Any], int]:
    """
    Map a flat or nested sequence to a bind pattern a parameter dict.

    For cases like [...] WHERE (x, y) IN (VALUES (1,2), (3,4))

    Will not stop you if you try to map something weird like (1, (2,3), 4, ((5,6), 7), 8).
    """
    bp, bd, _ = _recursive_bind_mapper(seq, key, key_idx)
    return f"{bp[1:-1]}", bd


def _recursive_bind_mapper(seq: Sequence[Any], key: str, key_idx: int) -> Tuple[str, Dict[str, Any], int]:
    """
    Recursively map arbitrarily nested sequences onto a bind pattern and parameter dict.
    """
    assert key.isalnum() and len(key) < 20, "Rudimentary sanity enforcement for bind-key"
    assert key_idx < 10000, "If you end up with more than 10k binds, something is wrong"
    seq = tuple(seq)  # If seq is some weird type, make sure it doesn't change order as we iterate
    bind_pattern = ""
    bind_dict = {}
    for entry in seq:
        if isinstance(entry, (tuple, list, set)):
            bp, bd, ki = _recursive_bind_mapper(entry, key, key_idx)
            key_idx = ki
            if bind_pattern:
                bind_pattern += f", {bp}"
            else:
                bind_pattern = bp

            assert not set.intersection(set(bd.keys()), set(bind_dict.keys())), "Key collision in bd!"
            bind_dict = {**bind_dict, **bd}
        else:
            key_idx += 1
            _k = f"{key}{key_idx}"
            bind_dict[_k] = entry
            if bind_pattern:
                bind_pattern += f", :{_k}"
            else:
                bind_pattern += f":{_k}"

    return f"({bind_pattern})", bind_dict, key_idx
   

Thanks for this, @arvidma! I can't believe more people are complaining about this issue and discussing workarounds on the popular forums. It took me quite a lot of searching to find this gist.

Thanks. If you have suggestions on how make it more findable, I am all ears!

I'll summarize what I was attempting to do and add a link to how I was able to work around it. I'll probably use your solution in the future but my use case was pretty limited and I wound up just using the ORM instead.

I was attempting to use a list or tuple of row IDs to exclude results from a SQLAlchemy text query. It was working fine in Postgres but SQLite3 was complaining about an unsupported type in my test environment.

Here's an example:

excluded_ids = ('some-uuid-abc-xyz')
try:
    query = "select * from figures where id not in (:excluded_ids)"
    results = conn.execute(query, {"excluded_ids": excluded_ids}).fetchall()
except Exception as e:
    print(f"Error: {str(e)}")
    # Exception => Error binding parameter :excluded_ids - probably unsupported type.

Here's a gist of my own where I provide a working example and some potential workarounds.