IT虾米网

对pymysql的简单封装详解

luoye 2021年07月15日 编程语言 244 0
#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) 
 
 

发布评论
IT虾米网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!

mac多版本python安装 pymysql详解
你是第一个吃螃蟹的人
发表评论

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。