-
Notifications
You must be signed in to change notification settings - Fork 1
/
db_query.py
105 lines (78 loc) · 3.26 KB
/
db_query.py
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
import sqlite3
from application.src.fill_mapping import create_shopping_tour
def read_from_sqlite(db_path, query):
"""
Connects to an SQLite database and reads data using the specified query.
Parameters:
db_path (str): Path to the SQLite database file.
query (str): SQL query to execute.
Returns:
list of tuple: Retrieved rows from the database.
"""
# Connect to the SQLite database
connection = sqlite3.connect(db_path)
# Create a cursor object
cursor = connection.cursor()
# Execute the query
cursor.execute(query)
# Fetch all results from the executed query
results = cursor.fetchall()
# Close the cursor and connection
cursor.close()
connection.close()
return results
def del_mapping_entries(db_path):
conn = sqlite3.connect(db_path) # Replace with your database file or connection string
cursor = conn.cursor()
# Delete all entries from the Mapping table
cursor.execute("DELETE FROM Mapping")
# Commit the changes and close the connection
conn.commit()
conn.close()
def add_mapping_entries(db_path, supermarked_id):
conn = sqlite3.connect(db_path) # Replace with your database file or connection string
cursor = conn.cursor()
# Fetch all ProductIDs from ProductList
cursor.execute("SELECT ProductID FROM ProductList")
product_ids = [row[0] for row in cursor.fetchall()]
# Insert mappings into the Mapping table
for product_id_1 in product_ids:
for product_id_2 in product_ids:
cursor.execute("INSERT INTO Mapping (ProductID1, ProductID2, SupermarketID, Counter) VALUES (?, ?, ?, ?)",
(product_id_1, product_id_2, supermarked_id, 0))
# Commit the changes and close the connection
conn.commit()
conn.close()
def add_shopping_tour(db_path, product_sorting, supermarket_id):
conn = sqlite3.connect(db_path)
cursor = conn.cursor()
for i in range (len(product_sorting)):
for j in range(i+1, len(product_sorting)):
product_id1 = product_sorting[i]
product_id2 = product_sorting[j]
cursor.execute("""
UPDATE Mapping
SET counter = counter + 1
WHERE ProductID1 = ? AND ProductID2 = ? AND SupermarketID = ?
""", (product_id1, product_id2, supermarket_id))
conn.commit()
conn.close()
if __name__ == '__main__':
# Example usage
db_path = 'smartGrocery.db' #Path to your SQLite database file
query = 'SELECT * FROM ProductList;' # Replace with your SQL query
# Read data from the SQLite database
data = read_from_sqlite(db_path, query)
# Print the fetched data
for row in data:
print(row)
product_ids = [8001, 5001,2001,3001,3002,4001,4002,5002,6001,6002,7001,7002,7003, 9001]
del_mapping_entries(db_path)
add_mapping_entries(db_path, 1)
add_mapping_entries(db_path, 2)
for i in range(100):
tour = create_shopping_tour(order_products=[i for i in range(len(product_ids))], min_products=3,
max_products=len(product_ids), randomness=0.8)
prod_tour_sorting = [product_ids[index] for index in tour]
print(prod_tour_sorting)
add_shopping_tour(db_path, prod_tour_sorting, 1)