· 5 years ago · Sep 05, 2020, 02:18 PM
1import json
2import os
3import sqlite3
4from sqlite3 import Error
5
6#Creating a JSON string and saving it in a python list.
7json_string = """[
8 {
9 "Name":"Uri Berger",
10 "Age": 22.0,
11 "Job": "Engineer",
12 "Years_left": 2
13 },
14
15 {
16 "Name":"Noam Halfon",
17 "Age": 91.0,
18 "Job": "Chef"
19 }
20]
21"""
22to_python = json.loads(json_string)
23
24
25#creating a dict that contains all the keys to add to the SQL table.
26def create_combined_dict(lst):
27 new_dict = {}
28 for dict in list(lst):
29 items = dict.items()
30 for (key,val) in items:
31 if key not in new_dict:
32 new_dict[key] = type(val)
33
34 return new_dict
35
36
37#The function gets a dictionary and creates a SQL string that creates a table with the keys and their types.
38def make_sql_string(dict):
39 sql_str = "CREATE TABLE IF NOT EXISTS workers( "
40 items = dict.items()
41 for (key,val) in items:
42 if val == str:
43 sql_str = sql_str + key + " text, "
44 elif val == int:
45 sql_str = sql_str + key + " integer, "
46 elif val == float:
47 sql_str = sql_str + key + " float, """
48
49 sql_str = sql_str[:-2] + ")"
50 return sql_str
51
52
53
54#A function that creates connection to a SQL database
55def create_connection(db_file):
56 conn = None
57 try:
58 conn = sqlite3.connect(db_file)
59 return conn
60
61
62 except Error as e:
63 print(e)
64
65 return conn
66
67
68
69#A function that creates a SQL table.
70def create_table(conn, create_table_sql):
71 res = False
72 try:
73 c = conn.cursor()
74 c.execute(create_table_sql)
75 res = True
76 return res
77 except Error as e:
78 print(e)
79
80 return res
81
82
83#A function that makes a sql statments to insert data to a table and execute it.
84def create_worker(conn, dict):
85
86 sql_keys = "("
87 sql_vals = "("
88 for (key, val) in dict.items():
89 sql_keys = sql_keys + key + ","
90
91 if type(val) != str:
92 sql_vals = sql_vals + str(val) + ","
93 else:
94 sql_vals = sql_vals + "\'" + val + "\',"
95
96
97 sql_str = "INSERT INTO workers" + sql_keys[:-1] + ") VALUES" + sql_vals[:-1]
98 sql_str = sql_str + ")"
99
100 cur = conn.cursor()
101 cur.execute(sql_str)
102 conn.commit()
103
104 return sql_str
105
106
107
108
109if __name__ == '__main__':
110 database = r"C:\sqlite\db\pythonsqlite.db"
111 table_dict = create_combined_dict(to_python)
112 sql_table = make_sql_string(table_dict)
113
114 conn = None
115 # create a database connection
116 conn = create_connection(database)
117
118 try:
119 # create workers table
120 res = create_table(conn, sql_table)
121
122 # inserts data to the table
123 for worker in to_python:
124 create_worker(conn, worker)
125
126 print("It works!")
127
128 except Error as e:
129 print(e)
130 print("Error! cannot create the database connection.")
131
132 if conn:
133 conn.close()
134
135
136
137
138
139