import chdb # Create a connection (in-memory by default) conn = chdb.connect(":memory:") # Or use file-based: conn = chdb.connect("test.db") # Create a cursor cur = conn.cursor() # Execute queries cur.execute("SELECT number, toString(number) as str FROM system.numbers LIMIT 3") # Fetch data in different ways print(cur.fetchone()) # Single row: (0, '0') print(cur.fetchmany(2)) # Multiple rows: ((1, '1'), (2, '2')) # Get column information print(cur.column_names()) # ['number', 'str'] print(cur.column_types()) # ['UInt64', 'String'] # Use the cursor as an iterator cur.execute("SELECT number FROM system.numbers LIMIT 3") for row in cur: print(row) # Always close resources when done cur.close() conn.close()

import chdb res = chdb.query('select version()', 'Pretty'); print(res)

# See more data type format in tests/format_output.py res = chdb.query('select * from file("data.parquet", Parquet)', 'JSON'); print(res) res = chdb.query('select * from file("data.csv", CSV)', 'CSV'); print(res) print(f"SQL read {res.rows_read()} rows, {res.bytes_read()} bytes, storage read {res.storage_rows_read()} rows, {res.storage_bytes_read()} bytes, elapsed {res.elapsed()} seconds")

import chdb df = chdb.query( "SELECT toDate({base_date:String}) + number AS date " "FROM numbers({total_days:UInt64}) " "LIMIT {items_per_page:UInt64}", "DataFrame", params={"base_date": "2025-01-01", "total_days": 10, "items_per_page": 2}, ) print(df) # date # 0 2025-01-01 # 1 2025-01-02

import chdb # Connection API conn = chdb.connect(":memory:?progress=auto") conn.query("SELECT sum(number) FROM numbers_mt(1e10) GROUP BY number % 10 SETTINGS max_threads=4")

import chdb # One-shot API res = chdb.query( "SELECT sum(number) FROM numbers_mt(1e10) GROUP BY number % 10 SETTINGS max_threads=4", options={"progress": "auto"}, )

# See more in https://clickhouse.com/docs/en/interfaces/formats chdb.query('select * from file("data.parquet", Parquet)', 'Dataframe')

import chdb.dataframe as cdf import pandas as pd # Join 2 DataFrames df1 = pd.DataFrame({'a': [1, 2, 3], 'b': ["one", "two", "three"]}) df2 = pd.DataFrame({'c': [1, 2, 3], 'd': ["①", "②", "③"]}) ret_tbl = cdf.query(sql="select * from __tbl1__ t1 join __tbl2__ t2 on t1.a = t2.c", tbl1=df1, tbl2=df2) print(ret_tbl) # Query on the DataFrame Table print(ret_tbl.query('select b, sum(a) from __table__ group by b')) # Pandas DataFrames are automatically registered as temporary tables in ClickHouse chdb.query("SELECT * FROM Python(df1) t1 JOIN Python(df2) t2 ON t1.a = t2.c").show()

from chdb import session as chs ## Create DB, Table, View in temp session, auto cleanup when session is deleted. sess = chs.Session() sess.query("CREATE DATABASE IF NOT EXISTS db_xxx ENGINE = Atomic") sess.query("CREATE TABLE IF NOT EXISTS db_xxx.log_table_xxx (x String, y Int) ENGINE = Log;") sess.query("INSERT INTO db_xxx.log_table_xxx VALUES ('a', 1), ('b', 3), ('c', 2), ('d', 5);") sess.query( "CREATE VIEW db_xxx.view_xxx AS SELECT * FROM db_xxx.log_table_xxx LIMIT 4;" ) print("Select from view:\n") print(sess.query("SELECT * FROM db_xxx.view_xxx", "Pretty"))

import chdb.dbapi as dbapi print("chdb driver version: {0}".format(dbapi.get_client_info())) conn1 = dbapi.connect() cur1 = conn1.cursor() cur1.execute('select version()') print("description: ", cur1.description) print("data: ", cur1.fetchone()) cur1.close() conn1.close()

from chdb.udf import chdb_udf from chdb import query @chdb_udf() def sum_udf(lhs, rhs): return int(lhs) + int(rhs) print(query("select sum_udf(12,22)"))

from chdb import session as chs sess = chs.Session() # Example 1: Basic example of using streaming query rows_cnt = 0 with sess.send_query("SELECT * FROM numbers(200000)", "CSV") as stream_result: for chunk in stream_result: rows_cnt += chunk.rows_read() print(rows_cnt) # 200000 # Example 2: Manual iteration with fetch() rows_cnt = 0 stream_result = sess.send_query("SELECT * FROM numbers(200000)", "CSV") while True: chunk = stream_result.fetch() if chunk is None: break rows_cnt += chunk.rows_read() print(rows_cnt) # 200000

import chdb import pandas as pd df = pd.DataFrame( { "a": [1, 2, 3, 4, 5, 6], "b": ["tom", "jerry", "auxten", "tom", "jerry", "auxten"], } ) chdb.query("SELECT b, sum(a) FROM Python(df) GROUP BY b ORDER BY b").show()

import chdb import pyarrow as pa arrow_table = pa.table( { "a": [1, 2, 3, 4, 5, 6], "b": ["tom", "jerry", "auxten", "tom", "jerry", "auxten"], } ) chdb.query("SELECT b, sum(a) FROM Python(arrow_table) GROUP BY b ORDER BY b").show()

import chdb # Use env OPENAI_API_KEY/AI_API_KEY/ANTHROPIC_API_KEY for credentials conn = chdb.connect("file::memory:?ai_provider=openai&ai_model=gpt-4o-mini") conn.query("CREATE TABLE nums (n UInt32) ENGINE = Memory") conn.query("INSERT INTO nums VALUES (1), (2), (3)") sql = conn.generate_sql("Select all rows from nums ordered by n desc") print(sql) # e.g., SELECT * FROM nums ORDER BY n DESC # ask(): one-call generate + execute print(conn.ask("List the numbers table", format="Pretty"))