RHBMBqAz

1import xlrd
2import sqlite3
3
4
5class ExcelImporter:
6    def __init__(self):
7        self.name = ""
8
9    @staticmethod
10    def read_excel(path):
11
12        excel_list = []
13        wb = xlrd.open_workbook(path)
14        sheet = wb.sheet_by_index(0)
15
16        number_of_rows = sheet.nrows
17        number_of_columns = sheet.ncols
18
19        for row in range(0, number_of_rows):
20            row_list = []
21            for column in range(0, number_of_columns):
22                row_list.append(sheet.cell_value(row, column))
23            excel_list.append(row_list)
24        return excel_list
25
26    @staticmethod
27    def create_data_table(table_name, data_list):
28        db = sqlite3.connect("data.db")
29        try:
30            create_table_sql = "CREATE TABLE IF NOT EXISTS {0} ({0}_id INTEGER PRIMARY KEY AUTOINCREMENT,"\
31                .format(table_name)
32            for column_name in data_list[0]:
33                create_table_sql += "{} TEXT,".format(column_name)
34            create_table_sql = create_table_sql[0:len(create_table_sql)-1]
35            create_table_sql += ");"
36
37            db.cursor().execute(create_table_sql)
38            return True
39        except Exception as err:
40            print(str(err))
41            return False
42
43    @staticmethod
44    def insert_data(table_name, data_list):
45        db = sqlite3.connect("data.db")
46        for data in data_list[1:]:
47            sql = "insert into {0} (".format(table_name)
48            sql += ",".join(data_list[0])
49            sql += ") VALUES ("
50            stringified_list = list(map(str,data))
51            for element in stringified_list:
52                sql += "'{}',".format(element)
53            sql = sql[0: len(sql)-1]
54            sql += ");"
55            db.cursor().execute(sql)
56            db.commit()