torndb是facebook开源的一个基于MySQLdb二次封装的一个mysql模块,新封装的这个模块比较小,是一个只有2百多行代码的py文件。虽然代码短,功能确相较MySQLdb简便不少,并且该模块由于增加了reconnect方法和max_idel_time参数,解决了mysql的断连问题。该模块之前在 python torndb模块 篇中也提到过。这里就结合上一篇 python mysql 断连报错处理 问题,比较下使用原生MySQLdb模块和使用torndb模块的代码。
一、代码比较
1、使用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) 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', 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: if termid: try: dbconn.ping() except: dbconn = MySQLdb.connect(host='localhost', user='root', passwd='123456', db='361way', port=3306, charset='utf8', init_command='set names utf8') print tag, 'tag id is ',termid termid = getTerm(dbconn,tag) tagids.extend(termid) else: try: dbconn.ping() except: dbconn = MySQLdb.connect(host='localhost', user='root', passwd='123456', db='361way', port=3306, charset='utf8', init_command='set names utf8') termid = addTerm(dbconn,tag) print 'add tag',tag,'id is ' ,termid tagids.append(termid) print 'tag id is ',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', user='root', passwd='123456', db='361way', port=3306, charset='utf8', init_command='set names utf8') addCTag(dbconn,ctagdata)
2、使用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,slug,term_group) values (%s,%s,0)" term_id = db.execute_lastrowid(query,tag,tag) sql = "INSERT into wp_term_taxonomy (term_id,taxonomy,description) values (%s,'post_tag',%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',user='root',password='123456') 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 'All tags id is ',tagids postid = '35' tagids = list(set(tagids)) ctagdata = [] for tagid in tagids: ctagdata.append((postid,tagid)) addCTag(dbconn,ctagdata)
从两者的代码上来看,使用torndb模块和原生相比,发现可以省略如下两部分:
1、torndb模块不需要db.cursor进行处理,无不需要db.comment提交,torndb是自动提交的;
2、torndb不需要在每次调用时,进行db.ping()判断数据库socket连接是否断开,因为torndb增加了reconnect方法,支持自动重连。
二、torndb的方法
torndb提供的参数和方法有:
execute 执行语句不需要返回值的操作。 execute_lastrowid 执行后获得表id,一般用于插入后获取返回值。 executemany 可以执行批量插入。返回值为第一次请求的表id。 executemany_rowcount 批量执行。返回值为第一次请求的表id。 get 执行后获取一行数据,返回dict。 iter 执行查询后,返回迭代的字段和数据。 query 执行后获取多行数据,返回是List。 close 关闭 max_idle_time 最大连接时间 reconnect 关闭后再连接
使用示例:
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, *parameters, **kwparameters) get(query, *parameters, **kwparameters) execute(query, *parameters, **kwparameters) execute_lastrowid(query, *parameters, **kwparameters) execute_rowcount(query, *parameters, **kwparameters) executemany(query, parameters) executemany_lastrowid(query, parameters) executemany_rowcount(query, parameters) update(query, *parameters, **kwparameters) updatemany(query, parameters) insert(query, *parameters, **kwparameters) insertmany(query, parameters)