def newInsertion_products(conn, coll1, coll2, data_input, attributes,
attributes2, fk):
lengte = len(data_input)
bar = '| |'
bargrens = 10
print(bar)
count = 0
for datapoint in data_input:
query = "INSERT INTO {} VALUES(".format(coll1)
count += 1
for element in attributes:
if element in datapoint:
if element == 'price':
attribute = datapoint[element]['selling_price']
else:
attribute = datapoint[element]
else:
attribute = 'Empty'
if element == fk:
data_fk = attribute
query = Modify.change_query(query, attribute)
query = query[:-1] + ')'
SQL.sqlexecute(conn, query, on_conflict=False)
insertion_stock(conn, coll2, datapoint, attributes2, fk, data_fk)
if (count / lengte) * 100 > bargrens:
bargrens += 10
print('*', end='')
def insertion_similar_product(conn, coll, datapoint, attributes, fk, data_fk):
similars = datapoint['similars']
for similar in similars:
query = "INSERT INTO {} VALUES('{}',".format(coll, data_fk)
query = Modify.change_query(query, similar)
query = query[:-1] + ')'
SQL.sqlexecute(conn, query, on_conflict=False)
def insertion_visitors_buids(conn, coll, datapoint, attributes, fk, data_fk):
if 'buids' in datapoint:
data = datapoint['buids']
for element in data:
query = "INSERT INTO {} VALUES('{}',".format(coll, data_fk)
attribute = str(element).replace("'", "")
query += "'" + attribute + "'" + ","
query = query[:-1] + ')'
SQL.sqlexecute(conn, query, on_conflict=False)
def insertion_order_products(conn, coll, datapoint, attributes, fk, data_fk):
ignored_records = 0
count = 0
if 'order' in datapoint:
if 'products' in datapoint['order']:
for product in datapoint['order']['products']:
query = "INSERT INTO {} VALUES(".format(coll)
query = Modify.change_query(query, product['id'])
query = Modify.change_query(query, data_fk)
query = query[:-1] + ')'
count += 1
try:
SQL.sqlexecute(conn, query, on_conflict=False)
except Exception as e:
ignored_records += 1
return ignored_records
def insertion_recommendation(conn, coll, datapoint, attributes, attributes2,
fk, data_fk):
query = "INSERT INTO {} VALUES(".format(coll)
if 'recommendations' in datapoint:
recommendations = datapoint['recommendations']
for element in attributes:
if element == fk:
attribute = data_fk
elif element in recommendations:
attribute = recommendations[element]
else:
attribute = 'Empty'
query = Modify.change_query(query, attribute)
query = query[:-1] + ')'
SQL.sqlexecute(conn, query, on_conflict=False)
insertion_similar_product(conn, 'similar_product', recommendations,
attributes2, fk, data_fk)
def insertion_stock(conn, coll, datapoint, attributes, fk, data_fk):
data = datapoint['stock']
for element in data:
query = "INSERT INTO {} VALUES(".format(coll)
for att in attributes:
if att == fk:
attribute = data_fk
attribute = str(attribute).replace("'", "")
query += "'" + attribute + "'" + ","
elif att in element:
attribute = element[att]
attribute = str(attribute).replace("'", "")
query += "'" + attribute + "'" + ","
else:
attribute = 'Empty'
query += "'" + attribute + "'" + ","
query = query[:-1] + ')'
SQL.sqlexecute(conn, query, on_conflict=False)
def newInsertion_sessions(conn, coll1, coll2, data_input, attributes,
attributes2, fk):
ignored_records = 0
for datapoint in data_input:
query = "INSERT INTO {} VALUES(".format(coll1)
for element in attributes:
if element in datapoint:
attribute = datapoint[element]
else:
attribute = 'Empty'
if element == fk:
data_fk = attribute
query = Modify.change_query(query, attribute)
query = query[:-1] + ')'
SQL.sqlexecute(conn, query, on_conflict=False)
ignored_records += insertion_order_products(conn, coll2, datapoint,
attributes2, fk, data_fk)
print('ignored order records:', ignored_records)
def newInsertion_visitors(conn, coll1, coll2, coll3, coll4, data_input,
attributes, attributes2, attributes3, attributes4,
fk):
count = 0
for datapoint in data_input:
query = "INSERT INTO {} VALUES(".format(coll1)
count += 1
for element in attributes:
if element in datapoint:
attribute = datapoint[element]
else:
attribute = 'Empty'
if element == fk:
data_fk = attribute
query = Modify.change_query(query, attribute)
query = query[:-1] + ')'
SQL.sqlexecute(conn, query, on_conflict=False)
insertion_visitors_buids(conn, coll2, datapoint, attributes2, fk,
data_fk)
insertion_recommendation(conn, coll3, datapoint, attributes3,
attributes4, fk, data_fk)
def create_table(conn, coll, columns_dict, primary_key, foreign_key):
qry_columns = ''
for column in columns_dict:
qry_columns += column + ' '
qry_columns += columns_dict[column] + ' '
qry_columns += ','
if primary_key:
qry_columns += 'PRIMARY KEY({})'.format(primary_key)
qry_columns += ','
if foreign_key:
for key in foreign_key:
value = foreign_key[key]
qry_columns += 'FOREIGN KEY({}) REFERENCES {}'.format(key, value)
qry_columns += ','
qry_columns = qry_columns[:-1]
SQL.sqlexecute(conn,
"CREATE TABLE {0}({1})".format(coll, qry_columns),
on_conflict=False)
# cursor.execute("CREATE TABLE products({})".format(columns))
print("Table {} has been created".format(coll))
succes = True
return succes
from insert import Insertion, MongoDB, SQL, Manage_Tables
limiet = 10000 #gaat erg lang duren vanaf limiet=1000. Op het moment zijn er 10.000 producten/sessies en visitors in SQL geladen
safety = input('are you sure you want to restart the insertion? ')
if safety == 'ja' or safety == 'yes':
print('make connection with db')
else:
exit()
data_list_products = MongoDB.get_data('products', limiet)
data_list_sessions = MongoDB.get_data('sessions', limiet)
data_list_visitors = MongoDB.get_data('visitors', limiet)
conn = SQL.connect('postgres', 'toegang', 'Tom-1998', '127.0.0.1')
cursor = conn.cursor()
Manage_Tables.drop_table(conn, 'similar_product')
Manage_Tables.drop_table(conn, 'recommendation')
Manage_Tables.drop_table(conn, 'order_products')
Manage_Tables.drop_table(conn, 'stock')
Manage_Tables.drop_table(conn, 'products')
Manage_Tables.drop_table(conn, 'sessions')
Manage_Tables.drop_table(conn, 'visitors_buids')
Manage_Tables.drop_table(conn, 'visitors')
print('\n')
products_attributes = {
'_id': 'varchar(255)',
'brand': 'varchar(255)',
'category': 'varchar(255)',
def drop_table(conn, coll):
exist = check(conn, coll)
if exist:
query = "DROP TABLE {}".format(coll)
print('table {} is being dropped...'.format(coll))
SQL.sqlexecute(conn, query, on_conflict=False)