Python SQL.where方法代码示例

# 需要导入模块: from app.base.db import SQL [as 别名]
# 或者: from app.base.db.SQL import where [as 别名]
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

# 需要导入模块: from app.base.db import SQL [as 别名]
# 或者: from app.base.db.SQL import where [as 别名]
def remove_role(handler, role_id):
    db = get_db()

    s = SQL(db)
    # 1. 判断是否存在
    s.select_from('role', ['name'], alt_name='r')
    s.where('r.id={rid}'.format(rid=role_id))
    err = s.query()
    if err != TPE_OK:
        return err
    if len(s.recorder) == 0:
        return TPE_NOT_EXISTS

    role_name = s.recorder[0].name

    sql_list = []

    sql = 'DELETE FROM `{}role` WHERE id={};'.format(db.table_prefix, role_id)
    sql_list.append(sql)

    # 更新此角色相关的用户信息
    sql = 'UPDATE `{}user` SET role_id=0 WHERE role_id={rid};'.format(db.table_prefix, rid=role_id)
    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(role_name))

    return TPE_OK

# 需要导入模块: from app.base.db import SQL [as 别名]
# 或者: from app.base.db.SQL import where [as 别名]
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

# 需要导入模块: from app.base.db import SQL [as 别名]
# 或者: from app.base.db.SQL import where [as 别名]
def get_by_id(gtype, gid):
    # 获取要查询的组的信息
    s = SQL(get_db())
    s.select_from('group', ['id', 'state', 'name', 'desc'], alt_name='g')
    s.where('g.type={} AND g.id={}'.format(gtype, gid))
    err = s.query()
    if err != TPE_OK:
        return err, {}
    if len(s.recorder) == 0:
        return TPE_NOT_EXISTS, {}
    return TPE_OK, s.recorder[0]

# 需要导入模块: from app.base.db import SQL [as 别名]
# 或者: from app.base.db.SQL import where [as 别名]
def get_host_info(host_id):
    s = SQL(get_db())
    s.select_from('host', ['id', 'type', 'ip', 'router_ip', 'router_port', 'state'], alt_name='h')
    s.where('h.id={}'.format(host_id))
    err = s.query()
    if err != TPE_OK:
        return err, None
    if len(s.recorder) != 1:
        return TPE_DATABASE, None

    return TPE_OK, s.recorder[0]

# 需要导入模块: from app.base.db import SQL [as 别名]
# 或者: from app.base.db.SQL import where [as 别名]
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

# 需要导入模块: from app.base.db import SQL [as 别名]
# 或者: from app.base.db.SQL import where [as 别名]
def get_by_id(pid):
    s = SQL(get_db())
    s.select_from('ops_policy', ['id', 'name', 'desc', 'flag_record', 'flag_rdp', 'flag_ssh', 'flag_telnet'], alt_name='p')
    s.where('p.id={}'.format(pid))
    err = s.query()
    if err != TPE_OK:
        return err, {}

    # if len(s.recorder) == 0:
    #     return TPE_NOT_EXISTS, {}

    return TPE_OK, s.recorder[0]

# 需要导入模块: from app.base.db import SQL [as 别名]
# 或者: from app.base.db.SQL import where [as 别名]
def get_by_id(pid):
    s = SQL(get_db())
    s.select_from('audit_policy', ['id', 'name', 'desc'], alt_name='p')
    s.where('p.id={}'.format(pid))
    err = s.query()
    if err != TPE_OK:
        return err, {}

    if len(s.recorder) == 0:
        return TPE_NOT_EXISTS, {}

    return TPE_OK, s.recorder[0]

# 需要导入模块: from app.base.db import SQL [as 别名]
# 或者: from app.base.db.SQL import where [as 别名]
def get_host_groups_for_user(user_id, user_privilege):
    # get all host-groups for current logged in user.

    db = get_db()

    # step 0. return all host-groups if user have all host-group access privilege
    if (user_privilege & (TP_PRIVILEGE_ASSET_CREATE | TP_PRIVILEGE_ASSET_DELETE | TP_PRIVILEGE_ASSET_GROUP)) != 0:
        s = SQL(get_db())
        s.select_from('group', ['id', 'name'], alt_name='g')
        s.where('g.type={}'.format(TP_GROUP_HOST))
        s.order_by('g.name')
        err = s.query()

        return err, s.recorder

    # step 1. get all hosts which could be access by this user.
    sql = 'SELECT `h_id` FROM `{dbtp}ops_map` WHERE `u_id`={dbph} GROUP BY `h_id`;'.format(dbtp=db.table_prefix, dbph=db.place_holder)
    db_ret = db.query(sql, (user_id, ))
    if db_ret is None or len(db_ret) == 0:
        return TPE_NOT_EXISTS, None

    hosts = []
    for db_item in db_ret:
        hosts.append(str(db_item[0]))

    if len(hosts) == 0:
        return TPE_NOT_EXISTS, None

    # step 2. get groups which include those hosts.
    sql = 'SELECT `gid` FROM `{dbtp}group_map` WHERE (`type`={gtype} AND `mid` IN ({hids})) GROUP BY `gid`;'.format(dbtp=db.table_prefix, gtype=TP_GROUP_HOST, hids=','.join(hosts))
    db_ret = db.query(sql)

    if db_ret is None or len(db_ret) == 0:
        return TPE_NOT_EXISTS, None

    groups = []
    for db_item in db_ret:
        groups.append(str(db_item[0]))

    # step 3. get those groups id and name.
    s = SQL(get_db())
    s.select_from('group', ['id', 'name'], alt_name='g')
    s.where('g.id IN ({})'.format(','.join(groups)))
    s.order_by('g.name')
    err = s.query()

    return err, s.recorder

# 需要导入模块: from app.base.db import SQL [as 别名]
# 或者: from app.base.db.SQL import where [as 别名]
def get_by_username(username):
    s = SQL(get_db())
    s.select_from('user',
                  ['id', 'type', 'auth_type', 'username', 'surname', 'ldap_dn', 'password', 'oath_secret', 'role_id',
                   'state', 'fail_count', 'lock_time', 'email', 'create_time', 'last_login', 'last_ip', 'last_chpass',
                   'mobile', 'qq', 'wechat', 'desc'], alt_name='u')
    s.left_join('role', ['name', 'privilege'], join_on='r.id=u.role_id', alt_name='r', out_map={'name': 'role'})
    s.where('u.username="{}"'.format(username))
    err = s.query()
    if err != TPE_OK:
        return err

    if len(s.recorder) == 0:
        return TPE_NOT_EXISTS, {}

    if s.recorder[0]['privilege'] is None:
        s.recorder[0]['privilege'] = 0

    return TPE_OK, s.recorder[0]

# 需要导入模块: from app.base.db import SQL [as 别名]
# 或者: from app.base.db.SQL import where [as 别名]
def get_user_info(user_id):
    """
    获取一个指定的用户的详细信息，包括关联的角色的详细信息、所属组的详细信息等等
    """
    s = SQL(get_db())
    s.select_from('user',
                  ['id', 'type', 'auth_type', 'username', 'surname', 'ldap_dn', 'password', 'oath_secret', 'role_id',
                   'state', 'fail_count', 'lock_time', 'email', 'create_time', 'last_login', 'last_ip', 'last_chpass',
                   'mobile', 'qq', 'wechat', 'desc'], alt_name='u')
    s.left_join('role', ['name', 'privilege'], join_on='r.id=u.role_id', alt_name='r', out_map={'name': 'role'})
    s.where('u.id="{}"'.format(user_id))
    err = s.query()
    if err != TPE_OK:
        return err, {}

    if len(s.recorder) == 0:
        return TPE_NOT_EXISTS, {}

    return TPE_OK, s.recorder[0]

# 需要导入模块: from app.base.db import SQL [as 别名]
# 或者: from app.base.db.SQL import where [as 别名]
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

# 需要导入模块: from app.base.db import SQL [as 别名]
# 或者: from app.base.db.SQL import where [as 别名]
def add_members(handler, policy_id, policy_type, ref_type, members):
    # step 1: select exists rid.
    s = SQL(get_db())
    s.select_from('audit_auz', ['rid'], alt_name='p')
    _where = list()
    _where.append('p.policy_id={}'.format(policy_id))
    _where.append('p.type={}'.format(policy_type))
    _where.append('p.rtype={}'.format(ref_type))
    s.where('( {} )'.format(' AND '.join(_where)))
    err = s.query()
    if err != TPE_OK:
        return err
    exists_ids = [r['rid'] for r in s.recorder]

    operator = handler.get_current_user()

    db = get_db()
    _time_now = tp_timestamp_utc_now()

    sql = []
    # for uid in members:
    #     sql.append('INSERT INTO `{}group_map` (type, gid, mid) VALUES ({}, {}, {});'.format(db.table_prefix, gtype, gid, uid))
    # print(args['members'])
    for m in members:
        if m['id'] in exists_ids:
            continue
        str_sql = 'INSERT INTO `{}audit_auz` (policy_id, type, rtype, rid, `name`, creator_id, create_time) VALUES ' \
                  '({pid}, {t}, {rtype}, {rid}, "{name}", {creator_id}, {create_time});' \
                  ''.format(db.table_prefix,
                            pid=policy_id, t=policy_type, rtype=ref_type,
                            rid=m['id'], name=m['name'],
                            creator_id=operator['id'], create_time=_time_now)
        sql.append(str_sql)

    if db.transaction(sql):
        # return TPE_OK
        return policy.rebuild_audit_auz_map()
    else:
        return TPE_DATABASE

# 需要导入模块: from app.base.db import SQL [as 别名]
# 或者: from app.base.db.SQL import where [as 别名]
def get_asset(sql_filter, sql_order, sql_limit):
    ss = SQL(get_db())
    ss.select_from('ops_auz', ['id', 'policy_id', 'rtype', 'rid', 'name'], alt_name='p')

    _where = list()
    _where.append('p.type=1')
    if len(sql_filter) > 0:
        for k in sql_filter:
            if k == 'policy_id':
                # _where.append('(p.name LIKE "%{filter}%" OR p.desc LIKE "%{filter}%")'.format(filter=sql_filter[k]))
                _where.append('p.policy_id={}'.format(sql_filter[k]))
            elif k == 'search':
                _where.append('(p.name LIKE "%{filter}%")'.format(filter=sql_filter[k]))
            else:
                log.e('unknown filter field: {}\n'.format(k))
                return TPE_PARAM, 0, 0, {}
    if len(_where) > 0:
        ss.where('( {} )'.format(' AND '.join(_where)))

    if sql_order is not None:
        _sort = False if not sql_order['asc'] else True
        if 'name' == sql_order['name']:
            ss.order_by('p.name', _sort)
        elif 'rtype' == sql_order['name']:
            ss.order_by('p.rtype', _sort)
        else:
            log.e('unknown order field: {}\n'.format(sql_order['name']))
            return TPE_PARAM, ss.total_count, 0, ss.recorder

    if len(sql_limit) > 0:
        ss.limit(sql_limit['page_index'], sql_limit['per_page'])

    err = ss.query()
    if err != TPE_OK:
        return err, 0, 0, {}

    # print(ss.recorder)
    return TPE_OK, ss.total_count, ss.page_index, ss.recorder

# 需要导入模块: from app.base.db import SQL [as 别名]
# 或者: from app.base.db.SQL import where [as 别名]
def get_account_info(acc_id):
    s = SQL(get_db())
    # s.select_from('acc', ['id', 'password', 'pri_key', 'state', 'host_ip', 'router_ip', 'router_port', 'protocol_type', 'protocol_port', 'auth_type', 'username'], alt_name='a')
    s.select_from('acc', ['id', 'password', 'pri_key', 'state', 'host_id', 'protocol_type', 'protocol_port', 'auth_type', 'username', 'username_prompt', 'password_prompt'], alt_name='a')
    s.where('a.id={}'.format(acc_id))
    err = s.query()
    if err != TPE_OK:
        return err, None
    if len(s.recorder) != 1:
        return TPE_DATABASE, None

    sh = SQL(get_db())
    sh.select_from('host', ['id', 'name', 'ip', 'router_ip', 'router_port', 'state'], alt_name='h')
    sh.where('h.id={}'.format(s.recorder[0].host_id))
    err = sh.query()
    if err != TPE_OK:
        return err, None
    if len(s.recorder) != 1:
        return TPE_DATABASE, None

    s.recorder[0]['_host'] = sh.recorder[0]

    return TPE_OK, s.recorder[0]