def delete_book(): """ Connect to MySQL database """ conn = None try: conn = mysql.connector.connect(host='db', port='3306', database='bookflix', user='******', password='******') book_id = request.values.get('book_id') sql_select_booking_query = "Select * from book where book_id = %s" cursor = conn.cursor() cursor.execute(sql_select_booking_query, (book_id, )) exits = cursor.fetchone() if exits != "": sql_Delete_query = "Delete from book where book_id = %s" cursor = conn.cursor() cursor.execute(sql_Delete_query, (book_id, )) conn.commit() except Error as e: print(e) finally: if conn is not None and conn.is_connected(): conn.close() return ""
def findByID(self, code): cursor= self.db.cursor() sql= "select * from shopStock where code = %s" values= [ code ] cursor.execute(sql, values) result = cursor.fetchone() return self.convertToDict(result)
def findByID(self, eeID): cursor = self.db.cursor() sql = "select * from employees where eeID = %s" values = [eeID] cursor.execute(sql, values) result = cursor.fetchone() return self.convertToDict(result)
def findById(self, id): cursor = self.db.cursor() sql = 'select * from movies where id = %s' values = [id] cursor.execute(sql, values) result = cursor.fetchone() return self.convertToDict(result)
def findByID(self, personid): cursor = self.db.cursor() sql = 'select * from person where personid = %s' values = [personid] cursor.execute(sql, values) result = cursor.fetchone() return self.convertToDict(result)
def findById(self, ClientID): cursor = self.db.cursor() sql = 'select * from clients where ClientID = %s' values = [ClientID] cursor.execute(sql, values) result = cursor.fetchone() return self.convertToDict(result)
def count(self,table,params={},join='AND'): # 根据条件统计行数 try : sql = 'SELECT COUNT(*) FROM %s' % table if params : where ,whereValues = self.__contact_where(params) sqlWhere= ' WHERE '+where if where else '' sql+=sqlWhere #sql = self.__joinWhere(sql,params,join) cursor = self.__getCursor() self.__display_Debug_IO(sql,tuple(whereValues)) #DEBUG if self.DataName=='ORACLE': cursor.execute(sql % tuple(whereValues)) else : cursor.execute(sql,tuple(whereValues)) #cursor.execute(sql,tuple(params.values())) result = cursor.fetchone(); return result[0] if result else 0 #except: # raise BaseError(707) except Exception as err: try : raise BaseError(707,err._full_msg) except : raise BaseError(707)
def findById(self, id): cursor = self.db.cursor() sql = "select * from coffeeconsumers where id = %s" values = [id] cursor.execute(sql, values) result = cursor.fetchone() return self.convertToDict(result)
def findById(self, ISBN): cursor = self.db.cursor() sql = 'select * from books where ISBN = %s' values = [ISBN] cursor.execute(sql, values) result = cursor.fetchone() return self.convertToDict(result)
def findByEmp(self, employee): cursor= self.db.cursor() sql= "select * from timetable where employee = %s" values= [ employee ] cursor.execute(sql, values) result = cursor.fetchone() return self.convertToDict2(result)
def findById(self, snr): # Find a supplier by entered the codenr cursor = self.db.cursor() sql = 'select * from supplier where snr = %s' values = [snr] cursor.execute(sql, values) result = cursor.fetchone() return self.convertToDict(result)
def findById(self, codenr): # Find a whiskey by entered the codenr cursor = self.db.cursor() sql = 'select * from whiskey where codenr = %s' values = [ codenr ] cursor.execute(sql, values) result = cursor.fetchone() return self.convertToDict(result)
def count(self, table, params={}, join='AND'): # 根据条件统计行数 try: sql = 'SELECT COUNT(*) FROM `%s`' % table if params: where, whereValues = self.__contact_where(params) sqlWhere = ' WHERE ' + where if where else '' sql += sqlWhere #sql = self.__joinWhere(sql,params,join) cursor = self.__getCursor() self.__display_Debug_IO(sql, tuple(whereValues)) #DEBUG cursor.execute(sql, tuple(whereValues)) #cursor.execute(sql,tuple(params.values())) result = cursor.fetchone() return result[0] if result else 0 #except: # raise BaseError(707) except Exception as err: try: raise BaseError(707, err._full_msg) except: raise BaseError(707)
def login(): """ Connect to MySQL database """ conn = None try: conn = mysql.connector.connect(host='db', port='3306', database='bookflix', user='******', password='******') user_name = request.values.get('user_name') password = request.values.get('password') sql_list_query = "Select * from user_info WHERE user_name = %s AND password = %s" cursor = conn.cursor() cursor.execute(sql_list_query, ( user_name, password, )) record = cursor.fetchone() print(record) conn.commit() except Error as e: print(e) finally: if conn is not None and conn.is_connected(): conn.close() return json.dumps(record)
def findById(self, userid): cursor = self.db.cursor() sql = 'select * from training where userid = %s' values = [ userid ] values = [ userid ] cursor.execute(sql,values) result = cursor.fetchone() return self.convertToDict(result)
def findByID(self, id): cursor = self.db.cursor() sql = "select * from dvds where id = %s" values = (id, ) cursor.execute(sql, values) result = cursor.fetchone() return self.convertToDictionary(result)
def getUserID(self, user): cursor = self.getCursor() sql = 'select * from user where id = %s' values = [user] cursor.execute(sql, values) result = cursor.fetchone() cursor.close() return self.convertToIDDict(result)
def checkUser(self, username, password): cursor = self.db.cursor() sql = "select * from users where username = %s and password = %s" values = [username, password] cursor.execute(sql, values) # Fetch one record and return result result = cursor.fetchone() cursor.close() return self.convertToDict2(result)
def getMessagebyID(self, message): cursor = self.getCursor() sql = 'select * from message where ID = %s' values = [message['id']] cursor.execute(sql, values) result = cursor.fetchone() print("result", result) cursor.close() return self.convertToMessageDict(result)
def getUserbyID(self, user): cursor = self.getCursor() print("GET USER BY ID") sql = 'select * from user where id = "%s"' values = [user['id']] cursor.execute(sql, values) result = cursor.fetchone() cursor.close() return self.convertToUserDict(result)
def countBySql(self,sql,params = {},join = 'AND'): # 自定义sql 统计影响行数 try: cursor = self.__getCursor() sql = self.__joinWhere(sql,params,join) cursor.execute(sql,tuple(params.values())) result = cursor.fetchone(); return result[0] if result else 0 except: raise BaseError(707)
def countBySql(self, sql, params={}, join='AND'): # 自定义sql 统计影响行数 try: cursor = self.__getCursor() sql = self.__joinWhere(sql, params, join) cursor.execute(sql, tuple(params.values())) result = cursor.fetchone() return result[0] if result else 0 except: raise BaseError(707)
def countAllFrom(table: str, condition=""): request = "SELECT COUNT(*) FROM {} ".format(table) + condition cnx = createConnexion() try: cursor = cnx.cursor(dictionary=True) cursor.execute(request) res = cursor.fetchone() except mysql.connector.Error as e: res = None closeConnexion(cnx) return str(res['COUNT(*)'])
def findById(self, id): #return{} cursor = self.db.cursor() sql = 'select * from stock where id = %s' values = [ id, ] cursor.execute(sql, values) result = cursor.fetchone() return self.convertToDict(result) print(result)
def countDistinctFrom(table, colonne, condition): request = "SELECT COUNT(DISTINCT {}) FROM {} ".format(colonne, table) + condition cnx = createConnexion() try: cursor = cnx.cursor(dictionary=True) cursor.execute(request) res = cursor.fetchone() except mysql.connector.Error as e: res = None closeConnexion(cnx) return str(res['COUNT(DISTINCT {})'.format(colonne)])
def verifUserEmail(email): request = "SELECT * FROM utilisateurs WHERE email = %s LIMIT 1" param = (email, ) cnx = createConnexion() try: cursor = cnx.cursor() cursor.execute(request, param) res = cursor.fetchone() return "userNoExist" if res is None else "userExist" except mysql.connector.Error as e: print("Failed verif email : {}".format(e)) closeConnexion(cnx)
def retornarUsuario(nome): try: con = mysql.connector.connect(host='localhost', database='db_redacoes', user='******', password='******') comando = "SELECT * FROM db_redacoes.usuario WHERE NOME = '{}'".format( nome) cursor = con.cursor() cursor.execute(comando) return cursor.fetchone() except: return 0
def displayTourTable(self): query = "SELECT * FROM reservation" try: #start coonnection self.db = mysql.connector.connect(user='******', database='adventure', password="******") # execute the query cursor = self.db.cursor() cursor.execute(query) # display tour table row = cursor.fetchone() while row is not None: print(row) row = cursor.fetchone() #ask the user if they want to delete a reservation self.deleteTourInput() except mysql.connector.Error as error: print(error)
def sumFrom(table, attribut, condition=""): request = "SELECT SUM({}) FROM {} ".format(attribut, table) + condition cnx = createConnexion() try: cursor = cnx.cursor(dictionary=True) cursor.execute(request) res = cursor.fetchone() except mysql.connector.Error as e: res = None closeConnexion(cnx) if str(res['SUM({})'.format(attribut)]) == "None": return 0 else: return str(res['SUM({})'.format(attribut)])
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)
def ifCategoryExists(self, category: str, username: str): try: dbConnector = self.__getConnector() cursor = self.__getDictionaryCursor() cursor.execute(""" SELECT EXISTS(SELECT * FROM budget WHERE username= '******' AND categories='{}'); """.format(username, category)) search = cursor.fetchone() cursor.close() result = search.values() return result except mysql.connector.IntegrityError as err: return False finally: if dbConnector.is_connected: cursor.close() dbConnector.close()
def verifAuthData(login, mdp): request = "SELECT * FROM utilisateurs WHERE email=%s and mdp=%s LIMIT 1" param = ( login, mdp, ) cnx = createConnexion() try: cursor = cnx.cursor(dictionary=True) cursor.execute(request, param) res = cursor.fetchone() msg = "okAuth" except mysql.connector.Error as e: res = None msg = "Failed authentification : {}".format(e) closeConnexion(cnx) return res, msg
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)
def insert(self,table,data,commit=True): # 新增一条记录 try : ''' 从data中分离含用SQL函数的字字段到funData字典中, 不含SQL函数的字段到newData ''' funData,newData=self.__split_expression(data) funFields='';funValues='' # 拼不含SQL函数的字段及值 fields = ','.join(k for k in newData.keys()) values = ','.join(("%s", ) * len(newData)) # 拼含SQL函数的字段及值 if funData : funFields = ','.join(k for k in funData.keys()) funValues =','.join( v for v in funData.values()) # 合并所有字段及值 fields += ','+funFields if funFields else '' values += ','+funValues if funValues else '' sql = 'INSERT INTO %s (%s) VALUES (%s)'%(table,fields,values) cursor = self.__getCursor() for (k,v) in newData.items() : try: if isinstance(v, str) : newData[k]="'%s'" % (v,) except : pass self.__display_Debug_IO(sql,tuple(newData.values())) #DEBUG sql= sql % tuple(newData.values()) if self.DataName=='POSTGRESQL' : sql+=' RETURNING id' cursor.execute(sql) #if self.DataName=='ORACLE': #sql= sql % tuple(newData.values()) #cursor.execute(sql) #else : #cursor.execute(sql,tuple(newData.values())) if self.DataName=='ORACLE': # 1. commit 一定要为假 # 2. Oracle Sequence 的命名规范为: [用户名.]SEQ_表名_ID # 3. 每张主表都应该有ID t_list=table.split('.') if len(t_list)>1 : SEQ_Name= t_list[0]+'.SEQ_'+t_list[1]+'_ID' else : SEQ_Name='SEQ_'+t_list[0]+'_ID' cursor.execute('SELECT %s.CURRVAL FROM dual' % SEQ_Name.upper()) result = cursor.fetchone() insert_id= result[0] if result else 0 #insert_id=cursor.rowcount elif self.DataName=='MYSQL' : insert_id = cursor.lastrowid elif self.DataName=='POSTGRESQL': item = cursor.fetchone() insert_id = item[0] if commit : self.commit() return insert_id except Exception as err: try : raise BaseError(705,err._full_msg) except : raise BaseError(705,err.args)