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

2018-03-01 11:09:45来源:网络收集作者:喵______"地球太危险人点击

分享

像下面这段代码,就会导致嵌套访问数据连接,因而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:AIpythonwebcaiforumforumTest.py", line 40, in


page_posts, page_count = model.Post().list(page)


File "D:AIpythonwebcaiforummodel.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:AIpythonwebcaiforummodel.py", line 172, in count


result = db.query("SELECT COUNT(*) AS count FROM comments WHERE parent_id=%d" % self.__parent_id)


File "C:UserstonyAppDataLocalProgramsPythonPython36libsite-packagesweb.py-0.40.dev0-py3.6.eggwebdb.py", line 664, in query


db_cursor = self._db_cursor()


File "C:UserstonyAppDataLocalProgramsPythonPython36libsite-packagesweb.py-0.40.dev0-py3.6.eggwebdb.py", line 575, in _db_cursor


return self.ctx.db.cursor()


File "C:UserstonyAppDataLocalProgramsPythonPython36libsite-packagesmysqlconnectorconnection.py", line 845, in cursor


self.handle_unread_result()


File "C:UserstonyAppDataLocalProgramsPythonPython36libsite-packagesmysqlconnectorconnection.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))



在VC2015里学会使用tinyxml库








http://edu.csdn.net/course/detail/2590








在Windows下SVN的版本管理与实战 








http://edu.csdn.net/course/detail/2579








Visual Studio 2015开发C++程序的基本使用 








http://edu.csdn.net/course/detail/2570








在VC2015里使用protobuf协议








http://edu.csdn.net/course/detail/2582








在VC2015里学会使用MySQL数据库







http://edu.csdn.net/course/detail/2672

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台