· 9 years ago · Jan 04, 2017, 08:12 PM
1"""
2Final project for UW's Intro to Python, Autumn 2014.
3The project's name is "json2sql", because this script
4will take a json object and create an sqlite3 database
5and matching schemas.
6"""
7
8import json, re, time, os, sqlite3
9
10sample_dict = json.loads(open("test.json", "r").read())
11
12model_dict = {}
13
14
15def reduce_dict(obj,lev=0):
16
17 def process_dict(obj,lev=lev):
18 global model_dict
19 keys = obj.keys()
20
21 for k in keys:
22
23 if lev in model_dict:
24 if k not in model_dict:
25 model_dict[lev][k] = type(obj[k])
26 else:
27 model_dict[lev] = {k:type(obj[k])}
28
29 if isinstance(obj[k], list): process_list(obj[k],lev)
30
31 def process_list(obj,lev=lev):
32 global model_dict
33 keys = len(obj)
34
35 for k in range(keys):
36 # model_dict.append(type(obj[k]))
37 if isinstance(obj[k], dict): process_dict(obj[k],lev+1)
38
39 def finalize_sql():
40 global model_dict
41 model_sql = []
42 table_name = "table_"
43 table_exp = 1
44 table_num = 64
45 patterns = [
46 ["(<type \'?\"?)|(\'?\"?>)", ""],
47 [",?[A-z|0-9]+ (list|dict),?[^A-z|0-9|\W]?", ","],
48 ["[\S]?(string|str|unicode)(?![A-z])(?=[,|)|\s])", "TEXT"],
49 ["[\S]?(int|long)(?![A-z])(?=[,|)|\s])", "INTEGER"],
50 ["[\S]?(float)(?![A-z])(?=[,|)|\s])", "FLOAT"],
51 ["[\S]?(tuple|set|frozenset)(?![A-z])(?=[,|)|\s])", "BLOB"],
52 ["[\S]?(bool)(?![A-z])(?=[,|)|\s])", "BOOLEAN"]
53 ]
54
55 for table in model_dict:
56 table_exp = table_exp + 1 if table_num == 90 else table_exp
57 table_num = table_num + 1 if table >= 64 or table < 91 else 65
58 columns = []
59 for k in model_dict[table]:
60 columns.append("%s %s" % (k, model_dict[table][k]))
61
62 table_vars = ",".join(columns)
63 for p in patterns:
64 table_vars = re.sub(p[0],p[1],table_vars)
65
66 model_sql.append("CREATE TABLE %s%s (%s)" % (table_name,chr(table_num)*table_exp,table_vars))
67
68 return model_sql
69
70 if isinstance(obj, dict):
71 process_dict(obj)
72 elif isinstance(obj, list):
73 process_list(obj)
74 else:
75 type(obj)
76
77 return finalize_sql()
78
79
80
81
82if __name__ == '__main__':
83 m = reduce_dict(sample_dict)
84 print m
85
86 t = str(int(time.time()))[-4:]
87 folder = "./output"
88 sql_file = "%s/test%s.sql"%(folder,t)
89
90 if not os.path.isdir(folder): os.mkdir(folder)
91
92 with open(sql_file,"a+") as f:
93 for i in m:
94 f.writelines("%s;\n"%i)
95
96
97 db_file = './output/main.db'
98
99 db_is_new = not os.path.exists(db_file)
100
101 if db_is_new:
102 print 'Creating database...'
103 else:
104 print 'Database exists...'
105
106 with sqlite3.connect(db_file) as conn:
107
108 with open(sql_file, 'rt') as f:
109 schema = f.read()
110
111 conn.executescript(schema)
112
113
114 # Project TODO
115 # =======================================
116 # Once database / schemas are created,
117 # insert sample data from original json
118 # into the new database (12/11)