def insertComment(parametri): with connection.cursor() as cursor: querystring = "insert into comments(username, allergy_name, comment) VALUES(%s,%s,%s)" cursor.execute(querystring, (parametri["username"], parametri["allergy_name"], parametri["comment"])) connection.commit() with connection.cursor() as cursor: querystring = "select id_user from user_allergy where id_allergy = (select id from allergies where name=%s)" cursor.execute(querystring, parametri["allergy_name"]) result = cursor.fetchall() querystring = "select id from users where username=%s" cursor.execute(querystring, parametri["username"]) user = cursor.fetchone()[0] mesaj = "New comment added on " + \ str(parametri["allergy_name"]) + " allergy" ids = list(sum(result, ())) for id_user in ids: if user != id_user: with connection.cursor() as cursor: querystring = "insert into notifications(id_user, mesaj, tip) VALUES(%s,%s,%s)" cursor.execute(querystring, (str(id_user), mesaj, 2)) connection.commit()
def priority_update(): currentRoutes = "SELECT route FROM wcs.verify_trailers" cursor.execute(currentRoutes) results = cursor.fetchall() currentRoutes = [] for idx, r in enumerate(results): currentRoutes.append(results[idx][0]) for route in currentRoutes: date = "SELECT date FROM wcs.verify_trailers WHERE route=" + \ "'"+str(route)+"'" cursor.execute(date) result = cursor.fetchone() date = result[0] priority = "SELECT priority FROM wcs.route_statuses WHERE route=" + \ "'"+str(route)+"' AND date="+"'"+str(date)+"'" cursor.execute(priority) result = cursor.fetchone() priority = result[0] trailer = "SELECT trailer_number FROM wcs.route_statuses WHERE route=" + \ "'"+str(route)+"' AND date="+"'"+str(date)+"'" cursor.execute(priority) result = cursor.fetchone() trailer = result[0] cursor.execute("UPDATE wcs.verify_trailers SET priority="+"'"+str(priority) + "',trailer_number="+"'"+str(trailer)+"' WHERE route="+"'"+str(route)+"'") connection.commit() return "priority/trailer update - complete"
def update_slot_to_free(self): connection = db_connection.connect() cursor = connection.cursor() try: cursor.execute("select slot from vehicle_details where vehicle_number='{}'".format(self.vehical_entry.get())) slot = cursor.fetchone() if slot == None or slot[0] != int(self.slot_entry.get()): messagebox.showerror("Error", "Sorry, you have not booked this slot.", parent=self.toplevel_free_slot) return cursor.execute("update slot SET booked_status='Available' where slot_number={}".format(self.slot_entry.get())) connection.commit() self.vehical_entry.delete(0, END) self.slot_entry.delete(0, END) messagebox.showinfo("Successful", "Slot is free now..", parent=self.toplevel_free_slot) except Exception as e: connection.rollback() messagebox.showerror("Error", e, parent=self.toplevel_free_slot) print(e) connection.close()
def insertAllergy(id, name, category, description, symptoms, prevention, treatment, medication, years, people, age, percent): with connection.cursor() as cursor: querystring = "insert into allergies VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" cursor.execute(querystring, (id, name, category, description, symptoms, prevention, treatment, medication, years, people, age, percent)) connection.commit()
def insertFeedback(parametri): with connection.cursor() as cursor: querystring = "insert into feedback(id_user, name, email, rating, message) VALUES(%s,%s,%s,%s,%s)" cursor.execute( querystring, (parametri["id_user"], parametri["name"], parametri["email"], parametri["rating"], parametri["message"])) connection.commit()
def insertVariblesIntoTable(name, number, proced, insurance): x = datetime.datetime.now() mySql_insert_query = """INSERT INTO myoffice (name,number,proced,insurance,rdate) VALUES (%s, %s, %s, %s, %s) """ recordTuple = (name, number, proced, insurance, x) cursor.execute(mySql_insert_query, recordTuple) connection.commit() # print("Record inserted successfully into Laptop table") return print("Thank you")
def getNotificari(userID): with connection.cursor() as cursor: querystring = "SELECT * from notifications where id_user=%s;" cursor.execute(querystring, (userID)) rezultat = cursor.fetchall() querystring = "DELETE FROM notifications where id_user=%s;" cursor.execute(querystring, (userID)) connection.commit() return rezultat
def tryregister(username, password, email): mycursor = connection.cursor() # mycursor.execute("SELECT * FROM frontdesk", username, password,email) # rs = mycursor.fetchall() mySql_insert_query = """INSERT INTO frontdesk (username,password,email) VALUES (%s, %s, %s) """ recordTuple = (username, password, email) cursor.execute(mySql_insert_query, recordTuple) connection.commit() return print("done")
def saveAllergies(userID, allergies): with connection.cursor() as cursor: querystring = "DELETE FROM user_allergy where id_user=%s" cursor.execute(querystring, (userID)) for i in allergies: print(i) querystring = "INSERT INTO user_allergy(id_user, id_allergy) VALUES(%s, %s);" cursor.execute(querystring, (userID, i)) connection.commit()
def savePesticides(list): connection = Pesticide.connection cursor = Pesticide.cursor sql = "INSERT INTO tarimsal(isim, fiyat, hastalik, dozaj, kullanim, tip) VALUES (%s, %s, %s, %s, %s, %s)" values = list cursor.executemany(sql, values) try: connection.commit() except mysql.connector.Error as err: pass finally: connection.close()
def insertSuggestion(name, category, description, symptoms, prevention, treatment, medication, id_user): with connection.cursor() as cursor: querystring = "select max(id) from allergies" cursor.execute(querystring) id = int(cursor.fetchone()[0]) + 1 id = str(id) querystring = "insert into allergies (id, name, category, description, symptoms, prevention, " \ "treatment, medication,user_id, validation) VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" cursor.execute(querystring, (id, name, category, description, symptoms, prevention, treatment, medication, id_user, 0)) connection.commit()
def add_spring_notification(): with connection.cursor() as cursor: querystring = "select id from users where id in (select id_user from user_allergy " \ "where id_allergy = ( select id_allergy from allergies where name = \"Pollen\")) " cursor.execute(querystring) result = cursor.fetchall() result = list(sum(result, ())) mesaj = "Atentie! Primavara este sezonul alergiilor la polen! " for id in result: with connection.cursor() as cursor: querystring = "insert into notifications(id_user, mesaj, tip) values(%s,%s,%s)" cursor.execute(querystring, (str(id), mesaj, 2)) connection.commit()
def add_summer_notification(): with connection.cursor() as cursor: querystring = "select id from users where id in (select id_user from user_allergy " \ "where id_allergy = ( select id_allergy from allergies where name = \"Sun\")) " cursor.execute(querystring) result = cursor.fetchall() result = list(sum(result, ())) mesaj = "Atentie! Vine vara, stati la umbra! " for id in result: with connection.cursor() as cursor: querystring = "insert into notifications(id_user, mesaj, tip) values(%s,%s,%s)" cursor.execute(querystring, (str(id), mesaj, 2)) connection.commit()
def updatePesticidePrice(isim, fiyat): connection = Pesticide.connection cursor = Pesticide.cursor isim = isim fiyat = fiyat sql = "UPDATE tarimsal SET fiyat= %s WHERE isim = %s" values = (fiyat, isim) cursor.execute(sql, values) try: connection.commit() except mysql.connector.Error as err: pass finally: connection.close()
def validate(id): with connection.cursor() as cursor: querystring = "UPDATE allergies SET validation=1 WHERE id= %s" cursor.execute(querystring, str(id)) connection.commit() with connection.cursor() as cursor: querystring = "SELECT name, category from allergies where id=%s" cursor.execute(querystring, id) rezultat = cursor.fetchall()[0] print(rezultat) usersToNotify = selectUsersByCategoryAllergy(id) for i in usersToNotify: userID = i[0] notify( userID, "New allergy added! \nCategory: " + rezultat[1] + "\nName: " + rezultat[0], 1)
def deletePesticide(isim): connection = Pesticide.connection cursor = connection.cursor() isim = isim value = str(isim) sql = """delete from tarimsal where isim = %s""" cursor.execute(sql, (value,)) try: connection.commit() except mysql.connector.Error as err: pass finally: connection.close()
def freezer_cooler_picks(): routes = "SELECT route FROM wcs.verify_trailers WHERE status<>'Shipped'" cursor.execute(routes) results = cursor.fetchall() routes = [] for idx, r in enumerate(results): routes.append(results[idx][0]) # print(routes) date = "SELECT date FROM wcs.verify_trailers WHERE route=" + \ "'"+str(routes[0])+"'" cursor.execute(date) result = cursor.fetchone() date = result[0] # print(date) for r in routes: pickGroup = ["Freezer", "Cooler"] for p in pickGroup: count = "SELECT COUNT(*) FROM assignment.dat_master WHERE route_no="+"'"+str( r)+"' AND date="+"'"+str(date)+"' AND pick_group="+"'"+str(p)+"'" cursor.execute(count) result = cursor.fetchone() count = int(result[0]) # print(count) if count > 0: count_scanned = "SELECT COUNT(*) FROM assignment.dat_master WHERE route_no="+"'"+str( r)+"' AND date="+"'"+str(date)+"' AND pick_group="+"'"+str(p)+"' AND stop_scan=1" cursor.execute(count_scanned) result = cursor.fetchone() count_scanned = int(result[0]) # print(count_scanned) if count_scanned > 0: cursor.execute("UPDATE wcs.verify_trailers SET " + str(p)+"=1 WHERE route="+"'"+str(r)+"'") cursor.execute("UPDATE assignment.dat_master SET dashboard_map=1, stop_scan=1 WHERE route_no=" + "'"+str(r)+"' AND date="+"'"+str(date)+"' AND pick_group="+"'"+str(p)+"'") connection.commit() else: pass else: pass return "freezer/cooler picks processed"
def updatePesticideDisease(isim, hastalik): connection = Pesticide.connection cursor = Pesticide.cursor isim = isim hastalik = hastalik sql = "UPDATE tarimsal SET hastalik= %s WHERE isim = %s" values = (hastalik, isim) cursor.execute(sql, values) try: connection.commit() except mysql.connector.Error as err: pass finally: connection.close()
def remove_table(): try: connection = create_server_connection() cursor = connection.cursor() mySql_insert_query = """DELETE FROM perfilcores""" cursor.execute(mySql_insert_query) connection.commit() print("Record removed successfully perfilcores table") except mysql.connector.Error as error: print("Failed to remove MySQL table {}".format(error)) finally: if connection.is_connected(): cursor.close() connection.close() print("MySQL connection is closed")
def add_sesonal_notifications(seson): with connection.cursor() as cursor: querystring = "delete from notifications where tip=3" cursor.execute(querystring) querystring = "select distinct id_user from user_allergy where id_allergy = %s " cursor.execute(querystring, seson["alergieID"]) result = cursor.fetchall() result = list(sum(result, ())) for id in result: querystring = "insert into notifications(id_user, mesaj, tip) values(%s, %s, %s)" cursor.execute(querystring, (id, seson["mesaj"], 3)) connection.commit()
def save_form_data(self): connection = db_connection.connect() cursor = connection.cursor() try: cursor.execute("insert into vehicle_details(full_name, vehicle_number, phone_number, gender, slot) values ('{}', '{}', {}, '{}', '{}')".format((self.fnameEntry.get()+" "+ self.lnameEntry.get()), self.velicle_num_Entry.get(), int(self.telnumEntry.get()), self.gender.get(), self.availabel_slot)) connection.commit() cursor.execute("update slot SET booked_status='Booked' where slot_number={}".format(self.availabel_slot)) connection.commit() messagebox.showinfo("Successful", "Slot Booked successfully..", parent=self.toplevel) except Exception as e: connection.rollback() messagebox.showerror("Error", e, parent=self.toplevel) print(e) connection.close() self.resetForm()
def notify(userID, mesaj, type): with connection.cursor() as cursor: querystring = "INSERT into notifications(id_user,mesaj,tip) VALUES(%s, %s, %s);" cursor.execute(querystring, (userID, mesaj, type)) connection.commit() with connection.cursor() as cursor: querystring = "select email from users where id=%s " cursor.execute(querystring, userID) email = cursor.fetchone() server = smtplib.SMTP('smtp.gmail.com', 25) server.connect("smtp.gmail.com", 587) server.ehlo() server.starttls() server.ehlo() server.login("seallergy.notifications", "web2019!") text = mesaj server.sendmail("*****@*****.**", email, text) server.quit()
def insert_varibles_into_table(codigo_item, seq_cor, desc_cor, anilox, densidade, tv, lab): try: connection = create_server_connection() cursor = connection.cursor() mySql_insert_query = """INSERT INTO perfilcores (codigo_item, seq_cor, desc_cor, anilox, densidade, tv, lab) VALUES (%s, %s, %s, %s, %s, %s, %s) """ record = (codigo_item, seq_cor, desc_cor, anilox, densidade, tv, lab) print(record) cursor.execute(mySql_insert_query, record) connection.commit() print("Record inserted successfully into perfilcores table") except mysql.connector.Error as error: print("Failed to insert into MySQL table {}".format(error)) finally: if connection.is_connected(): cursor.close() connection.close() print("MySQL connection is closed")
def insert_varibles_into_table(recurso, etapa, seq_fila, op, cod_item, desc_item, cod_clicheria, iniprog, fimprog, mrp): try: connection = create_server_connection() cursor = connection.cursor() mySql_insert_query = """INSERT INTO pcpfila (recurso, etapa, seq_fila, op, codigo_item, descricao_item, cod_clicheria, inicioprog, fimprog, mrp) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s) """ record = (recurso, etapa, seq_fila, op, cod_item, desc_item, cod_clicheria, iniprog, fimprog, mrp) print(record) cursor.execute(mySql_insert_query, record) connection.commit() print("Record inserted successfully into fila table") except mysql.connector.Error as error: print("Failed to insert into MySQL table {}".format(error)) finally: if connection.is_connected(): cursor.close() connection.close() print("MySQL connection is closed")
app["category"] = json.dumps(app["category"]) del dom, page except urllib2.URLError, e: debug("Start open page for pkg[%s]: 404 not found it." % pkg) app["type"] = 1 return app debug("Start...") connection = connection.MySQLConnection(**dbconfig) cursor = connection.cursor() cursor.execute("SET NAMES utf8") cursor.execute("SELECT pkg FROM appinfo WHERE type = 0 LIMIT 50") appinfo = cursor.fetchall() debug("Find %d appinfos." % len(appinfo)) threads = [] for pkg in appinfo: thread = Thread(target=updateApp, args=(pkg[0],)) # thread.setDaemon(True) thread.start() threads.append(thread) for thread in threads: thread.join() connection.commit() cursor.close() connection.close()
def deleteUser(id): with connection.cursor() as cursor: querystring = "delete from users WHERE id = %s" cursor.execute(querystring, str(id)) connection.commit()
def deleteAllergy(id): with connection.cursor() as cursor: querystring = "delete from allergies WHERE id = %s" cursor.execute(querystring, str(id)) connection.commit()
def deleteSuggestion(id): with connection.cursor() as cursor: querystring = "delete from suggestions WHERE id_suggestion = %s" cursor.execute(querystring, str(id)) connection.commit()
def insertUserAllergy(id_user, id_allergy): with connection.cursor() as cursor: querystring = "insert into user_allergy VALUES(%s,%s)" cursor.execute(querystring, (id_user, id_allergy)) connection.commit()
def seletUserAllergies(id_user): with connection.cursor() as cursor: querystring = "delete from user_allergy WHERE id_user = %s" cursor.execute(querystring, str(id_user)) connection.commit()
def saveNotifications(userID, val1, val2, val3): with connection.cursor() as cursor: querystring = "UPDATE users SET notificare1=%s, notificare2=%s, notificare3=%s WHERE id=%s;" cursor.execute(querystring, (val1, val2, val3, userID)) connection.commit()