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()
