def drop_table_bookmarks(self):
"""
drop the table
:return:
"""
with database_connect() as cursor:
sql = 'drop table if exists %s ' % self.table_name
cursor.execute(sql)
def drop_table_products(self):
"""
drop the table
:return:
"""
with database_connect() as cursor:
sql = "drop table if exists %s " % self.table_name
cursor.execute(sql)
def fill_temptable_products(self):
"""
fill the temp table
get the categories from the T_Category table.
For each category -->
Construct the url
request the url
get the result in json and parse it
insert datas in the temp table
:return:
"""
dbconn = database_connect()
with dbconn as cursor:
# get the categories
sql = "select idCategory,categoryName from T_Categories"
cursor.execute(sql)
categories = cursor.fetchall()
# for each category
for category in categories:
print("Récupère données : {} - {}".format(category[0], category[1]))
# prepare the sql
sql1 = "insert into %s (productName,url,nutriscore_score,nutriscore_grade,Store,idCategory)" \
" values" % self.temp_table_name
# 3 pages (page 2-4-6) of 20 products
for i in range(2, 7, 2):
# build the url
url = self.url1 + category[1] + self.url2 + str(i)
# get the response - parse the json file and then insert the product
response = requests.get(url)
if (response.status_code) == 200:
# text = json.loads(response.content.decode('latin1'))
text = response.json()
for t in text['products']:
if 'product_name' in t and len(t['product_name']) > 0:
t['product_name'] = t['product_name'][:79]
t['product_name'] = t['product_name'].replace("'", " ")
if 'url' not in t:
t['url'] = ''
if 'nutriscore_score' not in t:
t['nutriscore_score'] = "100"
if 'nutriscore_grade' not in t:
t['nutriscore_grade'] = "Z"
if 'brands' not in t:
t['brands'] = "Inconnu"
t['brands'] = t['brands'][:49]
if len(t['brands']) == 0:
t['brands'] = "Inconnu"
t['brands'] = t['brands'].replace("'", " ")
sql2 = "('%s','%s',%s,'%s','%s',%s)" % (t['product_name'],
t['url'],
t['nutriscore_score'],
t['nutriscore_grade'],
t['brands'],
category[0])
sql = sql1 + sql2
cursor.execute((sql))
dbconn.commit()
def get_all_bookmarks(self):
"""
get all the bookmarks
:return: the bookmarks list [(idproduct,idproduct1],...]
"""
sql = "select idproduct,idproduct1 from %s order by 1" % self.table_name
with database_connect() as cursor:
cursor.execute(sql)
rows = cursor.fetchall()
return rows
def fill_table_stores(self):
"""
get distinct Store in temp_products table and insert in Store table
:return:
"""
sql = 'insert into T_Stores (storeName) SELECT distinct(Store) FROM T_TempProducts order by 1;'
dbconn = database_connect()
with dbconn as cursor:
cursor.execute(sql)
dbconn.commit()
def list_categories(self):
"""
get all the categories
:return: row(s) - categories list [(idcategory, categoryName),...]
"""
sql = 'select idCategory, categoryName from ' + self.table_name + " order by 1"
with database_connect() as cursor:
cursor.execute(sql)
rows = cursor.fetchall()
return rows
def fill_table_products(self):
"""
fill the table T_Product from the temp table
:param self:
:return:
"""
sql = "insert into T_Products (productName,url,nutriscore_score,nutriscore_grade,idCategory) select " \
"productName,url, nutriscore_score,nutriscore_grade,idCategory from T_TempProducts group by productName;"
dbconn = database_connect()
with dbconn as cursor:
cursor.execute(sql)
dbconn.commit()
def get_bookmark_byproduct(self, idproduct):
"""
Get bookmark(s) - substitute(s) - by product id
:param idproduct:
:return: row(s) [(idproduct,idproduct1],...]
"""
sql = "select idproduct,idproduct1 from %s where idproduct=" % self.table_name
sql = sql + """%s"""
with database_connect() as cursor:
cursor.execute(sql, (idproduct, ))
rows = cursor.fetchall()
return rows
def get_category_byid(self, id):
"""
get a category by id (1 row)
:param id:
:return: 1 row [(idcategory, categoryName)]
"""
sql = 'select idCategory, categoryName,dateCreation from %s where idCategory=' % self.table_name
sql = sql + """%s"""
with database_connect() as cursor:
cursor.execute(sql, (id, ))
row = cursor.fetchone()
return row
def get_store_byproduct(self,idproduct):
"""
get storeName by idproduct
:param idproduct:
:return: row(s) Stores [(storename),...]
"""
sql = """Select storeName from T_Products_stores as T inner join T_Products as P \
on P.idProduct=T.idProduct inner join T_Stores as S on T.idStore = S.idStore \
where P.idProduct = %s"""
with database_connect() as cursor:
cursor.execute(sql,(idproduct,))
rows = cursor.fetchall()
return rows
def create_table_stores(self):
"""
Drop the table if exists and create it
:return:
"""
with database_connect() as cursor:
sql = "drop table if exists %s " % self.tableName
param = ""
cursor.execute(sql, param)
sql = "CREATE TABLE %s (idStore INT UNSIGNED NOT NULL AUTO_INCREMENT, " \
"storeName VARCHAR(80) NULL," \
"dateCreation TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP," \
"PRIMARY KEY (idStore)) ENGINE=InnoDB CHARSET latin1" % self.tableName
cursor.execute(sql, param)
def get_product_byid(self, idproduct):
"""
Get a product by id
:param idproduct:
:return: row of 1 product [(idProduct, productName,url, nutriscore_score,
nutriscore_grade,idCategory,dateCreation)]
"""
sql = "select idProduct, productName,url,nutriscore_score,nutriscore_grade,idCategory, " \
"dateCreation from %s where idProduct=" % self.table_name
sql = sql + """%s"""
with database_connect() as cursor:
cursor.execute(sql, (idproduct,))
row = cursor.fetchone()
return row
def list_products_bycat(self, idcategory):
"""
get all products for an idcategory
:param idcategory:
:return: row(s) of product [(idProduct, productName,url, nutriscore_score,
nutriscore_grade,idCategory,dateCreation),...]
"""
sql = "select idProduct, productName,url, nutriscore_score, nutriscore_grade,idCategory, " \
"dateCreation from %s where idCategory=" % self.table_name
sql = sql + """%s order by idProduct"""
with database_connect() as cursor:
cursor.execute(sql, (idcategory,))
rows = cursor.fetchall()
return rows
def test_database():
"""
Test database
- Connexion
- the tables' existence
- The age of the datas --> if > 30 days a message tell the user the datas are too old --> he
should recreate the databse and get newer datas
:return:
"""
# test the connection
connex = database_connect()
test = connex.connect_db()
if not test:
print("Erreur de connexion")
return False
print("Test connexion : OK")
# test if the tables exist
sql1 = "show tables like '"
connex.disconnect_db()
with connex as cursor:
print("Test tables")
for key, value in TABLES.items():
# don't test temp tables
if (key[:6]) == 'T_TEMP':
continue
print("Testing %s as %s" % (key, value))
sql = sql1 + value + "'"
cursor.execute(sql)
result = cursor.fetchone()
if result:
print("Test OK")
else:
print("ERREUR tests existence table")
# test the date of the datas
"""
ToDo mettre > au lieu de < --> tests et demo
"""
sql = 'SELECT idCategory from T_Categories where TIMESTAMPDIFF(DAY,now(),dateCreation)<30 limit 1'
try:
cursor.execute(sql)
result = cursor.fetchone()
if result:
print(
"ATTENTION : Les données ont plus de 30 jours. Il est vivement conseillé de récupérer les nouvelles "
"données. Pour cela réexecuter le programme avec le parametre '-d create'"
)
else:
print("La date des données est inférieure à 7 jours.")
except:
pass
def fill_table_category(self):
"""
Loop on CAT_LIST and insert items into table
:return: None
"""
sql1 = 'insert into %s (categoryName) values ' % self.table_name
dbconn = database_connect()
with dbconn as cursor:
for cat in CAT_LIST:
sql = sql1 + """(%s)"""
print('Insertion : ', cat)
cursor.execute(sql, (cat, ))
dbconn.commit()
dbconn.disconnect_db()
def create_table_category(self):
"""
Drop the table if exists and create it
:return:
"""
dbconn = database_connect()
with dbconn as cursor:
sql = "drop table if exists %s " % self.table_name
param = ""
cursor.execute(sql, param)
sql = 'CREATE TABLE %s (idCategory INT UNSIGNED NOT NULL AUTO_INCREMENT,' \
'categoryName VARCHAR(80) NULL,' \
' dateCreation TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,' \
'PRIMARY KEY (idCategory)) ENGINE=InnoDB CHARSET latin1' % self.table_name
cursor.execute(sql, param)
dbconn.commit()
def get_product_bookmarks(self, idproduct, ):
"""
Get the products which have been bookmarked for a product - get the substitutes for
a product
the url is cut as 53 char to fit in the chart but it keeps good (you can click on it)
:param idproduct:
:return: row(s) of products [(idProduct, productName,substr(url,1,53),nutriscore_score,
nutriscore_grade,idCategory)...]
"""
sql = "select idProduct, productName,substr(url,1,53),nutriscore_score,nutriscore_grade,idCategory " \
"from %s where idcategory = (select idcategory from T_Products where " \
"idProduct = %d) and nutriscore_score <= (select nutriscore_score from T_Products " \
"where idProduct = %d order by nutriscore_score desc)" % (self.table_name, idproduct, idproduct)
with database_connect() as cursor:
cursor.execute(sql)
rows = cursor.fetchall()
return rows
def create_temptable_products(self):
"""
Drop the temp table if exists and create it
:return:
"""
with database_connect() as cursor:
sql = "drop table if exists %s " % self.temp_table_name
cursor.execute(sql)
sql = "CREATE TABLE %s (idProduct INT UNSIGNED NOT NULL AUTO_INCREMENT, " \
"productName VARCHAR(80) NOT NULL," \
"url TEXT NULL, " \
"nutriscore_score SMALLINT DEFAULT 100," \
"nutriscore_grade CHAR(1) DEFAULT 'z', " \
"Store VARCHAR(100), " \
"idCategory INT UNSIGNED," \
"PRIMARY KEY (idProduct)) ENGINE=InnoDB CHARACTER SET latin1" % self.temp_table_name
# "CONSTRAINT fk_tempcategory FOREIGN KEY (idCategory) REFERENCES T_Categories(idCategory) ," \
cursor.execute(sql)
def add_bookmark(self, idproduct, idproduct1):
"""
add a bookmark then commit work
catch an error and rollback work if necessary
:param idproduct:
:param idproduct1:
:return: 0, 'OK' if OK or 1 and error message if sql insert error
"""
sql = f"insert into {self.table_name} (idproduct,idproduct1) values "
sql += """(%s,%s)"""
cnx = database_connect()
with cnx as cursor:
try:
cursor.execute(sql, (idproduct, idproduct1))
cnx.commit()
return 0, "OK"
except Error as e:
cnx.rollback()
return 1, e
def create_table_bookmarks(self):
"""
drop if exist then create the table
:return:
"""
with database_connect() as cursor:
sql = "drop table if exists %s " % self.table_name
cursor.execute(sql)
sql = "CREATE TABLE IF NOT EXISTS `T_Bookmarks` (" \
"`idproduct` INT(10) UNSIGNED NOT NULL," \
"`idproduct1` INT(10) UNSIGNED NOT NULL," \
"CONSTRAINT `I_Bookmarks` PRIMARY KEY (`idproduct`, `idproduct1`)," \
"CONSTRAINT `fk_T_Products_idproduct`" \
"FOREIGN KEY (`idproduct`) " \
"REFERENCES `T_Products` (`idproduct`) " \
"ON DELETE NO ACTION ON UPDATE NO ACTION, " \
"CONSTRAINT `fk_T_Products_Idproduct1` " \
"FOREIGN KEY (`idproduct1`) " \
"REFERENCES `T_Products` (`idproduct`) " \
"ON DELETE NO ACTION " \
"ON UPDATE NO ACTION) ENGINE = InnoDB DEFAULT CHARACTER SET = latin1"
cursor.execute(sql)
def choix(num_ordr):
dbconn = None
try:
dbconn = database_connect()
with dbconn as cursor:
if cursor != 0:
ordr_sql = sql_orders[num_ordr]
cursor.execute(ordr_sql)
if sql_orders[num_ordr][:6].lower() == 'select':
rows = cursor.fetchall()
cols = []
[cols.append(desc[0]) for desc in cursor.description]
print("")
print("Ordre SQL : %s" % ordr_sql)
print("")
affiche_result(cols, rows)
else:
dbconn.commit()
except MyError as error:
print("Error SQL : %s" % error)
finally:
if dbconn is not None:
dbconn.disconnect_db()
input("Appuyez sur entree pour continuer : ")