How to use QSqlDatabase to import database from disk to in-memory?

Question

I want to import a database from disk to memory at application start and on exit export back from memory to disk. I converted this code from C++ to Python. It successfully exports the database from memory to disk:

import sys
from PySide6.QtSql import QSqlDatabase, QSqlQuery

# Create the connection
con_mem = QSqlDatabase.addDatabase("QSQLITE", 'con_mem')
con_mem.setDatabaseName("file::memory:")
con_mem.setConnectOptions('QSQLITE_OPEN_URI;QSQLITE_ENABLE_SHARED_CACHE')

con_disk = QSqlDatabase.addDatabase("QSQLITE", 'con_disk')
con_disk.setDatabaseName("db.sqlite")

# Open the connection
if not con_disk.open() and con_mem.open():
    print("Databases open error")
    sys.exit(1)

db_mem = QSqlDatabase.database('con_mem')
db_disk = QSqlDatabase.database('con_disk')

def clone_db(scr, des, des_string):
    print('Before creating table')
    print('scr: ', scr.tables())
    print('des: ', des.tables())

# Create a test table at scr
    createTableQuery = QSqlQuery(scr)
    createTableQuery.exec(
        """
        CREATE TABLE contacts (
            id INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE NOT NULL,
            name VARCHAR(40) NOT NULL,
            job VARCHAR(50),
            email VARCHAR(40) NOT NULL
        )
        """
    )

    print('After creating table')
    print('scr: ', scr.tables())
    print('des: ', des.tables())

    VacumQuery = QSqlQuery(scr)
    VacumQuery.exec(f'VACUUM main INTO "{des_string}"')

    print('After vacum')
    print('scr: ', scr.tables())
    print('des: ', des.tables())

clone_db(db_mem, db_disk, 'db.sqlite')

Output:

Before creating table
scr:  []
des:  []
After creating table
scr:  ['contacts', 'sqlite_sequence']
des:  []
After vacum
scr:  ['contacts', 'sqlite_sequence']
des:  ['contacts', 'sqlite_sequence']

But when I swap scr and des this function doesn't work:

clone_db(db_disk, db_mem, 'file::memory:')

Output:

Before creating table
scr:  []
des:  []
After creating table
scr:  ['contacts', 'sqlite_sequence']
des:  []
After vacum
scr:  ['contacts', 'sqlite_sequence']
des:  []

It does not clone the disk database to an in-memory one. What is the problem?

I changed VacumQuery:

VacumQuery = QSqlQuery(scr)
    if not VacumQuery.exec(f'VACUUM main INTO "{des_string}"'):
        print(VacumQuery.lastError().text())

Result:

unable to open database: file::memory: Unable to fetch row

Result of different combinations of in-memory database setup and vacuum statement:

# Test 1: No error. Database not imported. A 'file' is created in current directory.

con_mem.setDatabaseName('file:db?mode=memory&cache=shared')
con_mem.setConnectOptions('QSQLITE_OPEN_URI;QSQLITE_ENABLE_SHARED_CACHE')

......
clone_db(db_disk, db_mem, 'file:db?mode=memory&cache=shared')

# Test 2: No error. Database not imported.

con_mem.setDatabaseName(':memory:')
con_mem.setConnectOptions('QSQLITE_OPEN_URI;QSQLITE_ENABLE_SHARED_CACHE')

......
clone_db(db_disk, db_mem, ':memory:')

# Test 3: No error. Database not imported.

con_mem.setDatabaseName(':memory:')
con_mem.setConnectOptions('QSQLITE_ENABLE_SHARED_CACHE')

......
clone_db(db_disk, db_mem, ':memory:')

[`exec()`](https://doc.qt.io/qt-6/qsqlquery.html#exec-1) returns `False` if the query wasn't successful, so you should add a check to see the possible error: `if not VacumQuery.exec():` `print(VacumQuery.lastError().text())`. — musicamante, Jul 28 '23 at 19:02
Got `unable to open database: file::memory: Unable to fetch row`. Could not find a valid string to access the in-memory database with `vacuum` — tom, Jul 29 '23 at 18:09

score 0 · Answer 1 · answered Aug 26 '23 at 13:12

This is a work around solution I am using.

import os
from PySide6.QtSql import QSqlDatabase, QSqlQuery

# Create a new database connection with the database name set to "file::memory:"
con_mem = QSqlDatabase.addDatabase("QSQLITE", "con_mem")
con_mem.setDatabaseName("file::memory:")
con_mem.setConnectOptions("QSQLITE_OPEN_URI;QSQLITE_ENABLE_SHARED_CACHE")

# Open the database connection
if not con_mem.open():
    print("Failed to open database connection")
    exit(1)

# Execute a SQL statement to create the tables in the in-memory database
query = QSqlQuery(con_mem)
query.exec(f"ATTACH DATABASE '{os.path.abspath('database.db')}' AS disk_db;")
query.exec("SELECT sql FROM disk_db.sqlite_master WHERE type='table';")
create_table_sql = []
while query.next():
    create_table_sql.append(query.value(0))
for sql in create_table_sql:
    query.exec(sql)

# Disable constraints to import data
query.exec("PRAGMA foreign_keys = OFF;")
query.exec("PRAGMA ignore_check_constraints = ON;")

# Execute a SQL statement to copy the data from the disk database to the in-memory database
query.exec("SELECT name FROM disk_db.sqlite_master WHERE type='table';")
table_names = []
while query.next():
    table_names.append(query.value(0))
for table_name in table_names:
    query.exec(f"INSERT INTO {table_name} SELECT * FROM disk_db.{table_name}")
query.exec("DETACH DATABASE disk_db;")

# Enable constraints
query.exec("PRAGMA foreign_keys = ON;")
query.exec("PRAGMA ignore_check_constraints = OFF;")

How to use QSqlDatabase to import database from disk to in-memory?

1 Answers1