在磁盘数据库和快速内存数据库之间来回移动? [英] Moving back and forth between an on-disk database and a fast in-memory database?

问题描述

Python 的 sqlite3 :memory: 选项提供比等效的磁盘数据库更快的查询和更新.如何将基于磁盘的数据库加载到内存中，对其进行快速操作，然后将更新后的版本写回磁盘?

Python's sqlite3 :memory: option provides speedier queries and updates than the equivalent on-disk database. How can I load a disk-based database into memory, do fast operations on it, and then write the updated version back to disk?

问题如何在 python 中浏览内存中的 sqlite 数据库 似乎相关，但它侧重于如何使用基于磁盘的内存数据库上的浏览工具.问题 如何将内存中的 SQLite 数据库复制到 Python 中的另一个内存中的 SQLite 数据库? 也是相关的，但它是特定于 Django.

The question How to browse an in memory sqlite database in python seems related but it focuses on how to use a disk-based browsing tool on an in-memory db. The question How can I copy an in-memory SQLite database to another in-memory SQLite database in Python? is also related but it is specific to Django.

我当前的解决方案是从基于磁盘的数据库中一次一个地读取所有表到元组列表中，然后手动重新创建内存数据库的整个数据库架构，然后加载元组列表中的数据到内存数据库中.对数据进行操作后，过程反过来.

My current solution is to read all of the tables, one-at-a-time, from the disk-based database into lists of tuples, then manually recreate the entire database schema for the in-memory db, and then load the data from the lists of tuples into the in-memory db. After operating on the data, the process is reversed.

一定有更好的方法！

推荐答案

如何在 Python sqlite3 中将现有 db 文件加载到内存中的答案? 提供了重要线索.基于该答案，这里是该代码的简化和概括.

The answer at How to load existing db file to memory in Python sqlite3? provided the important clues. Building on that answer, here is a simplification and generalization of that code.

它消除了对 StringIO 的不必要使用，并被打包成可重用的形式，用于读取和写入内存数据库.

It eliminates eliminate the unnecessary use of StringIO and is packaged into reusable form for both reading into and writing from an in-memory database.

import sqlite3

def copy_database(source_connection, dest_dbname=':memory:'):
    '''Return a connection to a new copy of an existing database.                        
       Raises an sqlite3.OperationalError if the destination already exists.             
    '''
    script = ''.join(source_connection.iterdump())
    dest_conn = sqlite3.connect(dest_dbname)
    dest_conn.executescript(script)
    return dest_conn

if __name__ == '__main__':
    from contextlib import closing

    with closing(sqlite3.connect('pepsearch.db')) as disk_db:
        mem_db = copy_database(disk_db)

    mem_db.execute('DELETE FROM documents WHERE uri="pep-3154"')
    mem_db.commit()

    copy_database(mem_db, 'changed.db').close()

这篇关于在磁盘数据库和快速内存数据库之间来回移动?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！