python mysql断开连接_python – 为什么mysql连接器断开(“在查询期间丢失与MySQL服务器的连接”错误)...

当我运行大型查询(查询返回多行)时,我在查询错误期间得到了与MySQL服务器的丢失连接,我看不出我做错了什么.我使用mysql.com的“新”mysql驱动程序(不是“旧的”MySQLdb),以及与MAMP捆绑在一起的mysql版本. Python 2.7.表未损坏,分析表nrk2013b_tbl;返回状态确定.这是一个打破的例子：

#!/usr/bin/python2.7

# coding: utf-8

import sys

import mysql.connector # version 2.0.1

connection = mysql.connector.connect(

unix_socket="/Applications/MAMP/tmp/mysql/mysql.sock",

user="dbUsernam",

passwd="dbUserPassword",

db="nrk",

charset = "utf8",

use_unicode = True)

cur = connection.cursor()

cur.execute("USE nrk;")

sql = """SELECT id FROM nrk2013b_tbl WHERE main_news_category = 'Sport'"""

cur.execute(sql)

rows = cur.fetchall()

print rows

sys.exit(0)

这导致我大部分时间都会遇到错误：

Traceback (most recent call last):

File "train_trainer_test.py", line 20, in

remaining_rows = cur.fetchall()

File "/Library/Python/2.7/site-packages/mysql/connector/cursor.py", line 823, in fetchall

(rows, eof) = self._connection.get_rows()

File "/Library/Python/2.7/site-packages/mysql/connector/connection.py", line 669, in get_rows

rows = self._protocol.read_text_result(self._socket, count)

File "/Library/Python/2.7/site-packages/mysql/connector/protocol.py", line 309, in read_text_result

packet = sock.recv()

File "/Library/Python/2.7/site-packages/mysql/connector/network.py", line 226, in recv_plain

raise errors.InterfaceError(errno=2013)

mysql.connector.errors.InterfaceError: 2013: Lost connection to MySQL server during query

第20行是rows = cur.fetchall()

如果我将查询限制为结果更少结果SELECT id FROM nrk2013b_tbl WHERE main_news_category =’Sport’LIMIT 10一切都很好.但我确实希望使用更大的结果集.对于一些特殊的问题解决方案,我已经移动了限制并将我想要的数据分解为较小的批次,但这仍然是一个问题.

为了考虑connect-timeout和max_allowed_pa​​cket等,我有这个my.cnf文件：文件：/Applications/MAMP/conf/my.cnf

[mysqld]

max_allowed_packet = 64M

wait_timeout = 28800

interactive_timeout = 28800

connect-timeout=31536000

这似乎没有任何区别(我甚至不确定mysql是否识别这些设置).当我从终端或Sequel Pro运行查询时,它工作正常.它只是通过python mysql.connector我得到这些错误.

有任何想法吗？

PS：我暂时放弃了,改为PyMySQL而不是Oracle mysql.connector.通过改变这一点,问题似乎消失了(我自己总结出问题是在oracle mysql连接器中).

import pymysql

conn = pymysql.connect(

unix_socket="/Applications/MAMP/tmp/mysql/mysql.sock",

user="dbUsernam",

passwd="dbUserPassword",

db="nrk",

charset = "utf8",

use_unicode = True)

conn.autocommit(True) # this I

cur = conn.cursor()