sanjeev21/ProductRecommendationSystemv2

1import sqlite32import pandas as pd3import datetime4 5# Functions6 7def create_insert_table(db_name, table_name, df):8    try:9        sqliteconnection = sqlite3.connect('sqlite_databases/{}.db'.format(db_name))10        cursor = sqliteconnection.cursor()11        print('DB Init')12 13        # Write a query and execute it with cursor14        # query = 'SELECT sqlite_version();'15        # cursor.execute(query)16 17        # Fetch and Output Result18        # result = cursor.fetchall()19        # print('SQLite Version is {}'.format(result))20 21        # Drop the table if already exists.22        cursor.execute("DROP TABLE IF EXISTS {}".format(table_name))23 24        # Creating table25        bizrate_table_string = """ CREATE TABLE {} (26        title VARCHAR(500),27        Brand CHAR(100),28        url TEXT,29        Image TEXT,30        Skus TEXT,31        price VARCHAR(50),32        originalPrice VARCHAR(50),33        markdownPercent VARCHAR(50),34        totalPrice VARCHAR(50),35        condition VARCHAR(10),36        stock VARCHAR(10),37        relevancy REAL); """.format(table_name)38 39        recsys_table_string = """ CREATE TABLE {} (40            title VARCHAR(500),41            Brand CHAR(100),42            url TEXT,43            Image TEXT,44            Skus TEXT,45            price REAL,46            originalPrice REAL,47            markdownPercent REAL,48            totalPrice REAL,49            condition VARCHAR(10),50            stock VARCHAR(10),51            relevancy REAL); """.format(table_name)52 53        # Create Table54        if db_name == 'bizrate':55            table_string = bizrate_table_string56            cursor.execute(table_string)57        elif db_name == 'RecSysData':58            table_string = recsys_table_string59            cursor.execute(table_string)60 61 62        # Inserting the DataFrame into the Sqlite Table63        df.to_sql(table_name, sqliteconnection, if_exists='replace', index=False)64        sqliteconnection.commit()65    # Handle Errors66    except sqlite3.Error as error:67        print('Error Occured - ', error)68 69    # Close the DB Connection Irrespective of Success or Failure70    finally:71        if sqliteconnection:72            sqliteconnection.close()73            print('SQLite Connection Closed.')74 75 76def query_table(db_name, table_name):77    try:78        sqliteconnection = sqlite3.connect('sqlite_databases/{}.db'.format(db_name))79        cursor = sqliteconnection.cursor()80        print('DB Init')81 82 83        query_string = '''84        SELECT *85        FROM {}86        '''.format(table_name)87 88        query_op_df = pd.read_sql_query(query_string, sqliteconnection)89 90    # Handle Errors91    except sqlite3.Error as error:92        print('Error Occured - ', error)93 94    # Close the DB Connection Irrespective of Success or Failure95    finally:96        if sqliteconnection:97            sqliteconnection.close()98            print('SQLite Connection Closed.')99 100    return query_op_df101 102 103def insert_clickdata_table(session_id, keyword, publisherid, sku, count):104    try:105        conn = sqlite3.connect('sqlite_databases/{}.db'.format('session_data'))106        cursor = conn.cursor()107        print('Click Data DB Init')108 109        # Creating Table110        table_string = """ CREATE TABLE IF NOT EXISTS session_data (111                clicked_at TIMESTAMP,112                session_id TEXT,113                keyword VARCHAR(100),114                publisherid VARCHAR(100),115                Skus TEXT,116                count INTEGER); """.format(table_name)117        cursor.execute(table_string)118        currentDateTime = datetime.datetime.now()119        insert_string = '''INSERT INTO session_data VALUES ('{}', '{}', '{}', '{}', '{}', {})'''.format(currentDateTime, session_id, keyword, publisherid, sku, count)120        print(insert_string)121        cursor.execute(insert_string)122        #cursor.execute('''INSERT INTO click_data (keyword, Skus) VALUES ({}, {})'''.format(table_name, keyword, sku))123 124        conn.commit()125 126 127    # Handle Errors128    except sqlite3.Error as error:129        print('Error Occured - ', error)130 131    # Close the DB Connection Irrespective of Success or Failure132    finally:133        if conn:134            conn.close()135            print('SQLite Connection Closed.')136 137 138def check_for_table(db_name, table_name):139    '''Checks whether the specified table exists within the specified database.140        Returns True if it does.141        Else returns False.'''142 143    filepath = 'sqlite_databases/'144    conn = sqlite3.connect(filepath + db_name + ".db")145    cursor = conn.cursor()146 147    query_string = '''148    SELECT name149    FROM sqlite_master150    WHERE type = 'table' AND name='{}';151    '''.format(table_name)152 153    result = cursor.execute(query_string)154    list_of_tables = result.fetchall()155    conn.close()156    # print(len(list_of_tables))157    return bool(len(list_of_tables))158 159# Main Program160 161# Input162file_path = 'bizrate/aqua_725895.xlsx'163 164file_name = file_path.split('/')165db_name = file_name[0]166table_name = file_name[1].split('.')[0]167 168 169# # Creating Table/ Inserting Data170# print('Creating/Accessing the DataBase: {} ;  Inserting Data into Table: {}'.format(db_name, table_name))171 172# df = pd.read_excel(file_path)173# .drop(columns='markdownpercent', inplace=True)174 175# create_insert_table(db_name, table_name, df)176 177 178# # Query Sqlite Database179# df = query_table(db_name, table_name)180# print(df.head())181# print(df.info())182