· 5 years ago · Sep 05, 2020, 09:56 PM
1import json
2import os
3import sqlite3
4
5
6def create_types_dict(worker_lst):
7 """
8 creating a dict that contains all the keys and types to add to the SQL table.
9 """
10 new_dict = {}
11 for worker in worker_lst:
12 for key,val in worker.items():
13 if key not in new_dict:
14 new_dict[key] = type(val)
15
16 return new_dict
17
18
19def create_table_sql(worker):
20 """
21 The function gets a dictionary and creates a SQL string that creates a table with the keys and their types.
22 """
23 sql_str = "CREATE TABLE IF NOT EXISTS workers( "
24 for key,val in worker.items() :
25 if val == str:
26 sql_str = sql_str + key + " text, "
27 elif val == int:
28 sql_str = sql_str + key + " integer, "
29 elif val == float:
30 sql_str = sql_str + key + " float, """
31 # Deletes the comma and space in the end of the string.
32 sql_str = sql_str[:-2] + ")"
33 return sql_str
34
35
36def create_connection(db_file):
37 """
38 A function that creates connection to a SQL database
39 """
40 conn = sqlite3.connect(db_file)
41 return conn
42
43
44def create_table(conn, table_dict):
45 """
46 A function that creates a SQL table.
47 """
48 table_sql = create_table_sql(table_dict)
49 c = conn.cursor()
50 c.execute(table_sql)
51
52
53def create_worker(conn, worker):
54 """
55 A function that makes a sql statments to insert data to a table and execute it.
56
57 """
58 sql_keys = "("
59 sql_vals = "("
60 for key, val in worker.items():
61 sql_keys = sql_keys + key + ","
62
63 if type(val) is not str:
64 sql_vals = sql_vals + str(val) + ","
65 else:
66 sql_vals = sql_vals + "'" + val + "',"
67
68 # Deletes the comma in the end of the string.
69 sql_str = "INSERT INTO workers" + sql_keys[:-1] + ") VALUES" + sql_vals[:-1]
70 sql_str = sql_str + ")"
71
72 cur = conn.cursor()
73 cur.execute(sql_str)
74 conn.commit()
75
76 return sql_str
77
78
79if __name__ == '__main__':
80
81 # Reading a JSON string from a file.
82 with open('data.json', 'r') as json_file:
83 data_dict = json.loads(json_file.read())
84
85 database = r"pythonsqlite.db"
86 table_dict = create_types_dict(data_dict)
87
88 conn = None
89 # create a database connection
90 conn = create_connection(database)
91
92 try:
93 # create workers table
94 create_table(conn, table_dict)
95
96 # inserts data to the table
97 for worker in data_dict:
98 create_worker(conn, worker)
99
100 print("It works!")
101
102 except sqlite3.Error as e:
103 print(e)
104 print("Error! cannot create the database connection.")
105
106 finally:
107 if conn:
108 conn.close()
109