Advanced Usage

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/integrations/language-clients/python/advanced-usage.md)

## Raw API[​](#raw-api "Direct link to Raw API")

For use cases which don't require transformation between ClickHouse data and native or third party data types and structures, the ClickHouse Connect client provides methods for direct usage of the ClickHouse connection.

### Client `raw_query` method[​](#client-rawquery-method "Direct link to client-rawquery-method")

The `Client.raw_query` method allows direct usage of the ClickHouse HTTP query interface using the client connection. The return value is an unprocessed `bytes` object. It offers a convenient wrapper with parameter binding, error handling, retries, and settings management using a minimal interface:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Parameter Type Default Description|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | query str *Required* Any valid ClickHouse query|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | parameters dict or iterable *None* See [parameters description](/docs/integrations/language-clients/python/driver-api#parameters-argument).| settings dict *None* See [settings description](/docs/integrations/language-clients/python/driver-api#settings-argument).| fmt str *None* ClickHouse Output Format for the resulting bytes. (ClickHouse uses TSV if not specified)|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | use\_database bool True Use the ClickHouse Connect client-assigned database for the query context|  |  |  |  | | --- | --- | --- | --- | | external\_data ExternalData *None* An ExternalData object containing file or binary data to use with the query. See [Advanced Queries (External Data)](/docs/integrations/language-clients/python/advanced-querying#external-data) | | | | | | | | | | | | | | | | | | | | | | | | | | | |

It is the caller's responsibility to handle the resulting `bytes` object. Note that the `Client.query_arrow` is just a thin wrapper around this method using the ClickHouse `Arrow` output format.

### Client `raw_stream` method[​](#client-rawstream-method "Direct link to client-rawstream-method")

The `Client.raw_stream` method has the same API as the `raw_query` method, but returns an `io.IOBase` object which can be used as a generator/stream source of `bytes` objects. It is currently utilized by the `query_arrow_stream` method.

### Client `raw_insert` method[​](#client-rawinsert-method "Direct link to client-rawinsert-method")

The `Client.raw_insert` method allows direct inserts of `bytes` objects or `bytes` object generators using the client connection. Because it does no processing of the insert payload, it is highly performant. The method provides options to specify settings and insert format:

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Parameter Type Default Description|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | table str *Required* Either the simple or database qualified table name|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | column\_names Sequence[str] *None* Column names for the insert block. Required if the `fmt` parameter doesn't include names| insert\_block str, bytes, Generator[bytes], BinaryIO *Required* Data to insert. Strings will be encoded with the client encoding.|  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | | settings dict *None* See [settings description](/docs/integrations/language-clients/python/driver-api#settings-argument).| fmt str *None* ClickHouse Input Format of the `insert_block` bytes. (ClickHouse uses TSV if not specified) | | | | | | | | | | | | | | | | | | | | | | | |

It is the caller's responsibility to ensure that the `insert_block` is in the specified format and uses the specified compression method. ClickHouse Connect uses these raw inserts for file uploads and PyArrow Tables, delegating parsing to the ClickHouse server.

## Saving query results as files[​](#saving-query-results-as-files "Direct link to Saving query results as files")

You can stream files directly from ClickHouse to the local file system using the `raw_stream` method. For example, if you'd like to save the results of a query to a CSV file, you could use the following code snippet:

```
import clickhouse_connect

if __name__ == '__main__':
    client = clickhouse_connect.get_client()
    query = 'SELECT number, toString(number) AS number_as_str FROM system.numbers LIMIT 5'
    fmt = 'CSVWithNames'  # or CSV, or CSVWithNamesAndTypes, or TabSeparated, etc.
    stream = client.raw_stream(query=query, fmt=fmt)
    with open("output.csv", "wb") as f:
        for chunk in stream:
            f.write(chunk)
```

The code above yields an `output.csv` file with the following content:

```
"number","number_as_str"
0,"0"
1,"1"
2,"2"
3,"3"
4,"4"
```

Similarly, you could save data in [TabSeparated](/docs/interfaces/formats/TabSeparated) and other formats. See [Formats for Input and Output Data](/docs/interfaces/formats) for an overview of all available format options.

## Multithreaded, multiprocess, and async/event driven use cases[​](#multithreaded-multiprocess-and-asyncevent-driven-use-cases "Direct link to Multithreaded, multiprocess, and async/event driven use cases")

ClickHouse Connect works well in multithreaded, multiprocess, and event-loop-driven/asynchronous applications. All query and insert processing occurs within a single thread, so operations are generally thread-safe. (Parallel processing of some operations at a low level is a possible future enhancement to overcome the performance penalty of a single thread, but even in that case thread safety will be maintained.)

Because each query or insert executed maintains state in its own `QueryContext` or `InsertContext` object, respectively, these helper objects aren't thread-safe, and they shouldn't be shared between multiple processing streams. See the additional discussion about context objects in the [QueryContexts](/docs/integrations/language-clients/python/advanced-querying#querycontexts) and [InsertContexts](/docs/integrations/language-clients/python/advanced-inserting#insertcontexts) sections.

Additionally, in an application that has two or more queries and/or inserts "in flight" at the same time, there are two further considerations to keep in mind. The first is the ClickHouse "session" associated with the query/insert, and the second is the HTTP connection pool used by ClickHouse Connect Client instances.

## AsyncClient wrapper[​](#asyncclient-wrapper "Direct link to AsyncClient wrapper")

ClickHouse Connect provides an async wrapper over the regular `Client`, so that it is possible to use the client in an `asyncio` environment.

To get an instance of the `AsyncClient`, you can use the `get_async_client` factory function, which accepts the same parameters as the standard `get_client`:

```
import asyncio

import clickhouse_connect


async def main():
    client = await clickhouse_connect.get_async_client()
    result = await client.query("SELECT name FROM system.databases LIMIT 1")
    print(result.result_rows)
    # Output:
    # [('INFORMATION_SCHEMA',)]

asyncio.run(main())
```