FMTWhv2t

1import json
2import os
3
4
5def json2sql(json_path, sql_path, table_name, encoding='utf-8', is_multi_line=False):
6    # read the data
7    data = []
8    with open(json_path, 'r', encoding=encoding) as f:
9        if is_multi_line:
10            for line in f:
11                data.append(json.loads(line))
12        else:
13            data = json.loads(f)
14
15    # get keys for generating create table statements
16    keys = list(data[0].keys())
17
18    # open the output file
19    out_file = open(sql_path, 'w', encoding=encoding)
20
21    # write the create table
22    out_file.write('CREATE TABLE IF NOT EXISTS `%s` (\n' % table_name)
23    out_file.write('\t`TABLE_ID` INT(11) NOT NULL AUTO_INCREMENT,\n')
24    for key in keys:
25        out_file.write('\t`%s` VARCHAR(128) DEFAULT NULL,\n' % key)
26    out_file.write('\tPRIMARY KEY (`TABLE_ID`)\n')
27    out_file.write(')DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;\n\n')
28
29    # write insert statement
30    out_file.write('INSERT INTO %s(?) VALUES\n' % (table_name), ', '.join(keys))
31
32    # write multiple value
33    str_list = []
34
35    for item in data.items():
36        str_list.append('\t(%s)' % ', '.join(['\"' + str(value) + '\"' for value in item.values()]))
37
38    out_file.write(',\n'.join(str_list))
39    out_file.write(';')
40
41
42def main():
43    import argparse
44
45    # set up argument parser
46    parser = argparse.ArgumentParser(description='Convert multiple json-formatted data into sql statements.')
47    parser.add_argument('json_path', action='store')
48    parser.add_argument('-t', '--table',
49                        action='store', dest='table_name', default='TABLE',
50                        help='The name of the table to be created or inserted.')
51    parser.add_argument('-s', '--sql',
52                        action='store', dest='sql_path',
53                        help='The path of the output sql file.')
54    parser.add_argument('-l', '--lines',
55                        action='store_true', dest='is_multi_line', default=False,
56                        help='Whether the file contains many objects line by line or a big json object.')
57    parser.add_argument('-e', '--encoding',
58                        action='store', dest='encoding', default='utf-8',
59                        help='The encoding of the json and sql file.')
60
61    results = parser.parse_args()
62
63    if not results.sql_path:
64        root, _ = os.path.splitext(results.json_path)
65        results.sql_path = root + '.sql'
66
67    json2sql(results.json_path, results.sql_path, results.table_name,
68             encoding=results.encoding, is_multi_line=results.is_multi_line)
69
70
71if __name__ == '__main__':
72    main()