0

I'm trying to figure out how to copy an existing database file on disk to memory to make queries on it faster. I know how to do this in CPython3 with:

import sqlite3
db_path = r"C:\path to\database.db"
db_disk = sqlite3.connect(db_path)
db_memory = sqlite3.connect(':memory:')
db_disk.backup(db_memory)

but the .backup() function doesn't exist in IronPython2 (SQLite library version 3.7.7).

Through various researching, I've tried:

import clr
clr.AddReference('IronPython.SQLite.dll')
import sqlite3

db_path = r"C:\path to\database.db"
db_disk = sqlite3.connect(db_path)
db_memory = sqlite3.connect(':memory:')
script = ''.join(db_server.iterdump())
db_memory.executescript(script)

and

db_server = sqlite3.connect(db_path)
db_memory = sqlite3.connect(':memory:')
script = "".join(line for line in db_server.iterdump())
db_memory.executescript(script)

But I keep getting an error at the line script = ''.join(db_server.iterdump()) or script = "".join(line for line in db_server.iterdump()):

Warning: IronPythonEvaluator.EvaluateIronPythonScript operation failed. 
Traceback (most recent call last):
  File "<string>", line 72, in <module>
NotImplementedError: Not supported with C#-sqlite for unknown reasons.

The code above came from seeing these posts:

I was going to try the solution in this post, but I don't have apsw and I can't load any packages.

I was also going to try the solition in post 1 above, but again, I can't get pandas.io.sql or sqlalchemy.

Can anyone point me to a snippet of code that accomplishes this or correct my current code? Thanks.

LoganAC34
  • 13
  • 4
  • SQLite version? – PChemGuy Dec 07 '22 at 05:11
  • @PChemGuy SQLite format 3. Python sqlite3 2.7.9. – LoganAC34 Dec 07 '22 at 13:07
  • 2.7.9 is the SQLite library version? – PChemGuy Dec 07 '22 at 13:47
  • @PChemGuy No, sorry. The SQLite library version is 3.7.7. I got this with `sqlite3.sqlite_version`. I think I typed the function wrong the first time using `sqlite3.version` instead. – LoganAC34 Dec 07 '22 at 14:07
  • Right, this one. The thing is quite old. I do not know if you can do better than duplicate the schema via DDL and then import data, e.g., via INSERT INTO... SELECT. The latter may not be available. There is also a combined CREATE/SELECT statement, which may not be available as well, in which case you would probably have to export/import the data. – PChemGuy Dec 07 '22 at 14:23
  • I can try playing around with those commands and see what I can come up with. If you have anything I can reference, that would be helpful. Thanks for the info. – LoganAC34 Dec 07 '22 at 15:32

0 Answers0