def _test_callproc_setup(self, connection):
self._test_callproc_cleanup(connection)
stmt_create1 = (
"CREATE PROCEDURE myconnpy_sp_1"
"(IN pFac1 INT, IN pFac2 INT, OUT pProd INT) "
"BEGIN SET pProd := pFac1 * pFac2; END;")
stmt_create2 = (
"CREATE PROCEDURE myconnpy_sp_2"
"(IN pFac1 INT, IN pFac2 INT, OUT pProd INT) "
"BEGIN SELECT 'abc'; SELECT 'def'; SET pProd := pFac1 * pFac2; END;"
)
stmt_create3 = (
"CREATE PROCEDURE myconnpy_sp_3"
"(IN pStr1 VARCHAR(20), IN pStr2 VARCHAR(20), "
"OUT pConCat VARCHAR(100)) "
"BEGIN SET pConCat := CONCAT(pStr1, pStr2); END;")
stmt_create4 = (
"CREATE PROCEDURE myconnpy_sp_4"
"(IN pStr1 VARCHAR(20), INOUT pStr2 VARCHAR(20), "
"OUT pConCat VARCHAR(100)) "
"BEGIN SET pConCat := CONCAT(pStr1, pStr2); END;")
try:
cursor = connection.cursor()
cursor.execute(stmt_create1)
cursor.execute(stmt_create2)
cursor.execute(stmt_create3)
cursor.execute(stmt_create4)
except errors.Error as err:
self.fail("Failed setting up test stored routine; {0}".format(err))
cursor.close()
def selectAllFromUser(email):
with connection.cursor() as cursor:
querystring = "select * from users where email = %s"
rows_count = cursor.execute(querystring, str(email))
if rows_count > 0:
rs = cursor.fetchall()
return rs
else:
return 0
def checkUserPassword(email):
with connection.cursor() as cursor:
querystring = "select password from users where email = %s"
rows_count = cursor.execute(querystring, str(email))
if rows_count > 0:
rs = cursor.fetchall()
return rs
else:
return 0
def _test_execute_cleanup(self, connection, tbl="myconnpy_cursor"):
stmt_drop = """DROP TABLE IF EXISTS %s""" % (tbl)
try:
cursor = connection.cursor()
cursor.execute(stmt_drop)
except (StandardError), e:
self.fail("Failed cleaning up test table; %s" % e)
def _test_execute_cleanup(self, connection, tbl="myconnpy_cursor"):
stmt_drop = """DROP TABLE IF EXISTS %s""" % (tbl)
try:
cursor = connection.cursor()
cursor.execute(stmt_drop)
except (StandardError), e:
self.fail("Failed cleaning up test table; %s" % e)
def getWrapped():
connection = mysql.connector.connect(host=MYSQL_HOST, user=MYSQL_USER, password=MYSQL_PASSWORD, database=DB_NAME)
pointer = connection.cursor()
query = "SELECT SUM(duration_ms * count)/1000/60 FROM {TABLE}".format(TABLE=TABLE_NAME)
pointer.execute(query)
wrapped = pointer.fetchone()
return int(wrapped[0])
def checkIfAllergyExists(id):
with connection.cursor() as cursor:
querystring = "select name from allergies where id = %s and validation=1"
rows_count = cursor.execute(querystring, str(id))
if rows_count > 0:
rs = cursor.fetchall()
return 1
else:
return 0
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 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 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 _test_callproc_cleanup(self, connection):
sp_names = ('myconnpy_sp_1', 'myconnpy_sp_2', 'myconnpy_sp_3')
stmt_drop = "DROP PROCEDURE IF EXISTS %s"
try:
cursor = connection.cursor()
for sp_name in sp_names:
cursor.execute(stmt_drop % sp_name)
except errors.Error, e:
self.fail("Failed cleaning up test stored routine; %s" % e)
def _test_callproc_cleanup(self, connection):
sp_names = ('myconnpy_sp_1', 'myconnpy_sp_2', 'myconnpy_sp_3')
stmt_drop = "DROP PROCEDURE IF EXISTS {procname}"
try:
cursor = connection.cursor()
for sp_name in sp_names:
cursor.execute(stmt_drop.format(procname=sp_name))
except errors.Error as err:
self.fail("Failed cleaning up test stored routine; {0}".format(err))
cursor.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 executemysqlquery(self, query):
"""
docstring
"""
connection = mysql.connector.connect(cfg.mysql["host"],
cfg.mysql["user"],
cfg.mysql["password"])
cursor = connection.cursor(dictionary=True)
cursor.execute(query)
results = cursor.fetchall()
cursor.close()
connection.close()
return results
def _test_callproc_cleanup(self, connection):
sp_names = ('myconnpy_sp_1', 'myconnpy_sp_2', 'myconnpy_sp_3')
stmt_drop = "DROP PROCEDURE IF EXISTS {procname}"
try:
cursor = connection.cursor()
for sp_name in sp_names:
cursor.execute(stmt_drop.format(procname=sp_name))
except errors.Error as err:
self.fail(
"Failed cleaning up test stored routine; {0}".format(err))
cursor.close()
def _test_execute_setup(self,connection,
tbl="myconnpy_cursor", engine="MyISAM"):
self._test_execute_cleanup(connection, tbl)
stmt_create = """CREATE TABLE %s
(col1 INT, col2 VARCHAR(30), PRIMARY KEY (col1))
ENGINE=%s""" % (tbl,engine)
try:
cursor = connection.cursor()
cursor.execute(stmt_create)
except (StandardError), e:
self.fail("Failed setting up test table; %s" % e)
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 check_availabel_slot(self):
connection = db_connection.connect()
cursor = connection.cursor()
try:
cursor.execute("select * from slot where booked_status='Available'")
data = cursor.fetchall()
if data == None or len(data) <= 0:
messagebox.showerror("Error", "No slots available right now. Please try after some time.")
return 0
else:
return data
except Exception as e:
connection.rollback()
print(e)
return 0
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 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 _test_callproc_setup(self, connection):
self._test_callproc_cleanup(connection)
stmt_create1 = """CREATE PROCEDURE myconnpy_sp_1
(IN pFac1 INT, IN pFac2 INT, OUT pProd INT)
BEGIN SET pProd := pFac1 * pFac2;
END;"""
stmt_create2 = """CREATE PROCEDURE myconnpy_sp_2
(IN pFac1 INT, IN pFac2 INT, OUT pProd INT)
BEGIN SELECT 'abc'; SELECT 'def'; SET pProd := pFac1 * pFac2;
END;"""
try:
cursor = connection.cursor()
cursor.execute(stmt_create1)
cursor.execute(stmt_create2)
except errors.Error, e:
self.fail("Failed setting up test stored routine; %s" % e)
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 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 authenticate(self):
self.__username = self.username_entry.get()
self.__password = self.password_entry.get()
if len(self.__username) <= 0:
messagebox.showerror("Invalid Credentials", "Please enter the username", parent=self.master)
self.username_entry.delete(0, END)
self.username_entry.focus()
return
if len(self.__password) <= 0:
messagebox.showerror("Invalid Credentials", "Please enter the password", parent=self.master)
self.password_entry.delete(0, END)
self.password_entry.focus()
return
connection = db_connection.connect()
cursor = connection.cursor()
try:
cursor.execute("SELECT * from users where username='******'".format(self.__username))
data = cursor.fetchone()
print(data)
if data == None or data[2] != self.__password:
messagebox.showerror("Invalid Credentials", "Your credentials are invalid. Please try again.")
self.username_entry.delete(0, END)
self.password_entry.delete(0, END)
self.username_entry.focus()
return
connection.close()
except Exception as e:
connection.rollback()
messagebox.showerror("Error", e, parent=self.master)
print(e)
return
print("Username", self.__username, "is logged in into the system..")
Remove.remove_all_widgets(self.loginFrame)
self.homePortal()
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")
coord_arr.append(float("{0}".format(row[2])))
coord_arr.append(float("{0}".format(row[3])))
coord_arr = decoupTab(coord_arr)
return coord_arr
try:
connection = mysql.connector.connect(host="localhost",
user="******",
password="******",
database="gari")
if connection.is_connected():
#db_Info = connection.get_server_info()
#print("Version de votre logiciel SQL Server :", db_Info)
cursor = connection.cursor()
cursor.execute("select database();")
record = cursor.fetchone()
print("Vous êtes connecté à la base de données :", record)
cursor.execute("SELECT * FROM trouver")
rows = cursor.fetchall()
"""fichier = open("test.csv","w") #récuperer les données de la bdd et les mettres dans un fichier .csv (si jamais on arrive pas a utiliser les donnees a partir d'une bdd alors on met les donnees de la bdd dans un fichier, c'est plus facile a manier enfin mon avis)
fichier.write("id;lat_dep;lng_dep;adresse_dep;lat_arr;lng_arr;adresse_arr;date_heur;id_user;etat\n")
for row in rows:
fichier.write(str(row[0])+";")
fichier.write(str(row[1])+";")
fichier.write(str(row[2])+";")
fichier.write(str(row[3])+";")
fichier.write(str(row[4])+";")
fichier.write(str(row[5])+";")
fichier.write(str(row[6])+";")
def selectMale():
with connection.cursor() as cursor:
querystring = "select * from users where sex=0000000000 "
cursor.execute(querystring)
result = cursor.fetchall()
return result
def deleteAllergy(id):
with connection.cursor() as cursor:
querystring = "delete from allergies WHERE id = %s"
cursor.execute(querystring, str(id))
connection.commit()
app["category"].append(categories.eq(index).attr("href"))
else:
app["category"].append(categories.attr("href"))
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()
def homePortal(self):
self.vehicle_records = list()
connection = db_connection.connect()
cursor = connection.cursor()
try:
cursor.execute("select * from vehicle_details where DATE(time_stamp)='{}'".format(date.today()))
data = cursor.fetchall()
if len(data) > 0:
for i in data:
temp = []
temp.append(i[0])
temp.append(i[1])
temp.append(i[2])
todays_date = str(i[6]).split(' ')[0]
temp.append(todays_date)
temp.append(i[5])
self.vehicle_records.append(temp)
except Exception as e:
connection.rollback()
messagebox.showerror("Error", e, parent=self.master)
print(e)
connection.close()
heading = Label(self.master, text="Vehicle Parking Management System", font="verdana 22 bold", bg=self.color)
heading.grid(row=0, column=0, padx=(250,0), pady=20)
self.navFrame = LabelFrame(self.master, text="")
self.navFrame.grid(row=1, column=0, padx=(250,0), pady=50)
self.vehicle_entry_portal_btn = Button(self.navFrame, text="Vehicle Entry", font=self.font , bg="#ff55ff", fg="#fff", command=lambda: self.vehicle_entry())
self.vehicle_entry_portal_btn.grid(row=0, column=0, padx=20, pady=20)
self.veiw_slot_portal_btn = Button(self.navFrame, text="View Available Slots", font=self.font , bg="#ff55ff", fg="#fff", command=lambda: self.view_available_slots())
self.veiw_slot_portal_btn.grid(row=0, column=1, padx=20, pady=20)
self.free_slot_btn = Button(self.navFrame, text="Free Slot", font=self.font , bg="#ff55ff", fg="#fff", command=lambda: self.free_slot())
self.free_slot_btn.grid(row=0, column=2, padx=20, pady=20)
self.logout_btn = Button(self.navFrame, text="Logout", bg="#ff55ff", font=self.font , fg="#fff", command= lambda: self.logout())
self.logout_btn.grid(row=0, column=3, padx=20, pady=20)
self.refresh_btn = Button(self.navFrame, text="Refresh", bg="#ff55ff", font=self.font , fg="#fff", command= lambda: self.homePortal())
self.refresh_btn.grid(row=0, column=3, padx=20, pady=20)
self.tableFrame = LabelFrame(self.master, text="", bg="#5a9bad")
self.tableFrame.grid(row=2, column=0, padx=(200, 0), sticky="nsew")
self.heading = Label(self.tableFrame, text="Today's Records", bg="#5a9bad", fg="#fff", font="Verdana 14 bold")
self.heading.grid(row=0, column=0, pady=10, columnspan=2)
self.tv = ttk.Treeview(self.tableFrame, style="mystyle.Treeview", columns=(1, 2, 3, 4, 5), show="headings",height=25, selectmode="none")
self.tv.grid(row=1, column=0)
# changing font size of heading and body of tree view
self.style = ttk.Style()
self.style.configure('Treeview.Heading', font=("verdana bold", 12))
self.style.configure("mystyle.Treeview", highlightthickness=1, bd=1, font=('verdana', 11)) # Modify the font of the body
self.style.layout("mystyle.Treeview", [('mystyle.Treeview.treearea', {'sticky': 'nswe'})]) # Remove the borders
self.verscrlbar = ttk.Scrollbar(self.tableFrame, orient="vertical", command=self.tv.yview)
self.verscrlbar.grid(row=1, column=1, rowspan=30, sticky='ns')
self.tv.configure(yscrollcommand=self.verscrlbar.set)
self.tv.column(1, width=100, anchor='c')
self.tv.column(2, width=400)
self.tv.column(3, width=200, anchor='c')
self.tv.column(4, width=250, anchor='c')
self.tv.column(5, width=150, anchor='c')
self.tv.heading(1, text="ID")
self.tv.heading(2, text="Owner's Name")
self.tv.heading(3, text="Car Number")
self.tv.heading(4, text="Date")
self.tv.heading(5, text="Booked Slot")
for i in self.vehicle_records:
self.tv.insert("", 'end', values= i)
def nrOfAllergies():
with connection.cursor() as cursor:
querystring = "select count(id) from allergies where validation=1"
cursor.execute(querystring)
result = cursor.fetchone()
return result[0]
def selectAllergyName(id):
with connection.cursor() as cursor:
querystring = "select name from allergies where id =%s "
cursor.execute(querystring, id)
result = cursor.fetchone()
return result[0]
def selectComments(allergy_name):
with connection.cursor() as cursor:
querystring = "select username, comment from comments where allergy_name=%s "
cursor.execute(querystring, allergy_name)
result = cursor.fetchall()
return result
