TransactionException after raw sql CREATE TABLE queries against duckdb backend

[coroa]

Hello,

I am trying to import tables from an sqlite file directly into a duckdb database, without attaching it completely.

Ideally, I would like to run a raw duckdb query to import it, but the table is just not showing up in the table list afterwards.

con = ibis.duckdb.connect()
cur = con.raw_sql("CREATE TABLE tbl AS FROM sqlite_scan('<sqlite_path>', '<table>')")
cur.close()

finishes without any complaints, but then

con.list_tables()

throws a TransactionException even though I did close the cursor. The next call to con.list_tables() returns an empty list.

Do I need to commit the raw_sql call somehow?

I'm on ibis 7.0.0 and duckdb 0.9.2.

[gforsyth]

You can do

con = ibis.duckdb.connect()
table = con.read_sqlite("/path/to/sqlite.db", "table_name")

and that should handle it. Is the query you ran like the one above? raw_sql should work fine (but Ibis will handle the transaction stuff if you use the built-in methods). I ask because your example above appears to be missing a SELECT.

[cpcloud]

I forgot about read_sqlite. Much better than using raw_sql if you can avoid it!

[coroa]

Indeed, this directly solves my use case, many thanks. I'm still trying to understand a bit better the interfaces between duckdb and ibis.

[cpcloud]

This is a rough edge when running raw SQL: you only need to close the cursor for SELECT queries because those are the only queries that produce results (and might be in the middle of a transaction when raw_sql returns).

Since you're running a CREATE TABLE query there's no need to close the cursor. The SELECT its using will finish before returning.

Your code should work, without leaking resources, if you remove the cur.close() call.

I think are two things we can do here:

1. Document that calling close is only necessary for SELECT queries
2. Perhaps introduce a con.ddl to make it more explicit that the cursor doesn't need to be closed (at least to the next person that reads it)

[coroa]

Interesting that we have completely different behaviours:

In [1]: from ibis.interactive import *

In [2]: con = ibis.duckdb.connect()

In [3]: !file open-mastr.db
open-mastr.db: SQLite 3.x database, last written using SQLite version 3037000, file counter 576, database pages 1564914, 1st free page 151045, free pages 131113, cookie 0xa8, schema 4, UTF-8, version-valid-for 576

In [4]: cur = con.raw_sql("CREATE TABLE tbl AS FROM sqlite_scan('open-mastr.db',
   ...:  'solar_eeg')")
100% ▕████████████████████████████████████████████████████████████▏

In [5]: cur.fetchall()
Out[5]: [(3566194,)]

In [6]: con.list_tables()
Out[6]: []

[coroa]

I was also able to import it successfully using

con.create_table("tbl", con.sql("FROM sqlite_scan('open-mastr.db', 'solar_eeg')"))

even though sqlalchemy emitted a warning:

/Users/coroa/.local/conda/envs/duck/lib/python3.11/site-packages/ibis/backends/base/sql/alchemy/__init__.py:338: SAWarning: Number of columns in textual SQL (24) is smaller than number of columns requested (1)
  bind.execute(ctas)

[cpcloud]

Weird!

You're using DuckDB 0.9.2 right? I'm running with DuckDB 0.9.1, let me try with 0.9.2.

[gforsyth]

Also @coroa -- what version of duckdb_engine do you have installed? (this would've been installed as a dependency of the DuckDB backend)

[coroa]

In [12]: version("duckdb")
Out[12]: '0.9.2'

In [13]: version("duckdb_engine")
Out[13]: '0.9.2'

both installed through mamba create -n duck ibis-duckdb on Mac OS X.