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

[Random832]

On Wed, May 22, 2019, at 09:58, Inada Naoki wrote:
> Placeholders may be substituted by server.
> So we can't force to allow multiple values in one placeholder.

Can the placeholder be substituted for multiple placeholders by the client side, which are then further individually substituted by the server?

Like, the pattern I would consider correct for this with today's api is:

myset = { ... }
cur.execute(f"... x in ({','.join('?'*len(myset))}) ...", (..., *myset, ...))

[this may not behave correctly on some databases when the set is empty, but on many databases (null) will not match any values including null, and therefore could be substituted instead]

The main sticking point for this sort of thing in other languages is that DB layers often have a prepare method, and prepared queries as first-class objects, which often does not allow the actual query text executed to change based on the values of the parameters. But no such concept exists in DB-API (executemany does say it prepares once, but that is explicitly an implementation detail).

> (For example, MySQL allow only single value for one ?, while
> MySQLdb doesn't use MySQL's prepared statement at the moment.)
> 
> DB-API is low level interface.
> Higher level library (like SQLAlchemy) provides better API for
> building query.