sqlite 如何使用QSqlDatabase将数据库从磁盘导入到内存中？

我希望在应用程序启动时将数据库从磁盘导入到内存，并在退出时将数据库从内存导出到磁盘。我把this code从C++转换成了Python。它成功地将数据库从内存导出到磁盘：

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')

输出量：

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

但是当我交换scr和des时，这个函数不起作用：

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

输出量：

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

它不会将磁盘数据库克隆到内存中的数据库。有什么问题吗？
我修改了VacumQuery：

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

测试结果：
无法打开数据库：file：：memory：无法获取行
内存中数据库设置和vacuum语句的不同组合的结果：

# 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:')

这是我正在使用的解决方案。

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;")

sqlite 如何使用QSqlDatabase将数据库从磁盘导入到内存中？

1条答案

相关问题

热门标签

最新问答