def test_rawfetchall(self):
cursor = self.cnx.cursor(raw=True)
cursor.execute("SELECT 1")
try:
cursor.fetchall()
except errors.InterfaceError:
self.fail("fetchall() raises although result is available")
def test_columnnames_unicode(self):
"""Table names should unicode objects in cursor.description"""
exp = [("ham", 8, None, None, None, None, 0, 129)]
cursor = self.cnx.cursor()
cursor.execute("SELECT 1 as 'ham'")
cursor.fetchall()
self.assertEqual(exp, cursor.description)
def lock():
querry = ("select * from users where name = %s ")
try:
cursor=cnx.cursor()
cursor.execute(querry, (request.form['username'],))
result = cursor.fetchall()
logTime = datetime.now()
logUserId = result[0][0]
cursor.close()
if len(result) > 0:
if currentlyLocked[0] == True:
logAction = "Attempted to lock already locked lock"
logDbAction(logUserId,logAction,logTime)
return "Already Locked"
else:
logAction = "Locked the lock"
logDbAction(logUserId,logAction,logTime)
currentlyLocked[0] = True
return "locked"
else:
logAction = "tried to lock the lock but denied due to invalid credentials"
logDbAction(logUserId,logAction,logTime)
return 'denied'
cursor.close
except Exception, err:
print Exception,err
def open():
querry = "select * from users where name = %s "
try:
cursor = cnx.cursor()
cursor.execute(querry, (request.form["username"],))
result = cursor.fetchall()
logTime = datetime.now()
logUserId = result[0][0]
cursor.close()
if len(result) > 0:
if currentlyLocked[0] == True:
currentlyLocked[0] = False
logAction = "Opened the lock"
logDbAction(logUserId, logAction, logTime)
return "opend"
else:
logAction = "Tried to open already open lock"
logDbAction(logUserId, logAction, logTime)
return "Aleady Open"
else:
logAction = "tried to open the lock but denied due to invalid credentials"
logDbAction(logUserId, logAction, logTime)
return "denied"
except Exception, err:
print Exception, err
def findBySql(self,sql,params = {},limit = 0,join = 'AND',lock=False):
"""
自定义sql语句查找
limit = 是否需要返回多少行
params = dict(field=value)
join = 'AND | OR'
"""
try :
cursor = self.__getCursor()
sql = self.__joinWhere(sql,params,join)
cursor.execute(sql,tuple(params.values()))
rows = cursor.fetchmany(size=limit) if limit > 0 else cursor.fetchall()
result = [dict(zip(cursor.column_names,row)) for row in rows] if rows else None
return result
except:
raise BaseError(706)
def __query(self,table,criteria,all=False,isDict=True,join='AND'):
'''
table : 表名
criteria : 查询条件dict
all : 是否返回所有数据，默认为False只返回一条数据,当为真是返回所有数据
isDict : 返回格式是否为字典，默认为True ，即字典否则为数组
'''
try :
if all is not True:
criteria['limit'] = 1 # 只输出一条
sql,params = self.__contact_sql(table,criteria,join) #拼sql及params
'''
# 当Where为多个查询条件时，拼查询条件 key 的 valuse 值
if 'where' in criteria and 'dict' in str(type(criteria['where'])) :
params = criteria['where']
#params = tuple(params.values())
where ,whereValues = self.__contact_where(params)
sql+= ' WHERE '+where if where else ''
params=tuple(whereValues)
else :
params = None
'''
#__contact_where(params,join='AND')
cursor = self.__getCursor()
self.__display_Debug_IO(sql,params) #DEBUG
#if self.DataName=="ORACLE":
#sql="select * from(select * from(select t.*,row_number() over(order by %s) as rownumber from(%s) t) p where p.rownumber>%s) where rownum<=%s" % ()
#pass
cursor.execute(sql,params if params else ())
rows = cursor.fetchall() if all else cursor.fetchone()
if isDict :
result = [dict(zip(cursor.column_names,row)) for row in rows] if all else dict(zip(cursor.column_names,rows)) if rows else {}
else :
result = [row for row in rows] if all else rows if rows else []
return result
except Exception as err:
try :
raise BaseError(706,err._full_msg)
except :
raise BaseError(706)
import mysql.connector
from mysql.connector import cursor
from difflib import get_close_matches
from termcolor import colored
connection = mysql.connector.connect(user='******',
password='******',
host='108.167.140.122',
database='ardit700_pm1database')
cursor = connection.cursor()
query = cursor.execute(f'SELECT * FROM Dictionary')
results = cursor.fetchall()
#function to create a set of keys to search a word
def set_create(par):
myset = set()
for key, value in par:
myset.add(key)
return myset
# def a function to create a query:
def my_query(word):
query = cursor.execute(
f"SELECT * FROM Dictionary WHERE Expression = '{word}'")
result = cursor.fetchall()
return result
def changerequest(cnx, cursor, id):
while 1:
temp = input("Changing street or intersection? \n")
if temp == "street":
while (1):
street = input(
"Please provide the name of the street for the change request: \n"
)
stmt = ("SELECT s.streetname "
"FROM streets s "
"WHERE s.streetname = %s")
cursor.execute(stmt, (street, ))
if cursor.with_rows:
break
else:
"Not a valid street name, try again"
stmt = (
"SELECT s.streetname, f.description, f.startaddress, f.endaddress, f.featureid, s.streetid "
"FROM streets s "
"INNER JOIN features f "
"ON f.streetid = s.streetid "
"WHERE s.streetname = %s")
cursor.execute(stmt, (street, ))
results = cursor.fetchall()
if len(results) > 0:
hasfeats = 1
for result in results:
streetid = result[5]
print("feature id: ", result[4])
if result[2]:
if result[3]:
print(result[0], "has a", result[1], "from",
result[2], "to", result[3])
else:
print(result[0], "has a", result[1], "starting at",
result[2])
else:
print(result[0], "has a", result[1])
else:
hasfeats = 0
print("No features on this street, can only add new \n")
while 1:
type = input(
"Would you like to make a new feature, update an existing feature, or delete a feature?\n"
"input 'new', 'update', or 'delete\n")
if type.lower() == 'new':
newrequest(cnx, cursor, id, streetid, 0)
break
if hasfeats:
if type.lower() == 'update':
updaterequest(cnx, cursor, id, streetid, 0, results)
break
if type.lower() == 'delete':
deleterequest(cnx, cursor, id, streetid, 0, results)
break
print("Not a valid input \n")
type = input("Would you like to continue changing? Yes or No\n")
if type.lower() == "no":
return
if temp == "intersection":
while 1:
street1 = input("Please input a cross street name.\n")
street2 = input("Please input the other cross street name.\n")
stmt = ("SELECT i.intersectionid "
"FROM intersections i "
"INNER JOIN streets s "
"ON i.street1 = s.streetid "
"OR i.street2 = s.streetid "
"WHERE s.streetname = %s AND "
"i.intersectionid IN "
"(SELECT i.intersectionid "
" FROM intersections i "
" INNER JOIN streets s "
" ON i.street1 = s.streetid "
" OR i.street2 = s.streetid "
" WHERE s.streetname = %s) ")
cursor.execute(stmt, (street1, street2))
if cursor.with_rows:
interid = cursor.fetchone()[0]
break
else:
"Not a valid intersection, try again"
stmt = (
"SELECT DISTINCT f.description, i.intersectionid, f.featureid "
"FROM features f "
"INNER JOIN intersections i "
"ON f.intersectionid = i.intersectionid "
"INNER JOIN streets s "
"ON i.street1 = s.streetid "
"OR i.street2 = s.streetid "
"WHERE s.streetname = %s AND "
"i.intersectionid IN "
"(SELECT i.intersectionid "
" FROM intersections i "
" INNER JOIN streets s "
" ON i.street1 = s.streetid "
" OR i.street2 = s.streetid "
" WHERE s.streetname = %s) ")
cursor.execute(stmt, (street1, street2))
results = cursor.fetchall()
if len(results) > 0:
hasfeats = 1
print("The intersection of", street1, "and", street2,
"has the following: \n")
for result in results:
print(result[0])
else:
hasfeats = 0
print("No features found on the intersection of", street1,
"and", street2)
print("\n Can only make new features for this intersection")
newrequest(cnx, cursor, id, interid, 1)
return
while 1:
type = input(
"Would you like to make a new feature, update an existing feature, or delete a feature?\n"
"input 'new', 'update', or 'delete\n")
if type.lower() == 'new':
newrequest(cnx, cursor, id, interid, 1)
break
if hasfeats:
if type.lower() == 'update':
updaterequest(cnx, cursor, id, interid, 1, results)
break
if type.lower() == 'delete':
deleterequest(cnx, cursor, id, interid, 1, results)
break
else:
print("Not a valid input \n")
type = input("Would you like to continue changing? Yes or No")
if type.lower() == "no":
return
def update_info(idVol):
request = "SELECT idEtape, dep.latitude AS latitude_depart, dep.longitude AS longitude_depart, arr.latitude AS latitude_arrivee, arr.longitude AS longitude_arrivee, deg.latitude AS latitude_degagement, deg.longitude AS longitude_degagement FROM etapes JOIN vol on vol.idVol = etapes.idVol JOIN aerodrome AS dep ON etapes.OACIdep = dep.OACI JOIN aerodrome AS arr ON etapes.OACIarr = arr.OACI JOIN aerodrome AS deg ON etapes.OACIdeg = deg.OACI WHERE vol.idVol = %s"
param = (idVol, )
cnx = createConnexion()
cursor = cnx.cursor()
cursor.execute(request, param)
res = cursor.fetchall()
closeConnexion(cnx)
#Traitement des données
id_etapes = []
for i in range(len(res)):
id_etapes.append(res[i][0])
coordonnees = []
coordonnees_generales = []
for i in range(0, len(res)):
coordonnees.append((res[i][1], res[i][2]))
coordonnees_generales.append((res[i][1], res[i][2]))
coordonnees_generales.append((res[i][5], res[i][6]))
coordonnees.append((res[-1][3], res[-1][4]))
coordonnees_generales.append((res[-1][3], res[-1][4]))
coordonnees_deg = []
for x in coordonnees_generales:
if x not in coordonnees:
coordonnees_deg.append(x)
def calc_dist(A, B):
latA, longA = A[0], A[1]
latB, longB = B[0], B[1]
latA_rad = latA * pi / 180
longA_rad = longA * pi / 180
latB_rad = latB * pi / 180
longB_rad = longB * pi / 180
dlong = longB_rad - longA_rad
dlat = latB_rad - latA_rad
S_ab = acos(
sin(latA_rad) * sin(latB_rad) +
cos(latA_rad) * cos(latB_rad) * cos(dlong))
D = round(S_ab * 6378137) / 1000
theta = atan(dlat / dlong)
theta_deg = theta * 180 / pi
if longB < longA:
return (270 - theta_deg, D)
else:
return (90 - theta_deg, D)
def calcul_carbu(D, conso_h, Vcroisiere, cap_avion, dir_vent, v_vent):
# on met D en km
cap_avion_rad, direction_vent_rad = cap_avion * pi / 180, dir_vent * pi / 180
vitesse_vraie = Vcroisiere - (
v_vent * cos(cap_avion_rad - direction_vent_rad)) # en km/h
carbu_supp = 9 # carburant qui concerne le roulage et l'intégration à l'aérodrome
Tv = D / vitesse_vraie # temps de vol en h
Vcarb = Tv * conso_h + carbu_supp
return Vcarb
dist = []
cap = []
compteur = 0
for j in range(len(coordonnees) - 1):
A = coordonnees[j]
B = coordonnees[j + 1]
C = coordonnees_deg[compteur]
c1, d1 = calc_dist(A, B)
c2, d2 = calc_dist(A, C)
if d2 < d1:
dist.append(d1)
cap.append(c1)
else:
dist.append(d2)
cap.append(c2)
compteur += 1
request = "SELECT vitesseVent, directionVent, rayonAction, consoHoraire, VitesseCroisiere FROM vol JOIN avion ON avion.idAvion = vol.idAvion WHERE vol.idVol = %s"
param = (idVol, )
cnx = createConnexion()
cursor = cnx.cursor()
cursor.execute(request, param)
res = cursor.fetchall()
closeConnexion(cnx)
carb = []
for i in range(len(dist)):
x = calcul_carbu(dist[i], res[0][3], res[0][4], cap[i], res[0][1],
res[0][0])
carb.append(round(x))
for i in range(len(id_etapes)):
request = "UPDATE etapes SET distance = %s, carburant = %s WHERE idVol = %s AND idEtape = %s"
param = (
dist[i],
carb[i],
idVol,
id_etapes[i],
)
cnx = createConnexion()
cursor = cnx.cursor()
cursor.execute(request, param)
cnx.commit()
return dist, carb, coordonnees_generales
def get_histo(login):
request = "SELECT OACIdep, OACIarr , etapes.idVol, rang, vol.date, avion.reference, vol.typeVol FROM etapes " \
"INNER JOIN vol ON etapes.idVol = vol.idVol " \
"INNER JOIN avion ON vol.idAvion = avion.idAvion " \
"WHERE vol.idUtilisateur = %s order BY etapes.idVol ASC, etapes.rang ASC "
param = (login, )
cnx = createConnexion()
cursor = cnx.cursor(dictionary=True)
cursor.execute(request, param)
res = cursor.fetchall()
closeConnexion(cnx)
#Traitement des données
dico_type_vol = {
"vfr_local_vue": "VFR local en vue d'aérodrome",
"vfr_local_hors_aero": "VFR local hors aérodrome",
"nav_vfr": "Navigation VFR",
"vfr_nuit": "VFR nuit"
}
try:
liste_vol = []
idVolOld = 0
i = 0 #indice d'étape
j = res[0][
"idVol"] - 1 #pour calculer le décalage nécessaire de sorte que tous les id de vol d'un utilisateur commencent à 1
k = 0
while i < len(res):
idVol = res[i]["idVol"] - j
if idVol != idVolOld:
liste_vol.append([])
type_avion = res[i]["reference"]
liste_vol[k].append(type_avion)
date = res[i]["date"]
liste_vol[k].append(date)
type_vol = res[i]["typeVol"]
liste_vol[k].append(dico_type_vol[type_vol])
OACIdep = res[i]["OACIdep"]
liste_vol[k].append(OACIdep)
if idVolOld != 0:
OACIarr = res[i - 1]["OACIarr"]
liste_vol[k - 1].append(OACIarr)
k += 1
idVolOld = idVol
i += 1
OACIarr = res[i - 1]['OACIarr']
liste_vol[k - 1].append(OACIarr)
#Ajout des distances & conso totales par vol:
data = info_vols(login)
for i in range(len(liste_vol)):
liste_vol[i].append(round(data[i][1]))
liste_vol[i].append(data[i][0])
return liste_vol
except IndexError:
return
def busca_por_item():
resultado_por_item.show()
cursor = db_connection.cursor()
if pesquisar_item.sl_nome.isChecked():
nome = pesquisar_item.campo_1.text().title()
# Query para apresentar os dados pesquisados
sql = ("SELECT * from Pacientes WHERE Nome ='%s'") % (str(nome))
cursor.execute(sql)
# Salvando o resultado da query em uma variável
dados_lidos = cursor.fetchall()
# Informando o tamanho das linhas e colunas da base
resultado_por_item.tb_dados.setRowCount(len(dados_lidos))
resultado_por_item.tb_dados.setColumnCount(8)
# Realizando um loop para preencher os campos da tabela com os valores da base de dados
for i in range(0, len(dados_lidos)):
for j in range(0,8):
resultado_por_item.tb_dados.setItem(i,j, QtWidgets.QTableWidgetItem(str(dados_lidos[i][j])))
pesquisar_item.campo_1.setText("")
elif pesquisar_item.sl_cpf.isChecked():
cpf = pesquisar_item.campo_2.text().title()
# Query para apresentar os dados pesquisados
sql = ("SELECT * from Pacientes WHERE cpf ='%s'") % (str(cpf))
cursor.execute(sql)
# Salvando o resultado da query em uma variável
dados_lidos = cursor.fetchall()
# Informando o tamanho das linhas e colunas da base
resultado_por_item.tb_dados.setRowCount(len(dados_lidos))
resultado_por_item.tb_dados.setColumnCount(8)
# Realizando um loop para preencher os campos da tabela com os valores da base de dados
for i in range(0, len(dados_lidos)):
for j in range(0,8):
resultado_por_item.tb_dados.setItem(i,j, QtWidgets.QTableWidgetItem(str(dados_lidos[i][j])))
pesquisar_item.campo_2.setText("")
elif pesquisar_item.sl_email.isChecked():
email = pesquisar_item.campo_3.text().title()
# Query para apresentar os dados pesquisados
sql = ("SELECT * from Pacientes WHERE Email ='%s'") % (str(email))
cursor.execute(sql)
# Salvando o resultado da query em uma variável
dados_lidos = cursor.fetchall()
# Informando o tamanho das linhas e colunas da base
resultado_por_item.tb_dados.setRowCount(len(dados_lidos))
resultado_por_item.tb_dados.setColumnCount(8)
# Realizando um loop para preencher os campos da tabela com os valores da base de dados
for i in range(0, len(dados_lidos)):
for j in range(0,8):
resultado_por_item.tb_dados.setItem(i,j, QtWidgets.QTableWidgetItem(str(dados_lidos[i][j])))
pesquisar_item.campo_3.setText("")
elif pesquisar_item.sl_telefone.isChecked():
telefone = pesquisar_item.campo_4.text().title()
# Query para apresentar os dados pesquisados
sql = ("SELECT * from Pacientes WHERE Telefone ='%s'") % (str(telefone))
cursor.execute(sql)
# Salvando o resultado da query em uma variável
dados_lidos = cursor.fetchall()
# Informando o tamanho das linhas e colunas da base
resultado_por_item.tb_dados.setRowCount(len(dados_lidos))
resultado_por_item.tb_dados.setColumnCount(8)
# Realizando um loop para preencher os campos da tabela com os valores da base de dados
for i in range(0, len(dados_lidos)):
for j in range(0,8):
resultado_por_item.tb_dados.setItem(i,j, QtWidgets.QTableWidgetItem(str(dados_lidos[i][j])))
pesquisar_item.campo_4.setText("")
elif pesquisar_item.sl_procedimento.isChecked():
procedimento = pesquisar_item.campo_5.text().title()
# Query para apresentar os dados pesquisados
sql = ("SELECT * from Pacientes WHERE Procedimento ='%s'") % (str(procedimento))
cursor.execute(sql)
# Salvando o resultado da query em uma variável
dados_lidos = cursor.fetchall()
# Informando o tamanho das linhas e colunas da base
resultado_por_item.tb_dados.setRowCount(len(dados_lidos))
resultado_por_item.tb_dados.setColumnCount(8)
# Realizando um loop para preencher os campos da tabela com os valores da base de dados
for i in range(0, len(dados_lidos)):
for j in range(0,8):
resultado_por_item.tb_dados.setItem(i,j, QtWidgets.QTableWidgetItem(str(dados_lidos[i][j])))
pesquisar_item.campo_5.setText("")
elif pesquisar_item.sl_responsavel.isChecked():
responsavel = pesquisar_item.campo_6.text().title()
# Query para apresentar os dados pesquisados
sql = ("SELECT * from Pacientes WHERE Responsável ='%s'") % (str(responsavel))
cursor.execute(sql)
# Salvando o resultado da query em uma variável
dados_lidos = cursor.fetchall()
# Informando o tamanho das linhas e colunas da base
resultado_por_item.tb_dados.setRowCount(len(dados_lidos))
resultado_por_item.tb_dados.setColumnCount(8)
# Realizando um loop para preencher os campos da tabela com os valores da base de dados
for i in range(0, len(dados_lidos)):
for j in range(0,8):
resultado_por_item.tb_dados.setItem(i,j, QtWidgets.QTableWidgetItem(str(dados_lidos[i][j])))
pesquisar_item.campo_6.setText("")
def my_query(word):
cursor.execute(f"SELECT * FROM Dictionary WHERE Expression = '{word}'")
result = cursor.fetchall()
return result
