sqlalchemy一些复杂查询
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="父协议")
# 查询代码如下:
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',)]