def add_user(user_id, user_name, sys_lang): # Подписаться на рассылку connection = myconnutils.getConnection() i = 0 base = [] try: cursor = connection.cursor() sql = """ SELECT Chat_id FROM user_data """ # Выполнить sql и передать 1 параметр. cursor.execute(sql) row = cursor.fetchall() while i < len(row): # перемещаем все в одномерный список base.append(row[i][0]) i += 1 if str( user_id ) not in base: # если пользователя еще нет в базе данных, то добавляем try: sql = """ INSERT INTO user_data VALUES(%s,%s,%s,%s,%s) """ cursor.execute(sql, (user_id, user_name, sys_lang, '50', '25')) finally: connection.commit() try: sql = """ INSERT INTO Location VALUES(%s,%s,%s,%s,%s) """ cursor.execute(sql, (user_id, "None", "None", '0', '0')) finally: connection.commit() finally: # Закрыть соединение connection.commit() connection.close()
def check_authen(hashCode): connection = myconnutils.getConnection() #print("HashCode????? --- > "+hashCode) try: cursor = connection.cursor() hashCode = hashCode[6:] sql = "select rest_hash from rest_permission where rest_hash = '" + hashCode + "';" #print(sql) cursor.execute(sql) output = cursor.fetchone() finally: #print("output-- >" + output['rest_hash']+"") if hashCode == output['rest_hash']: # Die Verbindung schliessen (Close connection). connection.close() return int(1) else: # Die Verbindung schliessen (Close connection). connection.close() return int(0)
def add_album_to_sql(album_name, user_id): db_connection = myconnutils.getConnection() cursor = db_connection.cursor() insert_into_album = ("INSERT INTO chv_albums " "(album_name, album_user_id, album_date, album_date_gmt, album_creation_ip) " "VALUES (%s, %s, %s, %s, %s)") select_album_count = ("SELECT user_album_count FROM chv_users WHERE user_id = " + str(user_id)) cursor.execute(select_album_count) album_count = cursor.fetchone()[0] print("Album_count = ", album_count) update_chv_user = ("UPDATE chv_users SET user_album_count = " + str(album_count + 1) + " WHERE user_id = " + str(user_id)) print(update_chv_user) album_user_id = user_id album_date = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S") album_date_gmt = datetime.datetime.utcnow().strftime("%Y-%m-%d %H:%M:%S") album_creation_ip = socket.gethostbyname(socket.gethostname()) #album_image_count = 0 data_album = (album_name, album_user_id, album_date, album_date_gmt, album_creation_ip) # Insert into chv_albums cursor.execute(insert_into_album, data_album) cursor.execute(update_chv_user) # Make sure data is committed to the database db_connection.commit()
def lista_monitores_da_equipa(nome): with app.app_context(): g.db = getConnection() with g.db.cursor() as cursor: cursor.execute(sql_user_equipa_equipa, nome) list_dicts_mons_da_equipa = cursor.fetchall() return list_dicts_mons_da_equipa
def lista_tarefas(): with app.app_context(): g.db = getConnection() with g.db.cursor() as cursor: cursor.execute(sql_nome_tarefa) list_dicts_tarefas = cursor.fetchall() return list_dicts_tarefas
def lista_tarefas_com_coluna_categoria(): with app.app_context(): g.db = getConnection() with g.db.cursor() as cursor: cursor.execute(sql_tarefa_cat_categoria_tarefa) list_dicts_tarefas = cursor.fetchall() return list_dicts_tarefas
def lista_tarefas_escs_precos(escalao_preco): with app.app_context(): g.db = getConnection() with g.db.cursor() as cursor: cursor.execute(sql_tarefa_esc_preco, (escalao_preco)) list_dicts_tarefas = cursor.fetchall() return list_dicts_tarefas
def lista_escaloes(): with app.app_context(): g.db = getConnection() with g.db.cursor() as cursor: cursor.execute(sql_nome_nome_escalao_preco) escaloes_list_dicts = cursor.fetchall() return escaloes_list_dicts
def lista_equipas(): with app.app_context(): g.db = getConnection() with g.db.cursor() as cursor: cursor.execute(sql_equipa) list_dicts_equipas = cursor.fetchall() for equipa in list_dicts_equipas: equipa['link'] = url_for('equipa.equipa', nome=equipa['nome']) return list_dicts_equipas
def get_album_id(album_name): db_conn = myconnutils.getConnection() cursor = db_conn.cursor() select_album_id = "SELECT album_id FROM chv_albums WHERE album_name ='" + album_name + "'" cursor.execute(select_album_id) album_id = cursor.fetchone()[0] db_conn.close() return album_id
def lista_monitores(nome): with app.app_context(): g.db = getConnection() with g.db.cursor() as cursor: cursor.execute(sql_username_mon, 'Monitor') list_dicts_mons = cursor.fetchall() #Não vou adicionar monitores a uma equipa quando estes já existem #nesta return list(filter(lambda mon: mon not in lista_monitores_da_equipa(nome),\ list_dicts_mons))
def get_user(chat_id): connection = myconnutils.getConnection() try: cursor = connection.cursor() sql = """ SELECT Name, Rating, Radius FROM user_data WHERE Chat_id=%s """ cursor.execute(sql, (chat_id, )) row = cursor.fetchall() finally: connection.commit() connection.close() return row[0]
def lista_utentes(): with app.app_context(): g.db = getConnection() with g.db.cursor() as cursor: cursor.execute(sql_utente) l_dicts_utentes = cursor.fetchall() for utente in l_dicts_utentes: utente['link'] = url_for('utente.utente',\ user_id=current_user.id,\ email=utente['email']) return l_dicts_utentes
def get_logins(self): connection = myconnutils.getConnection() print("connect successful!!") try: with connection.cursor() as cursor: logins = "SELECT login FROM clients;" # SQL cursor.execute(logins) # Execute Query return cursor finally: connection.close()
def get_password(self, login): connection = myconnutils.getConnection() print("connect successful!!") try: with connection.cursor() as cursor: password = "******" % login cursor.execute(password) return cursor finally: connection.close()
def lista_grupos_tarefas(email): with app.app_context(): g.db = getConnection() with g.db.cursor() as cursor: cursor.execute(sql_visita_utente, (email)) list_dicts_grupos_tarefas = cursor.fetchall() for grupo_tarefas in list_dicts_grupos_tarefas: grupo_tarefas['link'] = url_for('tarefa.edit_grupo_tarefas',\ user_id=current_user.id,\ email = email,\ id = grupo_tarefas['id']) return list_dicts_grupos_tarefas
def send_message(cur_lang): connection = myconnutils.getConnection() try: cursor = connection.cursor() sql = """ SELECT *FROM message WHERE Lang=%s """ # Выполнить sql и передать 1 параметр. cursor.execute(sql, (cur_lang, )) row = cursor.fetchall() finally: connection.commit() connection.close() return row[0]
def write_new_user_to_BD(self, name, login, password): connection = myconnutils.getConnection() print("Connect successful!") list = [name[0], login, password] sql = "INSERT INTO clients (name,login, pass) VALUES ('%s','%s','%s')" % tuple( list) print(sql) try: cursor = connection.cursor() cursor.execute(sql) connection.commit() finally: connection.close()
def lista_tarefas_visitas(email,grupo_tarefas): with app.app_context(): g.db = getConnection() with g.db.cursor() as cursor: cursor.execute(sql_tarefa_visita_tarefa_visita, (grupo_tarefas)) list_dicts_tarefas_visitas = cursor.fetchall() for tarefa in list_dicts_tarefas_visitas: tarefa['link'] = url_for('tarefa.edit_tarefa_visita',\ user_id=current_user.id,\ email = email,\ id = grupo_tarefas,\ id_tarefa_visita = tarefa['id']) return list_dicts_tarefas_visitas
def display(self): # General information print("\n---------------------------------------------------------") my_date = time.strptime(str(self.res_date), "%Y-%m-%d %H:%M:%S") print("Enregistrement n°%s du %s/%s/%s à %sh%s" % (str(self.id), str(my_date[2]).rjust( 2, "0"), str(my_date[1]).rjust(2, "0"), str(my_date[0]), str(my_date[3]).rjust(2, "0"), str(my_date[4]).rjust(2, "0"))) print("Aliments de la catégorie %s = %s" % (str(self.category), self.categ_lab)) print("---------------------------------------------------------\n") # Load the initial and substitute products connection = myconnutils.getConnection() try: with connection.cursor() as cursor: # Read the initial product sql = """SELECT id, url, score, label, gen_label, quantity, packaging, brand, link, store FROM Products WHERE id = %s""" cursor.execute(sql, (self.initial)) init_prod = Product(cursor.fetchone()) # Read the substitute product sql = """SELECT id, url, score, label, gen_label, quantity, packaging, brand, link, store FROM Products WHERE id = %s""" cursor.execute(sql, (self.substitute)) subst_prod = Product(cursor.fetchone()) finally: # Close connection connection.close() # Call display method of Product class (a product description) my_food = (init_prod, subst_prod) for i in range(0, 2): if i == 0: print(" **** Aliment à remplacer ****") else: print(" **** Aliment de remplacement ****") my_food[i].display() print()
def list_products(self): connection = myconnutils.getConnection() try: with connection.cursor() as cursor: # Read all products from the category sql = """SELECT category, Cat_Prod.score AS score, id, url, label, gen_label, quantity, packaging, brand, link, store FROM Cat_Prod INNER JOIN Products ON Cat_Prod.product = Products.id WHERE Cat_Prod.category = %s """ cursor.execute(sql, (self.num)) food = [] # all products with complete information high_score = "e" # find the best score (nutrition_grades) for row in cursor: food.append(row) if row["score"] < high_score: high_score = row["score"] finally: # Close connection connection.close() return (food, high_score)
def getPatient(mpi): connection = myconnutils.getConnection() try: cursor = connection.cursor() # SQL sql = "select * from patient where patient_mpi = " + mpi + ";" # Den AbfragenBefehl implementieren (Execute Query). cursor.execute(sql) #print ("cursor.description: ", cursor.description) #print() for x in cursor: output = x finally: return str(output) # Die Verbindung schliessen (Close connection). connection.close()
def bwHC_data(searchValues): connection = myconnutils.getConnection() try: cursor = connection.cursor() # SQL sql = "select * from bwhc_data_set_consent where x = " + searchValues + ";" # Den AbfragenBefehl implementieren (Execute Query). cursor.execute(sql) #print ("cursor.description: ", cursor.description) #print() for x in cursor: output = x finally: #XML müsste man an dieser Stelle bauen.... return str(output) # Die Verbindung schliessen (Close connection). connection.close()
def substitute(self): # Return all products and the highest score in the category (my_products, high_score) = self.list_products() end_categ = False while not end_categ: # Display the products except the highest score level print("---------------------------------------------------------") print("Aliments de la catégorie %s = %s" % (str(self.num), self.label)) n = 1 prod_list = [] for prod in my_products: if prod["score"] != high_score: # Display the product print("%s : score %s, %s / %s" % (str(n).rjust(2), prod["score"].upper(), prod["label"], prod["brand"])) # Define prod_list for prod_menu prod_list.append({"num": str(n), "prod1": prod}) n += 1 print("---------------------------------------------------------") # Select a product prod_menu = Menu(prod_list) my_prod_num = prod_menu.make_choice() my_prod_ind = int(my_prod_num) - 1 my_prod = Product(prod_list[my_prod_ind]["prod1"]) # Display the whole description of the selected product print("--------------------") print("Aliment sélectionné :") print("--------------------") my_prod.display() # Give a substitute (choose randomly among better score products) better_prod = [] my_score = my_prod.score for prod in my_products: if prod["score"] < my_score: better_prod.append(prod) my_substit = Product(better_prod[randint(0, len(better_prod) - 1)]) # Display the whole description of the substitute print("--------------------------------") print("Produit de remplacement proposé :") print("--------------------------------") my_substit.display() print("--------------------------------") # Save this result ? Test another food from the category ? Quit ? # Display quit_prod menu print("MENU :") quit_prod = Menu(PROD_QUIT) quit_prod.display() qchoice = quit_prod.make_choice() if qchoice == "1": connection = myconnutils.getConnection() try: with connection.cursor() as cursor: # Create a new record in openff with the result to save sql = """INSERT INTO Results (category, initial, substitute, res_date) VALUES (%s, %s, %s, %s)""" cursor.execute(sql, (self.num, my_prod.id, my_substit.id, (time.strftime('%Y-%m-%d %H:%M:%S')))) #"res_date":time.strftime("%d %b %Y %H:%M") # time.strptime(str(time.localtime()), "%b %d %Y %H:%M") connection.commit() finally: # Close connection connection.close() print("Le remplacement de produit a bien été sauvegardé") end_categ = True if qchoice == "3": # Back to home_menu end_categ = True
def create_ftp_directory(ftp_conn, path): ftp_conn.mkd(path) def upload(filetoupload): ftp = ftp_module.getFtpConnection() if str(now.year) in ftp.nlst(): # check if '2018' exist inside 'images' ftp = change_ftp_directory(ftp, str(now.year)) else: create_ftp_directory(ftp, str(now.year)) ftp = change_ftp_directory(ftp, str(now.year)) if "%02d" % (now.month) in ftp.nlst(): ftp = change_ftp_directory(ftp, "%02d" % now.month) else: create_ftp_directory(ftp, "%02d" % now.month) ftp = change_ftp_directory(ftp, "%02d" % now.month) if "%02d" % (now.day) in ftp.nlst(): ftp = change_ftp_directory(ftp, "%02d" % (now.day)) else: create_ftp_directory(ftp, "%02d" % now.day) ftp = change_ftp_directory(ftp, "%02d" % now.day) f = open(filetoupload,'rb') ftp.storbinary(('STOR '+filetoupload),f) f.close() ftp.quit() db_connection = myconnutils.getConnection() make_screen() db_connection.close()
def get_db(): if 'db' not in g: g.db = getConnection()
#!/usr/bin/env python from myconnutils import getConnection from comandos_sql import sql_fkey_0 # Open database connection db = getConnection() # prepare a cursor object using cursor() method cursor = db.cursor() cursor.execute(sql_fkey_0) # Drop table if it already exist using execute() method. cursor.execute("DROP TABLE IF EXISTS Tarefa_Visita") # Create table as per requirement sql = """CREATE TABLE `Tarefa_Visita` ( `id` INT NOT NULL AUTO_INCREMENT, `title` VARCHAR(40), `preco` FLOAT, `duracao_esperada` VARCHAR(6), `start` VARCHAR(10), `hora_minutos_inicial` VARCHAR(5), `end` VARCHAR(10), `hora_minutos_final` VARCHAR(5), `visita` INT NOT NULL, PRIMARY KEY (`id`), CONSTRAINT `FK_tarefa_visita_tarefa`\ FOREIGN KEY (`title`) REFERENCES Tarefa(`nome`), CONSTRAINT `FK_tarefa_visita_visita`\
#!/usr/bin/python import myconnutils from comandos_sql import sql_fkey_0 # Open database connection db = myconnutils.getConnection() # prepare a cursor object using cursor() method cursor = db.cursor() cursor.execute(sql_fkey_0) # Drop table if it already exist using execute() method. cursor.execute("DROP TABLE IF EXISTS Utente") # Create table as per requirement sql = """CREATE TABLE `Utente` ( `id` INT NOT NULL AUTO_INCREMENT, `nome` VARCHAR(64) NOT NULL, `email` VARCHAR(30) NOT NULL UNIQUE, `telemovel` VARCHAR(9) NOT NULL UNIQUE, `morada` VARCHAR(64) NOT NULL, `nome_a_contactar` VARCHAR(64), `num_a_contactar` VARCHAR(9), `med_fam_apoio` varchar(64), `alergias` varchar(64), `doencas` varchar(64), `esc_de_preco` VARCHAR(32) NOT NULL, `comentarios` TEXT, PRIMARY KEY (`id`),
def insert(name): name = name.replace("-", " ") # name="thanh cong" connection = myconnutils.getConnection() print ("Connect successful!") sql = "SELECT Id FROM listtime WHERE Day = %s" sql_1 = "INSERT INTO listtime(Day) values(%s)" sql_2 = "INSERT INTO listname(Name,Time) values(%s,%s)" sql_3 = "SELECT Id FROM listname WHERE Name = %s" sql_4 = "SELECT Id FROM listname WHERE Time = %s" sql_5 = "INSERT listtime_listname(Id_Time,Id_Name) values(%s,%s)" sql_6 = "select listname.Id,listname.Time from listname,listtime_listname,listtime where Name = %s and listname.Id = listtime_listname.Id_Name and listtime.Id = %s order by Id desc limit 0,1" try: dateTime = datetime.now() cursor = connection.cursor() # Thực thi sql và truyền 1 tham số. cursor.execute(sql, str(date.today())) print("cursor.description: ", cursor.description) i = 0 idDay = 0 for row in cursor: i = i + 1 idDay = row["Id"] print("Id: ",row["Id"]) break if i == 0 : print("khong co du lieu thang ngu") # cursor = connection.cursor() cursor.execute(sql_1, date.today()) #thêm ngày vào list time # print("ok") # cursor = connection.cursor() cursor.execute(sql_2, (name, str(dateTime)))# thêm tên và ngày giờ k đeo khẩu trang print("ok") # lấy id của ngày trong bảng list time cursor = connection.cursor() cursor.execute(sql, date.today()) for row in cursor: idDay = row["Id"] break # lấy id của thằng vừa thêm vào dựa trên ngày giờ cursor = connection.cursor() cursor.execute(sql_4, str(dateTime)) for row in cursor: idName = row["Id"] break cursor = connection.cursor() cursor.execute(sql_5, (idDay, idName)) connection.commit() if i == 1 : print("Co du lieu thang ngu") print(name) print(idDay) cursor = connection.cursor() cursor.execute(sql_3, name) tempId = 0 for row in cursor: tempId = row["Id"] print(tempId) print("llllllllllll") break if tempId > 0: cursor.execute(sql_6, (name, idDay)) print("cl ma") tempTime = "" for row in cursor: tempTime = row["Time"] break # if tempTime != '': tempTime = time.strptime(tempTime, '%Y-%m-%d %H:%M:%S.%f') if dateTime.hour*3600 - dateTime.minute*60 - tempTime.tm_hour * 3600 - tempTime.tm_min * 60 >= 1200: cursor.execute(sql_2, (name, str(dateTime))) # thêm tên và ngày giờ k đeo khẩu trang print("ok") # lấy id của ngày trong bảng list time cursor = connection.cursor() cursor.execute(sql, date.today()) for row in cursor: idDay = row["Id"] break # lấy id của thằng vừa thêm vào dựa trên ngày giờ cursor = connection.cursor() cursor.execute(sql_4, str(dateTime)) for row in cursor: idName = row["Id"] break cursor = connection.cursor() cursor.execute(sql_5, (idDay, idName)) connection.commit() else: print("haha may ngu vcl may qua ngu") pass else: cursor.execute(sql_1, date.today()) # thêm ngày vào list time # print("ok") # cursor = connection.cursor() cursor.execute(sql_2, (name, str(dateTime))) # thêm tên và ngày giờ k đeo khẩu trang print("ok1") # lấy id của ngày trong bảng list time cursor = connection.cursor() cursor.execute(sql, date.today()) for row in cursor: idDay = row["Id"] break # lấy id của thằng vừa thêm vào dựa trên ngày giờ cursor = connection.cursor() cursor.execute(sql_4, str(dateTime)) for row in cursor: idName = row["Id"] break cursor = connection.cursor() cursor.execute(sql_5, (idDay, idName)) connection.commit() connection.commit() finally: # Đóng kết nối connection.close()