def update_password(self, user_id, old_password, new_password, is_log=True): res = yield self.get( "SELECT _id FROM sys_user WHERE _id =3 AND password=%s", (user_id, old_password)) err_code = 0 if res: with StoreContext() as store: ctx = yield store.begin() try: yield ctx.execute( "UPDATE sys_user SET password=%s WHERE _id=%s", (new_password, user_id)) yield ctx.commit() err_code = 1 except Exception as e: err_code = -1 app_log.error(("Update password failed! ", user_id, e)) yield ctx.rollback() if is_log: app_log.info(("Update password success ? ", err_code, user_id)) raise Return(err_code)
def execute_lastrowid(self, query, params=None): """ Executes the given query, returning the lastrowid from the query. """ with StoreContext() as store: cur = yield store.execute(query, params) res = cur.lastrowid raise Return(res)
def execute(self, sql, param=None): with StoreContext() as store: try: yield store.execute(sql, param) flag = True except: app_log.error("execute sql failed, raw_sql[{0}], details: {1}".format(sql, traceback.format_exc())) flag = False raise Return(flag)
def begin(self): """ Start transaction Wait to get connection and returns `Transaction` object. :return: Future[Transaction] :rtype: Future """ with StoreContext() as store: ctx = yield store.begin() raise Return(ctx)
def batch(self, sql, datas): with StoreContext() as store: ctx = yield store.begin() try: for param in datas: yield ctx.execute(sql, param) yield ctx.commit() flag = True except: app_log.error("batch execute sql failed, raw_sql[{0}], details: {1}".format(sql, traceback.format_exc())) yield ctx.rollback() flag = False raise Return(flag)
def update_phone(self, user_id, phone, is_log=True): with StoreContext() as store: ctx = yield store.begin() try: yield ctx.execute("UPDATE sys_user SET phone=%s WHERE _id=%s;", (phone, user_id)) yield ctx.commit() flag = True except Exception as e: app_log.error(("update_phone failed! ", user_id, e)) yield ctx.rollback() flag = False if is_log: app_log.info(("Update phone success? ", flag, user_id, phone)) raise Return(flag)
def activate_user(self, user_id, is_log=False): """ activate user, set flag = 1 """ with StoreContext() as store: ctx = yield store.begin() try: yield ctx.execute( "UPDATE sys_user SET email_token='', flag='1' WHERE _id=%s;", (user_id)) yield ctx.commit() flag = True except: yield ctx.rollback() flag = False if is_log: app_log.info(("activate_user success ? ", flag, user_id)) raise Return(flag)
def add_user(self, email, phone, password, is_log=False): """ 新注册用户,必须phone,email,password三者全部填写,初始暂时把username=email,后面提供修改的接口 """ email_token = 'WDASA#WSCC#544345' reg_timestamp = int(time.time()) with StoreContext() as store: ctx = yield store.begin() try: yield ctx.execute( """INSERT INTO webservice_user (username, email, phone, password, email_token, reg_date, flag) VALUES (%s,%s,%s,%s,%s,%s, 0)""", (email, email, phone, password, email_token, reg_timestamp)) yield ctx.commit() flag = True except: yield ctx.rollback() flag = False if is_log: app_log.info(("add_user success ?", flag, email, phone)) raise Return(flag)
def delete_more_with_deleted(self, ids, deleted=False, is_log=False): """deleted=False假删除,只是改数据库的字段。=True真删除 8.16""" if deleted: with StoreContext() as store: for _id in ids: ctx = yield store.begin() try: yield ctx.execute( """DELETE FROM order_info WHERE _id = %s""", _id) yield ctx.execute( """DELETE FROM order_product_info WHERE _id = %s""", _id) yield ctx.execute( """DELETE FROM order_finance_info WHERE order_id = %s""", _id) yield ctx.execute( """DELETE FROM order_invoice_info WHERE order_id = %s""", _id) yield ctx.execute( """DELETE FROM order_contract_info WHERE order_id = %s""", _id) yield ctx.execute( """DELETE FROM order_receive_info WHERE order_id = %s""", _id) yield ctx.execute( """DELETE FROM order_logistics_info WHERE order_id = %s""", _id) yield ctx.commit() flag = True except: app_log.error( "delete order _id={0} failed, details: {1}".format( _id, traceback.format_exc())) yield ctx.rollback() flag = False else: flag = yield self.batch( """UPDATE order_info SET deleted=1 WHERE _id = %s""", ids) if is_log: app_log.info(("delete ?", flag, ids)) raise Return(flag)
def update_user(self, user_id, key, vaule, is_log=False): """ key = password, email, phone, username # 这些update本来想省事,合成一个接口 Unknown column ''phone'' in 'field list' . 报错,update后面的表列字段不支持这样%s """ if key not in ["password", "email", "phone", "username"]: app_log.error( ("update_user error, the key is not supported ? ", key)) raise Return(False) with StoreContext() as store: ctx = yield store.begin() try: yield ctx.execute( "UPDATE webservice_user SET %s=%s WHERE _id=%s;", (key, vaule, user_id)) yield ctx.commit() flag = True except Exception as e: app_log.error(("update_user failed! ", user_id, key, vaule, e)) yield ctx.rollback() flag = False if is_log: app_log.info(("update_user success ? ", flag, user_id, key, vaule)) raise Return(flag)
def update_one_by_dicts(self, _id, param_dict, is_log=False): """ update 2017.9.5""" order_info_dict = param_dict["order_info_dict"] order_product_info_dict = param_dict["order_product_info_dict"] order_finance_info_dict = param_dict["order_finance_info_dict"] order_invoice_info_dict = param_dict["order_invoice_info_dict"] order_contract_info_dict = param_dict["order_contract_info_dict"] order_receive_info_dict = param_dict["order_receive_info_dict"] order_logistics_info_dict = param_dict["order_logistics_info_dict"] # self.filter_param_delimiting(order_info_dict) # self.filter_param_delimiting(order_finance_info_dict) # self.filter_param_delimiting(order_contract_info_dict) # self.filter_param_delimiting(order_logistics_info_dict) self.filter_param_delimiting_batch([ order_info_dict, order_finance_info_dict, order_contract_info_dict, order_logistics_info_dict ]) sql_01 = self.make_update_sql_by_dict('order_info', _id, order_info_dict) sql_02 = self.make_update_sql_by_dict('order_product_info', _id, order_product_info_dict) sql_03 = self.make_update_sql_by_dict('order_finance_info', _id, order_finance_info_dict, 'order_id') sql_04 = self.make_update_sql_by_dict('order_invoice_info', _id, order_invoice_info_dict, 'order_id') sql_05 = self.make_update_sql_by_dict('order_contract_info', _id, order_contract_info_dict, 'order_id') sql_06 = self.make_update_sql_by_dict('order_receive_info', _id, order_receive_info_dict, 'order_id') sql_07 = self.make_update_sql_by_dict('order_logistics_info', _id, order_logistics_info_dict, 'order_id') app_log.info((sql_01, order_info_dict)) app_log.info((sql_02, order_product_info_dict)) app_log.info((sql_03, order_finance_info_dict)) app_log.info((sql_04, order_invoice_info_dict)) app_log.info((sql_05, order_contract_info_dict)) app_log.info((sql_06, order_receive_info_dict)) app_log.info((sql_07, order_logistics_info_dict)) with StoreContext() as store: ctx = yield store.begin() try: yield store.execute(sql_01, tuple(order_info_dict.values())) yield store.execute(sql_02, tuple(order_product_info_dict.values())) yield store.execute(sql_03, tuple(order_finance_info_dict.values())) yield store.execute(sql_04, tuple(order_invoice_info_dict.values())) yield store.execute(sql_05, tuple(order_contract_info_dict.values())) yield store.execute(sql_06, tuple(order_receive_info_dict.values())) yield store.execute(sql_07, tuple(order_logistics_info_dict.values())) yield ctx.commit() flag = True except: app_log.error("update failed, details: {0}".format( traceback.format_exc())) yield ctx.rollback() flag = False if is_log: app_log.info(("update order ?", flag, _id, param_dict)) raise Return(flag)
def query(self, query, params=None): """Returns a row list for the given query and parameters.""" with StoreContext() as store: cur = yield store.execute(query, params) res = cur.fetchall() raise Return(res)
def execute_rowcount(self, query, params=None): """Executes the given query, returning the rowcount from the query.""" with StoreContext() as store: cur = yield store.execute(query, params) res = cur.rowcount raise Return(res)