Day 05 of the WClickHouse Open-Source Engineering Series.

Never use df.iterrows() to insert data into an analytical database. WClickHouse insert_dataframe() loads dataframes into ClickHouse at wire speed.

The Pain Points We Faced

• Data scientists using df.iterrows() or df.to_dict('records') taking 20 minutes to load data
• Type conversion mismatches between Pandas float64/NaN and ClickHouse Nullable types
• Crashing Jupyter notebooks trying to export large experimental datasets

The Implementation

import pandas as pd
from wclickhouse import WClickHouse

# Real-world Pandas DataFrame from ETL or model training
df = pd.DataFrame({
    "user_id": range(100000),
    "feature_score": [0.85] * 100000,
    "timestamp": pd.date_range("2026-01-01", periods=100000, freq="s")
})

db = WClickHouse(FeatureModel, db_config)
db.insert_dataframe(df)  # Vectorized native ingestion!

Why This Architecture Wins

• insert_dataframe(): One-line insertion of Pandas DataFrames using vectorized backend.
• Sub-Second Ingestion: Ingest 100,000 DataFrame rows in under 0.6 seconds.
• Automatic Type Alignment: Maps Pandas dtypes cleanly to ClickHouse table schema.

Verification & Status

Tested and verified against live ClickHouse server instances with 95%+ test coverage. Built for Python 3.9 through 3.14 with Apache Arrow and Pydantic v2.

• GitHub: https://github.com/wisrovi/wclickhouse
• PyPI: https://pypi.org/project/wclickhouse

ClickHouse #Python #DataEngineering #OLAP #BigData #Wisrovi