sqlalchemy中，使用concat（），concat_ws（），group_concat（）

朴灿烈づ我的快乐病毒、 2021-12-01 12:02 527阅读 0赞

原生sql语句，参考：[https://blog.csdn.net/gymaisyl/article/details/96478282][https_blog.csdn.net_gymaisyl_article_details_96478282]  
原始数据库数据：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d5bWFpc3ls_size_16_color_FFFFFF_t_70]

## concat（） ##

db.session.query(func.concat(Protocols.id, Protocols.protocolName, Protocols.parent_protocol)).all()

结果：

[('1sshtcp',), ('2httpstcp',), ('3telenttcp',), ('4rdptcp',), ('5ntptcp',), ('6httptcp',), ('7ftptcp',), ('8vnctcp',), ('9smbtcp',), ('10smtptcp',), ('11pop3tcp',), ('12imaptcp',), ('13netstattcp',), ('14netbios-nstcp',), ('15netbios-dgmtcp',), ('16netbios-ssntcp',), ('17browsertcp,udp',), ('18atsvctcp,udp',), ('19rtsptcp,udp',), ('20dnstcp,udp',), ('21bootpcudp',), ('22snmpudp',), ('23snmp trapudp',), ('24bootpsudp',), ('25tftpudp',), ('26dhcpudp',), ('27icmpip',), ('28tcpip',), ('29udpip',), ('30espip',), ('31ahip',), ('32sctpip',)]

## concat\_ws（） ##

db.session.query(func.concat_ws("-", Protocols.id, Protocols.protocolName, Protocols.parent_protocol)).all()

结果：

[('1-ssh-tcp',), ('2-https-tcp',), ('3-telent-tcp',), ('4-rdp-tcp',), ('5-ntp-tcp',), ('6-http-tcp',), ('7-ftp-tcp',), ('8-vnc-tcp',), ('9-smb-tcp',), ('10-smtp-tcp',), ('11-pop3-tcp',), ('12-imap-tcp',), ('13-netstat-tcp',), ('14-netbios-ns-tcp',), ('15-netbios-dgm-tcp',), ('16-netbios-ssn-tcp',), ('17-browser-tcp,udp',), ('18-atsvc-tcp,udp',), ('19-rtsp-tcp,udp',), ('20-dns-tcp,udp',), ('21-bootpc-udp',), ('22-snmp-udp',), ('23-snmp trap-udp',), ('24-bootps-udp',), ('25-tftp-udp',), ('26-dhcp-udp',), ('27-icmp-ip',), ('28-tcp-ip',), ('29-udp-ip',), ('30-esp-ip',), ('31-ah-ip',), ('32-sctp-ip',)]

## order\_by() ##

db.session.query(Protocols.parent_protocol, Protocols.protocolName).order_by(Protocols.parent_protocol).all()

结果：

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

## group\_by（），group\_concat（） ##

b.session.query(Protocols.parent_protocol, func.group_concat(Protocols.id,  Protocols.protocolName)).group_by(Protocols.parent_protocol).all()

结果：

[('ip', '27icmp,28tcp,29udp,30esp,31ah,32sctp'), ('tcp', '1ssh,2https,3telent,4rdp,5ntp,6http,7ftp,8vnc,9smb,10smtp,11pop3,12imap,13netstat,14netbios-ns,15netbios-dgm,16netbios-ssn'), ('tcp,udp', '17browser,18atsvc,19rtsp,20dns'), ('udp', '21bootpc,22snmp,23snmp trap,24bootps,25tftp,26dhcp')]

## group\_concat（），concat\_ws（），group\_by（） ##

db.session.query(Protocols.parent_protocol, func.group_concat(func.concat_ws("-", Protocols.id, Protocols.protocolName),SEPARATOR="|")).group_by(Protocols.parent_protocol).all()

结果

[('ip', '27-icmp,28-tcp,29-udp,30-esp,31-ah,32-sctp'), ('tcp', '1-ssh,2-https,3-telent,4-rdp,5-ntp,6-http,7-ftp,8-vnc,9-smb,10-smtp,11-pop3,12-imap,13-netstat,14-netbios-ns,15-netbios-dgm,16-netbios-ssn'), ('tcp,udp', '17-browser,18-atsvc,19-rtsp,20-dns'), ('udp', '21-bootpc,22-snmp,23-snmp trap,24-bootps,25-tftp,26-dhcp')]

## group\_by（）单独使用： ##

db.session.query(Protocols.parent_protocol).group_by(Protocols.parent_protocol).all()

结果：

[('ip',), ('tcp',), ('tcp,udp',), ('udp',)]

一目了然的代码截图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d5bWFpc3ls_size_16_color_FFFFFF_t_70 1]

[https_blog.csdn.net_gymaisyl_article_details_96478282]: https://blog.csdn.net/gymaisyl/article/details/96478282
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d5bWFpc3ls_size_16_color_FFFFFF_t_70]: /images/20211201/d1008a07d7a54b04acba808d53f858d5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2d5bWFpc3ls_size_16_color_FFFFFF_t_70 1]: /images/20211201/339b2084553e49b98a208b4f1c6758fa.png