Skip to content

Instantly share code, notes, and snippets.

@lanbugs

lanbugs/SQLiteToMemory.py

Last active Aug 26, 2019
Embed
What would you like to do?
SQLiteDB from file to memory and back to file
#!/usr/bin/env python3
#
# SQLiteToMemory
# Python class to load sqlite3 database to memory and back to file
# Written by Maximilian Thoma 2019
# Version 0.1
#
import sqlite3
from io import StringIO
import shutil
class SQLiteToMemory:
def __init__(self, file):
self.file = file
self.new_file = file + "_NEW"
self.con_file = sqlite3.connect(self.file)
self.con_new_file = sqlite3.connect(self.new_file)
self.con_memory = sqlite3.connect(":memory:")
self.tmp_file = StringIO()
self.tmp_memory = StringIO()
def load_to_memory(self):
# Load database from file to memory
for line in self.con_file.iterdump():
self.tmp_file.write('%s\n' % line)
self.tmp_file.seek(0)
self.con_memory.cursor().executescript(self.tmp_file.read())
self.con_memory.commit()
def dump_to_file(self):
# Dump memory database back to file
for line in self.con_memory.iterdump():
self.tmp_memory.write('%s\n' % line)
self.tmp_memory.seek(0)
self.con_new_file.cursor().executescript(self.tmp_memory.read())
self.con_new_file.commit()
self.con_new_file.close()
self.con_memory.close()
shutil.move(self.new_file, self.file)
def con_memory(self):
# Returns connection to memory database
return self.con_memory
########################################################################################################
# Example:
db = SQLiteToMemory("test.db")
db.load_to_memory()
cursor = db.con_memory.cursor()
cursor.execute("SELECT * FROM testtable")
rows = cursor.fetchall()
print(len(rows))
cursor.execute("INSERT INTO testtable VALUES(NULL, 'TEST1','TEST1',NULL,'TEST1')")
db.dump_to_file()
########################################################################################################
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment