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

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

Work with Parquet or CSV

# See more data type format in tests/format_output.pyres=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")

Pandas dataframe output

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

Query On Pandas DataFrame

importchdb.dataframeascdfimportpandasaspd# Join 2 DataFramesdf1=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 Tableprint(ret_tbl.query('select b, sum(a) from __table__ group by b'))# Pandas DataFrames are automatically registered as temporary tables in ClickHousechdb.query("SELECT * FROM Python(df1) t1 JOIN Python(df2) t2 ON t1.a = t2.c").show()

fromchdbimportsessionaschs## 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"))

importchdb.dbapiasdbapiprint("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()

fromchdb.udfimportchdb_udffromchdbimportquery@chdb_udf()defsum_udf(lhs,rhs):returnint(lhs)+int(rhs)print(query("select sum_udf(12,22)"))

fromchdbimportsessionaschssess=chs.Session()# Example 1: Basic example of using streaming queryrows_cnt=0withsess.send_query("SELECT * FROM numbers(200000)","CSV")asstream_result:forchunkinstream_result:rows_cnt+=chunk.rows_read()print(rows_cnt)# 200000# Example 2: Manual iteration with fetch()rows_cnt=0stream_result=sess.send_query("SELECT * FROM numbers(200000)","CSV")whileTrue:chunk=stream_result.fetch()ifchunkisNone:breakrows_cnt+=chunk.rows_read()print(rows_cnt)# 200000# Example 3: Early cancellation demorows_cnt=0stream_result=sess.send_query("SELECT * FROM numbers(200000)","CSV")whileTrue:chunk=stream_result.fetch()ifchunkisNone:breakifrows_cnt>0:stream_result.close()breakrows_cnt+=chunk.rows_read()print(rows_cnt)# 65409# Example 4: Using PyArrow RecordBatchReader for batch export and integration with other librariesimportpyarrowaspafromdeltalakeimportwrite_deltalake# Get streaming result in arrow formatstream_result=sess.send_query("SELECT * FROM numbers(100000)","Arrow")# Create RecordBatchReader with custom batch size (default rows_per_batch=1000000)batch_reader=stream_result.record_batch(rows_per_batch=10000)# Use RecordBatchReader with external libraries like Delta Lakewrite_deltalake(table_or_uri="./my_delta_table",data=batch_reader,mode="overwrite")stream_result.close()sess.close()

Query on Pandas DataFrame

importchdbimportpandasaspddf=pd.DataFrame(    {"a": [1,2,3,4,5,6],"b": ["tom","jerry","auxten","tom","jerry","auxten"],"dict_col": [            {'id':1,'tags': ['urgent','important'],'metadata': {'created':'2024-01-01'}},            {'id':2,'tags': ['normal'],'metadata': {'created':'2024-02-01'}},            {'id':3,'name':'tom'},            {'id':4,'value':'100'},            {'id':5,'value':101},            {'id':6,'value':102},        ],    })chdb.query("SELECT b, sum(a) FROM Python(df) GROUP BY b ORDER BY b").show()chdb.query("SELECT dict_col.id FROM Python(df) WHERE dict_col.value='100'").show()

Query on Arrow Table

importchdbimportpyarrowaspaarrow_table=pa.table(    {"a": [1,2,3,4,5,6],"b": ["tom","jerry","auxten","tom","jerry","auxten"],"dict_col": [            {'id':1,'value':'tom'},            {'id':2,'value':'jerry'},            {'id':3,'value':'auxten'},            {'id':4,'value':'tom'},            {'id':5,'value':'jerry'},            {'id':6,'value':'auxten'},        ],    })chdb.query("SELECT b, sum(a) FROM Python(arrow_table) GROUP BY b ORDER BY b").show()chdb.query("SELECT dict_col.id FROM Python(arrow_table) WHERE dict_col.value='tom'").show()

Query on chdb.PyReader class instance

importchdbclassmyReader(chdb.PyReader):def__init__(self,data):self.data=dataself.cursor=0super().__init__(data)defread(self,col_names,count):print("Python func read",col_names,count,self.cursor)ifself.cursor>=len(self.data["a"]):self.cursor=0return []block= [self.data[col]forcolincol_names]self.cursor+=len(block[0])returnblockdefget_schema(self):return [            ("a","int"),            ("b","str"),            ("dict_col","json")        ]reader=myReader(    {"a": [1,2,3,4,5,6],"b": ["tom","jerry","auxten","tom","jerry","auxten"],"dict_col": [            {'id':1,'tags': ['urgent','important'],'metadata': {'created':'2024-01-01'}},            {'id':2,'tags': ['normal'],'metadata': {'created':'2024-02-01'}},            {'id':3,'name':'tom'},            {'id':4,'value':'100'},            {'id':5,'value':101},            {'id':6,'value':102}        ],    })chdb.query("SELECT b, sum(a) FROM Python(reader) GROUP BY b ORDER BY b").show()chdb.query("SELECT dict_col.id FROM Python(reader) WHERE dict_col.value='100'").show()

JSON Type Inference

Limitations