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