Batch loading with pandas, PETL, Polars and Dask

Route and prerequisites

Complete the connection and fixtures. Validation invoked pandas to_sql, PETL appenddb, Polars write_database, Dask to_sql and native transformation APIs. Updates, deletes, replay and DDL used explicit SQL effect adapters; these batch APIs do not supply CDC automatically. Reference versions include pandas 3.0.6, Polars 2.0.0, SQLAlchemy 2.1.4 and PyMySQL 1.2.3; reference PETL 1.7.29 and Dask 2026.8.0, with pinned dependencies.

Install pandas petl polars dask[dataframe] pyarrow sqlalchemy pymysql. The following blocks are four alternative loading routes to save and run separately. They use different example IDs; repeating an append raises a primary-key conflict. Use a pre-created destination rather than replacing a business table with if_exists="replace".

pandas: explicit dtype and transaction

Keep decimals as Decimal in an object column and define integer columns as int64. The pre-created table owns the final SQL types; dtype specifies bind types. Pass a transactional connection to to_sql when a load and other SQL effects must share a transaction.

from decimal import Decimal
import pandas as pd
import sqlalchemy as sa
from sqlalchemy.dialects import mysql
from mo_common import engine

frame = pd.DataFrame({
    "id": pd.Series([101], dtype="int64"),
    "amount": pd.Series([Decimal("12345678901234567890.123456789012345678")], dtype=object),
    "payload": pd.Series([b"\x00\xff\x80"], dtype=object),
    "label": ["pandas中文😀"], "rev": pd.Series([0], dtype="int64")})
frame["label"] = frame["label"] + "→ETL"
types = dict(id=sa.BigInteger(), amount=mysql.DECIMAL(38, 18),
             payload=mysql.VARBINARY(256), label=sa.String(256), rev=sa.BigInteger())
e = engine()
try:
    with e.begin() as c:
        frame.to_sql("lake_demo", c, if_exists="append", index=False, chunksize=31, dtype=types)
finally:
    e.dispose()

PETL: DBAPI and identifier quoting

PETL database writing uses double-quoted identifiers. This route enabled ANSI_QUOTES on a dedicated connection while retaining other sql_mode entries; keep the setting session-local.

from decimal import Decimal
import petl as etl
from mo_common import connect

t = etl.wrap([("id", "amount", "payload", "label", "rev"),
              (102, Decimal("0.000000000000000001"), b"", "PETL😀", 0)])
t = t.convert("label", lambda value: value + "→ETL")
with connect() as d:
    with d.cursor() as c:
        c.execute("SELECT @@sql_mode")
        modes = [x for x in c.fetchone()[0].split(",") if x]
        if "ANSI_QUOTES" not in modes:
            modes.append("ANSI_QUOTES")
        c.execute("SET SESSION sql_mode=%s", (",".join(modes),))
    try:
        etl.appenddb(t, d, "lake_demo", commit=True)
    except Exception:
        d.rollback()
        raise

Polars: Arrow types and SQLAlchemy writing

Arrow decimal128 retains precision 38. Convert duration columns such as negative TIME to a defined SQL time string before SQL loading rather than letting them become a time of day or an untyped integer.

from decimal import Decimal
import pyarrow as pa
import polars as pl
import sqlalchemy as sa
from sqlalchemy.dialects import mysql
from mo_common import engine

schema = pa.schema([("id", pa.int64()), ("amount", pa.decimal128(38,18)),
                    ("payload", pa.binary()), ("label", pa.string()), ("rev", pa.int64())])
t = pl.from_arrow(pa.Table.from_pylist([dict(id=103, amount=Decimal("0.000000000000000001"),
                                           payload=b"", label="Polars😀", rev=0)], schema=schema))
t = t.with_columns((pl.col("label") + pl.lit("→ETL")).alias("label"))
e = engine()
try:
    t.write_database("lake_demo", e, if_table_exists="append", engine="sqlalchemy",
                     engine_options={"chunksize":31, "dtype":{"amount":mysql.DECIMAL(38,18),
                       "payload":mysql.VARBINARY(256), "id":sa.BigInteger(), "rev":sa.BigInteger()}})
finally:
    e.dispose()

Dask: partitions are not a global transaction

Disable automatic string conversion so Decimal or bytes do not become strings. Even with parallel=False, partitions can commit in separate transactions; a to_sql call is not a global rollback boundary. Use frame and types from the pandas example, first changing the ID to 104, and reuse the shared engine URL and connection arguments.

import dask
import dask.dataframe as dd
from mo_common import engine, options

dask.config.set({"dataframe.convert-string": False})
frame["id"] = 104
e = engine()
try:
    kw = options()
    args = {k:kw[k] for k in ("charset", "connect_timeout", "read_timeout", "write_timeout")}
    import os
    if os.environ.get("MO_SSL_CA"):
        args.update(ssl={"ca":os.environ["MO_SSL_CA"]}, ssl_verify_cert=True, ssl_verify_identity=True)
    parts = dd.from_pandas(frame, npartitions=2, sort=False)
    parts.to_sql("lake_demo", e.url.render_as_string(hide_password=False),
                 if_exists="append", index=False, chunksize=31, dtype=types,
                 parallel=False, engine_kwargs={"pool_size":2,"max_overflow":0,"connect_args":args})
finally:
    e.dispose()

Readback, replay and load

Read IDs101–104 through a fresh connection and check the full decimal string, HEX, Unicode label and rev. Keep secrets in the URL out of logs. After a partial failure, identify committed partitions and recover through batch-keyed staging plus an explicit merge, rather than appending all partitions again.

Start with batch size 31 and low parallelism, measure actual connection counts, then increase load. The main plan covered 5000 scenario combinations per tool, including native transforms and explicit SQL effects. This does not establish native update, delete, schema evolution or exactly-once behavior in each DataFrame API.