sqlalchemy-运算符like,notlike,in_,notin_,is_,isnot,startswith,endswith,contains,desc,asc,between,distin

缺乏、安全感 2021-12-01 13:18 497阅读 0赞

## like,notlike,in\_,notin\_,is\_,isnot,startswith,endswith,contains,desc,asc,between,distinct ##

本次数据查询参考模型类和数据如下：（Flask环境）

class Protocols(db.Model)
     id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=False, comment="主键id")
     protocolName = db.Column(db.String(255), unique=True, comment="协议名称")
     parent_protocol = db.Column(db.String(255), comment="父协议")

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d5bWFpc3ls_size_16_color_FFFFFF_t_70]

# 查询代码如下：
     	0.like:模糊查询
        result0 = db.session.query(Protocols.protocolName).filter(Protocols.protocolName.like("%" + "cp" + "%")).all()
        1.notlike：模糊查询，不在查询范围内
        result1 = db.session.query(Protocols.protocolName).filter(Protocols.protocolName.notlike("%" + "cp" + "%")).all()
        2.in_:在某个范围内，参数为元组或者列表类型的数据
        result2 = db.session.query(Protocols.protocolName).filter(Protocols.id.in_((1, 2))).all()
        3.notin_：和in_相反
        result3 = db.session.query(Protocols.protocolName).filter(Protocols.id.notin_((1, 2))).all()
        4.is_:是否为null的比较
        result4 = db.session.query(Protocols.protocolName).filter(Protocols.protocolName.is_(None)).all()
        5.isnot:不为null
        result5 = db.session.query(Protocols.protocolName).filter(Protocols.protocolName.isnot(None)).all()
        6.startswith：以某个数据开头
        result6 = db.session.query(Protocols.protocolName).filter(Protocols.protocolName.startswith("t")).all()
        7.endswith：以某数据结尾
        result7 = db.session.query(Protocols.protocolName).filter(Protocols.protocolName.endswith("t")).all()
        8.contains：数据中包含，和like功能差不多
        result8 = db.session.query(Protocols.protocolName).filter(Protocols.protocolName.contains("cp")).all()
        9.desc：对查询出来的数据进行降序排序
        result9 = db.session.query(Protocols.protocolName).order_by(Protocols.id.desc()).all()
        10.asc：对查询出来的数据进行升序排序
        result10 = db.session.query(Protocols.protocolName).order_by(Protocols.id.asc()).all()
        11.between：某个字段的参数在某个范围内
        result11 = db.session.query(Protocols.protocolName).filter(Protocols.id.between(1, 3)).all()
        12.distinct：对查询出来的数据进行去重
        result12 = db.session.query(Protocols.parent_protocol).distinct().all()

上面查询语句获取的结果

[('dhcp',), ('tcp',)]
    [('ah',), ('atsvc',), ('bootpc',), ('bootps',), ('browser',), ('dns',), ('esp',), ('ftp',), ('http',), ('https',), ('icmp',), ('imap',), ('netbios-dgm',), ('netbios-ns',), ('netbios-ssn',), ('netstat',), ('ntp',), ('pop3',), ('rdp',), ('rtsp',), ('sctp',), ('smb',), ('smtp',), ('snmp',), ('snmp trap',), ('ssh',), ('telent',), ('tftp',), ('udp',), ('vnc',)]
    [('ssh',), ('https',)]
    [('telent',), ('rdp',), ('ntp',), ('http',), ('ftp',), ('vnc',), ('smb',), ('smtp',), ('pop3',), ('imap',), ('netstat',), ('netbios-ns',), ('netbios-dgm',), ('netbios-ssn',), ('browser',), ('atsvc',), ('rtsp',), ('dns',), ('bootpc',), ('snmp',), ('snmp trap',), ('bootps',), ('tftp',), ('dhcp',), ('icmp',), ('tcp',), ('udp',), ('esp',), ('ah',), ('sctp',)]
    []
    [('ah',), ('atsvc',), ('bootpc',), ('bootps',), ('browser',), ('dhcp',), ('dns',), ('esp',), ('ftp',), ('http',), ('https',), ('icmp',), ('imap',), ('netbios-dgm',), ('netbios-ns',), ('netbios-ssn',), ('netstat',), ('ntp',), ('pop3',), ('rdp',), ('rtsp',), ('sctp',), ('smb',), ('smtp',), ('snmp',), ('snmp trap',), ('ssh',), ('tcp',), ('telent',), ('tftp',), ('udp',), ('vnc',)]
    [('tcp',), ('telent',), ('tftp',)]
    [('netstat',), ('telent',)]
    [('dhcp',), ('tcp',)]
    [('sctp',), ('ah',), ('esp',), ('udp',), ('tcp',), ('icmp',), ('dhcp',), ('tftp',), ('bootps',), ('snmp trap',), ('snmp',), ('bootpc',), ('dns',), ('rtsp',), ('atsvc',), ('browser',), ('netbios-ssn',), ('netbios-dgm',), ('netbios-ns',), ('netstat',), ('imap',), ('pop3',), ('smtp',), ('smb',), ('vnc',), ('ftp',), ('http',), ('ntp',), ('rdp',), ('telent',), ('https',), ('ssh',)]
    [('ssh',), ('https',), ('telent',), ('rdp',), ('ntp',), ('http',), ('ftp',), ('vnc',), ('smb',), ('smtp',), ('pop3',), ('imap',), ('netstat',), ('netbios-ns',), ('netbios-dgm',), ('netbios-ssn',), ('browser',), ('atsvc',), ('rtsp',), ('dns',), ('bootpc',), ('snmp',), ('snmp trap',), ('bootps',), ('tftp',), ('dhcp',), ('icmp',), ('tcp',), ('udp',), ('esp',), ('ah',), ('sctp',)]
    [('ssh',), ('https',), ('telent',)]
    [('tcp',), ('tcp,udp',), ('udp',), ('ip',)]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d5bWFpc3ls_size_16_color_FFFFFF_t_70]: /images/20211201/f9fe8cbd728a48bdb7e30aed5f2f2592.png