Without installing additional modules, how can I use the SQLite backup API to backup an in-memory database to an on-disk database? I have managed to successfully perform a disk-to-disk backup, but passing the already-extant in-memory connection to the sqlite3_backup_init
function appears to be the problem.
My toy example, adapted from https://gist.github.com/achimnol/3021995 and cut down to the minimum, is as follows:
import sqlite3
import ctypes
# Create a junk in-memory database
sourceconn = sqlite3.connect(':memory:')
cursor = sourceconn.cursor()
cursor.execute('''CREATE TABLE stocks
(date text, trans text, symbol text, qty real, price real)''')
cursor.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
sourceconn.commit()
target = r'C:\data\sqlite\target.db'
dllpath = u'C:\\Python27\DLLs\\sqlite3.dll'
# Constants from the SQLite 3 API defining various return codes of state.
SQLITE_OK = 0
SQLITE_ERROR = 1
SQLITE_BUSY = 5
SQLITE_LOCKED = 6
SQLITE_OPEN_READONLY = 1
SQLITE_OPEN_READWRITE = 2
SQLITE_OPEN_CREATE = 4
# Tweakable variables
pagestocopy = 20
millisecondstosleep = 100
# dllpath = ctypes.util.find_library('sqlite3') # I had trouble with this on Windows
sqlitedll = ctypes.CDLL(dllpath)
sqlitedll.sqlite3_backup_init.restype = ctypes.c_void_p
# Setup some ctypes
p_src_db = ctypes.c_void_p(None)
p_dst_db = ctypes.c_void_p(None)
null_ptr = ctypes.c_void_p(None)
# Check to see if the first argument (source database) can be opened for reading.
# ret = sqlitedll.sqlite3_open_v2(sourceconn, ctypes.byref(p_src_db), SQLITE_OPEN_READONLY, null_ptr)
#assert ret == SQLITE_OK
#assert p_src_db.value is not None
# Check to see if the second argument (target database) can be opened for writing.
ret = sqlitedll.sqlite3_open_v2(target, ctypes.byref(p_dst_db), SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, null_ptr)
assert ret == SQLITE_OK
assert p_dst_db.value is not None
# Start a backup.
print 'Starting backup to SQLite database "%s" to SQLite database "%s" ...' % (sourceconn, target)
p_backup = sqlitedll.sqlite3_backup_init(p_dst_db, 'main', sourceconn, 'main')
print ' Backup handler: {0:#08x}'.format(p_backup)
assert p_backup is not None
# Step through a backup.
while True:
ret = sqlitedll.sqlite3_backup_step(p_backup, pagestocopy)
remaining = sqlitedll.sqlite3_backup_remaining(p_backup)
pagecount = sqlitedll.sqlite3_backup_pagecount(p_backup)
print ' Backup in progress: {0:.2f}%'.format((pagecount - remaining) / float(pagecount) * 100)
if remaining == 0:
break
if ret in (SQLITE_OK, SQLITE_BUSY, SQLITE_LOCKED):
sqlitedll.sqlite3_sleep(millisecondstosleep)
# Finish the bakcup
sqlitedll.sqlite3_backup_finish(p_backup)
# Close database connections
sqlitedll.sqlite3_close(p_dst_db)
sqlitedll.sqlite3_close(p_src_db)
I receive an error ctypes.ArgumentError: argument 3: <type 'exceptions.TypeError'>: Don't know how to convert parameter 3
on line 49 (p_backup = sqlitedll.sqlite3_backup_init(p_dst_db, 'main', sourceconn, 'main')
). Somehow, I need to pass a reference to the in-memory database to that sqlite3_backup_init function.
I do not know enough C to grasp the specifics of the API itself.
Setup: Windows 7, ActiveState Python 2.7
SQLite Python: Querying Data First, establish a connection to the SQLite database by creating a Connection object. Next, create a Cursor object using the cursor method of the Connection object. Then, execute a SELECT statement. After that, call the fetchall() method of the cursor object to fetch the data.
SQLite in-memory databases are databases stored entirely in memory, not on disk. Use the special data source filename :memory: to create an in-memory database. When the connection is closed, the database is deleted. When using :memory: , each connection creates its own database.
Inserting data using pythonImport sqlite3 package. Create a connection object using the connect() method by passing the name of the database as a parameter to it. The cursor() method returns a cursor object using which you can communicate with SQLite3.
It looks like as of Python 3.7, this functionality is available within the standard library. Here are some examples copied directly out of the official docs:
Example 1, copy an existing database into another:
import sqlite3
def progress(status, remaining, total):
print(f'Copied {total-remaining} of {total} pages...')
con = sqlite3.connect('existing_db.db')
bck = sqlite3.connect('backup.db')
with bck:
con.backup(bck, pages=1, progress=progress)
bck.close()
con.close()
Example 2, copy an existing database into a transient copy:
import sqlite3
source = sqlite3.connect('existing_db.db')
dest = sqlite3.connect(':memory:')
source.backup(dest)
To answer your specific question of backing up an in-memory database to disk, it looks like this works. Here's a quick script using the standard library backup
method:
import sqlite3
source = sqlite3.connect(':memory:')
dest = sqlite3.connect('backup.db')
c = source.cursor()
c.execute("CREATE TABLE test(id INTEGER PRIMARY KEY, msg TEXT);")
c.execute("INSERT INTO test VALUES (?, ?);", (1, "Hello World!"))
source.commit()
source.backup(dest)
dest.close()
source.close()
And the backup.db
database can be loaded into sqlite3
and inspected:
$ sqlite3 backup.db
SQLite version 3.24.0 2018-06-04 14:10:15
Enter ".help" for usage hints.
sqlite> .schema
CREATE TABLE test(id INTEGER PRIMARY KEY, msg TEXT);
sqlite> SELECT * FROM test;
1|Hello World!
An in-memory database can be accessed only through the SQLite library that created it (in this case, Python's built-in SQLite).
Python's sqlite3
module does not give access to the backup API, so it is not possible to copy an in-memory database.
You would need to install an additional module, or use an on-disk database in the first place.
While this isn't strictly a solution to your questions (as it's not using the backup API) it serves as a minimal effort approach and works well for small in memory databases.
import os
import sqlite3
database = sqlite3.connect(':memory:')
# fill the in memory db with your data here
dbfile = 'dbcopy.db'
if os.path.exists(dbfile):
os.remove(dbfile) # remove last db dump
new_db = sqlite3.connect(dbfile)
c = new_db.cursor()
c.executescript("\r\n".join(database.iterdump()))
new_db.close()
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With