python pickle + sqlite3 存储常见数据格式

最近在学习阿里云函数计算，利用函数计算 + NAS，做个把函数中数据持久化的 key-value 数据库方案。项目地址 https://github.com/jneeee/aliyun_func_study 多多 star

pickle.dumps pickle.loads 是 python 中常见的序列化、反序列化的工具。可以将 Python 中的数据结构（甚至是自定义的类实例）写入文件、从文件恢复，并且效率非常高。

以下示例利用 pickle 和 sqlite3 完成如下功能：

• 创建一个 key value 数据库
• value 为 Python 中的 list/dict/set
• insert 类似 PUT 逻辑（create or update）

文件地址：https://github.com/jneeee/aliyun_func_study/blob/master/crontask/utils/db.py

# -*- coding: utf-8 -*-
import sqlite3
import os
import pickle

# every 'pickle' obj in db should be dumped
sqlite3.register_converter("pickle", pickle.loads)
sqlite3.register_adapter(list, pickle.dumps)
sqlite3.register_adapter(dict, pickle.dumps)
sqlite3.register_adapter(set, pickle.dumps)

class DB():

    def __init__(self, db_path="/home/ubuntu/crontask.db"):
        create_table = False
        if not os.path.exists(db_path):
            create_table = True
        self.conn = sqlite3.connect(db_path, check_same_thread=False, 
                                    detect_types=sqlite3.PARSE_DECLTYPES)
        table_name = "kvdb"
        if create_table:
            self.conn.execute('''
                CREATE TABLE %s(
                    key TEXT PRIMARY KEY,
                    value pickle
                )''' % (table_name)
            )
        self.op_string = {
            'insert_string': "INSERT into %s values (?, ?)" % (table_name),
            'update_string': "UPDATE %s SET value=? WHERE key=?" % (table_name),
            'select_value': "SELECT value FROM %s WHERE key=?" % (table_name),
            'delete_key': 'DELETE from %s where key=?' % (table_name),
        }

    def select(self, key):
        cur = self.conn.cursor()
        if not key:
            cmd = "select * from kvdb"
            ret = cur.execute(cmd).fetchall()
        else:
            cmd = self.op_string['select_value']
            ret = cur.execute(cmd, (key,)).fetchone()
            if ret:
                ret = ret[0]
        cur.close()
        return ret

    def insert(self, k, v):
        if type(v) not in (list, set, dict):
            raise TypeError
        cur = self.conn.cursor()
        try:
            cur.execute(self.op_string['insert_string'], (k, v))
            oprate = 'insert'
        except sqlite3.IntegrityError:
            oprate = 'update'
            cur.execute(self.op_string['update_string'], (v, k))
        cur.close()
        self.conn.commit()
        return oprate

    def delete(self, key):
        cur = self.conn.cursor()
        cur.execute(self.op_string['delete_key'], (key,))
        cur.close()
        self.conn.commit()

    def sqlcmd(self, cmd):
        cur = self.conn.cursor()
        res = cur.execute(cmd).fetchall()
        cur.close()
        return res

    def close(self):
        self.conn.close()

    def commit(self):
        self.conn.commit()

dbclient = DB()

ref: https://gist.github.com/JonathanRaiman/aa0bdfd8e3511c59f3af