clickhouse_sqlalchemy_declarative_table_create_insert_query.py

1from sqlalchemy import create_engine, Column, MetaData
2from clickhouse_sqlalchemy import types, engines, Table, make_session
3from sqlalchemy.ext.declarative import declarative_base
4
5# 1. Define the connection string (using the native protocol)
6uri = 'clickhouse+native://localhost/default'
7engine = create_engine(uri)
8session = make_session(engine)
9metadata = MetaData(bind=engine)
10
11Base = declarative_base(metadata=metadata)
12
13# 2. Define a model using ClickHouse-specific engines
14class Rate(Base):
15    day = Column(types.Date, primary_key=True)
16    value = Column(types.Int32)
17    other = Column(types.String)
18
19    __table_args__ = (
20        engines.Memory(),
21    )
22
23# 3. Create the table
24Rate.__table__.drop(checkfirst=True)
25Rate.__table__.create()
26
27# 4. Insert data
28from datetime import date
29row = Rate(day=date(2020, 1, 1), value=42, other='foo')
30session.add(row)
31session.commit()
32
33# 5. Query data
34query = session.query(Rate).filter(Rate.day == date(2020, 1, 1))
35print(query.first().value)