File size: 5,344 Bytes
20db834 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 | 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)
# 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)
# 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())
|