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)
