Python中MySQLdb和torndb模块对MySQL的断连问题处理
在使用python 对wordpress tag 进行细化代码处理时,遇到了调用MySQLdb模块时的出错,由于错误提示和问题原因相差甚远,查看了N久代码也未发现代码有问题。后来问了下师傅,被告知MySQLdb里有一个断接的坑 ,需要进行数据库重连解决。 一、报错代码及提示 运行出错的代码如下: import MySQLdb def getTerm(db,tag): cursor = db.cursor() query = "SELECT term_id FROM wp_terms where name=%s " count = cursor.execute(query,tag) rows = cursor.fetchall() db.commit() #db.close() if count: term_id = [int(rows[id][0]) for id in range(count)] return term_id else:return None def addTerm(db,tag): cursor = db.cursor() query = "INSERT into wp_terms (name,slug,term_group) values (%s,%s,0)" data = (tag,tag) cursor.execute(query,data) db.commit() term_id = cursor.lastrowid sql = "INSERT into wp_term_taxonomy (term_id,taxonomy,description) values (%s,'post_tag',%s) " value = (term_id,tag) cursor.execute(sql,value) db.commit() db.close() return int(term_id) dbconn = MySQLdb.connect(host='localhost',user='root',passwd='123456',db='361way',port=3306,charset='utf8',init_command='set names utf8') tags = ['mysql','1111','aaaa','bbbb','ccccc','php','abc','python','java'] tagids = [] for tag in tags: termid = getTerm(dbconn,tag) if termid: print tag,'tag id is ',termid tagids.extend(termid) else: termid = addTerm(dbconn,tag) print 'add tag',tag,'id is ',termid tagids.append(termid) print 'tag id is ',tagids 直接可以执行,在第for循环里第二次调用getTerm函数时,报错如下: Traceback (most recent call last): File "a.py",line 40,in <module> termid = getTerm(dbconn,tag) File "a.py",line 11,in getTerm count = cursor.execute(query,tag) File "/usr/lib64/python2.6/site-packages/MySQLdb/cursors.py",line 154,in execute charset = db.character_set_name() _mysql_exceptions.InterfaceError: (0,'') 二、解决方法 初始时以为是编码问题了,又细核对了几遍未发现编码有问题,在python代码里也未发现异常。后来问过师傅后,师傅来了句提示: 只看代码有啥用,mysql 的超时时间调长点或捕获异常从连,原因是 mysql> show GLOBAL VARIABLES like "%timeout%"; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | connect_timeout | 10 | | delayed_insert_timeout | 300 | | innodb_lock_wait_timeout | 50 | | innodb_rollback_on_timeout | OFF | | interactive_timeout | 28800 | | net_read_timeout | 30 | | net_write_timeout | 60 | | slave_net_timeout | 3600 | | table_lock_wait_timeout | 50 | | wait_timeout | 28800 | +----------------------------+-------+ 10 rows in set (0.00 sec) 发现最小的超时时间是10s ,而我的程序执行起来显然就不了10s 。因为之前查过相关的报错,这里估计这个很可能是另外一个报错:2006,MySQL server has gone away 。即然和这个超时时间应该没关系,那就尝试通过MySQLdb ping测试,如果捕获异常,就再进行重连,修改后的代码为: #!/usr/bin/python #coding=utf-8 import MySQLdb def getTerm(db,tag): cursor = db.cursor() query = "SELECT term_id FROM wp_terms where name=%s " count = cursor.execute(query,tag) rows = cursor.fetchall() db.commit() #db.close() if count: term_id = [int(rows[id][0]) for id in range(count)] print term_id return term_id else:return None def addTerm(db,tag): cursor = db.cursor() query = "INSERT into wp_terms (name,0)" data = (tag,tag) cursor.execute(query,data) db.commit() term_id = cursor.lastrowid sql = "INSERT into wp_term_taxonomy (term_id,%s) " value = (term_id,tag) cursor.execute(sql,value) db.commit() db.close() return int(term_id) dbconn = MySQLdb.connect(host='localhost','java'] if __name__ == "__main__": tagids = [] for tag in tags: try: dbconn.ping() except: print 'mysql connect have been close' dbconn = MySQLdb.connect(host='localhost',init_command='set names utf8') termid = getTerm(dbconn,tag) if termid: print tag,termid tagids.extend(termid) else: termid = addTerm(dbconn,tag) print 'add tag',termid tagids.append(termid) print 'All tags id is ',tagids 再执行发现竟然OK了,而细看下结果,发现基本上每1-2次getTerm或addTerm函数调用就会打印一次'mysql connect have been close' 。 三、使用torndb模块解决mysql断连问题 import MySQLdb def getTerm(db,value) db.commit() db.close() return int(term_id) def addCTag(db,data): cursor = db.cursor() query = '''INSERT INTO `wp_term_relationships` ( `object_id`,`term_taxonomy_id` ) VALUES ( %s,%s) ''' cursor.executemany(query,data) db.commit() db.close() dbconn = MySQLdb.connect(host='localhost','java'] tagids = [] for tag in tags: if termid: try: dbconn.ping() except: dbconn = MySQLdb.connect(host='localhost',init_command='set names utf8') print tag,termid termid = getTerm(dbconn,tag) tagids.extend(termid) else: try: dbconn.ping() except: dbconn = MySQLdb.connect(host='localhost',init_command='set names utf8') termid = addTerm(dbconn,tagids postid = '35' tagids = list(set(tagids)) ctagdata = [] for tagid in tagids: ctagdata.append((postid,tagid)) try: dbconn.ping() except: dbconn = MySQLdb.connect(host='localhost',init_command='set names utf8') addCTag(dbconn,ctagdata) 使用torndb的代码 #!/usr/bin/python #coding=utf-8 import torndb def getTerm(db,tag): query = "SELECT term_id FROM wp_terms where name=%s " rows = db.query(query,tag) termid = [] for row in rows: termid.extend(row.values()) return termid def addTerm(db,tag): query = "INSERT into wp_terms (name,0)" term_id = db.execute_lastrowid(query,tag) sql = "INSERT into wp_term_taxonomy (term_id,%s) " db.execute(sql,term_id,tag) return term_id def addCTag(db,data): query = "INSERT INTO wp_term_relationships (object_id,term_taxonomy_id) VALUES (%s,%s) " db.executemany(query,data) dbconn = torndb.Connection('localhost:3306','361way',password='123456') tags = ['mysql','java'] tagids = [] for tag in tags: termid = getTerm(dbconn,tag) if termid: print tag,termid tagids.extend(termid) else: termid = addTerm(dbconn,tag) print 'add tag',termid tagids.append(termid) print 'All tags id is ',tagid)) addCTag(dbconn,ctagdata) 从两者的代码上来看,使用torndb模块和原生相比,发现可以省略如下两部分: torndb模块不需要db.cursor进行处理,无不需要db.comment提交,torndb是自动提交的; torndb不需要在每次调用时,进行db.ping()判断数据库socket连接是否断开,因为torndb增加了reconnect方法,支持自动重连。 2.torndb的方法 torndb提供的参数和方法有: execute 执行语句不需要返回值的操作。 mysql> CREATE TABLE `ceshi` (`id` int(1) NULL AUTO_INCREMENT,`num` int(1) NULL,PRIMARY KEY (`id`)); >>> import torndb >>> db = torndb.Connection("127.0.0.1","数据库名","用户名","密码",24*3600) # 24*3600为超时时间 >>> get_id1 = db.execute_lastrowid("insert ceshi(num) values('1')") >>> print get_id1 1 >>> args1 = [('2'),('3'),('4')] >>> get1 = db.executemany("insert ceshi(num) values(%s)",args1) >>> print get1 2 >>> rows = db.iter("select * from ceshi") >>> for i in rows: … print i 在使用过程中可能遇到的错误: File "/home/361way/database.py",line 145,in execute_lastrowid self._execute(cursor,query,parameters) File "/home/361way/database.py",line 207,in _execute return cursor.execute(query,parameters) File "/usr/lib/pymodules/python2.7/MySQLdb/cursors.py",line 159,in execute query = query % db.literal(args) TypeError: not enough arguments for format string 写上面的代码时,我刚开始还是试着使用MySQLdb模块的方式引用数据,结果发现报参数的错误 ,经查看代码发现 ,torndb在使用几个sql方法时较MySQLdb精简过了。具体各个方法的传参方法如下(注意参数个数): close() reconnect() iter(query,*parameters,**kwparameters) query(query,**kwparameters) get(query,**kwparameters) execute(query,**kwparameters) execute_lastrowid(query,**kwparameters) execute_rowcount(query,**kwparameters) executemany(query,parameters) executemany_lastrowid(query,parameters) executemany_rowcount(query,parameters) update(query,**kwparameters) updatemany(query,parameters) insert(query,**kwparameters) insertmany(query,parameters) (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |