优秀的编程知识分享平台

网站首页 > 技术文章 正文

Python 基础教程十三之从零搭建 SQLAlchemy 连接 ClickHouse指南

nanyue 2025-03-25 16:00:42 技术文章 8 ℃

前言

这小节，我们从零搭建一个使用Python 访问ClickHouse 的运行环境。首先会介绍 Python 的安装步骤，然后介绍SQLAlchemy 依赖库的安装。

Unix & Linux 平台安装 Python3:

以下为在 Unix & Linux 平台上安装 Python 的简单步骤：

  • 打开 WEB 浏览器访问 https://www.python.org/downloads/source/
  • 选择适用于 Unix/Linux 的源码压缩包。
  • 下载及解压压缩包 Python-3.13.2.tgz，3.13.2 为你下载的对应版本号。
  • 如果你需要自定义一些选项修改 Modules/Setup

以 Python-3.13.2版本为例：

1、首先安装 openssl

sudo yum install openssl-devel
[root@VM-0-152-tencentos test]# sudo yum install openssl-devel
TencentOS Server 4 - BaseOS                                                                                                                     6.7 MB/s | 3.3 MB     00:00    
TencentOS Server 4 - AppStream                                                                                                                   21 MB/s |  14 MB     00:00    
TencentOS Server 4 - extras                                                                                                                     1.2 kB/s | 257  B     00:00    
Extra Packages for TencentOS Server 4 - EPOL                                                                                                     34 MB/s |  24 MB     00:00    
Dependencies resolved.
================================================================================================================================================================================
 Package                                      Architecture                          Version                                      Repository                                Size
================================================================================================================================================================================
Installing:
 openssl-devel                                x86_64                                3.0.12-16.tl4                                AppStream                                2.6 M
Upgrading:
 openssl                                      x86_64                                3.0.12-16.tl4                                BaseOS                                   1.0 M
 openssl-libs                                 x86_64                                3.0.12-16.tl4                                BaseOS                                   2.5 M

Transaction Summary
================================================================================================================================================================================
Install  1 Package
Upgrade  2 Packages
.........
Upgraded:
  openssl-3.0.12-16.tl4.x86_64                                                         openssl-libs-3.0.12-16.tl4.x86_64                                                        
Installed:
  openssl-devel-3.0.12-16.tl4.x86_64                                                                                                                                            

Complete!

2、解压 Python-3.13.2.tgz

[root@VM-0-152-tencentos test]# tar -zxvf Python-3.13.2.tgz 
[root@VM-0-152-tencentos test]# tar -zxvf Python-3.13.2.tgz 
Python-3.13.2/
Python-3.13.2/.coveragerc
Python-3.13.2/.devcontainer/
Python-3.13.2/.devcontainer/Dockerfile
Python-3.13.2/.devcontainer/devcontainer.json
Python-3.13.2/.editorconfig
Python-3.13.2/.mailmap
Python-3.13.2/.pre-commit-config.yaml
Python-3.13.2/.readthedocs.yml
Python-3.13.2/Android/
Python-3.13.2/Android/README.md
Python-3.13.2/Android/android-env.sh
Python-3.13.2/Android/android.py
..............
Python-3.13.2/iOS/testbed/iOSTestbedTests/iOSTestbedTests.m
Python-3.13.2/install-sh
Python-3.13.2/pyconfig.h.in

3、configure 命令

[root@VM-0-152-tencentos test]# cd Python-3.13.2/
[root@VM-0-152-tencentos Python-3.13.2]# ./configure --with-ssl
[root@VM-0-152-tencentos test]# cd Python-3.13.2/
[root@VM-0-152-tencentos Python-3.13.2]# ./configure
checking build system type... x86_64-pc-linux-gnu
checking host system type... x86_64-pc-linux-gnu
checking for Python interpreter freezing... ./_bootstrap_python
checking for python3.13... no
checking for python3.13... no
checking for python3.12... no
checking for python3.11... python3.11
checking Python for regen version... Python 3.11.6
checking for pkg-config... /usr/bin/pkg-config
checking pkg-config is at least version 0.9.0... yes
..............
If you want a release build with all stable optimizations active (PGO, etc),
please run ./configure --enable-optimizations

4、make && make install

[root@VM-0-152-tencentos Python-3.13.2]# make && make install
[root@VM-0-152-tencentos Python-3.13.2]# make && make install
gcc -c -fno-strict-overflow -Wsign-compare -DNDEBUG -g -O3 -Wall    
..........
Looking in links: /tmp/tmpxog9x74n
Processing /tmp/tmpxog9x74n/pip-24.3.1-py3-none-any.whl
Installing collected packages: pip
Successfully installed pip-24.3.1

5、检查 Python3 是否正常可用：

[root@VM-0-152-tencentos Python-3.13.2]# python3 -V
Python 3.13.2

看到以上信息，表示 Python-3.13.2 安装成功 ！

SQLAlchemy 是什么？

SQLAlchemy 是 Python 中一个非常流行的 ORM（对象关系映射）框架，通常用于与数据库进行交互。相比于直接编写 SQL 语句，SQLAlchemy 提供了更高层次的抽象，支持多种数据库，并且可以灵活地生成 SQL 查询。通过 SQLAlchemy，我们可以更加优雅地与数据库交互，编写可维护、可扩展的代码。

虽然 ClickHouse 本身有许多客户端和 API 接口可供使用，但通过 SQLAlchemy 进行连接可以将 ClickHouse 与现有的 Python 数据库交互代码无缝集成，尤其在你使用多个数据库时会更加方便。

安装所需库

要使用 SQLAlchemy 连接 ClickHouse，需要安装以下几个 Python 库：

SQLAlchemy：用于与数据库进行高层次的交互。

ClickHouse SQLAlchemy：这是 SQLAlchemy 的 ClickHouse 方言库。

ClickHouse-Connect（或其他驱动）：这是用于与 ClickHouse 通信的 Python 驱动。

在终端中使用以下命令来安装这些依赖项：

1、升级pip

pip3 install --upgrade pip
[root@VM-0-152-tencentos bin]# pip3 install --upgrade pip
Requirement already satisfied: pip in /usr/local/lib/python3.13/site-packages (24.3.1)
Collecting pip
  Downloading pip-25.0.1-py3-none-any.whl.metadata (3.7 kB)
Downloading pip-25.0.1-py3-none-any.whl (1.8 MB)
   ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 1.8/1.8 MB 24.4 kB/s eta 0:00:00
Installing collected packages: pip
  Attempting uninstall: pip
    Found existing installation: pip 24.3.1
    Uninstalling pip-24.3.1:
      Successfully uninstalled pip-24.3.1
Successfully installed pip-25.0.1

2、pip install sqlalchemy

[root@VM-0-152-tencentos bin]# pip3 install sqlalchemy
Collecting sqlalchemy
  Downloading SQLAlchemy-2.0.38-cp313-cp313-manylinux_2_17_x86_64.manylinux2014_x86_64.whl.metadata (9.6 kB)
Collecting greenlet!=0.4.17 (from sqlalchemy)
  Downloading greenlet-3.1.1-cp313-cp313-manylinux_2_24_x86_64.manylinux_2_28_x86_64.whl.metadata (3.8 kB)
Collecting typing-extensions>=4.6.0 (from sqlalchemy)
  Downloading typing_extensions-4.12.2-py3-none-any.whl.metadata (3.0 kB)
Downloading SQLAlchemy-2.0.38-cp313-cp313-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (3.2 MB)
   ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 3.2/3.2 MB 649.1 kB/s eta 0:00:00
Downloading greenlet-3.1.1-cp313-cp313-manylinux_2_24_x86_64.manylinux_2_28_x86_64.whl (615 kB)
   ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 615.6/615.6 kB 1.7 MB/s eta 0:00:00
Downloading typing_extensions-4.12.2-py3-none-any.whl (37 kB)
Installing collected packages: typing-extensions, greenlet, sqlalchemy
Successfully installed greenlet-3.1.1 sqlalchemy-2.0.38 typing-extensions-4.12.2

3、pip install clickhouse-sqlalchemy

[root@VM-0-152-tencentos bin]# pip3 install clickhouse-sqlalchemy
Collecting clickhouse-sqlalchemy
  Using cached 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 /usr/local/lib/python3.13/site-packages (from clickhouse-sqlalchemy) (2.0.38)
Collecting requests (from clickhouse-sqlalchemy)
  Using cached requests-2.32.3-py3-none-any.whl.metadata (4.6 kB)
Collecting clickhouse-driver>=0.1.2 (from clickhouse-sqlalchemy)
  Using cached clickhouse-driver-0.2.9.tar.gz (357 kB)
  Installing build dependencies ... done

4、pip install clickhouse-connect

[root@VM-0-152-tencentos bin]# pip3 install clickhouse-connect
Collecting clickhouse-connect
  Downloading clickhouse_connect-0.8.15-cp313-cp313-manylinux_2_17_x86_64.manylinux2014_x86_64.whl.metadata (3.4 kB)
Requirement already satisfied: certifi in /usr/local/lib/python3.13/site-packages (from clickhouse-connect) (2025.1.31)
Requirement already satisfied: urllib3>=1.26 in /usr/local/lib/python3.13/site-packages (from clickhouse-connect) (2.3.0)
Requirement already satisfied: pytz in /usr/local/lib/python3.13/site-packages (from clickhouse-connect) (2025.1)
Collecting zstandard (from clickhouse-connect)
  Downloading zstandard-0.23.0-cp313-cp313-manylinux_2_17_x86_64.manylinux2014_x86_64.whl.metadata (3.0 kB)
Requirement already satisfied: lz4 in /usr/local/lib/python3.13/site-packages (from clickhouse-connect) (4.4.3)
Downloading clickhouse_connect-0.8.15-cp313-cp313-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (1.1 MB)
   ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 1.1/1.1 MB 27.9 kB/s eta 0:00:00
Downloading zstandard-0.23.0-cp313-cp313-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (5.4 MB)
   ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 5.4/5.4 MB 28.2 kB/s eta 0:00:00
Installing collected packages: zstandard, clickhouse-connect
Successfully installed clickhouse-connect-0.8.15 zstandard-0.23.0

到这里，所有的依赖项都已安装成功！python3 和 pip 命令的路径：

[root@VM-0-152-tencentos bin]# pwd
/usr/local/bin
[root@VM-0-152-tencentos bin]# ls
cloud-init  idle3  idle3.13  normalizer  pip  pip3  pip3.13  pydoc3  pydoc3.13  python3  python3.13  python3.13-config  python3-config

栗子

ClickHouse 使用的是 HTTP 或 TCP 协议，因此连接 ClickHouse 时，我们可以选择 HTTP 端点或 TCP 端点。为了与 SQLAlchemy 配合使用，我们使用 clickhouse-sqlalchemy 库，它支持通过 SQLAlchemy 的连接字符串格式连接 ClickHouse。

SQLAlchemy 使用统一的数据库 URI 连接格式，ClickHouse 也遵循类似的格式。以下是 ClickHouse 的基本连接字符串格式：

from sqlalchemy import create_engine
# ClickHouse 数据库的连接字符串格式
CLICKHOUSE_URI = 'clickhouse+http://:@:/'

其中：

username: ClickHouse 用户名，通常是 default。

password: 用户密码。

host: ClickHouse 服务器的地址。

port: ClickHouse 服务器使用的端口（默认 HTTP 端口是 8123）。

database: 要连接的 ClickHouse 数据库名称。

让我们开发一个访问ClickHouse的程序来 测试一下吧！

import requests
import json
import datetime
import time
import sys
import sqlalchemy as sa
import clickhouse_sqlalchemy as cksa
from sqlalchemy.sql import text

ck_url = 'clickhouse://ck_test_name:ck_test_pwd@127.0.0.1:8123/ads'
engine = sa.create_engine(url=ck_url, pool_size=100, pool_recycle=3600, pool_timeout=20)
session = cksa.make_session(engine)

rs = session.execute(text("SELECT * from ads.ads_event_entity where send_time>='2025-01-20'  and action_type=6 limit 20"))

try:
    for item in rs.fetchall():
        print(item)
except:
    print("error:",sys.exc_info()[0])
finally:
    rs.close()
    session.close()

运行以上程序：

[root@VM-0-152-tencentos test]# python3 ck.py 
(datetime.datetime(2025, 2, 4, 23, 50, 6), 'f036a99f3484b0ea270414bba48100c2', datetime.datetime(2025, 2, 4, 23, 46, 58), 6, 0, '100086', '6e18bd90fbca440ddd5b1d0bd2946c5d', 2, 208823999, 0, '', '05a8fd3be5115050', '', '05a8fd3be5115050', '', '', '', '113.248.62.144', 'REDMI', 'M2007J17C', 1, '31', '25012417', '', '', '', 103, 2, 1, '31013', 'xiaomi', '9', 'kdkyy', '574482', '104944912', '107111033', '143363428', '', '', '', '', 7, '241108003042', 'xm_zn', 8, 1, 2, 0, Decimal('0'), Decimal('0'), Decimal('0'), Decimal('1.14723'), Decimal('1.1829'), '', '', 0, '', 565678543, '')
(datetime.datetime(2025, 2, 4, 23, 51, 6), '1738684053988_7963', datetime.datetime(2025, 2, 4, 23, 47, 33), 6, 0, '100098', 'b7d7a1d2b7ed1b6a2b1ccf87f0c3533b', 2, 208823768, 0, '', 'e213c0073cc818bb', '', 'e213c0073cc818bb', '', '', '', '27.19.2.254', 'REDMI', '22101320C', 1, '33', '25012417', '', '', '', 103, 2, 1, '34934', 'kuaishou', '9', 'zt-heh', '54226485', '', '', '', '', '', '', '', 9, '250109003012', 'ks_zn', 1, 1, 2, 0, Decimal('0'), Decimal('0'), Decimal('0'), Decimal('17.39283'), Decimal('18.224'), '', '', 0, '', 565678608, '')

Tags：

猜你喜欢

最近发表
标签列表