> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-parallel-read-in-order-multi-part.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
> Prise en charge de SQLAlchemy et Alembic pour ClickHouse
# Prise en charge de SQLAlchemy
ClickHouse Connect inclut le dialecte SQLAlchemy `clickhousedb`, basé sur le pilote principal. Le dialecte synchrone prend en charge SQLAlchemy 1.4.40 et les versions ultérieures, y compris SQLAlchemy 2.x, avec un accent particulier sur les requêtes Core, le DDL ClickHouse, l’introspection et les insertions ORM simples. Le dialecte asynchrone nécessite SQLAlchemy 2.0.44 ou une version ultérieure.
Installez les dépendances SQLAlchemy avec l’extra du paquet :
```bash theme={null}
pip install "clickhouse-connect[sqlalchemy]"
```
Se connecter avec SQLAlchemy
Créez un moteur avec l’une des URL suivantes : `clickhousedb://` ou `clickhousedb+connect://` :
```python theme={null}
from sqlalchemy import create_engine, text
engine = create_engine(
"clickhousedb://user:password@host:8123/mydb?compression=zstd"
)
with engine.connect() as conn:
version = conn.execute(text("SELECT version()")).scalar_one()
print(version)
```
ID de session ClickHouse
Par défaut, chaque connexion du pool, que ce soit avec le dialecte synchrone ou asynchrone, génère un ID de session ClickHouse distinct. Lorsque les requêtes de cette connexion aboutissent au même processus serveur ClickHouse, les paramètres modifiés avec `SET` et les tables temporaires sont conservés pour cette connexion. L’état des sessions nommées et les vérifications de chevauchement au sein d’une même session sont propres à chaque processus. Sur un même processus serveur, une requête concurrente pour le même utilisateur et le même ID de session est immédiatement rejetée avec le code serveur 373, au lieu d’être placée en file d’attente. Si vous configurez un `session_id` fixe, utilisez `pool_size=1, max_overflow=0` ou sérialisez l’accès avant que les requêtes n’atteignent ClickHouse. Dans ClickHouse Cloud ou dans d’autres déploiements avec répartition de charge, les requêtes portant le même ID de session peuvent aboutir sur des serveurs différents : ne vous appuyez donc pas sur un `session_id` fixe comme état distribué ni comme mutex distribué.
Connexions asynchrones
Le dialecte asynchrone nécessite SQLAlchemy 2.0.44 ou une version ultérieure et s'appuie sur l'`AsyncClient` natif de ClickHouse Connect. Installez ses dépendances, puis créez un moteur asynchrone avec l'URL `clickhousedb+async://` :
```bash theme={null}
pip install "clickhouse-connect[sqlalchemy-async]"
```
```python theme={null}
import asyncio
from sqlalchemy import text
from sqlalchemy.ext.asyncio import create_async_engine
async def main():
engine = create_async_engine(
"clickhousedb+async://user:password@host:8123/mydb"
)
try:
async with engine.connect() as conn:
version = (await conn.execute(text("SELECT version()"))).scalar_one()
print(version)
finally:
await engine.dispose()
asyncio.run(main())
```
Les résultats sont mis en tampon. Les curseurs côté serveur sont désactivés : `AsyncConnection.stream()` lève donc une `InvalidRequestError`. SQLAlchemy accepte `AsyncSession.stream()`, mais le dialecte met en tampon l'intégralité du résultat avant de le renvoyer. Pour les résultats volumineux, utilisez les méthodes de streaming natives d'`AsyncClient`. Le client natif brut est accessible via `driver_connection` tant que la connexion SQLAlchemy correspondante est empruntée au pool :
```python theme={null}
async def stream_events(engine):
async with engine.connect() as conn:
raw_connection = await conn.get_raw_connection()
client = raw_connection.driver_connection
async with await client.query_rows_stream("SELECT * FROM events") as rows:
async for row in rows:
print(row)
```
N'utilisez pas la connexion SQLAlchemy en même temps que son client brut. Terminez les flux du client brut avant de quitter le bloc de connexion SQLAlchemy, et ne conservez pas le client brut une fois la connexion rendue au pool. SQLAlchemy gère le cycle de vie du client emprunté ; n'appelez donc jamais `client.close()` ni aucune de ses méthodes privées de cycle de vie. C'est le pool de SQLAlchemy qui gère la concurrence des connexions. Chaque connexion du pool possède un client asynchrone natif et limite par défaut son connector aiohttp à une connexion au total et à une connexion par hôte. Définissez `connector_limit`, `connector_limit_per_host` ou `keepalive_timeout` dans l'URL ou dans `connect_args` pour remplacer ces paramètres de transport. Avec `pool_pre_ping=True`, SQLAlchemy vérifie les connexions réutilisées à l'aide de `SELECT 1` lorsqu'une connexion est extraite du pool.
Les insertions executemany asynchrones de SQLAlchemy envoient actuellement une requête HTTP par jeu de paramètres au lieu d'utiliser le protocole d'insertion en masse Native du driver. Réservez cette méthode aux petits lots. Pour les données volumineuses, utilisez le modèle d'accès `driver_connection` géré par le pool présenté ci-dessus et appelez `client.insert()` avec `await` avant de rendre la connexion SQLAlchemy au pool. Comme executemany asynchrone utilise la liaison des paramètres de requête, les valeurs `datetime` sans fuseau horaire suivent `naive_datetime_binding`, et non le paramètre `naive_datetime_insert` utilisé par executemany Native synchrone. Les liaisons SQLAlchemy typées `DateTime64` conservent les fractions de seconde, avec des paramètres côté client comme côté serveur. Les paramètres non typés `%s` ou `%(name)s` passés à `exec_driver_sql()` conservent le formatage par défaut à la seconde près pour les valeurs `datetime` sans fuseau horaire. Utilisez des valeurs avec fuseau horaire pour éviter toute ambiguïté. Utilisez `client.insert()` pour bénéficier de la sémantique d'insertion en masse Native.
Créez et libérez un moteur asynchrone dans la boucle d'événements où il est utilisé. Rendez chaque connexion extraite, puis appelez `engine.dispose()` avec `await` lors de l'arrêt et avant d'utiliser le moteur depuis une autre boucle d'événements. Si la boucle propriétaire du moteur est déjà fermée, appelez `engine.dispose()` avec `await` dans la boucle courante avant de le réutiliser. aiohttp peut encore signaler un transport non fermé lorsque le nettoyage ne commence qu'après la fermeture de la boucle propriétaire ; dans la mesure du possible, libérez donc le moteur avant de le transférer. `pool_pre_ping=True` ne dispense pas de libérer un moteur asynchrone avec pool lorsqu'on le déplace d'une boucle d'événements à une autre. Pour partager un même moteur entre plusieurs boucles d'événements sans conserver de connexions liées à une boucle, configurez `poolclass=NullPool`. Si la libération s'exécute alors qu'une connexion est encore extraite, le dialecte ferme cette connexion lorsqu'elle est rendue ou collectée par le ramasse-miettes. N'appelez pas `engine.sync_engine.dispose()` depuis du code synchrone : SQLAlchemy ne peut pas y attendre le nettoyage asynchrone des connexions et risque de journaliser l'erreur au lieu de fermer les transports du pool.
Les paramètres de requête de l'URL peuvent contenir des paramètres ClickHouse, des options du client ClickHouse Connect telles que `compression`, `query_limit` et les délais d'expiration, ou des options HTTP/TLS telles que `ca_cert`. Si nécessaire, préfixez un paramètre ClickHouse par `ch_` pour forcer son traitement en tant que paramètre serveur, par exemple `ch_http_max_field_name_size=99999`.
Consultez [Arguments et paramètres de connexion](/fr/integrations/language-clients/python/driver-api#connection-arguments) pour connaître les options client disponibles.
Exécutez les utilitaires SQLAlchemy synchrones, comme le DDL et l'inspection, via `AsyncConnection.run_sync()` :
```python theme={null}
from sqlalchemy import inspect
async def prepare_schema(engine, metadata):
async with engine.begin() as conn:
await conn.run_sync(metadata.create_all)
return await conn.run_sync(
lambda sync_conn: inspect(sync_conn).get_table_names()
)
```
Paramètres par requête
Transmettez les paramètres ClickHouse via les options d’exécution de SQLAlchemy. Les paramètres peuvent être définis au niveau du moteur, de la connexion ou de l’instruction. Une valeur définie sur l’instruction prévaut sur une valeur de connexion ou de moteur avec la même clé.
```python theme={null}
from sqlalchemy import text
stmt = text("SELECT getSetting('max_threads')").execution_options(
settings={"max_threads": 2}
)
with engine.connect() as conn:
value = conn.execute(stmt).scalar_one()
```
Définissez les formats de lecture ClickHouse pour un moteur, une connexion ou une instruction à l’aide des options d’exécution SQLAlchemy et de `query_formats`. Les formats définis au niveau de l’instruction sont appliqués en premier et remplacent les keys et wildcards correspondants définis au niveau de la connexion ou du moteur.
```python theme={null}
from sqlalchemy import text
stmt = text("SELECT user_uuid FROM users").execution_options(
query_formats={"UUID": "string"}
)
with engine.connect() as conn:
rows = conn.execute(stmt).all()
```
Gestion des erreurs
Les erreurs levées par le driver via une connexion SQLAlchemy utilisent les classes DB-API exportées par `clickhouse_connect.dbapi`. Ce sont les mêmes objets de classe que les classes correspondantes de `clickhouse_connect.driver.exceptions` : SQLAlchemy les encapsule donc dans la sous-classe `sqlalchemy.exc.DBAPIError` correspondante. `StreamFailureError` est une `OperationalError` et est encapsulée sous la forme d'une `sqlalchemy.exc.OperationalError`.
Si une annulation déclenchée par l'appelant risque d'interrompre un appel explicite à `AsyncConnection.invalidate()`, exécutez l'invalidation dans une task dédiée et attendez la fin de cette task avant de propager l'annulation. SQLAlchemy peut ainsi finaliser la mise à jour de ses enregistrements de connexion :
```python theme={null}
import asyncio
async def invalidate_safely(connection):
invalidate_task = asyncio.create_task(connection.invalidate())
cancellation = None
while not invalidate_task.done():
try:
await asyncio.wait({invalidate_task})
except asyncio.CancelledError as ex:
cancellation = ex
if cancellation is not None:
try:
invalidate_task.result()
finally:
raise cancellation
invalidate_task.result()
```
N'utilisez pas la connexion tant que sa tâche d'invalidation est encore en cours. Si un appel direct à `await connection.invalidate()` est annulé et que `connection.invalidated` reste à false, rappelez `connection.invalidate()` avec await pour terminer le nettoyage avant d'utiliser ou de fermer la connexion.
Paramètres côté serveur
SQLAlchemy génère normalement des paramètres côté client. Pour utiliser les paramètres côté serveur de ClickHouse, activez-les lors de la création du moteur :
```python theme={null}
engine = create_engine(
"clickhousedb://user:password@host:8123/mydb",
server_side_params=True,
)
```
Utilisez le même argument `server_side_params=True` avec `create_async_engine()` pour le dialecte asynchrone.
Dans ce mode, chaque valeur liée doit avoir un type SQLAlchemy compatible avec ClickHouse. Les listes `IN` prises en charge deviennent des paramètres ClickHouse `Array` typés. Le compilateur lève une `CompileError` lorsqu’il ne peut pas déduire un type compatible ni traiter un bind en toute sécurité.
Les noms de bind doivent être des noms BareWord ASCII ClickHouse. Les noms qui commencent et se terminent par `$` sont rejetés, car le pilote principal les réserve aux paramètres de requête binaires bruts.
Requêtes Core
Le dialecte prend en charge les requêtes `SELECT` de SQLAlchemy Core avec des jointures, des filtres, le tri, des clauses LIMIT et OFFSET, `DISTINCT` et des sélections composées.
Les fonctions SQLAlchemy `union()`, `intersect()` et `except_()` sont compilées en `UNION DISTINCT`, `INTERSECT DISTINCT` et `EXCEPT DISTINCT` de ClickHouse. Leurs équivalents `union_all()`, `intersect_all()` et `except_all()` sont compilés en opérateurs `ALL` correspondants. Cette correspondance explicite préserve la sémantique des doublons de SQLAlchemy, quels que soient les paramètres par défaut de ClickHouse pour les opérations sur les ensembles.
```python theme={null}
from sqlalchemy import MetaData, Table, select
metadata = MetaData(schema="mydb")
users = Table("users", metadata, autoload_with=engine)
orders = Table("orders", metadata, autoload_with=engine)
events = Table("events", metadata, autoload_with=engine)
stmt = (
select(users.c.name, orders.c.product)
.select_from(users.join(orders, users.c.id == orders.c.user_id))
.order_by(users.c.name)
.limit(10)
)
with engine.connect() as conn:
rows = conn.execute(stmt).all()
```
Le `DELETE` léger est pris en charge et nécessite une clause `WHERE` explicite :
```python theme={null}
from sqlalchemy import delete
stmt = delete(users).where(users.c.name.like("%temporary%"))
with engine.connect() as conn:
conn.execute(stmt)
```
Rendu des littéraux
Lorsque SQLAlchemy intègre une valeur liée directement dans la requête via `literal_binds` ou `literal_execute`, le dialecte utilise les règles de guillemetage de ClickHouse pour les types de chaînes génériques et les types ClickHouse. Cela s'applique également aux wrappers `TypeDecorator` et aux sélections `with_variant()`. Les valeurs de chaîne conservent les signes de pourcentage et les barres obliques inverses, même lorsque d'autres paramètres liés sont présents.
Les valeurs Python `datetime` associées à un type SQLAlchemy ClickHouse `DateTime64` conservent leurs microsecondes dans les paramètres côté client et dans les littéraux intégrés, y compris les valeurs nullables et les valeurs imbriquées dans des tableaux et des tuples. ClickHouse applique ensuite la précision déclarée. Le type Python `datetime` fournit jusqu'à six chiffres après la virgule. Les valeurs `DateTime` simples restent formatées à la seconde près. Pour un statement `text()`, spécifiez explicitement le type avec `bindparam("ts", type_=DateTime64(6))` afin de préserver les fractions de seconde.
Les types de colonnes SQLAlchemy doivent correspondre au schéma du serveur. Déclarer `DateTime64` sur une colonne `DateTime` côté serveur produit des fractions de seconde et peut provoquer des erreurs de conversion lors de l'insertion et dans les comparaisons `IN`.
Avec SQLAlchemy 2.x, les littéraux intégrés de types génériques `sqlalchemy.ARRAY` contenant des éléments ClickHouse `Tuple` nécessitent `dimensions=1`, ou un nombre de dimensions supérieur adapté pour les tableaux imbriqués, afin que SQLAlchemy traite chaque tuple comme un seul élément. SQLAlchemy 1.4 ne prend pas en charge les littéraux intégrés pour les types génériques `ARRAY`.
Si un paramètre datetime nommé est réutilisé, chaque occurrence doit avoir un type de liaison `DateTime64` compatible pour que les fractions soient préservées. Une occurrence non typée ou un type en conflit entraîne un formatage à la seconde près. Définissez `type_=DateTime64(6)` sur chaque `bindparam`, ou utilisez des noms de paramètres distincts avec les types appropriés.
Indications de type JSON
Déclarez des chemins JSON typés grâce au mappage `typed_paths`. Un type de chemin peut être une classe de type ClickHouse SQLAlchemy, une instance configurée ou une chaîne contenant un nom de type ClickHouse. Les chaînes de noms de type prennent en charge les types dépourvus de constructeur SQLAlchemy, comme `Dynamic`, et peuvent également servir à exprimer des types configurés complexes. Elles préservent les noms dans un `Tuple` nommé.
Les chaînes de noms de type peuvent contenir des types JSON imbriqués configurés, par exemple ``Array(JSON(`child` UInt32))``. Dans ces chaînes, les noms de types ClickHouse reconnus sont insensibles à la casse et sont restitués avec leur capitalisation canonique. Une chaîne doit contenir une seule expression de type complète. Tout texte résiduel en fin de chaîne ainsi que les arguments JSON imbriqués mal formés sont rejetés.
Un `Tuple()` vide n'est pas pris en charge comme chemin typé JSON, car ClickHouse ne peut pas le sérialiser via le format Native d'une colonne JSON. Le pilote principal prend en charge `Tuple()` dans les colonnes de requête et d'insertion à n'importe quelle position, y compris imbriqué dans des tuples positionnels ou nommés, à l'intérieur d'un `Array`, et sous la forme `Nullable(Tuple())` lorsque le serveur l'autorise.
```python theme={null}
from sqlalchemy import Column, MetaData, Table
from clickhouse_connect.cc_sqlalchemy.datatypes.sqltypes import JSON, UInt32
events = Table(
"events",
MetaData(),
Column(
"payload",
JSON(
typed_paths={
"event.id": UInt32,
"details": "Tuple(id UInt32, label Nullable(String))",
"attributes": "Variant(String, Array(String))",
},
max_dynamic_paths=256,
max_dynamic_types=16,
skip_paths=["internal.debug"],
skip_regexps=[r"^private\."],
),
),
)
```
Pour les chemins correspondant à des identifiants Python simples, les arguments nommés sont un raccourci pour `typed_paths`, par exemple `JSON(user_id=UInt32)`. Utilisez `typed_paths` pour les chemins pointés, les espaces, les accents graves, les points encodés `%2E` ou les noms qui coïncident avec des options du constructeur. Un typed path nommé `SKIP` est pris en charge via la table de correspondance. Les clés de `typed_paths` et les valeurs de `skip_paths` sont des noms décodés. Les accents graves et les double quotes en début ou en fin sont traités comme des caractères littéraux du chemin, et non comme un quoting SQL déjà appliqué. À l'intérieur d'une type string brute, les accents graves et les double quotes relèvent de la syntaxe d'identifiant ClickHouse.
Il est possible de configurer jusqu'à 1000 typed paths. `max_dynamic_paths` accepte des valeurs de 0 à 10000. `max_dynamic_types` accepte des valeurs de 0 à 254. Ces plages s'appliquent également à l'intérieur des type strings JSON imbriquées brutes. Les valeurs par défaut explicites du server, 1024 et 32, sont omises du DDL généré. Les skip paths simples sont dédupliqués. Python ne valide pas les chaînes de regular expression, car ClickHouse utilise la syntaxe RE2. Les regular expressions en double sont conservées.
Un skip path simple ne peut pas porter exactement le nom `REGEXP`, car ClickHouse réserve ce token pour `SKIP REGEXP`. Des noms tels que `REGEXP_foo` restent valides. Dans une type string JSON brute, un opérande `SKIP` simple doit être un identifiant ClickHouse unique ou un identifiant composé séparé par des points. Un identifiant composé non quoté ne peut pas commencer par `REGEXP` ; quotez ce premier composant lorsqu'il s'agit d'une donnée de chemin. `SKIP REGEXP` doit comporter un unique littéral de chaîne entre simples quotes. Quotez les parties d'identifiant avec des accents graves ou des double quotes lorsqu'elles contiennent des espaces ou de la ponctuation. Les type hints JSON bruts prennent en charge `Variant(...)` ; `Variant` standalone n'a pas de constructeur SQLAlchemy public. Les membres de `Variant` sont ordonnés et dédupliqués selon les mêmes noms canoniques que ceux utilisés par ClickHouse.
Le constructeur ordonne les arguments selon la même forme canonique que celle renvoyée par ClickHouse. Les types réfléchis, les copies de types SQLAlchemy et l'autogénération Alembic préservent la configuration.
Sous-colonnes JSON
Pour une colonne déclarée ou représentée en `JSON` ClickHouse, utilisez des crochets pour sélectionner un segment à la fois dans le chemin d'une sous-colonne stockée :
```python theme={null}
from sqlalchemy import Column, MetaData, Table, select
from clickhouse_connect.cc_sqlalchemy.datatypes.sqltypes import JSON, UInt32
events = Table(
"events",
MetaData(),
Column("payload", JSON),
)
request_id = events.c.payload["context"]["request"].subcolumn(
"id",
type_=UInt32,
)
stmt = select(
events.c.payload["severity"].label("severity"),
request_id.label("request_id"),
)
```
`payload["severity"]` est compilé selon la syntaxe d’identifiant pointé de ClickHouse. Chaque partie est entourée de guillemets séparément, par exemple `` `events`.`payload`.`severity` ``. Cette syntaxe lit la sous-colonne JSON stockée dans ClickHouse et n’appelle pas `getSubcolumn`. Chaînez `[]` ou `.subcolumn()` une fois pour chaque segment du chemin. Chaque segment doit être une chaîne non vide.
Passer `type_` à `.subcolumn()` encapsule le chemin pointé dans un `CAST` SQL et affecte ce type à l’expression SQLAlchemy. Sans `type_`, `.subcolumn("segment")` se comporte comme `["segment"]`.
Un chemin non typé a le type `Dynamic` de ClickHouse. ClickHouse n’autorise pas les valeurs `Dynamic` directement dans `ORDER BY` ou `GROUP BY`. Passez `type_` lorsqu’une sous-colonne y est utilisée.
Pour du code à typage statique, importez `json_subcolumn` depuis `clickhouse_connect.cc_sqlalchemy`. Cet assistant accepte également un segment à la fois et préserve le type de résultat Python de `type_` :
```python theme={null}
from clickhouse_connect.cc_sqlalchemy import json_subcolumn
context = json_subcolumn(events.c.payload, "context")
request = json_subcolumn(context, "request")
request_id = json_subcolumn(request, "id", type_=UInt32)
```
Dans cet exemple, les vérificateurs de types considèrent `request_id` comme un `ColumnElement[int]`.
Chaque segment est entouré de guillemets individuellement, y compris les noms contenant des espaces ou des accents graves. Les accents graves ne font pas d’un point un caractère littéral pour le traitement des chemins JSON par ClickHouse. Lorsque `json_type_escape_dots_in_keys` est activé, utilisez l’encodage `%2E` de ClickHouse pour les points littéraux dans les clés. Accédez à une clé nommée `a.b` avec `payload["a%2Eb"]`, et non `payload["a.b"]`.
Extensions des requêtes ClickHouse
Importez `select` depuis `clickhouse_connect.cc_sqlalchemy` pour exposer des méthodes ClickHouse typées aux outils de vérification statique des types. Le `sqlalchemy.select` standard propose également ces méthodes à l’exécution.
```python theme={null}
from clickhouse_connect.cc_sqlalchemy import select
stmt = (
select(events.c.user_id, events.c.event_type)
.final()
.prewhere(events.c.event_date >= "2026-01-01")
.sample(0.1)
.limit_by([events.c.user_id], 3)
)
```
Les méthodes `Select` de ClickHouse sont :
| Méthode | Fonctionnalité SQL |
| - | - |
| `.final()` | `FINAL` pour une table |
| `.sample(value)` | `SAMPLE`, à l’aide d’une fraction, d’un nombre de lignes ou d’une expression |
| `.prewhere(expression)` | `PREWHERE` ; les appels répétés sont combinés avec `AND` |
| `.limit_by(columns, limit, offset=None)` | `LIMIT ... BY` |
| `.array_join(...)` | `ARRAY JOIN` |
| `.left_array_join(...)` | `LEFT ARRAY JOIN` |
| `.ch_join(...)` | jointures ClickHouse avec les options `strictness`, `distribution`, `using` et `cross` |
| `.cte(name, materialized=True)` | `WITH name AS MATERIALIZED (...)` |
`Select.with_hint()` de SQLAlchemy est une API d’indication de table. Le dialecte ClickHouse ne génère pas d’indications de table. Une indication générique ou `clickhousedb` applicable émet un `SAWarning` et laisse le SQL généré inchangé. Utilisez `final()`, `sample()`, `prewhere()` ou `limit_by()` pour ces clauses ClickHouse.
`Select.with_statement_hint()` est une API de directive brute de fin. Elle ajoute le texte fourni à la fin du `SELECT` sans validation spécifique à ClickHouse. Elle reste disponible pour du SQL statique de confiance, tel que `SETTINGS max_threads=1` :
```python theme={null}
stmt = select(events.c.id).with_statement_hint("SETTINGS max_threads=1")
```
Pour les paramètres ClickHouse, privilégiez les options d’exécution afin que le driver les gère séparément du texte SQL :
```python theme={null}
stmt = select(events.c.id).execution_options(settings={"max_threads": 1})
```
Par exemple, un `GLOBAL ANY LEFT JOIN` ClickHouse peut être chaîné sans avoir à imbriquer une `FromClause` personnalisée :
```python theme={null}
stmt = (
select(events.c.id, users.c.name)
.select_from(events)
.ch_join(
users,
events.c.user_id == users.c.id,
isouter=True,
strictness="ANY",
distribution="GLOBAL",
)
)
```
Utilisez la syntaxe explicite `Lambda` pour les fonctions d’ordre supérieur de ClickHouse :
```python theme={null}
from sqlalchemy import column, func
from clickhouse_connect.cc_sqlalchemy import Lambda, select
stmt = select(
func.arrayMap(
Lambda("x", column("x") * 2),
events.c.metrics,
).label("doubled")
)
```
La construction standard SQLAlchemy `values()` est compilée vers la syntaxe de fonction de table `VALUES` de ClickHouse, y compris lorsqu'elle est utilisée dans une expression de table commune. La forme CTE nécessite SQLAlchemy 2.0.42 ou version ultérieure, où `Values.cte()` a été ajoutée.
CTE matérialisées
Par défaut, ClickHouse intègre une expression de table commune ; ainsi, le corps d’une CTE référencée plusieurs fois est exécuté une fois par référence. Passez `materialized=True` à `.cte()` pour générer `WITH AS MATERIALIZED (...)`, ce qui calcule le corps une seule fois :
```python theme={null}
from sqlalchemy import func
from clickhouse_connect.cc_sqlalchemy import select
ranked = (
select(book.c.book_id, func.row_number().over(order_by=book.c.score.desc()).label("result_rank"))
.where(book.c.genre == "sci-fi")
.order_by(book.c.score.desc())
.limit(100)
.cte("ranked", materialized=True)
)
stmt = (
select(book.c.book_id, ranked.c.result_rank)
.select_from(book)
.ch_join(ranked, book.c.book_id == ranked.c.book_id, strictness="ANY")
.where(book.c.book_id.in_(select(ranked.c.book_id)))
.execution_options(settings={"enable_materialized_cte": 1, "enable_analyzer": 1})
)
```
Le serveur ne matérialise la CTE que si le mot-clé est présent, si `enable_materialized_cte=1` et si l’analyseur est activé. Définissez `enable_materialized_cte` au niveau de l’instruction, de la connexion ou du moteur, comme indiqué dans [Paramètres par requête](#sqlalchemy-per-query-settings). L’analyseur est activé par défaut sur tous les serveurs prenant en charge cette fonctionnalité. Définir explicitement `enable_analyzer=1` constitue donc une mesure de précaution. `enable_materialized_cte` est un paramètre ClickHouse expérimental. Avec `enable_materialized_cte=0` ou `enable_analyzer=0`, la requête aboutit et renvoie les mêmes lignes. ClickHouse ignore silencieusement `MATERIALIZED` et intègre de nouveau la CTE, de sorte qu’un paramètre oublié dégrade les performances sans générer d’erreur. Les CTE matérialisées nécessitent ClickHouse 26.3 ou une version ultérieure. Les serveurs plus anciens rejettent le mot-clé avec une erreur de syntaxe.
Pour une instruction construite avec le `sqlalchemy.select` standard, utilisez plutôt `cte()` au niveau du module. Cette fonction prend l’instruction comme premier argument et correspond par ailleurs à `Select.cte()` :
```python theme={null}
from sqlalchemy import select as sa_select
from clickhouse_connect.cc_sqlalchemy import cte
ranked = cte(sa_select(book.c.book_id), "ranked", materialized=True)
```
Le mot-clé est généré uniquement avec le dialecte ClickHouse. Une instruction partagée avec un autre backend y est donc compilée sans modification.
ClickHouse ne prend pas en charge les CTE matérialisées récursives. Les helpers SQLAlchemy lèvent une `ValueError` lorsque `recursive=True` et `materialized=True` sont tous deux définis.
DDL et introspection
ClickHouse Connect fournit les types de données ClickHouse, les moteurs de table, les structures de dictionnaire, le DDL des bases de données et l’introspection des tables.
Les colonnes `Variant` autonomes sont introspectées via un type SQLAlchemy interne, et l’autogénération d’Alembic préserve leurs noms de types bruts canoniques sans changements de type répétés. Les colonnes `Geometry` et `MultiPoint` sont introspectées en tant que types SQLAlchemy publics.
```python theme={null}
import sqlalchemy as db
from sqlalchemy import MetaData
from clickhouse_connect.cc_sqlalchemy.datatypes.sqltypes import DateTime64, String, UInt32
from clickhouse_connect.cc_sqlalchemy.ddl.custom import CreateDatabase
from clickhouse_connect.cc_sqlalchemy.ddl.tableengine import MergeTree
with engine.connect() as conn:
conn.execute(CreateDatabase("example_db", exists_ok=True))
metadata = MetaData(schema="example_db")
events = db.Table(
"events",
metadata,
db.Column("id", UInt32, primary_key=True),
db.Column("user", String),
db.Column("created_at", DateTime64(3)),
MergeTree(order_by="id"),
)
events.create(conn)
reflected = db.Table("events", MetaData(schema="example_db"), autoload_with=conn)
assert reflected.engine is not None
```
Les colonnes introspectées utilisent `server_default` pour les expressions `DEFAULT`, ainsi que des attributs propres au dialecte tels que `clickhouse_codec`, `clickhouse_ttl`, `clickhouse_materialized` et `clickhouse_alias`, lorsqu’ils sont présents.
Les valeurs de chaîne dans les clauses `DEFAULT`, `MATERIALIZED`, `ALIAS` et `TTL` utilisent les règles d’échappement des chaînes de caractères de ClickHouse. Le même échappement s’applique aux commentaires de tables, de dictionnaires et de colonnes, y compris aux commentaires générés par Alembic.
Les arguments de clé MergeTree tels que `order_by`, `partition_by`, `primary_key`, `sample_by` et `ttl` acceptent des colonnes SQLAlchemy, des expressions SQL ainsi que de simples chaînes de caractères.
`Memory()`, `Log()`, `StripeLog()`, `TinyLog()`, `Null()` et `Set()` acceptent zéro argument et sont restitués à l’identique après un cycle complet d’autogénération Alembic. L’argument de dictionnaire existant reste pris en charge. Utilisez `settings={...}` pour fournir les paramètres du moteur.
`SummingMergeTree` et `ReplicatedSummingMergeTree` acceptent un argument `columns` facultatif, à passer uniquement par mot-clé. Les arguments positionnels existants conservent leur signification : `SummingMergeTree("id")` définit donc toujours `ORDER BY id`.
```python theme={null}
from clickhouse_connect.cc_sqlalchemy.ddl.tableengine import SummingMergeTree
engine_clause = SummingMergeTree("id", columns=("delta", "n_tx"))
# Sum delta and n_tx for rows with the same id.
```
Passez une chaîne de caractères, une colonne SQLAlchemy, un attribut de colonne mappée, ou une liste ou un tuple non vide de ces valeurs. Les chaînes contenues dans une liste ou un tuple sont mises entre guillemets en tant qu’identifiants. Une chaîne scalaire fournit du SQL brut, par exemple `"delta"` ou `"(delta, n_tx)"`. Le serveur exige des identifiants pour ces colonnes. Omettez `columns` pour laisser ClickHouse sélectionner les colonnes à additionner. L’introspection et l’autogénération d’Alembic conservent une liste de colonnes explicite.
Insertions et utilisation de l’ORM de base
Les insertions Core et les modèles ORM simples sont pris en charge. Pour le dialecte synchrone, privilégiez les insertions Core via executemany pour les flux de données volumineux compatibles. Pour les insertions en masse asynchrones, utilisez la méthode native `AsyncClient.insert()` décrite dans [Connexions asynchrones](#sqlalchemy-async-connections).
```python theme={null}
with engine.connect() as conn:
conn.execute(
events.insert(),
[
{"id": 13, "user": "user_1"},
{"id": 79, "user": "user_2"},
],
)
```
Pour le dialecte synchrone, les insertions Core `executemany` simples générées par le compilateur SQLAlchemy utilisent une seule insertion en masse Native. En mode asynchrone, executemany envoie une requête par jeu de paramètres, comme décrit dans [Connexions asynchrones](#sqlalchemy-async-connections). Le SQL brut et les insertions contenant des expressions ou d’autres sémantiques qui ne peuvent pas être acheminées de manière sûre conservent le SQL d’origine et sont exécutés une fois par jeu de paramètres. Si un jeu de paramètres ultérieur échoue, les lignes écrites par les jeux de paramètres précédents restent validées.
Les statements multi-lignes explicites `insert(events).values([...])` acceptent des lignes sous forme de dictionnaires, des tuples dans l’ordre des colonnes de la table et des expressions SQL par ligne. Pandas `to_sql(method="multi")` utilise cette forme. Les lignes sont bien insérées, mais la méthode renvoie `0`, car les statements INSERT textuels signalent un nombre de lignes de `0` via le curseur DB-API. SQLAlchemy détermine la liste des colonnes à partir de la première ligne. Les clés de dictionnaire supplémentaires dans les lignes suivantes et les valeurs de tuple situées hors de cette liste de colonnes sont ignorées. Si une ligne ultérieure ne fournit pas l’une des valeurs sélectionnées, la compilation échoue. Veillez à ce que toutes les lignes comportent les mêmes colonnes.
Avec les limites de formulaire HTTP par défaut de ClickHouse 26.4 et versions ultérieures, `server_side_params=True` ne convient qu’aux petits lots explicites, en dessous d’environ 1000 valeurs liées, en gardant une marge pour les autres champs. La configuration du serveur permet de relever ce plafond. Pour les grands lots simples avec le dialecte synchrone, passez les lignes comme second argument de `execute()` afin que le pilote puisse utiliser son chemin d’insertion en masse Native. Pour les données en masse en mode asynchrone, utilisez `await` sur la méthode native `AsyncClient.insert()`.
```python theme={null}
import sqlalchemy as db
from sqlalchemy import MetaData
from sqlalchemy.orm import Session, declarative_base
from clickhouse_connect.cc_sqlalchemy.datatypes.sqltypes import String, UInt32
from clickhouse_connect.cc_sqlalchemy.ddl.tableengine import MergeTree
Base = declarative_base(metadata=MetaData(schema="example_db"))
class User(Base):
__tablename__ = "users"
__table_args__ = (MergeTree(order_by=["id"]),)
id = db.Column(UInt32, primary_key=True)
name = db.Column(String)
Base.metadata.create_all(engine)
with Session(engine) as session:
session.add(User(id=13, name="user_1"))
session.bulk_save_objects([User(id=79, name="user_2")])
session.commit()
```
Migrations avec Alembic
ClickHouse Connect inclut une intégration à Alembic pour les migrations de schéma de ClickHouse. Installez-la avec :
```bash theme={null}
pip install "clickhouse-connect[alembic]"
```
Pour les migrations via le dialecte asynchrone, installez les deux extras :
```bash theme={null}
pip install "clickhouse-connect[alembic,sqlalchemy-async]"
```
Créez un projet Alembic asynchrone, puis remplacez l’environnement qu’il a généré par l’exemple adapté à ClickHouse :
```bash theme={null}
alembic init -t async alembic
```
Le fichier `alembic.ini` généré utilise `script_location = %(here)s/alembic`. Conservez ce paramètre si le répertoire de migration s’appelle `alembic`, ou remplacez-le par le répertoire passé à `alembic init`. Remplacez `alembic/env.py` par l’[exemple de `env.py` Alembic asynchrone](https://github.com/ClickHouse/clickhouse-connect/blob/main/examples/alembic_async/env.py) versionné dans le dépôt, puis définissez `sqlalchemy.url` dans `alembic.ini`.
Importez `clickhouse_connect.cc_sqlalchemy.alembic` dans le fichier `env.py` d’Alembic pour enregistrer l’intégration du dialecte. L’autogénération prend en charge les évolutions courantes des tables, notamment la création et la suppression de tables, l’ajout, la modification et la suppression de colonnes, les valeurs par défaut et les commentaires. Utilisez des opérations manuelles pour renommer les tables et les colonnes. Examinez chaque migration générée avant de l’appliquer.
Les fonctions de migration d’Alembic restent synchrones. Un environnement asynchrone crée un `AsyncEngine`, ouvre une `AsyncConnection` et transmet la fonction de migration synchrone à `await connection.run_sync(...)`. Les migrations hors ligne appellent directement `context.configure(url=..., literal_binds=True, dialect_opts={"paramstyle": "named"})` et ne créent pas de moteur. L’[exemple de `env.py` Alembic asynchrone](https://github.com/ClickHouse/clickhouse-connect/blob/main/examples/alembic_async/env.py) versionné dans le dépôt couvre ces deux cas et lit l’URL de connexion via la configuration standard `sqlalchemy.url` d’Alembic. Il reprend les hooks et options Alembic de ClickHouse de l’exemple complet, notamment `include_object`, `make_include_name(...)`, `clickhouse_writer` et `version_table`. N’utilisez pas `engine.sync_engine` pour exécuter ou libérer des migrations asynchrones.
Les helpers `op.*` spécifiques à ClickHouse couvrent :
* les index de saut de données, y compris les opérations d’ajout, de matérialisation et de suppression.
* les projections, y compris les opérations d’ajout, de matérialisation et de suppression.
* la modification et la réinitialisation des paramètres de table MergeTree.
* la création et la suppression de vues matérialisées.
* la création, la suppression et le rechargement de dictionnaires.
Les index de saut de données de ClickHouse ne sont pas des index SQLAlchemy. `Index`, `Column(index=True)`, `op.create_index` et `op.drop_index` sont rejetés afin d’éviter un DDL partiel ou incorrect. Utilisez `op.add_clickhouse_index` et `op.drop_clickhouse_index`.
Consultez l’[exemple complet d’Alembic](https://github.com/ClickHouse/clickhouse-connect/blob/main/clickhouse_connect/cc_sqlalchemy/alembic/WORKED_EXAMPLE.md). Les utilisateurs qui migrent depuis `clickhouse-sqlalchemy` devraient également lire le [guide de migration](https://github.com/ClickHouse/clickhouse-connect/blob/main/clickhouse_connect/cc_sqlalchemy/MIGRATING_FROM_CLICKHOUSE_SQLALCHEMY.md).
Portée et limites
* ClickHouse ne fournit pas de transactions traditionnelles via ce dialecte HTTP. `engine.begin()` et `Session.commit()` organisent le travail côté Python, mais commit et rollback sont des opérations sans effet côté serveur.
* `UPDATE`, les transactions en deux phases, les séquences, `RETURNING` et les niveaux d’isolation avancés ne sont pas implémentés par ce dialecte. Utilisez du ClickHouse SQL explicite pour les mutations côté serveur si nécessaire.
* `Column(..., primary_key=True)` fournit l’identité de l’objet SQLAlchemy. Cela ne crée pas de contrainte d’unicité côté serveur. Définissez le tri et, si nécessaire, les expressions de clé primaire via le moteur de table.
* Les métadonnées traditionnelles de clés étrangères, de contraintes d’unicité et d’index standard ne sont pas disponibles, car ClickHouse n’applique pas ces contraintes.
* La gestion des relations ORM, les mises à jour de type unit-of-work, les cascades, ainsi que le chargement eager ou lazy des relations, ne font pas partie du périmètre ORM pris en charge.