8xfBAAUH

1# csvtosql.py
2
3import sqlite3, csv, time, os
4from contextlib import closing
5
6db_file = 'database.db'
7input_dir = 'input/'
8
9table_scheme = """create table if not exists records (
10	record_id integer primary key autoincrement,
11	month text not null,
12	region text not null,
13	branch text not null,
14	factory_id integer not null,
15	factory_name integer not null
16);"""
17
18
19def connect_db():
20	return sqlite3.connect(db_file)
21	
22def init_db():
23	with closing(connect_db()) as db:
24		db.cursor().executescript(table_scheme)
25		db.commit()
26
27def insert_row(row):
28	with closing(connect_db()) as db:
29		c = db.cursor()
30		db.commit()
31
32def insert_rows(rows, skip_first = True):
33	with closing(connect_db()) as db:
34		c = db.cursor()
35		for i, row in enumerate(rows):
36			if skip_first and i == 0:
37				continue
38			# print("Current row is {}".format(i))	
39			c.execute('INSERT into records (month, region, branch, factory_id, factory_name) values (?, ?, ?, ?, ?)', 
40			row[:5])
41		db.commit()
42
43def count_rows():
44	with closing(connect_db()) as db:
45		c = db.cursor()
46		c.execute('SELECT COUNT(record_id) from records')
47		counter = c.fetchall()
48	print(counter[0][0])
49
50def count_rows1():
51	with closing(connect_db()) as db:
52		c = db.cursor()
53		c.execute('SELECT region, count(*) as NUM FROM records GROUP BY region')
54		counter_table = c.fetchall()
55		print(counter_table)
56		
57init_db()
58t0 = time.time()
59
60input_files = os.listdir(input_dir)
61
62for file_i, csv_filename in enumerate(input_files):
63	print('Current file is #{} ({:.2f} secs).'.format(file_i, time.time()-t0))
64	with open(input_dir+csv_filename, 'r', newline='') as csvf:
65		csvreader = csv.reader(csvf, delimiter=';')
66		insert_rows(csvreader)
67
68print('It took {:.2f} secs'.format(time.time()-t0))