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 : ")