> ## Documentation Index > Fetch the complete documentation index at: https://private-7c7dfe99-postgresql-tls-support.mintlify.site/llms.txt > Use this file to discover all available pages before exploring further. > Advanced inserting with ClickHouse Connect # Advanced inserting

Inserting data with ClickHouse Connect: Advanced usage

InsertContexts

ClickHouse Connect executes Native-format inserts, the `insert` and `insert_df` methods, within an `InsertContext`. The `insert_arrow`, `insert_df_arrow`, and `raw_insert` methods send their payloads directly and don't use one. The `InsertContext` includes all the values sent as arguments to the client `insert` method. In addition, when an `InsertContext` is originally constructed, ClickHouse Connect retrieves the data types for the insert columns required for efficient Native format inserts. By reusing the `InsertContext` for multiple inserts, this "pre-query" is avoided and inserts are executed more quickly and efficiently. An `InsertContext` can be acquired using the client `create_insert_context` method. The method takes the same arguments as the `insert` function, except for `context` itself. Note that only the `data` property of `InsertContext`s should be modified for reuse. This is consistent with its intended purpose of providing a reusable object for repeated inserts of new data to the same table. ```python theme={null} test_data = [[13, "v1", "v2"], [79, "v3", "v4"]] ic = client.create_insert_context(table="test_table", data=test_data) client.insert(context=ic) assert client.command("SELECT count() FROM test_table") == 2 new_data = [[101, "v5", "v6"], [113, "v7", "v8"]] ic.data = new_data client.insert(context=ic) qr = client.query("SELECT * FROM test_table ORDER BY key DESC") assert qr.row_count == 4 assert qr.first_row[0] == 113 ``` `InsertContext`s include mutable state that is updated during the insert process, so they're not thread safe.

Write formats

Write formats are implemented for a limited number of types. In most cases ClickHouse Connect automatically determines the correct write format for a column from its first non-null data value. For example, when the first value for a `DateTime` column is an integer, the client treats it as an epoch second. It is normally unnecessary to override a write format, but the methods in `clickhouse_connect.datatypes.format` can set one globally. Container wrappers such as `Array`, `Nullable`, and `LowCardinality` preserve the element type's formatting behavior.

Write format options

| ClickHouse Type | Native Python Type | Write Formats | Comments | | ----------------------- | ----------------------- | ----------------- | --------------------------------------------------------------------------------------------------------------------------------- | | Int\[8-64], UInt\[8-32] | int | | | | UInt64 | int | | | | \[U]Int\[128,256] | int | | | | BFloat16 | float | | | | Float32 | float | | | | Float64 | float | | | | Decimal | decimal.Decimal | | | | String | str or bytes | | A column must consistently contain text or bytes. | | FixedString | bytes | string | If inserted as a string, additional bytes will be set to zeros | | Enum\[8,16] | str or int | | Insert labels as strings or their underlying integer values. | | Date | datetime.date | int | Integer values are interpreted as days since 1970-01-01. | | Date32 | datetime.date | int | Integer values are interpreted as signed day offsets. | | DateTime | datetime.datetime | int | Integer values are interpreted as epoch seconds. | | DateTime64 | datetime.datetime | int | Integer values are interpreted as ticks at the column precision. | | Time | datetime.timedelta | int, string, time | Integer values are interpreted as seconds. | | Time64 | datetime.timedelta | int, string, time | Integer values are interpreted as ticks at the column precision. | | IPv4 | `ipaddress.IPv4Address` | string | Properly formatted strings can be inserted as IPv4 addresses | | IPv6 | `ipaddress.IPv6Address` | string | Properly formatted strings can be inserted as IPv6 addresses | | Tuple | dict or tuple | | | | Map | dict | | | | Nested | Sequence\[dict] | | | | UUID | uuid.UUID | string | Properly formatted strings can be inserted as ClickHouse UUIDs | | JSON | dict | string | Dictionaries and JSON object strings are supported. The legacy `Object('json')` type is not supported. | | Variant | object | | Values use native member serialization. Use `clickhouse_connect.datatypes.dynamic.typed_variant` when Python types are ambiguous. | | Dynamic | object | | Values are currently inserted through their String representation. | | QBit | Sequence\[float] | | NumPy is used automatically for faster bit transposition when installed. |

Specialized insert methods

ClickHouse Connect provides specialized insert methods for common data formats: * `insert_df` -- Insert a Pandas DataFrame as column-oriented Native data. It also supports explicit column names/types or a reusable `InsertContext`. * `insert_arrow` -- Insert a PyArrow Table using the ClickHouse Arrow input format. * `insert_df_arrow` -- Insert an Arrow-backed Pandas DataFrame or a Polars DataFrame. Pandas columns must all use Arrow-backed dtypes. All three methods accept `database`, `settings`, and per-request HTTP `transport_settings`. A NumPy array is a valid Sequence of Sequences and can be used as the `data` argument to the main `insert` method, so a specialized method isn't required.

Pandas DataFrame insert

```python theme={null} import clickhouse_connect import pandas as pd client = clickhouse_connect.get_client() df = pd.DataFrame({ "id": [13, 79], "name": ["user_1", "user_2"], "age": [25, 30], }) client.insert_df("users", df) ```

PyArrow Table insert

```python theme={null} import clickhouse_connect import pyarrow as pa client = clickhouse_connect.get_client() arrow_table = pa.table({ "id": [13, 79], "name": ["user_1", "user_2"], "age": [25, 30], }) client.insert_arrow("users", arrow_table) ```

Arrow-backed DataFrame insert (pandas 2.x)

```python theme={null} import clickhouse_connect import pandas as pd client = clickhouse_connect.get_client() # Convert to Arrow-backed dtypes for better performance df = pd.DataFrame({ "id": [13, 79], "name": ["user_1", "user_2"], "age": [25, 30], }).convert_dtypes(dtype_backend="pyarrow") client.insert_df_arrow("users", df) ```

Create a table from a PyArrow schema

`create_table_from_arrow_schema` builds a `CREATE TABLE` statement from common scalar Arrow fields. The mapping covers signed and unsigned integers, floating-point values, booleans, strings, dates, and timestamps. It intentionally creates non-nullable ClickHouse columns and raises `TypeError` for unsupported Arrow types, so review the generated DDL before executing it. ```python theme={null} import clickhouse_connect import pyarrow as pa from clickhouse_connect.driver.ddl import create_table_from_arrow_schema client = clickhouse_connect.get_client() schema = pa.schema( [ ("id", pa.uint32()), ("name", pa.string()), ("event_time", pa.timestamp("ms", tz="UTC")), ] ) ddl = create_table_from_arrow_schema( table_name="arrow_events", schema=schema, engine="MergeTree", engine_params={"ORDER BY": "id"}, ) client.command(ddl) ```

Time zones

When inserting Python `datetime` objects into `DateTime` or `DateTime64` columns, ClickHouse Connect converts them to epoch values.

Timezone-aware datetime objects

Timezone-aware objects preserve the represented instant. The source timezone does not need to match the timezone declared on the ClickHouse column. ```python theme={null} from datetime import datetime, timezone from zoneinfo import ZoneInfo client.command("CREATE TABLE events (event_time DateTime) ENGINE Memory") data = [ [datetime(2023, 6, 15, 10, 30, tzinfo=timezone.utc)], [datetime(2023, 6, 15, 10, 30, tzinfo=ZoneInfo("America/Denver"))], [datetime(2023, 6, 15, 10, 30, tzinfo=ZoneInfo("Asia/Tokyo"))], ] client.insert("events", data, column_names=["event_time"]) results = client.query( "SELECT event_time FROM events ORDER BY event_time", query_tz="UTC", tz_mode="aware", ) assert [row[0].hour for row in results.result_rows] == [1, 10, 16] ``` ClickHouse Connect uses the standard library `zoneinfo` module. The driver no longer depends on `pytz`.

Timezone-naive datetime objects

Python interprets a naive `datetime` in the system's local timezone when `.timestamp()` is called. That makes inserts dependent on the environment. Prefer one of these approaches: 1. Use timezone-aware datetime objects. 2. Ensure the process timezone is UTC. 3. Attach the intended timezone or convert to an epoch integer explicitly. ```python theme={null} from datetime import datetime, timezone utc_time = datetime(2023, 6, 15, 10, 30, tzinfo=timezone.utc) client.insert("events", [[utc_time]], column_names=["event_time"]) naive_time = datetime(2023, 6, 15, 10, 30) epoch_timestamp = int(naive_time.replace(tzinfo=timezone.utc).timestamp()) client.insert("events", [[epoch_timestamp]], column_names=["event_time"]) ```

DateTime columns with timezone metadata

ClickHouse columns can declare timezone metadata, for example `DateTime('America/Denver')` or `DateTime64(3, 'Asia/Tokyo')`. The metadata controls how values are presented when queried. When inserting into such a column, ClickHouse Connect converts the Python value according to its own `tzinfo`. When queried, the result uses the column timezone unless a per-column override is supplied with the `column_tzs` argument. The `query_tz` argument doesn't override a column's declared timezone. ```python theme={null} from datetime import datetime from zoneinfo import ZoneInfo client.command( "CREATE TABLE events_with_timezone " "(event_time DateTime('America/Los_Angeles')) " "ENGINE Memory" ) data = datetime(2023, 6, 15, 10, 30, tzinfo=ZoneInfo("America/New_York")) client.insert("events_with_timezone", [[data]], column_names=["event_time"]) result = client.query("SELECT event_time FROM events_with_timezone") returned = result.first_row[0] assert returned.hour == 7 assert returned.tzinfo == ZoneInfo("America/Los_Angeles") ```

File inserts

`clickhouse_connect.driver.tools.insert_file` streams a local file into an existing table and delegates parsing to ClickHouse. | Parameter | Type | Default | Description | | -------------- | -------------- | --------------------------- | --------------------------------------------------------------------------------------------------------------------- | | `client` | `Client` | Required | Synchronous client used for the insert. | | `table` | str | Required | Simple or database-qualified target table. | | `file_path` | str | Required | Local path to the input file. | | `fmt` | str | `"CSV"` or `"CSVWithNames"` | Input format. Defaults to `"CSV"` when `column_names` is supplied and `"CSVWithNames"` otherwise. | | `column_names` | Sequence\[str] | `None` | Columns represented by the file. Not required for formats that include names. | | `database` | str | `None` | Target database when the table is not qualified. | | `settings` | dict | `None` | See [Settings argument](/integrations/language-clients/python/driver-api#settings-argument-1). | | `compression` | str | `None` | Existing file compression, such as `"zstd"`, `"lz4"`, or `"gzip"`. gzip is inferred from `.gz` and `.gzip` filenames. | Input-format settings such as `input_format_allow_errors_ratio` and `input_format_allow_errors_num` can be passed through `settings`. ```python theme={null} import clickhouse_connect from clickhouse_connect.driver.tools import insert_file client = clickhouse_connect.get_client() insert_file( client, "example_table", "my_data.csv", settings={ "input_format_allow_errors_ratio": 0.2, "input_format_allow_errors_num": 5, }, ) ``` For an `AsyncClient`, await `insert_file_async` with the same arguments: ```python theme={null} from clickhouse_connect.driver.tools import insert_file_async await insert_file_async(async_client, "example_table", "my_data.csv") ``` The async helper reads the file in a worker thread before awaiting `raw_insert`, so the file contents are held in memory.