Skip to main content

tidbcloudlake-sqlalchemy

TiDB Cloud Lake dialect for SQLAlchemy.

Installation

The package is installable through PIP:

pip install tidbcloudlake-sqlalchemy

Usage

Use a lake:// URL with SQLAlchemy. The database name and warehouse are part of the URL:

from sqlalchemy import create_engine, text

engine = create_engine(
    "lake://<username>:<password>@<host>:443/default?warehouse=default"
)
with engine.connect() as connection:
    assert connection.execute(text("SELECT 1")).scalar_one() == 1

Merge Command Support

TiDB Cloud Lake SQLAlchemy supports upserts via its Merge custom expression. See Merge for full documentation.

The Merge command can be used as below:

from sqlalchemy.orm import sessionmaker
from sqlalchemy import MetaData, create_engine
from tidbcloudlake_sqlalchemy.tidbcloudlake_dialect import Merge

engine = create_engine("lake://<username>:<password>@<host>:443/default?warehouse=default")
session = sessionmaker(bind=engine)()
connection = engine.connect()

meta = MetaData()
meta.reflect(bind=session.bind)
t1 = meta.tables['t1']
t2 = meta.tables['t2']

merge = Merge(target=t1, source=t2, on=t1.c.t1key == t2.c.t2key)
merge.when_matched_then_delete().where(t2.c.marked == 1)
merge.when_matched_then_update().where(t2.c.isnewstatus == 1).values(val = t2.c.newval, status=t2.c.newstatus)
merge.when_matched_then_update().values(val=t2.c.newval)
merge.when_not_matched_then_insert().values(val=t2.c.newval, status=t2.c.newstatus)
connection.execute(merge)

Copy Into Command Support

TiDB Cloud Lake SQLAlchemy supports copy into operations through its CopyIntoTable and CopyIntoLocation methods. See CopyIntoLocation or CopyIntoTable for full documentation.

The CopyIntoTable command can be used as below:

import base64

from sqlalchemy.orm import sessionmaker
from sqlalchemy import MetaData, create_engine
from tidbcloudlake_sqlalchemy import (
    CopyIntoTable, GoogleCloudStorage, ParquetFormat, CopyIntoTableOptions,
    FileColumnClause, CSVFormat, Compression,
)

engine = create_engine("lake://<username>:<password>@<host>:443/default?warehouse=default")
session = sessionmaker(bind=engine)()
connection = engine.connect()

meta = MetaData()
meta.reflect(bind=session.bind)
t1 = meta.tables['t1']
t2 = meta.tables['t2']
gcs_private_key = 'full_gcs_json_private_key'
case_sensitive_columns = True

copy_into = CopyIntoTable(
    target=t1,
    from_=GoogleCloudStorage(
        uri='gcs://bucket-name/path/to/file',
        credentials=base64.b64encode(gcs_private_key.encode()).decode(),
    ),
    file_format=ParquetFormat(),
    options=CopyIntoTableOptions(
        force=True,
        column_match_mode='CASE_SENSITIVE' if case_sensitive_columns else None,
    )
)
result = connection.execute(copy_into)
result.fetchall()  # always call fetchall() to ensure the cursor executes to completion

# More involved example with column selection clause that can be altered to perform operations on the columns during import.

copy_into = CopyIntoTable(
    target=t2,
    from_=FileColumnClause(
        columns=', '.join([
            f'${index + 1}'
            for index, column in enumerate(t2.columns)
        ]),
        from_=GoogleCloudStorage(
            uri='gcs://bucket-name/path/to/file',
            credentials=base64.b64encode(gcs_private_key.encode()).decode(),
        )
    ),
    pattern='*.*',
    file_format=CSVFormat(
        record_delimiter='\n',
        field_delimiter=',',
        quote='"',
        escape='',
        skip_header=1,
        empty_field_as='NULL',
        compression=Compression.AUTO,
    ),
    options=CopyIntoTableOptions(
        force=True,
    )
)
result = connection.execute(copy_into)
result.fetchall()  # always call fetchall() to ensure the cursor executes to completion

The CopyIntoLocation command can be used as below:

import base64

from sqlalchemy import MetaData, create_engine, select
from sqlalchemy.orm import sessionmaker
from tidbcloudlake_sqlalchemy import (
    CopyIntoLocation, GoogleCloudStorage, ParquetFormat, CopyIntoLocationOptions,
)

engine = create_engine("lake://<username>:<password>@<host>:443/default?warehouse=default")
session = sessionmaker(bind=engine)()
connection = engine.connect()

meta = MetaData()
meta.reflect(bind=session.bind)
t1 = meta.tables['t1']
gcs_private_key = 'full_gcs_json_private_key'

copy_into = CopyIntoLocation(
    target=GoogleCloudStorage(
        uri='gcs://bucket-name/path/to/target_file',
        credentials=base64.b64encode(gcs_private_key.encode()).decode(),
    ),
    from_=select(t1).where(t1.c['col1'] == 1),
    file_format=ParquetFormat(),
    options=CopyIntoLocationOptions(
        single=True,
        overwrite=True,
        include_query_id=False,
        use_raw_path=True,
    )
)
result = connection.execute(copy_into)
result.fetchall()  # always call fetchall() to ensure the cursor executes to completion

Table Options

TiDB Cloud Lake SQLAlchemy supports Lake-specific table options for Engine, Cluster Keys and Transient tables

The table options can be used as below:

from sqlalchemy import Column, Integer, MetaData, String, Table, cast, create_engine

engine = create_engine("lake://<username>:<password>@<host>:443/default?warehouse=default")

meta = MetaData()
# Example of Transient Table
t_transient = Table(
    "t_transient",
    meta,
    Column("c1", Integer),
    lake_transient=True,
)

# Example of Engine
t_engine = Table(
    "t_engine",
    meta,
    Column("c1", Integer),
    lake_engine='Memory',
)

# Examples of Table with Cluster Keys
t_cluster_1 = Table(
    "t_cluster_1",
    meta,
    Column("c1", Integer),
    lake_cluster_by=[c1],
)
#
c = Column("id", Integer)
c2 = Column("Name", String)
t_cluster_2 = Table(
    't_cluster_2',
    meta,
    c,
    c2,
    lake_cluster_by=[cast(c, String), c2],
)

meta.create_all(engine)

Release Notes

0.5.8

  • Security: the __repr__ of the COPY INTO storage clauses (AmazonS3, AzureBlobStorage, GoogleCloudStorage) no longer renders cloud credentials. Secret-bearing fields (SECRET_ACCESS_KEY, MASTER_KEY, SECURITY_TOKEN, ACCESS_KEY_ID, the Azure ACCOUNT_KEY and the GCS CREDENTIAL service-account key) are shown as '***' ('<unset>' when not set), so a repr, a log record or a pytest assertion dump can be shared safely. The compiled SQL still carries the real values: str(stmt) / print(stmt) are unchanged and must not be logged.

  • Fixed two latent repr bugs on the same path: AmazonS3.__repr__ used to raise AttributeError when optional fields (endpoint_url and friends) were unset, and the CopyIntoTable / CopyIntoLocation reprs used to raise UnsupportedCompilationError when the target was a StageClause or a storage clause (the target was rendered with str(), i.e. compiled). The repr is now complete and stable instead of truncated. Behaviour change: repr(CopyIntoTable(target=<TableClause>)) now prints the SQLAlchemy object text instead of schema.table; use str(stmt) to compile the statement.

  • ParamEscaper.escape_args error messages now report the parameter type (e.g. Unsupported param format: SecretParams) instead of interpolating the parameter values into the exception text.

Metadata

Release files for tidbcloudlake-sqlalchemy 0.5.8

For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.

Source distribution (sdist)

Source distribution for tidbcloudlake-sqlalchemy 0.5.8
File Size Uploaded Provenance Actions
tidbcloudlake_sqlalchemy-0.5.8.tar.gz 42.9 kB With provenance Details for tidbcloudlake_sqlalchemy-0.5.8.tar.gz

Built distribution (wheel)

Table of built distributions (wheels) for tidbcloudlake-sqlalchemy 0.5.8
File Interpreter ABI Platform Actions
tidbcloudlake_sqlalchemy-0.5.8-py3-none-any.whl Python 3 none any Details for tidbcloudlake_sqlalchemy-0.5.8-py3-none-any.whl

Total release size: 77.7 kB

Release files / tidbcloudlake_sqlalchemy-0.5.8.tar.gz

Download URL tidbcloudlake_sqlalchemy-0.5.8.tar.gz
Size 42.9 kB
Tags Source
SHA-256 checksum
How to use checksums
aeb6f1f3604ad81a53fbdb664b27d7c8538ff6b6485a77bfb88c63a91fb422c5
BLAKE2b-256 checksum
How to use checksums
2e55947bf457cded2da808c08921a7b7045879cbe6714629705bbe054fda6522
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
Yes
Uploaded via twine/7.0.0 CPython/3.13.16

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

PyPI Publish Attestation

PyPI verified that this artifact, at this checksum, originated from the publisher listed below.

Signed by GitHub Actions, verified by PyPI on Nov 3, 2026.

Transparency log

Release files / tidbcloudlake_sqlalchemy-0.5.8-py3-none-any.whl

Download URL tidbcloudlake_sqlalchemy-0.5.8-py3-none-any.whl
Size 34.7 kB
Tags Python 3
SHA-256 checksum
How to use checksums
fdc17c01e1fb0fab078d74d953d39d2cc17ab768e14cb463885830ac6fd3c748
BLAKE2b-256 checksum
How to use checksums
0c6c94647c1c300c4a5a9df654a022f7c1fa39aaadfbe1fcdf5573c81a39fbcf
Upload date
Uploaded using Trusted Publishing?
What is trusted publishing?
Yes
Uploaded via twine/7.0.0 CPython/3.13.16

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

PyPI Publish Attestation

PyPI verified that this artifact, at this checksum, originated from the publisher listed below.

Signed by GitHub Actions, verified by PyPI on Nov 3, 2026.

Transparency log

Security for tidbcloudlake-sqlalchemy 0.5.8

Provenance

Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.

Full provenance

Both files in this release have been uploaded with provenance metadata.

Select from 2 files

Attestation for tidbcloudlake_sqlalchemy-0.5.8.tar.gz

PyPI Publish Attestation

PyPI verified that this artifact, at this checksum, originated from the publisher listed below.

Signed by GitHub Actions, verified by PyPI on Nov 3, 2026.

Transparency log

A note on package security

PyPI is a repository for distributing packages, not a service for auditing them. While third-party security researchers and our staff do their best to detect and remove malicious packages, PyPI cannot guarantee the quality, security or suitability of any given package.

To learn more about protecting yourself from malicious actors, visit our package security documentation.

Anthropic, PBC Visionary sponsor Bloomberg Visionary sponsor Hudson River Trading Visionary sponsor Meta Visionary sponsor NVIDIA Visionary sponsor Microsoft Sustainability sponsor Depot Continuous Integration AWS Cloud computing and Security Sponsor Datadog Monitoring Fastly CDN Google Download Analytics Sentry Error logging StatusPage Status page