pymysql的简单封装代码实例

这篇文章主要介绍了pymysql的简单封装代码实例,文中通过示例代码介绍的非常详细，对大家

这篇文章主要介绍了pymysql的简单封装代码实例,文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下

#coding=utf-8 
#!/usr/bin/python

import pymysql


class MYSQL:
 """
 对pymysql的简单封装
 """
 def __init__(self,host,user,pwd,db):
 self.host = host
 self.user = user
 self.pwd = pwd
 self.db = db

 def __GetConnect(self):
 """
 得到连接信息
 返回: conn.cursor()
 """
 if not self.db:
 raise(NameError,"没有设置数据库信息")
 self.conn = pymysql.connect(host=self.host,user=self.user,password=self.pwd,database=self.db,charset="utf8")
 cur = self.conn.cursor()
 if not cur:
 raise(NameError,"连接数据库失败")
 else:
 return cur

 def ExecQuery(self,sql):
 """
 执行查询语句
 返回的是一个包含tuple的list，list的元素是记录行，tuple的元素是每行记录的字段

 调用示例：
 ms = MYSQL(host="localhost",user="sa",pwd="123456",db="PythonWeiboStatistics")
 resList = ms.ExecQuery("SELECT id,NickName FROM WeiBoUser")
 for (id,NickName) in resList:
 print str(id),NickName
 """
 cur = self.__GetConnect()
 cur.execute(sql)
 resList = cur.fetchall()

 #查询完毕后必须关闭连接
 self.conn.close()
 return resList

 def ExecNonQuery(self,sql):
 """
 执行非查询语句

 调用示例：
 cur = self.__GetConnect()
 cur.execute(sql)
 self.conn.commit()
 self.conn.close()
 """
 cur = self.__GetConnect()
 cur.execute(sql)
 self.conn.commit()
 self.conn.close()

def main():

 mysql = MYSQL(host="192.168.163.36",user="wisdomhr",pwd="wisdomhr",db="WISDOMHR")
 resList = mysql.ExecQuery("SELECT CITY FROM RES_SCHOOL")
 for inst in resList:
 print(inst)
if __name__ == '__main__':
 main()

用法如下:

#!/usr/bin/python
#version 3.4
import wispymysql
mysql = wispymysql.MYSQL(host="192.168.163.36",user="wisdomhr",pwd="wisdomhr",db="WISDOMHR")
selectsql = "SELECT ID, CITY FROM RES_SCHOOL WHERE CITY LIKE '%\r\n%'"
result = mysql.ExecQuery(selectsql)

for (dbid, city) in result:
 rightcity = city.replace('\r\n','')
 updatesql= "UPDATE RES_SCHOOL SET CITY = '" + rightcity + "' WHERE ID = " + str(dbid)
 print(updatesql)
 mysql.ExecNonQuery(updatesql)

以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持脚本之家。