{"id":2891,"library":"clickhouse-sqlalchemy","title":"ClickHouse SQLAlchemy Dialect","description":"The `clickhouse-sqlalchemy` library provides a SQLAlchemy dialect for connecting to and interacting with ClickHouse databases. It enables users to leverage SQLAlchemy's ORM and SQL Expression Language for querying and manipulating data in ClickHouse. The current version is `0.3.2`. The project releases updates on an as-needed basis rather than a fixed schedule.","status":"active","version":"0.3.2","language":"en","source_language":"en","source_url":"https://github.com/xzkostyan/clickhouse-sqlalchemy","tags":["database","sql","orm","clickhouse","sqlalchemy","dialect"],"install":[{"cmd":"pip install clickhouse-sqlalchemy","lang":"bash","label":"Install `clickhouse-sqlalchemy`"}],"dependencies":[],"imports":[{"note":"SQLAlchemy dialects are activated via the engine connection string; `create_engine` itself is from `sqlalchemy`.","wrong":"from clickhouse_sqlalchemy import create_engine","symbol":"create_engine","correct":"from sqlalchemy import create_engine"}],"quickstart":{"code":"import os\nfrom sqlalchemy import create_engine, text\n\nCH_HOST = os.environ.get('CLICKHOUSE_HOST', 'localhost')\nCH_PORT = os.environ.get('CLICKHOUSE_PORT', '8123')\nCH_USER = os.environ.get('CLICKHOUSE_USER', 'default')\nCH_PASSWORD = os.environ.get('CLICKHOUSE_PASSWORD', '')\nCH_DATABASE = os.environ.get('CLICKHOUSE_DATABASE', 'default')\n\ntry:\n    # Establish a connection to ClickHouse\n    engine = create_engine(\n        f'clickhouse://{CH_USER}:{CH_PASSWORD}@{CH_HOST}:{CH_PORT}/{CH_DATABASE}'\n    )\n\n    # Execute a simple query\n    with engine.connect() as connection:\n        result = connection.execute(text('SELECT 1 as one')).scalar()\n        print(f\"Query result: {result}\")\n\n    # Example: Create a table and insert data\n    with engine.connect() as connection:\n        connection.execute(text('DROP TABLE IF EXISTS my_test_table'))\n        connection.execute(text('CREATE TABLE my_test_table (id Int32, name String) ENGINE = MergeTree ORDER BY id'))\n        connection.execute(text('INSERT INTO my_test_table (id, name) VALUES (1, \\'Alice\\'), (2, \\'Bob\\')'))\n        connection.commit()\n\n        # Query data\n        rows = connection.execute(text('SELECT id, name FROM my_test_table ORDER BY id')).fetchall()\n        print(f\"Table data: {rows}\")\n\nexcept Exception as e:\n    print(f\"An error occurred: {e}\")\n    print(\"Ensure a ClickHouse instance is running and connection details are correct.\")\n    print(\"You can set environment variables like CLICKHOUSE_HOST, CLICKHOUSE_PORT, etc.\")","lang":"python","description":"This quickstart demonstrates how to establish a connection to a ClickHouse database using SQLAlchemy's `create_engine` and execute basic SQL queries, including DDL and DML operations. It uses environment variables for connection details for secure and flexible configuration. A running ClickHouse instance is required."},"warnings":[{"fix":"Replace `ClickHouseEngine(...)` with `create_engine('clickhouse://...')` and `create_session(...)` with `sqlalchemy.orm.sessionmaker(bind=engine)()`.","message":"The aliases `ClickHouseEngine` and `create_session` were removed in version `0.3.0`. Users should directly use `sqlalchemy.create_engine` and standard SQLAlchemy session management.","severity":"breaking","affected_versions":">=0.3.0"},{"fix":"If nullable strings are desired, explicitly define columns using `sa.Column('name', sa.String(255), nullable=True)` or import `NullableString` from `clickhouse_sqlalchemy.types`.","message":"The default string type mapping changed from `Nullable(String)` to `String` in version `0.3.0`. This affects DDL generation; columns defined as `String` in SQLAlchemy will no longer implicitly be `Nullable(String)` in ClickHouse.","severity":"breaking","affected_versions":">=0.3.0"},{"fix":"Use specific types like `DateTime(timezone=...)` or `DateTime64(precision=..., timezone=...)` from `clickhouse_sqlalchemy.types` for precise control over timestamp columns.","message":"ClickHouse's `DateTime` and `DateTime64` types have strict requirements for timezones and precision. SQLAlchemy's default `DateTime` might not always align perfectly, leading to timezone or precision issues during data insertion/retrieval.","severity":"gotcha","affected_versions":"<all>"},{"fix":"For complex types and features, it's often more robust to use `engine.execute(text('RAW SQL HERE'))` to run native ClickHouse queries directly, bypassing the ORM layer.","message":"Advanced ClickHouse data types (e.g., `Nested`, `Array`, `Map`, `AggregateFunction`) and features (e.g., `FINAL` modifier, complex table engines) often lack direct, high-level ORM support. Attempting to use them with the ORM might require complex workarounds or fail.","severity":"gotcha","affected_versions":"<all>"}],"env_vars":null,"last_verified":"2026-04-11T00:00:00.000Z","next_check":"2026-07-10T00:00:00.000Z"}