def update_password(in1,in2):
logins_db = connect_mysql(login_conn)
logins_cursor = logins_db.cursor()
logins_cursor.callproc('sp_updatePassword',(in1,in2))
logins_db.commit()
logins_db.close()
def put_sub(in1,in2,in3,in4,in5,in6):
subs_db = connect_mysql(sub_conn)
subs_cursor = subs_db.cursor()
subs_cursor.callproc('sp_createSub',(in1,in2,in3,in4,in5,in6))
subs_db.commit()
subs_db.close()
def put_payment(in1,in2,in3,in4,in5,in6,in7,in8):
payments_db = connect_mysql(pay_conn)
payments_cursor = payments_db.cursor()
payments_cursor.callproc('sp_createPayment',(in1,in2,in3,in4,in5,in6,in7,in8))
payments_db.commit()
payments_db.close()
def get_sub_dets(in1):
subs_db = connect_mysql(sub_conn)
subs_cursor = subs_db.cursor()
subs_cursor.callproc('sp_getSub',s(in1))
data = subs_cursor.fetchone()
subs_db.close()
return data
def get_all_payments(in1):
payments_db = connect_mysql(pay_conn)
payments_cursor = payments_db.cursor()
payments_cursor.callproc('sp_getPayments',s(in1))
data = payments_cursor.fetchall()
payments_db.close()
return data
def QueryTable(tablename, fieldname_list, query_condition, fetch_type):
'''
:param tablename: 表名
:param fieldname_list: 查询字段列表，[field, field2,...]
:param query_condition: 查询条件， {'k1': 'v1', 'k2': 'v2', ...}
:param fetch_type: 1-查询所有结果，2-查询一条结果
:return: fetch_type-1:[{},{}], fetch_type-2:{}
'''
if len(fieldname_list) == 0:
return False
if fieldname_list == '*': # 暂时不支持select *
return False
sql = 'select '
count = 0
for name in fieldname_list:
sql += name
count += 1
if count == len(fieldname_list):
sql += ''
else:
sql += ','
sql += ' from %s' % tablename
sql += ' where 1=1'
if len(query_condition) > 0:
for (k, v) in query_condition.items():
if isinstance(v, str):
sql += " and %s = %s " % (k, "'" + v + "'")
else:
sql += " and %s = %s " % (k, v)
db = connect_mysql()
try:
cursor = db.cursor()
cursor.execute(sql)
if fetch_type == 1:
rs_ret = []
result = cursor.fetchall()
if result != ():
for rs in result:
for index, name in enumerate(fieldname_list):
rs_dict = {}
rs_dict[name] = rs[index]
rs_ret.append(rs_dict)
elif fetch_type == 2:
rs_ret = {}
result = cursor.fetchone()
if result != ():
for index, name in enumerate(fieldname_list):
rs_ret[name] = result[index]
else:
rs_ret = None
return rs_ret
except:
return False
finally:
db.close()
def login_check(str1,str2):
logins_db = connect_mysql(login_conn)
logins_cursor = logins_db.cursor()
logins_cursor.callproc('sp_login16',(str1,str2))
data = logins_cursor.fetchall()
logins_db.close()
return data
def sub_check(in1):
subs_db = connect_mysql(sub_conn)
subs_cursor = subs_db.cursor()
subs_cursor.callproc('sp_checkSub',(s(in1)))
data = len(subs_cursor.fetchall())
subs_db.close()
if data == 0:
return False
else:
return True
def registration_check(str1,str2):
logins_db = connect_mysql(login_conn)
logins_cursor = logins_db.cursor()
logins_cursor.callproc('sp_createUser3',(str1,str2))
data = len(logins_cursor.fetchall())
if data == 0:
logins_db.commit()
logins_db.close()
return True
else:
logins_db.close()
return False
def DeleteTable(tablename, delete_condition):
if type(delete_condition) != dict:
return False
sql = "delete from %s" % (tablename)
if len(delete_condition) > 0:
sql += " where 1 = 1"
for (k, v) in delete_condition.items():
sql += " and %s = %s " % (k, v)
db = connect_mysql()
try:
cursor = db.cursor()
cursor.execute(sql)
db.commit()
db.close()
return True
except:
db.rollback()
db.close()
return False
def UpdateTable(tablename, update_action, update_condition):
if type(update_action) != str:
return False
if type(update_condition) != dict:
return False
sql = "update %s set %s " % (tablename, update_action)
if len(update_condition) > 0:
for (k, v) in update_condition.items():
sql += " where %s = %s " % (k, v)
db = connect_mysql()
try:
cursor = db.cursor()
cursor.execute(sql)
db.commit()
db.close()
return True
except:
db.rollback()
db.close()
return False
def InsertTable(tablename, fieldname_list, fieldvalue_list):
if len(fieldname_list) == 0:
return False
if len(fieldvalue_list) == 0:
return False
sql = "insert into %s(" % tablename
count = 0
for name in fieldname_list:
sql += name
count += 1
if count == len(fieldname_list):
sql += ')'
else:
sql += ','
sql += ' VALUES('
count = 0
for value in fieldvalue_list:
if type(value) == str:
sql += "'"
sql += value
sql += "'"
else:
sql += str(value)
count += 1
if count == len(fieldvalue_list):
sql += ')'
else:
sql += ','
db = connect_mysql()
try:
cursor = db.cursor()
cursor.execute(sql)
db.commit()
db.close()
return True
except:
db.rollback()
db.close()
return False