`KeyError` when inserting list of dictionaries with SQLAlchemy core

[pankotsias]

Describe the bug Using a (standard) batch insertion with sqlalchemy, i.e. a list of dictionaries with each dict corresponding to a new row, results in an error.

To Reproduce

from sqlalchemy import create_engine, Column, MetaData, func
from clickhouse_sqlalchemy import (
    make_session, get_declarative_base, types, engines
)
from datetime import date

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import insert

uri = 'clickhouse+native://clickhouse-server/default'

engine = create_engine(uri)
session = make_session(engine)
metadata = MetaData(bind=engine)

Base = get_declarative_base(metadata=metadata)

class MyTable(Base):
    day = Column(types.Date, primary_key=True)
    value = Column(types.Int32)

    __table_args__ = (
        engines.Memory(),
    )

# create the table
MyTable.__table__.create()

# create some dummy data
data = [
    dict(day=date(2019, 1, 1), value=1),
    dict(day=date(2019, 1, 2), value=2),
    dict(day=date(2019, 1, 3), value=3),
    dict(day=date(2019, 1, 4), value=4),
    dict(day=date(2019, 1, 5), value=5),
]

# insert
stmt = insert(MyTable).values(data)
session.execute(stmt)

results in:

KeyError                                  Traceback (most recent call last)
Cell In [4], line 2
      1 stmt = insert(MyTable).values(data)
----> 2 session.execute(stmt)

File /usr/local/lib/python3.10/site-packages/sqlalchemy/orm/session.py:1714, in Session.execute(self, statement, params, execution_options, bind_arguments, _parent_execute_state, _add_event, **kw)
   1712 else:
   1713     conn = self._connection_for_bind(bind)
-> 1714 result = conn._execute_20(statement, params or {}, execution_options)
   1716 if compile_state_cls:
   1717     result = compile_state_cls.orm_setup_cursor_result(
   1718         self,
   1719         statement,
   (...)
   1723         result,
   1724     )

File /usr/local/lib/python3.10/site-packages/sqlalchemy/engine/base.py:1705, in Connection._execute_20(self, statement, parameters, execution_options)
   1701     util.raise_(
   1702         exc.ObjectNotExecutableError(statement), replace_context=err
   1703     )
   1704 else:
-> 1705     return meth(self, args_10style, kwargs_10style, execution_options)
...
    190         new_data.append(
    191             self._pure_mutate_dicts_to_rows(row[name], cwt, check_row_type)
    192         )

KeyError: 'day'

Expected behavior The code above works flawlessly when sqlite backend is used, i.e. uri = sqlite:////mydata.db.

Versions

• package versions: sqlalchemy-1.4.42, clickhouse_sqlalchemy-0.2.2, clickhouse_driver-0.2.4
• python version: 3.10.8