import json import sqlite3 from datetime import datetime from six import string_types def connect(path): sqlite3.register_adapter(dict, jsonify) sqlite3.register_adapter(list, jsonify) db = sqlite3.connect(path) db.create_function('json_get', 2, json_get) return db def jsonify(obj): return json.dumps(obj, default=json_serialize) def json_serialize(obj): """ Simple generic JSON serializer for common objects. """ if isinstance(obj, datetime): return obj.isoformat() if hasattr(obj, 'id'): return jsonify(obj.id) if hasattr(obj, 'name'): return jsonify(obj.name) raise TypeError('{0} is not JSON serializable'.format(obj)) def json_get(serialized_object, field): """ This emulates the HSTORE `->` get value operation. It get value from JSON serialized column by given key and return `null` if not present. Key can be either an integer for array index access or a string for object field access. :return: JSON serialized value of key in object """ # return null if serialized_object is null or "serialized null" if serialized_object is None: return None obj = json.loads(serialized_object) if obj is None: return None if isinstance(field, int): # array index access res = obj[field] if 0 <= field < len(obj) else None else: # object field access res = obj.get(field) if not isinstance(res, (int, float, string_types)): res = json.dumps(res) return res def create_table(db, schema_name, table_name, columns): """ Create a table, schema_name.table_name, in given database with given list of column names. """ table = '{0}.{1}'.format(schema_name, table_name) if schema_name else table_name db.execute('DROP TABLE IF EXISTS {0}'.format(table)) columns_list = ', '.join(columns) db.execute('CREATE TABLE {0} ({1})'.format(table, columns_list)) def insert_all(db, schema_name, table_name, columns, items): """ Insert all item in given items list into the specified table, schema_name.table_name. """ table = '{0}.{1}'.format(schema_name, table_name) if schema_name else table_name columns_list = ', '.join(columns) values_list = ', '.join(['?'] * len(columns)) query = 'INSERT INTO {table} ({columns}) VALUES ({values})'.format( table=table, columns=columns_list, values=values_list) for item in items: values = [getattr(item, col) for col in columns] db.execute(query, values)