insert_json_to_sqldb.py

#!/usr/bin/python
#-*- coding: utf-8 -*-
import sys, json
import argparse

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine

Base = declarative_base()

def main(argv):
    parser = argparse.ArgumentParser(description='')
    parser.add_argument('-d', '--database', default='/tmp/tmp.sqlite', action='store')
    parser.add_argument('-t', '--table', action='store')
    parser.add_argument('file', action='store')
    args = parser.parse_args(argv)
    obj = json.load(open(args.file, 'r'))
    table_name = (args.table if args.table else args.file.strip('.')[0])
    database_url = 'sqlite:///' + args.database
    engine = create_engine(database_url)

    schema = ', '.join(obj.keys())
    #schema = obj['schema'].split(',')
    q = '(' + ','.join(['?' for i in range(len(obj.keys()))]) + ')'
    #q = '(' + ','.join(['?' for i in range(len(schema))]) + ')'
    query = 'INSERT INTO %(table_name)s (%(schema)s) VALUES %(value)s;' % {'table_name': table_name,
                                                                        'schema': schema,
                                                                        'value': q}
    engine.execute(query, obj.values())
    
if __name__ == '__main__':
    main(sys.argv[1:])