Python：封装pymysql模块操作mysql

简介：PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2中则使用mysqldb。PyMySQL 遵循 Python 数据库 API v2.0 规范，并包含了 pure-Python MySQL 客户端库。

安装：

pip install pymysql

检查安装结果：

pip show pymysql

图片

案例前提1：本地或者服务器创建MySQL

MySQL安装和使用：centos7.6：安装docker下mysql并外网远程连接

案例前提2：新增DB - testerDB ；表userinfo。新增了3条数据

图片

封装pymysql操作：

import pymysql


class MysqlHelper(object):
    conn = None

    def __init__(self, host, username, password, db, charset='utf8', port=3306):
        self.host = host
        self.username = username
        self.password = password
        self.db = db
        self.charset = charset
        self.port = port

    def connect(self):
        self.conn = pymysql.connect(host=self.host,
                                    port=self.port,
                                    user=self.username,
                                    password=self.password,
                                    db=self.db,
                                    charset=self.charset)

        self.cursor = self.conn.cursor()

    def close(self):
        self.cursor.close()
        self.conn.close()

    def get_one(self, sql, params=()):
        result = None
        try:
            self.connect()
            self.cursor.execute(sql, params)
            result = self.cursor.fetchone()
            self.close()
        except Exception as e:
            print(e)
        return result

    def get_all(self, sql, params=()):
        list_data = ()
        try:
            self.connect()
            self.cursor.execute(sql, params)
            list_data = self.cursor.fetchall()
            self.close()
        except Exception as e:
            print(e)
        return list_data

    def delete_one(self, sql, params=()):
        result = None
        try:
            self.connect()
            self.cursor.execute(sql, params)
            result = self.cursor.fetchone()
            self.close()
        except Exception as e:
            print(e)
        return result


if __name__ == '__main__':
    host = "你的MySQL服务地址"
    username = "账号"
    password = "密码"
    db = "数据库名称"
    my_db = MysqlHelper(host=host, username=username, password=password, db=db)
    
    # 获取单个
    sql = 'SELECT name FROM `userinfo`  where age > 25;'
    result = my_db.get_one(sql)
    print("result:{}".format(result))

    # 获取多个
    sql2 = 'SELECT name FROM `userinfo`  where age > 25;'
    result2 = my_db.get_all(sql2)
    print("result2:{}".format(result2))

运行结果：

图片

对测试而言：请使用查询和查询多个，慎用更新和删除，尽量通过接口进行更新和删除，除非万不得已。

微信公众号：玩转测试开发
欢迎关注，共同进步，谢谢！