B3(BigBrotherBot) Python2.7 MySQL查询报错：Cursor无fetchone属性

问题现象 #

调用!maprating命令时触发错误，日志输出如下：

230205 14:19:09 DEBUG    'AdminPlugin: handle command !maprating'
230205 14:19:09 VERBOSE  "RCON sending (127.0.0.1:27960) 'tell 0 ^3: ^8[pm]^7 ^7There was an error processing your command'"
230205 14:19:09 ERROR    "Handler AdminPlugin could not handle event Say: AttributeError: 'Cursor' object has no attribute 'fetchone'

问题分析 #

B3 Bot框架封装的Cursor对象并非标准Python MySQLdb游标，它没有fetchone()方法。查看插件内其他数据库操作代码（如is_rated方法），框架提供的正确单行数据获取方法是getRow()。

修复代码 #

修改cmd_maprating方法，将cursor.fetchone()替换为cursor.getRow()：

def cmd_maprating(self, data, client, cmd=None):
    map_name = self.console.game.mapName
    query = "SELECT map_name, SUM(rating) AS total_rating FROM mapratings WHERE map_name = %s GROUP BY map_name"
    cursor = self.console.storage.query(query, (map_name,))
    if cursor.rowcount == 0:
        client.message("No ratings for map %s" % map_name)
    else:
        result = cursor.getRow()
        client.message("Map %s has a total rating of %d" % (result['map_name'], result['total_rating']))

完整修复后插件代码 #

import os
import sys
import b3
import b3.events
import b3.plugin


class MapratingPlugin(b3.plugin.Plugin):
    def onLoadConfig(self):
        self.rated_players = {}
        self.map_ratings = {}
    def onStartup(self):
        self.registerEvent(b3.events.EVT_CLIENT_SAY)

        self._adminPlugin = self.console.getPlugin('admin')
        if self._adminPlugin:
            self._adminPlugin.registerCommand(self, 'rategood', 0, self.cmd_rategood)
            self._adminPlugin.registerCommand(self, 'ratebad', 0, self.cmd_ratebad)
            self._adminPlugin.registerCommand(self, 'maprating', 0, self.cmd_maprating)

    def is_rated(self, client, map_name):
        cursor = self.console.storage.query("""            SELECT rating FROM mapratings WHERE guid = %s AND map_name = %s        """, (client.guid, map_name))
        
        if cursor.rowcount:
            return (True, cursor.getRow()['rating'])
        else:
            return (False, 0)

    def add_rating(self, client, map_name, rating):
        self.console.storage.query("""            INSERT INTO mapratings (guid, player_name, map_name, rating)            VALUES (%s, %s, %s, %s)        """, (client.guid, client.name, map_name, rating))

    def update_rating(self, client, map_name, rating):
        self.console.storage.query("""            UPDATE mapratings SET rating = %s WHERE guid = %s AND map_name = %s        """, (rating, client.guid, map_name))

    def cmd_rategood(self, data, client, cmd=None):
        map_name = self.console.game.mapName
        (is_rated, existing_rating) = self.is_rated(client, map_name)
        rating = 1
        if is_rated:
            if existing_rating == rating:
                client.message('You have already rated this map as good')
            else:
                self.update_rating(client, map_name, rating)
                client.message('Map %s rated as good by you' % map_name)
        else:
            self.add_rating(client, map_name, rating)
            client.message('Map %s rated as good by you' % map_name)

    def cmd_ratebad(self, data, client, cmd=None):
        map_name = self.console.game.mapName
        (is_rated, existing_rating) = self.is_rated(client, map_name)
        rating = -1
        if is_rated:
            if existing_rating == rating:
                client.message('You have already rated this map as bad')
            else:
                self.update_rating(client, map_name, rating)
                client.message('Map %s rated as bad by you' % map_name)
        else:
            self.add_rating(client, map_name, rating)
            client.message('Map %s rated as bad by you' % map_name)

    def cmd_maprating(self, data, client, cmd=None):
        map_name = self.console.game.mapName
        query = "SELECT map_name, SUM(rating) AS total_rating FROM mapratings WHERE map_name = %s GROUP BY map_name"
        cursor = self.console.storage.query(query, (map_name,))
        if cursor.rowcount == 0:
            client.message("No ratings for map %s" % map_name)
        else:
            result = cursor.getRow()
            client.message("Map %s has a total rating of %d" % (result['map_name'], result['total_rating']))

内容的提问来源于stack exchange，提问作者Juli199696