[Python-ideas] DB-API support for sets?

[Chris Angelico]

On Wed, May 22, 2019 at 11:47 PM Skip Montanaro
<skip.montanaro at gmail.com> wrote:
>
> The DB-API doesn't support sets directly, so you wind up having to
> manually expand them:
>
> >>> curs.execute("select count(*) from sometable where somecol in ?", ({4126,11638},))
> Traceback (most recent call last):
>   File "<stdin>", line 1, in <module>
> sqlite3.OperationalError: near "?": syntax error
> >>> curs.execute("select count(*) from sometable where somecol in (?)", ({4126,11638},))
> Traceback (most recent call last):
>   File "<stdin>", line 1, in <module>
> sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.
>
> >>> curs.execute("select count(*) from sometable where somecol in (11638, 4126)")
> <sqlite3.Cursor object at 0x7f8ef5f6c570>
>
> In the example above, I'm able to skip input validation because the
> element values in the set are ints, but programmers being the lazy
> sort that they are, when such values are strings there's probably also
> the tendency to skip argument escaping.

I don't know whether sqlite3 supports this kind of operation.
PostgreSQL/psycopg2 does, in a slightly different form:

>>> import psycopg2
>>> db = psycopg2.connect("") # assumes database on localhost
>>> cur = db.cursor()
>>> cur.execute("select * from generate_series(1, 6)")
>>> list(cur)
[(1,), (2,), (3,), (4,), (5,), (6,)]
>>> cur.execute("select * from generate_series(1, 6) where generate_series = any(%s)", (list({1, 4, 2, 8}),))
>>> list(cur)
[(1,), (2,), (4,)]

With current psycopg2 builds, you have to convert the set into a list.
It would be a relatively simple feature request (I hope!) to add
support for sets the same way.

Are you able to do an "any" operation with sqlite3? That might be
easier to represent (PostgreSQL sees this as an ARRAY value, which can
be compared in this way).

ChrisA