DBAPI implementation loses all Clickhouse state on every query

[yunyu]

Describe what's wrong

It is impossible to use dbapi for anything involving writes, because it seems like the Clickhouse state is reset on every single query.

How to reproduce

from chdb import dbapi

conn1 = dbapi.connect()
cur1 = conn1.cursor()

cur1.execute("create database test_db; select 1")
cur1.execute("use test_db")

(The select 1 in CREATE DATABASE is there due to #162)

Expected behavior

test_db is created and can be used in the subsequent query

Error message and/or stacktrace

Code: 81. DB::Exception: Database test_db does not exist. (UNKNOWN_DATABASE)

Additional information

This can be easily verified by running select * from system.databases in succession:

from chdb import dbapi

conn1 = dbapi.connect()
cur1 = conn1.cursor()

cur1.execute("create database test_db; select * from system.databases;")
print(cur1.fetchall())

cur1.execute("select * from system.databases;")
print(cur1.fetchall())

You will notice that test_db disappears in the second call, and the directory names reflect a different unix timestamp which is probably not correct.

[lmangani]

@yunyu correct. DBAPI by default doesn't create a stateful session yet but it should be quite simple to do so