本文整理汇总了Python中app.base.db.SQL类的典型用法代码示例。如果您正苦于以下问题:Python SQL类的具体用法?Python SQL怎么用?Python SQL使用的例子?那么, 这里精选的类代码示例或许可以为您提供帮助。
在下文中一共展示了SQL类的15个代码示例,这些例子默认根据受欢迎程度排序。您可以为喜欢或者感觉有用的代码点赞,您的评价将有助于系统推荐出更棒的Python代码示例。
示例1: get_policies
def get_policies(sql_filter, sql_order, sql_limit):
dbtp = get_db().table_prefix
s = SQL(get_db())
s.select_from('audit_policy', ['id', 'rank', 'name', 'desc', 'state'], alt_name='p')
str_where = ''
_where = list()
if len(sql_filter) > 0:
for k in sql_filter:
if k == 'search':
_where.append('(p.name LIKE "%{filter}%" OR p.desc LIKE "%{filter}%")'.format(filter=sql_filter[k]))
if k == 'state':
_where.append('p.state={}'.format(sql_filter[k]))
else:
log.e('unknown filter field: {}\n'.format(k))
return TPE_PARAM, s.total_count, 0, s.recorder
if len(_where) > 0:
str_where = '( {} )'.format(' AND '.join(_where))
s.where(str_where)
s.order_by('p.rank', True)
if len(sql_limit) > 0:
s.limit(sql_limit['page_index'], sql_limit['per_page'])
err = s.query()
return err, s.total_count, s.page_index, s.recorder
示例2: set_password
def set_password(handler, user_id, password):
db = get_db()
operator = handler.get_current_user()
# print('----------', operator)
# 1. get user info (user name)
s = SQL(db)
err = s.reset().select_from('user', ['username', 'surname']).where('user.id={}'.format(user_id)).query()
if err != TPE_OK:
return err
if len(s.recorder) == 0:
return TPE_NOT_EXISTS
name = s.recorder[0]['username']
surname = s.recorder[0]['surname']
if len(surname) == 0:
surname = name
sql = 'UPDATE `{}user` SET password="{password}" WHERE id={user_id};' \
''.format(db.table_prefix, password=password, user_id=user_id)
db_ret = db.exec(sql)
if not db_ret:
return TPE_DATABASE
if operator['id'] == 0:
syslog.sys_log({'username': name, 'surname': surname}, handler.request.remote_ip, TPE_OK,
"用户 {} 通过邮件方式重置了密码".format(name))
else:
syslog.sys_log(operator, handler.request.remote_ip, TPE_OK, "为用户 {} 手动重置了密码".format(name))
return TPE_OK
示例3: get_users_by_type
def get_users_by_type(_type):
s = SQL(get_db())
err = s.select_from('user', ['id', 'type', 'ldap_dn'], alt_name='u').where('u.type={}'.format(_type)).query()
if err != TPE_OK:
return None
if len(s.recorder) == 0:
return None
return s.recorder
示例4: create_users
def create_users(handler, user_list, success, failed):
"""
批量创建用户
"""
db = get_db()
_time_now = tp_timestamp_utc_now()
operator = handler.get_current_user()
name_list = list()
s = SQL(db)
for i in range(len(user_list)):
user = user_list[i]
if 'type' not in user:
user['type'] = TP_USER_TYPE_LOCAL
if 'ldap_dn' not in user:
user['ldap_dn'] = ''
err = s.reset().select_from('user', ['id']).where('user.username="{}"'.format(user['username'])).query()
if err != TPE_OK:
failed.append({'line': user['_line'], 'error': '数据库查询失败'})
if len(s.recorder) > 0:
failed.append({'line': user['_line'], 'error': '账号 `{}` 已经存在'.format(user['username'])})
continue
if user['type'] == TP_USER_TYPE_LOCAL:
_password = tp_password_generate_secret(user['password'])
else:
_password = ''
sql = 'INSERT INTO `{}user` (' \
'`role_id`, `username`, `surname`, `type`, `ldap_dn`, `auth_type`, `password`, ' \
'`state`, `email`, `creator_id`, `create_time`, `last_login`, `last_chpass`, `desc`' \
') VALUES (' \
'0, "{username}", "{surname}", {user_type}, "{ldap_dn}", 0, "{password}", ' \
'{state}, "{email}", {creator_id}, {create_time}, {last_login}, {last_chpass}, "{desc}");' \
''.format(db.table_prefix, username=user['username'], surname=user['surname'], user_type=user['type'],
ldap_dn=user['ldap_dn'], password=_password, state=TP_STATE_NORMAL, email=user['email'],
creator_id=operator['id'], create_time=_time_now, last_login=0, last_chpass=_time_now,
desc=user['desc'])
db_ret = db.exec(sql)
if not db_ret:
failed.append({'line': user['_line'], 'error': '写入数据库时发生错误'})
continue
success.append(user['username'])
name_list.append(user['username'])
user['_id'] = db.last_insert_id()
if len(name_list) > 0:
syslog.sys_log(operator, handler.request.remote_ip, TPE_OK, "批量导入方式创建用户:{}".format(','.join(name_list)))
# tp_stats().user_counter_change(len(name_list))
# calc count of users.
err, cnt = s.reset().count('user')
if err == TPE_OK:
tp_stats().user_counter_change(cnt)
示例5: remove_members
def remove_members(handler, policy_id, policy_type, ids):
s = SQL(get_db())
auz_ids = [str(i) for i in ids]
# 将用户从所在组中移除
where = 'policy_id={} AND type={} AND id IN ({})'.format(policy_id, policy_type, ','.join(auz_ids))
err = s.reset().delete_from('ops_auz').where(where).exec()
if err != TPE_OK:
return err
#return TPE_OK
return policy.rebuild_ops_auz_map()
示例6: create_user
def create_user(handler, user):
"""
创建一个用户账号
"""
db = get_db()
_time_now = tp_timestamp_utc_now()
operator = handler.get_current_user()
if 'type' not in user:
user['type'] = TP_USER_TYPE_LOCAL
if 'ldap_dn' not in user:
user['ldap_dn'] = ''
# 1. 判断此账号是否已经存在了
s = SQL(db)
err = s.reset().select_from('user', ['id']).where('user.username="{}"'.format(user['username'])).query()
if err != TPE_OK:
return err, 0
if len(s.recorder) > 0:
return TPE_EXISTS, 0
# _password = tp_password_generate_secret(user['password'])
if user['type'] == TP_USER_TYPE_LOCAL:
_password = tp_password_generate_secret(user['password'])
else:
_password = ''
sql = 'INSERT INTO `{}user` (' \
'`role_id`, `username`, `surname`, `type`, `ldap_dn`, `auth_type`, `password`, `state`, ' \
'`email`, `creator_id`, `create_time`, `last_login`, `last_chpass`, `desc`' \
') VALUES (' \
'{role}, "{username}", "{surname}", {user_type}, "{ldap_dn}", {auth_type}, "{password}", {state}, ' \
'"{email}", {creator_id}, {create_time}, {last_login}, {last_chpass}, "{desc}");' \
''.format(db.table_prefix, role=user['role'], username=user['username'], surname=user['surname'],
user_type=user['type'], ldap_dn=user['ldap_dn'], auth_type=user['auth_type'], password=_password,
state=TP_STATE_NORMAL, email=user['email'], creator_id=operator['id'], create_time=_time_now,
last_login=0, last_chpass=_time_now, desc=user['desc'])
db_ret = db.exec(sql)
if not db_ret:
return TPE_DATABASE, 0
_id = db.last_insert_id()
syslog.sys_log(operator, handler.request.remote_ip, TPE_OK, "创建用户:{}".format(user['username']))
# calc count of users.
err, cnt = s.reset().count('user')
if err == TPE_OK:
tp_stats().user_counter_change(cnt)
return TPE_OK, _id
示例7: get_auth
def get_auth(auth_id):
db = get_db()
s = SQL(db)
err = s.select_from('ops_map', ['id', 'p_id', 'h_id', 'u_id', 'a_id']).where('ops_map.uni_id="{}"'.format(auth_id)).query()
if err != TPE_OK:
return None, err
if len(s.recorder) == 0:
return None, TPE_NOT_EXISTS
# if len(s.recorder) != 1:
# return None, TPE_FAILED
# log.v(s.recorder[0])
return s.recorder[0], TPE_OK
示例8: remove_users
def remove_users(handler, users):
db = get_db()
s = SQL(db)
str_users = ','.join([str(i) for i in users])
# 1. 获取用户名称,用于记录系统日志
where = 'u.id IN ({})'.format(str_users)
err = s.select_from('user', ['username'], alt_name='u').where(where).query()
if err != TPE_OK:
return err
if len(s.recorder) == 0:
return TPE_NOT_EXISTS
str_names = ','.join([n['username'] for n in s.recorder])
sql_list = []
# 将用户从所在组中移除
sql = 'DELETE FROM `{tpdp}group_map` WHERE type={t} AND mid IN ({ids});' \
''.format(tpdp=db.table_prefix, t=TP_GROUP_USER, ids=str_users)
sql_list.append(sql)
# 删除用户
sql = 'DELETE FROM `{tpdp}user` WHERE id IN ({ids});'.format(tpdp=db.table_prefix, ids=str_users)
sql_list.append(sql)
# 将用户从运维授权中移除
sql = 'DELETE FROM `{}ops_auz` WHERE rtype={rtype} AND rid IN ({ids});' \
''.format(db.table_prefix, rtype=TP_USER, ids=str_users)
sql_list.append(sql)
sql = 'DELETE FROM `{}ops_map` WHERE u_id IN ({ids});'.format(db.table_prefix, ids=str_users)
sql_list.append(sql)
# 将用户从审计授权中移除
sql = 'DELETE FROM `{}audit_auz` WHERE rtype={rtype} AND rid IN ({ids});' \
''.format(db.table_prefix, rtype=TP_USER, ids=str_users)
sql_list.append(sql)
sql = 'DELETE FROM `{}audit_map` WHERE u_id IN ({ids});'.format(db.table_prefix, ids=str_users)
sql_list.append(sql)
if not db.transaction(sql_list):
return TPE_DATABASE
syslog.sys_log(handler.get_current_user(), handler.request.remote_ip, TPE_OK, "删除用户:{}".format(str_names))
# calc count of users.
err, cnt = s.reset().count('user')
if err == TPE_OK:
tp_stats().user_counter_change(cnt)
return TPE_OK
示例9: rank_reorder
def rank_reorder(handler, pid, new_rank, start_rank, end_rank, direct):
db = get_db()
# 调节顺序:
# 由pid获取被移动的策略,得到其rank,即,p_rank
# p_rank > new_rank,向前移动
# 所有 new_rank <= rank < p_rank 的条目,其rank+1
# p_rank < new_rank,向后移动
# 所有 new_rank >= rank > p_rank 的条目,其rank-1
# 最后令pid条目的rank为new_rank
# 1. 判断此账号是否已经存在
s = SQL(db)
err = s.select_from('ops_policy', ['id', 'name', 'rank']).where('ops_policy.id={}'.format(pid)).query()
if err != TPE_OK:
return err
if len(s.recorder) == 0:
return TPE_NOT_EXISTS
p_name = s.recorder[0]['name']
p_rank = s.recorder[0]['rank']
# if p_rank > new_rank:
# compare = '>'
# if insert_before:
# compare = '>='
# sql = 'UPDATE `{dbtp}ops_policy` SET rank=rank+1 WHERE (rank{compare}{new_rank} AND rank<{p_rank});' \
# ''.format(dbtp=db.table_prefix, compare=compare, new_rank=new_rank, p_rank=p_rank)
# else:
# compare = '<'
# if insert_before:
# compare = '<='
# sql = 'UPDATE `{dbtp}ops_policy` SET rank=rank-1 WHERE (rank{compare}{new_rank} AND rank>{p_rank});' \
# ''.format(dbtp=db.table_prefix, compare=compare, new_rank=new_rank, p_rank=p_rank)
sql = 'UPDATE `{dbtp}ops_policy` SET rank=rank{direct} WHERE (rank>={start_rank} AND rank<={end_rank});' \
''.format(dbtp=db.table_prefix, direct=direct, start_rank=start_rank, end_rank=end_rank)
db_ret = db.exec(sql)
if not db_ret:
return TPE_DATABASE
sql = 'UPDATE `{dbtp}ops_policy` SET rank={new_rank} WHERE id={pid};' \
''.format(dbtp=db.table_prefix, new_rank=new_rank, pid=pid)
db_ret = db.exec(sql)
if not db_ret:
return TPE_DATABASE
syslog.sys_log(handler.get_current_user(), handler.request.remote_ip, TPE_OK, "调整运维授权策略顺序:{},从{}到{}".format(p_name, p_rank, new_rank))
return policy.rebuild_ops_auz_map()
示例10: get_host_accounts
def get_host_accounts(host_id):
# 获取指定主机的所有账号
s = SQL(get_db())
# s.select_from('acc', ['id', 'state', 'host_ip', 'router_ip', 'router_port', 'protocol_type', 'protocol_port', 'auth_type', 'username', 'pri_key'], alt_name='a')
s.select_from('acc', ['id', 'state', 'protocol_type', 'protocol_port', 'auth_type', 'username', 'username_prompt', 'password_prompt'], alt_name='a')
s.where('a.host_id={}'.format(host_id))
s.order_by('a.username', True)
err = s.query()
return err, s.recorder
示例11: generate_reset_password_token
def generate_reset_password_token(handler, user_id):
db = get_db()
operator = handler.get_current_user()
s = SQL(db)
_time_now = tp_timestamp_utc_now()
# 0. query user's email by user_id
err = s.select_from('user', ['email'], alt_name='u').where('u.id={user_id}'.format(user_id=user_id)).query()
if err != TPE_OK:
return err, None, None
if len(s.recorder) == 0:
return TPE_DATABASE, None, None
email = s.recorder[0].email
# 1. clean all timed out tokens.
s.reset().delete_from('user_rpt').where('create_time<{}'.format(_time_now - 24 * 60 * 60)).exec()
# 2. find out if this user already have a token.
err = s.reset().select_from('user_rpt', ['id'], alt_name='u').where('u.user_id={}'.format(user_id)).query()
if err != TPE_OK:
return err, None, None
token = tp_generate_random(16)
if len(s.recorder) == 0:
sql = 'INSERT INTO `{dbtp}user_rpt` (user_id, token, create_time) VALUES ' \
'({user_id}, "{token}", {create_time});' \
''.format(dbtp=db.table_prefix, user_id=user_id, token=token, create_time=_time_now)
db_ret = db.exec(sql)
if not db_ret:
return TPE_DATABASE, None, None
else:
sql = 'UPDATE `{dbtp}user_rpt` SET token="{token}", create_time={create_time} WHERE user_id={user_id};' \
''.format(dbtp=db.table_prefix, token=token, create_time=_time_now, user_id=user_id)
db_ret = db.exec(sql)
if not db_ret:
return TPE_DATABASE, None, None
# syslog.sys_log(operator, handler.request.remote_ip, TPE_OK, "为用户 {} 手动重置了密码".format(name))
return TPE_OK, email, token
示例12: set_flags
def set_flags(self, policy_id, flag_record, flag_rdp, flag_ssh):
db = get_db()
# 1. 判断此账号是否已经存在
s = SQL(db)
err = s.select_from('ops_policy', ['id']).where('ops_policy.id={}'.format(policy_id)).query()
if err != TPE_OK:
return err
if len(s.recorder) == 0:
return TPE_NOT_EXISTS
sql = 'UPDATE `{}ops_policy` SET flag_record={flag_record}, flag_rdp={flag_rdp}, flag_ssh={flag_ssh} WHERE id={p_id};' \
''.format(db.table_prefix,
flag_record=flag_record, flag_rdp=flag_rdp, flag_ssh=flag_ssh, p_id=policy_id
)
db_ret = db.exec(sql)
if not db_ret:
return TPE_DATABASE
return TPE_OK
示例13: update_policy
def update_policy(handler, args):
db = get_db()
# 1. 判断此账号是否已经存在
s = SQL(db)
err = s.reset().select_from('ops_policy', ['id']).where('ops_policy.id={}'.format(args['id'])).query()
if err != TPE_OK:
return err
if len(s.recorder) == 0:
return TPE_NOT_EXISTS
sql = 'UPDATE `{}ops_policy` SET `name`="{name}", `desc`="{desc}" WHERE `id`={p_id};' \
''.format(db.table_prefix,
name=args['name'], desc=args['desc'], p_id=args['id']
)
db_ret = db.exec(sql)
if not db_ret:
return TPE_DATABASE
return TPE_OK
示例14: get_logs
def get_logs(sql_filter, sql_order, sql_limit):
s = SQL(get_db())
s.select_from('syslog', ['id', 'user_name', 'user_surname', 'client_ip', 'code', 'log_time', 'message'], alt_name='l')
str_where = ''
_where = list()
if len(sql_filter) > 0:
for k in sql_filter:
if k == 'log_user_name':
_where.append('l.user_name="{}"'.format(sql_filter[k]))
# elif k == 'search_record':
# _where.append('(h.name LIKE "%{}%" OR h.ip LIKE "%{}%" OR h.router_addr LIKE "%{}%" OR h.desc LIKE "%{}%" OR h.cid LIKE "%{}%")'.format(sql_filter[k], sql_filter[k], sql_filter[k], sql_filter[k], sql_filter[k]))
if len(_where) > 0:
str_where = '( {} )'.format(' AND '.join(_where))
s.where(str_where)
if sql_order is not None:
_sort = False if not sql_order['asc'] else True
if 'log_time' == sql_order['name']:
s.order_by('l.log_time', _sort)
# elif 'name' == sql_order['name']:
# s.order_by('h.name', _sort)
# elif 'os_type' == sql_order['name']:
# s.order_by('h.os_type', _sort)
# elif 'cid' == sql_order['name']:
# s.order_by('h.cid', _sort)
# elif 'state' == sql_order['name']:
# s.order_by('h.state', _sort)
else:
log.e('unknown order field: {}\n'.format(sql_order['name']))
return TPE_PARAM, s.total_count, s.recorder
if len(sql_limit) > 0:
s.limit(sql_limit['page_index'], sql_limit['per_page'])
err = s.query()
return err, s.total_count, s.recorder
示例15: get_list
def get_list(gtype):
s = SQL(get_db())
s.select_from('group', ['id', 'name'], alt_name='g')
s.where('g.type={}'.format(gtype))
err = s.query()
return err, s.recorder