Use case I want to insert data into a chdb instance by using the

You should be able to perform <a href="https://clickhouse.com/docs/en/sql-reference/st

I've managed to make this work using dbapi. <div class="highlight highlight-source

Insert with parameters about chdb HOT 4 CLOSED

nevinpuri commented on June 12, 2024

Insert with parameters

from chdb.

Comments (4)

lmangani commented on June 12, 2024 2

You should be able to perform INSERT INTO from any defined dataframe passed to the query function.

tbl = cdf.Table(dataframe=pd.DataFrame({'a': [1, 2, 3], 'b': ['a', 'b', 'c']}))
ret_tbl = tbl.query('INSERT INTO somewhere SELECT * FROM __table__')

from chdb.

nevinpuri commented on June 12, 2024

Thank you, this is a really smart solution.

from chdb.

nevinpuri commented on June 12, 2024

It seems I can't access other databases from the table query function. For example, the following code fails with Code: 81. DB::Exception: Database e does not exist. (UNKNOWN_DATABASE)

from chdb.session import Session
import chdb.dataframe as cdf
import pandas as pd

client = Session()
client.query("CREATE DATABASE e ENGINE = Atomic;")
res = client.query(
    "CREATE TABLE e.hi (a String primary key, b Int32) Engine = MergeTree ORDER BY a;"
)
df = pd.DataFrame({"a": [str("hi")], "b": [32]})
tbl = cdf.Table(dataframe=df)
h = tbl.query("INSERT INTO e.hi SELECT a, b FROM __table__")
sys.exit(0)

Do you have any ideas how I could access the database from the table query function?

from chdb.

nevinpuri commented on June 12, 2024

I've managed to make this work using dbapi.

conn = dbapi.connect()
cur = conn.cursor()

cur.execute("CREATE DATABASE e ENGINE = Atomic;")
cur.execute("CREATE TABLE e.hi (a String primary key, b Int32) Engine = MergeTree ORDER BY a;")

cur.execute("INSERT INTO e.hi (a, b) VALUES (%s, %s);", ["test", 32]) # only use %s, not %i or %d etc

cur.execute("SELECT * FROM e.hi;")
res = cur.fetchall()
# res should be [("test", 32)]

With my pull request, you will be able to pass a path into the dbapi.connect() function like so: dbapi.connect(path="./folder"). This will enable the same functionality as session, but with using the dbapi.

from chdb.

Recommend Projects