前言

SQLAlchemy是一个功能强大且灵活的Python库，主要用于数据库操作。它提供了SQL工具包和对象关系映射（ORM）功能，允许开发者通过Python代码与关系型数据库交互，而不需要直接编写SQL语句。

SQLAlchemy的主要功能和特点

1. 数据库抽象层：SQLAlchemy提供了一个统一的API来处理不同的数据库系统，如MySQL、PostgreSQL、SQLite、Oracle等，这意味着你可以编写一次代码，然后在不同的数据库系统之间切换而不需要做太多修改。
2. ORM功能：SQLAlchemy的ORM功能允许开发者将数据库表映射为Python类，表中的行映射为类的实例。这样，你可以使用Python的面向对象特性来操作数据库，而无需直接编写SQL代码。
3. SQL表达式语言：除了ORM，SQLAlchemy还提供了一个强大的SQL表达式语言，允许你以Pythonic的方式构建SQL查询，同时保持与底层数据库的兼容性。
4. 灵活性和扩展性：SQLAlchemy设计得非常灵活，允许开发者根据需要选择使用其ORM功能、SQL表达式语言，或者直接使用原生SQL。
5. 活跃的社区和良好的文档：由于其广泛的应用和强大的功能，SQLAlchemy拥有一个活跃的社区和详尽的文档，这使得学习和解决问题相对容易。

SQLAlchemy的使用场景

• 数据库访问和操作：SQLAlchemy提供了高层抽象来操作数据库，可以避免写原生SQL语句。
• ORM映射：建立Python类与数据库表的映射关系，简化数据模型的操作。
• 复杂查询：提供丰富的查询方式，如过滤、分组、联结等。
• 异步查询：基于Greenlet等实现异步查询，提高查询效率。
• 事务控制：通过Session管理数据库会话和事务。
• 工具集成：如数据迁移工具Alembic，可以实现Schema版本控制和迁移。
• 大数据集查询：基于Pagination实现数据分页，避免大量数据查询内存溢出。
• 多数据库支持：支持Postgres、MySQL、Oracle等主流数据库。
• Web框架集成：如Flask，可以集成SQLAlchemy，便于Web应用开发。

clickhouse-sqlalchemy安装

pip install clickhouse-sqlalchemy

edy@edydeMBP ~ % python3 -m pip install clickhouse-sqlalchemy
Collecting clickhouse-sqlalchemy
  Downloading clickhouse-sqlalchemy-0.3.2.tar.gz (45 kB)
  Installing build dependencies ... done
  Getting requirements to build wheel ... done
  Preparing metadata (pyproject.toml) ... done
Requirement already satisfied: sqlalchemy<2.1.0,>=2.0.0 in /Library/Frameworks/Python.framework/Versions/3.13/lib/python3.13/site-packages (from clickhouse-sqlalchemy) (2.0.37)
Requirement already satisfied: requests in /Library/Frameworks/Python.framework/Versions/3.13/lib/python3.13/site-packages (from clickhouse-sqlalchemy) (2.32.3)
Requirement already satisfied: clickhouse-driver>=0.1.2 in /Library/Frameworks/Python.framework/Versions/3.13/lib/python3.13/site-packages (from clickhouse-sqlalchemy) (0.2.9)
Collecting asynch>=0.2.2 (from clickhouse-sqlalchemy)
  Downloading asynch-0.2.5-py3-none-any.whl.metadata (7.0 kB)
Collecting ciso8601 (from asynch>=0.2.2->clickhouse-sqlalchemy)
  Downloading ciso8601-2.3.2.tar.gz (28 kB)
  Installing build dependencies ... done
  Getting requirements to build wheel ... done
  Preparing metadata (pyproject.toml) ... done
Collecting leb128 (from asynch>=0.2.2->clickhouse-sqlalchemy)
  Downloading leb128-1.0.8-py3-none-any.whl.metadata (2.5 kB)
Collecting lz4 (from asynch>=0.2.2->clickhouse-sqlalchemy)
  Downloading lz4-4.4.3-cp313-cp313-macosx_11_0_arm64.whl.metadata (3.8 kB)
Requirement already satisfied: pytz in /Library/Frameworks/Python.framework/Versions/3.13/lib/python3.13/site-packages (from asynch>=0.2.2->clickhouse-sqlalchemy) (2025.1)
Requirement already satisfied: tzlocal in /Library/Frameworks/Python.framework/Versions/3.13/lib/python3.13/site-packages (from asynch>=0.2.2->clickhouse-sqlalchemy) (5.2)
Collecting zstd (from asynch>=0.2.2->clickhouse-sqlalchemy)
  Downloading zstd-1.5.6.2-cp313-cp313-macosx_11_0_arm64.whl.metadata (10 kB)
Requirement already satisfied: typing-extensions>=4.6.0 in /Library/Frameworks/Python.framework/Versions/3.13/lib/python3.13/site-packages (from sqlalchemy<2.1.0,>=2.0.0->clickhouse-sqlalchemy) (4.12.2)
Requirement already satisfied: charset-normalizer<4,>=2 in /Library/Frameworks/Python.framework/Versions/3.13/lib/python3.13/site-packages (from requests->clickhouse-sqlalchemy) (3.4.1)
Requirement already satisfied: idna<4,>=2.5 in /Library/Frameworks/Python.framework/Versions/3.13/lib/python3.13/site-packages (from requests->clickhouse-sqlalchemy) (3.10)
Requirement already satisfied: urllib3<3,>=1.21.1 in /Library/Frameworks/Python.framework/Versions/3.13/lib/python3.13/site-packages (from requests->clickhouse-sqlalchemy) (2.3.0)
Requirement already satisfied: certifi>=2017.4.17 in /Library/Frameworks/Python.framework/Versions/3.13/lib/python3.13/site-packages (from requests->clickhouse-sqlalchemy) (2025.1.31)
Downloading asynch-0.2.5-py3-none-any.whl (81 kB)
Downloading leb128-1.0.8-py3-none-any.whl (3.8 kB)
Downloading lz4-4.4.3-cp313-cp313-macosx_11_0_arm64.whl (189 kB)
Downloading zstd-1.5.6.2-cp313-cp313-macosx_11_0_arm64.whl (207 kB)
Building wheels for collected packages: clickhouse-sqlalchemy, ciso8601
  Building wheel for clickhouse-sqlalchemy (pyproject.toml) ... done
  Created wheel for clickhouse-sqlalchemy: filename=clickhouse_sqlalchemy-0.3.2-py3-none-any.whl size=53570 sha256=d8121c13c9505b1c6c70fa3ded205c9941735f237a7a0adba2219100d9a4c456
  Stored in directory: /Users/edy/Library/Caches/pip/wheels/f0/c2/79/1b0312f33d94876e4bfa7719137b4a763e565a62def5e2b053
  Building wheel for ciso8601 (pyproject.toml) ... done
  Created wheel for ciso8601: filename=ciso8601-2.3.2-cp313-cp313-macosx_10_13_universal2.whl size=23837 sha256=feb4c2f34d948bd71b8278964209b030ac6f2c1b825b908190e71bd1dffd7ecb
  Stored in directory: /Users/edy/Library/Caches/pip/wheels/a2/b2/a6/bbb256f5ce5eb73218529e60726bc640a17d8fcab77017c198
Successfully built clickhouse-sqlalchemy ciso8601
Installing collected packages: zstd, leb128, ciso8601, lz4, asynch, clickhouse-sqlalchemy
Successfully installed asynch-0.2.5 ciso8601-2.3.2 clickhouse-sqlalchemy-0.3.2 leb128-1.0.8 lz4-4.4.3 zstd-1.5.6.2

[notice] A new release of pip is available: 24.3.1 -> 25.0
[notice] To update, run: pip3 install --upgrade pip

应用

编写测试脚本sql.py，查询ClickHouse数据，代码如下：

from clickhouse_sqlalchemy import make_session
from sqlalchemy import create_engine
from sqlalchemy.sql import text

#"clickhouse://{user}:{pw}@{addr}/{database}"
connection = "clickhouse://default:123456@localhost/launch"
engine = create_engine(connection, pool_size=100, pool_recycle=3600, pool_timeout=10)
clickhouse_cursor = make_session(engine=engine)
# sql='show tables;'
query_result = clickhouse_cursor.execute(text('select * from launch.users'))
try:
    fields = query_result._metadata.keys  # 字段名
    print(fields._keys)
    for item in query_result.fetchall():  # 具体内容
        print(item)
finally:
    query_result.close()
    clickhouse_cursor.close()

运行结果：

['id', 'name', 'age', 'create_date']
('1', 'yyp', 18, datetime.date(2002, 1, 1))