sqlite_database.py

import sqlite3
import pandas as pd
import datetime

# Functions

def create_insert_table(db_name, table_name, df):
    try:
        sqliteconnection = sqlite3.connect('sqlite_databases/{}.db'.format(db_name))
        cursor = sqliteconnection.cursor()
        print('DB Init')

        # Write a query and execute it with cursor
        # query = 'SELECT sqlite_version();'
        # cursor.execute(query)

        # Fetch and Output Result
        # result = cursor.fetchall()
        # print('SQLite Version is {}'.format(result))

        # Drop the table if already exists.
        cursor.execute("DROP TABLE IF EXISTS {}".format(table_name))

        # Creating table
        bizrate_table_string = """ CREATE TABLE {} (
        title VARCHAR(500),
        Brand CHAR(100),
        url TEXT,
        Image TEXT,
        Skus TEXT,
        price VARCHAR(50),
        originalPrice VARCHAR(50),
        markdownPercent VARCHAR(50),
        totalPrice VARCHAR(50),
        condition VARCHAR(10),
        stock VARCHAR(10),
        relevancy REAL); """.format(table_name)

        recsys_table_string = """ CREATE TABLE {} (
            title VARCHAR(500),
            Brand CHAR(100),
            url TEXT,
            Image TEXT,
            Skus TEXT,
            price REAL,
            originalPrice REAL,
            markdownPercent REAL,
            totalPrice REAL,
            condition VARCHAR(10),
            stock VARCHAR(10),
            relevancy REAL); """.format(table_name)

        clickReport_table_string = """ CREATE TABLE {} (
                    report_date VARCHAR(50),
                    publisher_id VARCHAR(10),
                    campaign_id VARCHAR(50),
                    placement_id VARCHAR(10),
                    rid TEXT,
                    keyword TEXT,
                    Skus TEXT,
                    clicks REAL,
                    earnings REAL,
                    cpc REAL); """.format(table_name)

        # Create Table
        if db_name == 'bizrate':
            table_string = bizrate_table_string
            cursor.execute(table_string)
        elif db_name == 'RecSysData':
            table_string = recsys_table_string
            cursor.execute(table_string)
        elif db_name == 'clickReport':
            table_string = clickReport_table_string
            cursor.execute(table_string)


        # Inserting the DataFrame into the Sqlite Table
        df.to_sql(table_name, sqliteconnection, if_exists='replace', index=False)
        sqliteconnection.commit()
    # Handle Errors
    except sqlite3.Error as error:
        print('Error Occured - ', error)

    # Close the DB Connection Irrespective of Success or Failure
    finally:
        if sqliteconnection:
            sqliteconnection.close()
            print('SQLite Connection Closed.')


def query_table(db_name, table_name):
    try:
        sqliteconnection = sqlite3.connect('sqlite_databases/{}.db'.format(db_name))
        cursor = sqliteconnection.cursor()
        print('DB Init')


        query_string = '''
        SELECT *
        FROM {}
        '''.format(table_name)

        query_op_df = pd.read_sql_query(query_string, sqliteconnection)

    # Handle Errors
    except sqlite3.Error as error:
        print('Error Occured - ', error)

    # Close the DB Connection Irrespective of Success or Failure
    finally:
        if sqliteconnection:
            sqliteconnection.close()
            print('SQLite Connection Closed.')

    return query_op_df


def insert_clickdata_table(session_id, keyword, publisherid, sku, count):
    try:
        conn = sqlite3.connect('sqlite_databases/{}.db'.format('session_data'))
        cursor = conn.cursor()
        print('Click Data DB Init')

        # Creating Table
        table_string = """ CREATE TABLE IF NOT EXISTS session_data (
                clicked_at TIMESTAMP,
                session_id TEXT,
                keyword VARCHAR(100),
                publisherid VARCHAR(100),
                Skus TEXT,
                count INTEGER); """.format(table_name)
        cursor.execute(table_string)
        currentDateTime = datetime.datetime.now()
        insert_string = '''INSERT INTO session_data VALUES ('{}', '{}', '{}', '{}', '{}', {})'''.format(currentDateTime, session_id, keyword, publisherid, sku, count)
        print(insert_string)
        cursor.execute(insert_string)
        #cursor.execute('''INSERT INTO click_data (keyword, Skus) VALUES ({}, {})'''.format(table_name, keyword, sku))

        conn.commit()


    # Handle Errors
    except sqlite3.Error as error:
        print('Error Occured - ', error)

    # Close the DB Connection Irrespective of Success or Failure
    finally:
        if conn:
            conn.close()
            print('SQLite Connection Closed.')


def check_for_table(db_name, table_name):
    '''Checks whether the specified table exists within the specified database.
        Returns True if it does.
        Else returns False.'''

    filepath = 'sqlite_databases/'
    conn = sqlite3.connect(filepath + db_name + ".db")
    cursor = conn.cursor()

    query_string = '''
    SELECT name
    FROM sqlite_master
    WHERE type = 'table' AND name='{}';
    '''.format(table_name)

    result = cursor.execute(query_string)
    list_of_tables = result.fetchall()
    conn.close()
    # print(len(list_of_tables))
    return bool(len(list_of_tables))

# Main Program

# Input
file_path = 'bizrate/aqua_725895.xlsx'

file_name = file_path.split('/')
db_name = file_name[0]
table_name = file_name[1].split('.')[0]


# # Creating Table/ Inserting Data
# print('Creating/Accessing the DataBase: {} ;  Inserting Data into Table: {}'.format(db_name, table_name))

# df = pd.read_excel(file_path)
# .drop(columns='markdownpercent', inplace=True)

# create_insert_table(db_name, table_name, df)


# # Query Sqlite Database
# df = query_table(db_name, table_name)
# print(df.head())
# print(df.info())