I have a setup where I'm utilizing two connections for sqlite: A dbapi-based sqlite connection from Arrow ADBC so I can have access to ingesting and fetching arrow data, and a native sqlite3 connection to be able to have access to other tools (function generation). I'm feeding data into the sqlite3 connection via data on disk, and all of the data being processed is in the sqlite3 in-memory database. I however, for my program, need the adbc connection to "see" the sqlite3 database.

So far, I've tried to follow This answer modified for my purpose by doing this:

import import adbc_driver_sqlite.dbapi as arrowdb
import sqlite3

arrow_conn= arrowdb.connect("file::memory:?cache=shared")
arrow_cursor=arrow_conn.cursor()
memory_conn=sqlite3.connect("file::memory:?cache=shared",uri=True)

... #this is some setup code for various tables

result=data.arrow_cursor.execute("SELECT name FROM sqlite_master WHERE type = 'table'; ")

print(result) #this query and print is a debug to see the tables that the adbc connection can see

I've tried this, as well as things similar to this, and every time, the return is there being no tables within the sqlite_master, and if any of my main code is ran, I'm seeing errors showing that the adbc connection cannot see a referenced table in a query:

adbc_driver_manager.ProgrammingError: INVALID_ARGUMENT: [SQLite] Failed to prepare 

query: no such table: example_table
query:
                            SELECT *
                            FROM example_table;
                            
                            

At this point, I'm not sure what I can do to make the connections work.

I have a setup where I'm utilizing two connections for sqlite: A dbapi-based sqlite connection from Arrow ADBC so I can have access to ingesting and fetching arrow data, and a native sqlite3 connection to be able to have access to other tools (function generation). I'm feeding data into the sqlite3 connection via data on disk, and all of the data being processed is in the sqlite3 in-memory database. I however, for my program, need the adbc connection to "see" the sqlite3 database.

So far, I've tried to follow This answer modified for my purpose by doing this:

import import adbc_driver_sqlite.dbapi as arrowdb
import sqlite3

arrow_conn= arrowdb.connect("file::memory:?cache=shared")
arrow_cursor=arrow_conn.cursor()
memory_conn=sqlite3.connect("file::memory:?cache=shared",uri=True)

... #this is some setup code for various tables

result=data.arrow_cursor.execute("SELECT name FROM sqlite_master WHERE type = 'table'; ")

print(result) #this query and print is a debug to see the tables that the adbc connection can see

I've tried this, as well as things similar to this, and every time, the return is there being no tables within the sqlite_master, and if any of my main code is ran, I'm seeing errors showing that the adbc connection cannot see a referenced table in a query:

adbc_driver_manager.ProgrammingError: INVALID_ARGUMENT: [SQLite] Failed to prepare 

query: no such table: example_table
query:
                            SELECT *
                            FROM example_table;
                            
                            

At this point, I'm not sure what I can do to make the connections work.

• python
• apache-arrow
• sqlite3-python

2 Answers 2

What versions are you using for everything?

The following simple example worked for me:

import adbc_driver_sqlite.dbapi as arrowdb
import sqlite3

db = "file::memory:?cache=shared"
con1 = arrowdb.connect(db)
con2 = sqlite3.connect(db, uri=True)
cur1 = con1.cursor()

with con2:
    con2.execute("CREATE TABLE shared(data)")
    con2.execute("INSERT INTO shared VALUES(28)")

cur1.execute("SELECT data FROM shared")
print(cur1.fetchone())

cur1.close()
con1.close()
con2.close()

The output of this is (28,) just as you'd expect, showing that the adbc sqlite instance was able to see the result of the sqlite3 version. So, this definitely works. I'm guessing there's some other issue in your code.

In the comments under the answer from Zeroshade, li.davidm gave an insight to a solution that works, in that a normal pip installation of the packages will not work. Instead, you need to use a conda environment to have this sort of system work. At the time of writing this, the latest drivers work just fine.