python web py入门（11）- 双重查询访问MYSQL数据库出错

最新推荐文章于 2022-07-17 06:00:00 发布

caimouse

最新推荐文章于 2022-07-17 06:00:00 发布

阅读量1.4k

点赞数 1

分类专栏： webpy 文章标签： python webpy mysql

本文链接：https://blog.csdn.net/caimouse/article/details/79345606

版权

webpy 专栏收录该内容

71 篇文章 33 订阅

订阅专栏

像下面这段代码，就会导致嵌套访问数据连接，因而python的mysql连接器就会抛出错误，代码如下：

 posts = db.query('''SELECT posts.id, title, posts.time, user_id, users.name AS username
                            FROM posts JOIN users
                            ON posts.user_id = users.id
                            ORDER BY posts.id DESC
                            LIMIT %d OFFSET %d''' % (per_page, offset))
        page_posts = []
        for p in posts:
            comment = Comment(p.id)
            comment.count()
            last = comment.last()
            last_time = last.time if last else p.time
            page_posts.append({'id': p.id, 'title': p.title, 'userid': p.user_id, 'username': p.username, 'comment_count': comment.count(), 'last_time': last_time})

在这里第一个查询 posts = db.query，接着遍历数据结果集时，又来了一个查询comment.count()，这时就会抛出下面的错误：
Traceback (most recent call last):
File "D:\AI\python\web\caiforum\forumTest.py", line 40, in <module>
page_posts, page_count = model.Post().list(page)
File "D:\AI\python\web\caiforum\model.py", line 35, in list
page_posts.append({'id': p.id, 'title': p.title, 'userid': p.user_id, 'username': p.username, 'comment_count': comment.count(), 'last_time': last_time})
File "D:\AI\python\web\caiforum\model.py", line 172, in count
result = db.query("SELECT COUNT(*) AS count FROM comments WHERE parent_id=%d" % self.__parent_id)
File "C:\Users\tony\AppData\Local\Programs\Python\Python36\lib\site-packages\web.py-0.40.dev0-py3.6.egg\web\db.py", line 664, in query
db_cursor = self._db_cursor()
File "C:\Users\tony\AppData\Local\Programs\Python\Python36\lib\site-packages\web.py-0.40.dev0-py3.6.egg\web\db.py", line 575, in _db_cursor
return self.ctx.db.cursor()
File "C:\Users\tony\AppData\Local\Programs\Python\Python36\lib\site-packages\mysql\connector\connection.py", line 845, in cursor
self.handle_unread_result()
File "C:\Users\tony\AppData\Local\Programs\Python\Python36\lib\site-packages\mysql\connector\connection.py", line 1109, in handle_unread_result
raise errors.InternalError("Unread result found")
mysql.connector.errors.InternalError: Unread result found

原因如下：
The reason is that without a buffered cursor, the results are "lazily" loaded, meaning that "fetchone" actually only fetches one row from the full result set of the query. When you will use the same cursor again, it will complain that you still have n-1 results (where n is the result set amount) waiting to be fetched. However when you use a buffered cursor the connector fetches ALL rows behind the scenes and you just take one from the connector so the mysql db won't complain. Hope it helps.

大体意思就是mysql连接游标只会获取一行结果返回，如果在迭代过程中，再去使用相同的游标，就会出错。知道原因解决起来，就简单了，就是使用缓存游标，或者使用双连接来解决。

cursor = cnx.cursor(buffered=True)

或者：

config={'host':'127.0.0.1',#default localhost  
        'user':'root',  
        'password':'buaascse',  
        'port':3306 ,#默认即为3306  
        #'database':'mobilephone', 无默认数据库  
        'charset':'utf8',#默认即为utf8  
        'buffered': True,  
        }  
    try:  
        cnn = mysql.connector.connect(**config)  
    except mysql.connector.Error as e:  
         print('connect fails!{}'.format(e))