title_wip

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/chdb/install/python.md)

## Requirements[​](#requirements "Direct link to Requirements")

- Python 3.8+
- Supported platforms: macOS and Linux (x86\_64 and ARM64)

## Installation[​](#install "Direct link to Installation")

```
pip install chdb
```

## Usage[​](#usage "Direct link to Usage")

### Command line interface[​](#command-line-interface "Direct link to Command line interface")

Run SQL queries directly from the command line:

```
# Basic query
python3 -m chdb "SELECT 1, 'abc'" Pretty

# Query with formatting
python3 -m chdb "SELECT version()" JSON
```

### Basic python usage[​](#basic-python-usage "Direct link to Basic python usage")

```
import chdb

# Simple query
result = chdb.query("SELECT 1 as id, 'Hello World' as message", "CSV")
print(result)

# Get query statistics
print(f"Rows read: {result.rows_read()}")
print(f"Bytes read: {result.bytes_read()}")
print(f"Execution time: {result.elapsed()} seconds")
```

### Connection-based API (recommended)[​](#connection-based-api "Direct link to Connection-based API (recommended)")

For better resource management and performance:

```
import chdb

# Create connection (in-memory by default)
conn = chdb.connect(":memory:")
# Or use file-based: conn = chdb.connect("mydata.db")

# Create cursor for query execution
cur = conn.cursor()

# Execute queries
cur.execute("SELECT number, toString(number) as str FROM system.numbers LIMIT 3")

# Fetch results in different ways
print(cur.fetchone())    # Single row: (0, '0')
print(cur.fetchmany(2))  # Multiple rows: ((1, '1'), (2, '2'))

# Get metadata
print(cur.column_names())  # ['number', 'str']
print(cur.column_types())  # ['UInt64', 'String']

# Use cursor as iterator
for row in cur:
    print(row)

# Always close resources
cur.close()
conn.close()
```

## Data input methods[​](#data-input "Direct link to Data input methods")

### File-based data sources[​](#file-based-data-sources "Direct link to File-based data sources")

chDB supports 70+ data formats for direct file querying:

```
import chdb
# Prepare your data
# ...

# Query Parquet files
result = chdb.query("""
    SELECT customer_id, sum(amount) as total
    FROM file('sales.parquet', Parquet) 
    GROUP BY customer_id 
    ORDER BY total DESC 
    LIMIT 10
""", 'JSONEachRow')

# Query CSV with headers
result = chdb.query("""
    SELECT * FROM file('data.csv', CSVWithNames) 
    WHERE column1 > 100
""", 'DataFrame')

# Multiple file formats
result = chdb.query("""
    SELECT * FROM file('logs*.jsonl', JSONEachRow)
    WHERE timestamp > '2024-01-01'
""", 'Pretty')
```

### Output format examples[​](#output-format-examples "Direct link to Output format examples")

```
# DataFrame for analysis
df = chdb.query('SELECT * FROM system.numbers LIMIT 5', 'DataFrame')
print(type(df))  # <class 'pandas.core.frame.DataFrame'>

# Arrow Table for interoperability  
arrow_table = chdb.query('SELECT * FROM system.numbers LIMIT 5', 'ArrowTable')
print(type(arrow_table))  # <class 'pyarrow.lib.Table'>

# JSON for APIs
json_result = chdb.query('SELECT version()', 'JSON')
print(json_result)

# Pretty format for debugging
pretty_result = chdb.query('SELECT * FROM system.numbers LIMIT 3', 'Pretty')
print(pretty_result)
```

### DataFrame operations[​](#dataframe-operations "Direct link to DataFrame operations")

#### Legacy DataFrame API[​](#legacy-dataframe-api "Direct link to Legacy DataFrame API")

```
import chdb.dataframe as cdf
import pandas as pd

# Join multiple DataFrames
df1 = pd.DataFrame({'a': [1, 2, 3], 'b': ["one", "two", "three"]})
df2 = pd.DataFrame({'c': [1, 2, 3], 'd': ["①", "②", "③"]})

result_df = cdf.query(
    sql="SELECT * FROM __tbl1__ t1 JOIN __tbl2__ t2 ON t1.a = t2.c",
    tbl1=df1, 
    tbl2=df2
)
print(result_df)

# Query the result DataFrame
summary = result_df.query('SELECT b, sum(a) FROM __table__ GROUP BY b')
print(summary)
```

#### Python table engine (recommended)[​](#python-table-engine-recommended "Direct link to Python table engine (recommended)")

```
import chdb
import pandas as pd
import pyarrow as pa

# Query Pandas DataFrame directly
df = pd.DataFrame({
    "customer_id": [1, 2, 3, 1, 2],
    "product": ["A", "B", "A", "C", "A"],
    "amount": [100, 200, 150, 300, 250],
    "metadata": [
        {'category': 'electronics', 'priority': 'high'},
        {'category': 'books', 'priority': 'low'},
        {'category': 'electronics', 'priority': 'medium'},
        {'category': 'clothing', 'priority': 'high'},
        {'category': 'books', 'priority': 'low'}
    ]
})

# Direct DataFrame querying with JSON support
result = chdb.query("""
    SELECT 
        customer_id,
        sum(amount) as total_spent,
        toString(metadata.category) as category
    FROM Python(df)
    WHERE toString(metadata.priority) = 'high'
    GROUP BY customer_id, toString(metadata.category)
    ORDER BY total_spent DESC
""").show()

# Query Arrow Table
arrow_table = pa.table({
    "id": [1, 2, 3, 4],
    "name": ["Alice", "Bob", "Charlie", "David"],
    "score": [98, 89, 86, 95]
})

chdb.query("""
    SELECT name, score
    FROM Python(arrow_table)
    ORDER BY score DESC
""").show()
```

### Stateful sessions[​](#stateful-sessions "Direct link to Stateful sessions")

Sessions maintain query state across multiple operations, enabling complex workflows:

```
from chdb import session

# Temporary session (auto-cleanup)
sess = session.Session()

# Or persistent session with specific path
# sess = session.Session("/path/to/data")

# Create database and tables
sess.query("CREATE DATABASE IF NOT EXISTS analytics ENGINE = Atomic")
sess.query("USE analytics")

sess.query("""
    CREATE TABLE sales (
        id UInt64,
        product String,
        amount Decimal(10,2),
        sale_date Date
    ) ENGINE = MergeTree() 
    ORDER BY (sale_date, id)
""")

# Insert data
sess.query("""
    INSERT INTO sales VALUES 
        (1, 'Laptop', 999.99, '2024-01-15'),
        (2, 'Mouse', 29.99, '2024-01-16'),
        (3, 'Keyboard', 79.99, '2024-01-17')
""")

# Create materialized views
sess.query("""
    CREATE MATERIALIZED VIEW daily_sales AS
    SELECT 
        sale_date,
        count() as orders,
        sum(amount) as revenue
    FROM sales 
    GROUP BY sale_date
""")

# Query the view
result = sess.query("SELECT * FROM daily_sales ORDER BY sale_date", "Pretty")
print(result)

# Session automatically manages resources
sess.close()  # Optional - auto-closed when object is deleted
```

### Advanced session features[​](#advanced-session-features "Direct link to Advanced session features")

```
# Session with custom settings
sess = session.Session(
    path="/tmp/analytics_db",
)

# Query performance optimization
result = sess.query("""
    SELECT product, sum(amount) as total
    FROM sales 
    GROUP BY product
    ORDER BY total DESC
    SETTINGS max_threads = 4
""", "JSON")
```

See also: [test\_stateful.py](https://github.com/chdb-io/chdb/blob/main/tests/test_stateful.py).

### Python DB-API 2.0 interface[​](#python-db-api-20 "Direct link to Python DB-API 2.0 interface")

Standard database interface for compatibility with existing Python applications:

```
import chdb.dbapi as dbapi

# Check driver information
print(f"chDB driver version: {dbapi.get_client_info()}")

# Create connection
conn = dbapi.connect()
cursor = conn.cursor()

# Execute queries with parameters
cursor.execute("""
    SELECT number, number * ? as doubled 
    FROM system.numbers 
    LIMIT ?
""", (2, 5))

# Get metadata
print("Column descriptions:", cursor.description)
print("Row count:", cursor.rowcount)

# Fetch results
print("First row:", cursor.fetchone())
print("Next 2 rows:", cursor.fetchmany(2))

# Fetch remaining rows
for row in cursor.fetchall():
    print("Row:", row)

# Batch operations
data = [(1, 'Alice'), (2, 'Bob'), (3, 'Charlie')]
cursor.execute("""
    CREATE TABLE temp_users (
        id UInt64,
        name String
    ) ENGINE = MergeTree()
    ORDER BY (id)
""")
cursor.executemany(
    "INSERT INTO temp_users (id, name) VALUES (?, ?)", 
    data
)
```