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

[amotl]

Hi,

when evaluating loading Dask DataFrames into CrateDB using the method="multi" option, the program croaks.

Traceback (most recent call last):
  File "/path/to/lib/python3.10/site-packages/pandas/io/sql.py", line 1325, in insert_records
    return table.insert(chunksize=chunksize, method=method)
  File "/path/to/lib/python3.10/site-packages/pandas/io/sql.py", line 946, in insert
    num_inserted = exec_insert(conn, keys, chunk_iter)
  File "/path/to/lib/python3.10/site-packages/pandas/io/sql.py", line 869, in _execute_insert_multi
    result = conn.execute(stmt)
  File "/path/to/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1414, in execute
    return meth(
  File "/path/to/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 486, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/path/to/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1630, in _execute_clauseelement
    compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
  File "/path/to/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 665, in _compile_w_cache
    compiled_sql = self._compiler(
  File "/path/to/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 291, in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
  File "/path/to/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 1269, in __init__
    Compiled.__init__(self, dialect, statement, **kwargs)
  File "/path/to/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 710, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "/path/to/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 755, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/path/to/lib/python3.10/site-packages/sqlalchemy/sql/visitors.py", line 143, in _compiler_dispatch
    return meth(self, **kw)  # type: ignore  # noqa: E501
  File "/path/to/lib/python3.10/site-packages/sqlalchemy/sql/compiler.py", line 5350, in visit_insert
    raise exc.CompileError(
sqlalchemy.exc.CompileError: The 'crate' dialect with current database version settings does not support in-place multirow inserts.

With kind regards, Andreas.

[amotl]

I think those are the relevant pointers to the corresponding SQLAlchemy documentation.

• https://docs.sqlalchemy.org/glossary.html#term-executemany
• https://docs.sqlalchemy.org/core/connections.html#engine-insertmanyvalues
• https://docs.sqlalchemy.org/orm/queryguide/dml.html#orm-queryguide-bulk-insert
• https://docs.sqlalchemy.org/orm/queryguide/dml.html#orm-queryguide-legacy-bulk-insert

A minimal reproducible example, using pandas, is:

import pandas as pd
import sqlalchemy as sa

df = pd.DataFrame.from_records([{"foo": "baz", "bar": "qux"}])
engine = sa.create_engine("crate://localhost:4200", echo=True)
df.to_sql(name="foo", con=engine, if_exists="replace", index=False, method="multi")

A corresponding example using SQLAlchemy only, is [^1]:

import sqlalchemy as sa

metadata = sa.MetaData()
table = sa.Table(
    "testdrive",
    metadata,
    sa.Column("foo", sa.String),
)
insertable = table.insert().values([{"foo": "bar"}])

engine = sa.create_engine("crate://", echo=True)
table.drop(bind=engine, checkfirst=True)
table.create(bind=engine)
with engine.begin() as conn:
    conn.execute(insertable)

While working on this, crate/sqlalchemy-cratedb#97 has also been discovered, which is related, but not the same.

[^1]: A full program can be found at sqlalchemy_efficient_inserts.py.

[amotl]

There is now a fix for this issue.

• crate/crate-python#538

[seut]

Looks like it is fixed.