create table issue use sqlalchemy-clickhouse

[lyssom]

engine = create_engine('clickhouse://default:@localhost/test',encoding='utf-8',echo=True) Base = declarative_base() class Users(Base): tablename = 'users' id = Column(Integer,primary_key=True) name = Column(String(32)) password = Column(String(64)) engine = Engine() Base.metadata.create_all(engine) traceback like this: Exception: Code: 62, e.displayText() = DB::Exception: Syntax error: failed at position 45 (line 3, col 13): NOT NULL, name VARCHAR(32), password VARCHAR(64), PRIMARY KEY (id)

I want create table use python code like this, but can't create it ,so what could i do to create it. thanks

[knoguchi]

I looked at the code. The DDL compiler hasn't been implemented. The create table statement was generated by the generic DDL compiler which doesn't work for Clickhouse.

DDL compiler for Clickhouse is going to be something like below.

class ClickHouseDdlCompiler(compiler.DDLCompiler):
    def get_column_specification(self, column, **kwargs):
        coltype = self.dialect.type_compiler.process(
            column.type, type_expression=column
        )
        colspec = self.preparer.format_column(column) + " " + coltype
        return colspec

    def visit_primary_key_constraint(self, constraint):
        # don't add PRIMARY(columns) at the bottom.
        return ""

    def visit_create_table(self, create):
        stmt = super(ClickHouseDdlCompiler, self).visit_create_table(create)
        stmt += " ENGINE = Memory()"
        return stmt

class ClickHouseTypeCompiler(compiler.GenericTypeCompiler):
    def visit_ARRAY(self, t, **kw):
        return "Array(%s)" % str(t)

    def visit_VARCHAR(self, type_, **kw):
        return self._render_string_type(type_, "String")

class ClickHouseDialect(default.DefaultDialect):
    ddl_compiler = ClickHouseDdlCompiler

Using the above the create table stmt looks like this

CREATE TABLE users (
    id INTEGER,
    name String,
    password String
)

 ENGINE = Memory()

It still gets an error but it's enough to create the users table.

[vladimir-golovchenko]

The alternative way of table creation:

import pandas as pd
from infi.clickhouse_orm.engines import Memory
from infi.clickhouse_orm.fields import UInt16Field, StringField
from infi.clickhouse_orm.models import Model
from sqlalchemy import create_engine

# define the ClickHouse table schema
class Test_Humans(Model):
    year = UInt16Field()
    first_name = StringField()
    engine = Memory()

engine = create_engine('clickhouse://default:@localhost/test')

# create table manually
with engine.connect() as conn:
    conn.connection.create_table(Test_Humans) # https://github.com/Infinidat/infi.clickhouse_orm/blob/master/src/infi/clickhouse_orm/database.py#L142

(see for details https://stackoverflow.com/a/61270873/303298)