sqlalchemy中的group by ,count, having

sql_mode模式导致下面的sql语句不通过，报错如下：

OperationalError: (pymysql.err.OperationalError) (1055ause and contains nonaggregated column 'ats_wkk.devcurrentstatus.created_ip' which is not functionally dependent on columns in GROUP BY cla, u"Expression #1 of SELECT list is not in GROUP BY cluse; this is incompatible with sql_mode=only_full_group_by")

SELECT @@GLOBAL.SQL_mode;
显示如下：

模式中有“ONLY_FULL_GROUP_BY”，所以下面的这个sql语句不通过；

        result = self.session.query(DevCurrentStatus, DevCurrentStatus.devmac, DevCurrentStatus.sid,
                                    func.count('*')).group_by(
            DevCurrentStatus.devmac).having(func.count('*') > 1) 
            .outerjoin(McuServer, DevCurrentStatus.sid == McuServer.mcuid) 
            .filter(McuServer.mcutype == 'dougongnengcaijiyi') 
            .filter(McuServer.wirelessno == '' and McuServer.wirelessno is None) 
            .all()

想知道上面这段代码的sql语句，加str就行了。如下：

str(self.session.query(DevCurrentStatus, DevCurrentStatus.devmac, DevCurrentStatus.sid,
                                    func.count('*')).group_by(
            DevCurrentStatus.devmac).having(func.count('*') > 1) 
            .outerjoin(McuServer, DevCurrentStatus.sid == McuServer.mcuid) 
            .filter(McuServer.mcutype == 'duogongnengcaijiyi') 
            .filter(McuServer.wirelessno == '' and McuServer.wirelessno is None))

最后显示的SQL语句如下：

SELECT 
  devcurrentstatus.created_ip AS devcurrentstatus_created_ip,
  devcurrentstatus.created_at AS devcurrentstatus_created_at,
  devcurrentstatus.creater AS devcurrentstatus_creater,
  devcurrentstatus.updated_ip AS devcurrentstatus_updated_ip,
  devcurrentstatus.updated_at AS devcurrentstatus_updated_at,
  devcurrentstatus.updater AS devcurrentstatus_updater,
  devcurrentstatus.deleted_ip AS devcurrentstatus_deleted_ip,
  devcurrentstatus.deleted_at AS devcurrentstatus_deleted_at,
  devcurrentstatus.deleter AS devcurrentstatus_deleter,
  devcurrentstatus.deleted AS devcurrentstatus_deleted,
  devcurrentstatus.sid AS devcurrentstatus_sid,
  devcurrentstatus.netstatus AS devcurrentstatus_netstatus,
  devcurrentstatus.netstatustime AS devcurrentstatus_netstatustime,
  devcurrentstatus.devmac AS devcurrentstatus_devmac,
  devcurrentstatus.devdns AS devcurrentstatus_devdns,
  devcurrentstatus.devgw AS devcurrentstatus_devgw,
  devcurrentstatus.devserverip AS devcurrentstatus_devserverip,
  devcurrentstatus.devserverport AS devcurrentstatus_devserverport,
  devcurrentstatus.devsub AS devcurrentstatus_devsub,
  devcurrentstatus.devparsgettime AS devcurrentstatus_devparsgettime,
  COUNT(*) AS count_1 
FROM
  devcurrentstatus 
  LEFT OUTER JOIN common_mcuserver 
    ON devcurrentstatus.sid = common_mcuserver.mcuid 
GROUP BY devcurrentstatus.devmac 
HAVING COUNT(*) >  1;

最后的解决方案：用sql语句把所有数据都拿出来，然后用python去取有mac地址冲突的记录，而不是用sql语句的group by 直接拿到mac冲突的记录。

sqlalchemy中的group by ,count, having

相关推荐