simple example presenting how persistent id can be used to pickle external objects by reference simple class representing a record in our database instead of pickling memorecord as a regular class instance we emit a persistent id here our persistent id is simply a tuple containing a tag and a key which refers to a specific record in the database if obj does not have a persistent id return none this means obj needs to be pickled as usual this method is invoked whenever a persistent id is encountered here pid is the tuple returned by dbpickler fetch the referenced record from the database and return it always raises an error if you cannot return the correct object otherwise the unpickler will think none is the object referenced by the persistent id initialize and populate our database fetch the records to be pickled save the records using our custom dbpickler update a record just for good measure load the records from the pickle data stream simple example presenting how persistent id can be used to pickle external objects by reference simple class representing a record in our database instead of pickling memorecord as a regular class instance we emit a persistent id here our persistent id is simply a tuple containing a tag and a key which refers to a specific record in the database if obj does not have a persistent id return none this means obj needs to be pickled as usual this method is invoked whenever a persistent id is encountered here pid is the tuple returned by dbpickler fetch the referenced record from the database and return it always raises an error if you cannot return the correct object otherwise the unpickler will think none is the object referenced by the persistent id initialize and populate our database fetch the records to be pickled save the records using our custom dbpickler update a record just for good measure load the records from the pickle data stream
import pickle
import sqlite3
from collections import namedtuple


MemoRecord = namedtuple("MemoRecord", "key, task")

class DBPickler(pickle.Pickler):

    def persistent_id(self, obj):
        
        
        if isinstance(obj, MemoRecord):
            
            
            return ("MemoRecord", obj.key)
        else:
            
            
            return None


class DBUnpickler(pickle.Unpickler):

    def __init__(self, file, connection):
        super().__init__(file)
        self.connection = connection

    def persistent_load(self, pid):
        
        
        cursor = self.connection.cursor()
        type_tag, key_id = pid
        if type_tag == "MemoRecord":
            
            cursor.execute("SELECT * FROM memos WHERE key=?", (str(key_id),))
            key, task = cursor.fetchone()
            return MemoRecord(key, task)
        else:
            
            
            
            raise pickle.UnpicklingError("unsupported persistent object")


def main():
    import io
    import pprint

    
    conn = sqlite3.connect(":memory:")
    cursor = conn.cursor()
    cursor.execute("CREATE TABLE memos(key INTEGER PRIMARY KEY, task TEXT)")
    tasks = (
        'give food to fish',
        'prepare group meeting',
        'fight with a zebra',
        )
    for task in tasks:
        cursor.execute("INSERT INTO memos VALUES(NULL, ?)", (task,))

    
    cursor.execute("SELECT * FROM memos")
    memos = [MemoRecord(key, task) for key, task in cursor]
    
    file = io.BytesIO()
    DBPickler(file).dump(memos)

    print("Pickled records:")
    pprint.pprint(memos)

    
    cursor.execute("UPDATE memos SET task='learn italian' WHERE key=1")

    
    file.seek(0)
    memos = DBUnpickler(file, conn).load()

    print("Unpickled records:")
    pprint.pprint(memos)


if __name__ == '__main__':
    main()