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

[M.-A. Lemburg]

Hi Skip,

On 22.05.2019 15:46, Skip Montanaro 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.
> 
> A quick check on bugs.python.org as well as Google yielded no obvious
> earlier discussion. Is it simply that underlying database drivers are
> unable to support this or was DB-API 2.0 frozen (no pun intended)
> before sets were available or in wide use in Python?
> 
> Set support and the SQL "in" operator would seem to be handy, both as
> a convenience and as a barrier to SQL injection errors.

The DB-API provides freedom to drivers to support many different data
types, so it's really up to the driver whether can deal with sets,
lists, generators, complex numbers, geo data objects, etc. etc. in
the data and how they map these to the binding variables.

This is intentional to allow database drivers to expose as much
functionality support by the database backend as they can.

In your use case, the above would be a request to raise with
the sqlite3 driver maintainers. It certainly sounds like a useful
addition, if SQLite's API has support for binding variables to
IN clauses and can accept arrays with values in the API.

In many cases, it should be possible to convert sets or lists
to comma separated quoted value strings and then pass these to
the database.

Thanks,
-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Services directly from the Experts (#1, May 22 2019)
>>> Python Projects, Coaching and Consulting ...  http://www.egenix.com/
>>> Python Database Interfaces ...           http://products.egenix.com/
>>> Plone/Zope Database Interfaces ...           http://zope.egenix.com/
________________________________________________________________________

::: We implement business ideas - efficiently in both time and costs :::

   eGenix.com Software, Skills and Services GmbH  Pastor-Loeh-Str.48
    D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
           Registered at Amtsgericht Duesseldorf: HRB 46611
               http://www.egenix.com/company/contact/
                      http://www.malemburg.com/