ignitariumcloud/knowledge_model

1import sqlite32from sqlite3 import Error3import csv4import pandas as pd5import os6 7 8def create_connection(db_file):9    """ create a database connection to a database that resides10        in the memory11    """12    conn = None;13    try:14        conn = sqlite3.connect(db_file)15        return conn16    17    except Error as e:18        print(e)19 20    return conn21 22 23def create_table(conn, create_table_sql, table_name):24    """ create a table from the create_table_sql statement25    :param conn: Connection object26    :param create_table_sql: a CREATE TABLE statement27    :return:28    """29    try:30        c = conn.cursor()31        c.execute(f"""DROP TABLE IF EXISTS {table_name}""")32        c.execute(create_table_sql)33    except Error as e:34        print(e)35 36 37def insert_values(conn, task, sql):38 39    cur = conn.cursor()40    cur.execute(sql, task)41    conn.commit()42    return cur.lastrowid43 44 45def populate(csv_file, db_file, table_insert):46    47    conn = create_connection(db_file)48    with conn:49        50        51        with open(csv_file, mode ='r')as file:52            csvfile = csv.reader(file)53 54            for n,lines in enumerate(csvfile):55                56                if n>0:57                    lines = tuple(i for i in lines)58                    insert_values(conn, lines, table_insert)59 60                else:pass61 62def main():63 64    # name = "sql_pdf.xlsx"65    name="ticket_dataset.xlsx"66    excel_file = (pd.read_excel(name))67    csv_file = f"""{name.split(".")[0]}.csv"""68    excel_file.to_csv(csv_file,69                    index=None,70                    header=True)71    column = [x for x in excel_file.columns]72    column_type = {}73    type_map = {74        "<class 'str'>": "TEXT",75        "<class 'int'>": "INTEGER",76        "<class 'float'>": "REAL",77    }78 79 80    for i in range(len(column)):81        datatype = {}82        for j in excel_file.values:83            if type(j[i]) not in list(datatype.keys()):datatype[type(j[i])] = 184            else: datatype[type(j[i])] += 185        86        ma_x = 087        max_type = "<class 'str'>"88        89        for k in list(datatype.keys()):90            if ma_x < datatype[k]:max_type = str(k)91 92        try:93            column_type[column[i]] = type_map[max_type]94 95        except KeyError:96            column_type[column[i]] = "TEXT"97 98    print(column_type)99 100    table_construct = f"""CREATE TABLE IF NOT EXISTS {name.split(".")[0]}( """101    table_insert = f"""INSERT INTO {name.split(".")[0]}("""102    table_values = f"""VALUES ("""103    for l in list(column_type.keys()):104        table_construct += f"""{l} {column_type[l]}, """105        table_insert += f"""{l}, """106        table_values += "?, "107    table_construct = f"""{table_construct[:-2]});"""108    table_values = f"""{table_values[:-2]})"""109    table_insert = f"""{table_insert[:-2]})\n{table_values}"""110 111    print(table_construct)112    print("\n\n", table_insert)113 114    database = f"""{name.split(".")[0]}.db"""115    116    conn = create_connection(database)117 118    # create tables119    if conn is not None:120        # create projects table121        create_table(conn, table_construct, name.split(".")[0])122    else:123        print("Error! cannot create the database connection.")124 125 126    populate(csv_file, database, table_insert)127 128if __name__ == '__main__':129    main()130