Batch insert with nested columns does not work as expected

[erosennin]

Describe the bug There seems to be no way to specify the values of nested columns when inserting data.

To Reproduce

import sqlalchemy as sa
from clickhouse_sqlalchemy import engines, types, select

engine = sa.create_engine("clickhouse+native://localhost/default", echo=True)

metadata = sa.MetaData()

family = sa.Table(
    "family",
    metadata,
    sa.Column("id", types.UInt32, primary_key=True),
    sa.Column("members", types.Nested(sa.Column("age", types.UInt32))),
    engines.Memory(),
)

metadata.create_all(engine)
engine.execute(family.insert(), [{"id": 1, "members.age": [7, 31, 33]}])
print(engine.execute(select([family.c.id, family.c.members.age])).fetchall())

The nested column members.age is ignored:

2020-08-17 13:50:26,775 INFO sqlalchemy.engine.base.Engine INSERT INTO family (id) VALUES
2020-08-17 13:50:26,775 INFO sqlalchemy.engine.base.Engine ({'id': 1})
2020-08-17 13:50:26,779 INFO sqlalchemy.engine.base.Engine SELECT family.id, family.members.age 
FROM family
2020-08-17 13:50:26,780 INFO sqlalchemy.engine.base.Engine {}
[(1, [])]

Expected behavior

2020-08-17 13:50:26,775 INFO sqlalchemy.engine.base.Engine INSERT INTO family (id, members.age) VALUES
2020-08-17 13:50:26,775 INFO sqlalchemy.engine.base.Engine ({'id': 1, 'members.age': [7, 31, 33]})
2020-08-17 13:50:26,779 INFO sqlalchemy.engine.base.Engine SELECT family.id, family.members.age 
FROM family
2020-08-17 13:50:26,780 INFO sqlalchemy.engine.base.Engine {}
[(1, [7, 31, 33])]

Versions

• clickhouse-sqlalchemy 0.1.4
• Python 3.8.3

[xzkostyan]

It seems that there are some deep problems with Nested columns.

If you want it "just work" right now you can replace Nested with list of Array columns. But, accessing these columns with __getattr__ will be painful:

import sqlalchemy as sa
from clickhouse_sqlalchemy import engines, types, select

engine = sa.create_engine("clickhouse+native://localhost/default", echo=True)

metadata = sa.MetaData()

family = sa.Table(
    "family",
    metadata,
    sa.Column("id", types.UInt32, primary_key=True),
    sa.Column("members.age", types.Array(types.UInt32)),
    engines.Memory(),
)

metadata.create_all(engine)
engine.execute(family.insert(), [{"id": 1, "members.age": [7, 31, 33]}])
print(engine.execute(select([family.c.id, getattr(family.c, 'members.age')])).fetchall())

[LightningOrionis]

You need to bind metadata and engine + add databasename