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)
