crate / crate-python

Python DB API client library for CrateDB, using HTTP.
https://cratedb.com/docs/python/
Apache License 2.0
78 stars 31 forks source link

SQLAlchemy: Support `INSERT...VALUES` with multiple value sets #538

Closed amotl closed 1 year ago

amotl commented 1 year ago

Enable supports_multivalues_insert on the CrateDB dialect, mitigating the error reported at crate/sqlalchemy-cratedb#101:

CompileError: The 'crate' dialect with current database version
settings does not support in-place multirow inserts

The Insert construct also supports being passed a list of dictionaries or full-table-tuples, which on the server will render the less common SQL syntax of "multiple values" - this syntax is supported on backends such as SQLite, PostgreSQL, MySQL, but not necessarily others.

It is essential to note that passing multiple values is NOT the same as using traditional executemany() form. The above syntax is a special syntax not typically used. To emit an INSERT statement against multiple rows, the normal method is to pass a multiple values list to the Connection.execute() method [^1], which is supported by all database backends and is generally more efficient for a very large number of parameters [^2].

-- https://docs.sqlalchemy.org/core/dml.html#sqlalchemy.sql.expression.Insert.values.params.*args

[^1]: Introduction to the traditional Core method of multiple parameter set invocation for INSERTs and other statements [^2]: With this method, there is curently an issue reported at crate/sqlalchemy-cratedb#97.

/cc @SStorm, @WalBeh, @seut, @hlcianfagna