sanjeev21/ProductRecv3

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        clickReport_table_string = """ CREATE TABLE {} (54                    report_date VARCHAR(50),55                    publisher_id VARCHAR(10),56                    campaign_id VARCHAR(50),57                    placement_id VARCHAR(10),58                    rid TEXT,59                    keyword TEXT,60                    Skus TEXT,61                    clicks REAL,62                    earnings REAL,63                    cpc REAL); """.format(table_name)64 65        # Create Table66        if db_name == 'bizrate':67            table_string = bizrate_table_string68            cursor.execute(table_string)69        elif db_name == 'RecSysData':70            table_string = recsys_table_string71            cursor.execute(table_string)72        elif db_name == 'clickReport':73            table_string = clickReport_table_string74            cursor.execute(table_string)75 76 77        # Inserting the DataFrame into the Sqlite Table78        df.to_sql(table_name, sqliteconnection, if_exists='replace', index=False)79        sqliteconnection.commit()80    # Handle Errors81    except sqlite3.Error as error:82        print('Error Occured - ', error)83 84    # Close the DB Connection Irrespective of Success or Failure85    finally:86        if sqliteconnection:87            sqliteconnection.close()88            print('SQLite Connection Closed.')89 90 91def query_table(db_name, table_name):92    try:93        sqliteconnection = sqlite3.connect('sqlite_databases/{}.db'.format(db_name))94        cursor = sqliteconnection.cursor()95        print('DB Init')96 97 98        query_string = '''99        SELECT *100        FROM {}101        '''.format(table_name)102 103        query_op_df = pd.read_sql_query(query_string, sqliteconnection)104 105    # Handle Errors106    except sqlite3.Error as error:107        print('Error Occured - ', error)108 109    # Close the DB Connection Irrespective of Success or Failure110    finally:111        if sqliteconnection:112            sqliteconnection.close()113            print('SQLite Connection Closed.')114 115    return query_op_df116 117 118def insert_clickdata_table(session_id, keyword, publisherid, sku, count):119    try:120        conn = sqlite3.connect('sqlite_databases/{}.db'.format('session_data'))121        cursor = conn.cursor()122        print('Click Data DB Init')123 124        # Creating Table125        table_string = """ CREATE TABLE IF NOT EXISTS session_data (126                clicked_at TIMESTAMP,127                session_id TEXT,128                keyword VARCHAR(100),129                publisherid VARCHAR(100),130                Skus TEXT,131                count INTEGER); """.format(table_name)132        cursor.execute(table_string)133        currentDateTime = datetime.datetime.now()134        insert_string = '''INSERT INTO session_data VALUES ('{}', '{}', '{}', '{}', '{}', {})'''.format(currentDateTime, session_id, keyword, publisherid, sku, count)135        print(insert_string)136        cursor.execute(insert_string)137        #cursor.execute('''INSERT INTO click_data (keyword, Skus) VALUES ({}, {})'''.format(table_name, keyword, sku))138 139        conn.commit()140 141 142    # Handle Errors143    except sqlite3.Error as error:144        print('Error Occured - ', error)145 146    # Close the DB Connection Irrespective of Success or Failure147    finally:148        if conn:149            conn.close()150            print('SQLite Connection Closed.')151 152 153def check_for_table(db_name, table_name):154    '''Checks whether the specified table exists within the specified database.155        Returns True if it does.156        Else returns False.'''157 158    filepath = 'sqlite_databases/'159    conn = sqlite3.connect(filepath + db_name + ".db")160    cursor = conn.cursor()161 162    query_string = '''163    SELECT name164    FROM sqlite_master165    WHERE type = 'table' AND name='{}';166    '''.format(table_name)167 168    result = cursor.execute(query_string)169    list_of_tables = result.fetchall()170    conn.close()171    # print(len(list_of_tables))172    return bool(len(list_of_tables))173 174# Main Program175 176# Input177file_path = 'bizrate/aqua_725895.xlsx'178 179file_name = file_path.split('/')180db_name = file_name[0]181table_name = file_name[1].split('.')[0]182 183 184# # Creating Table/ Inserting Data185# print('Creating/Accessing the DataBase: {} ;  Inserting Data into Table: {}'.format(db_name, table_name))186 187# df = pd.read_excel(file_path)188# .drop(columns='markdownpercent', inplace=True)189 190# create_insert_table(db_name, table_name, df)191 192 193# # Query Sqlite Database194# df = query_table(db_name, table_name)195# print(df.head())196# print(df.info())197