python接口自动化之数据库操作

想要做python接口自动化，首先得安装2个第三方库。一个是requests另一个是PyMySQL,
requests先不管，我们先配置连接mysql数据库。做接口自动化要做数据库迁移测试库跟正式库保持独立

1. 配置文件
   配置文件是.ini文件，如：db_config.ini

[mysqlconf]
host = 127.0.0.1     #数据库IP
port = 3306    #端口号
user = root  #用户名
password  = 123456   #密码
db_name   =   dataname    #数据库名

2. 连接数据库，封装数据库基本操作

from pymysql import connect,cursors
from pymysql.err import OperationalError
import os,time
import configparser as cparser

#====================读取db_config.ini文件设置=======================
base_dir = str(os.path.dirname(os.path.dirname(__file__)))
base_dir = base_dir.replace("\\","/")     #将路径中“\\”替换为“/”
file_path = base_dir + "/db_config.ini"      #拼接路径

cf = cparser.ConfigParser()
cf.read(file_path)      #读取内容

host = cf.get("mysqlconf","host")      #get配置文件“host”的值并赋值给host
port = cf.get("mysqlconf","port")
db = cf.get("mysqlconf","db_name")
user = cf.get("mysqlconf","user")
password = cf.get("mysqlconf","password")

#===================封装mysql基本操作==========================
class DB:

    def __init__(self):
        """初始化数据"""
        try:
        #连接数据库
            self.conn = connect(host=host,
                                user=user,
                                password=password,
                                db=db,
                                charset="utf8mb4",
                                cursorclass=cursors.DictCursor)
        except OperationalError as e:
            print("Mysql Error %d: %s" %(e.args[0],e.args[1]))

    def clear(self,table_name):
        """清除表数据"""
        real_sql = "truncate table " +table_name +";"
        with self.conn.cursor() as cursor:
            cursor.execute("SET FOREIGN_KEY_CHECKS=0;")
            cursor.execute(real_sql)
        self.conn.commit()

    def insert(self,table_name,table_data):
        """插入表数据"""
        real_sql = "INSERT INTO " + table_name + " VALUES(%r,%r,%r,%r,%r,%r,%r)" %(table_data)
        print(real_sql)

        with self.conn.cursor() as cursor:
            cursor.execute(real_sql)
        self.conn.commit()

    def close(self):
        """关闭数据库连接"""
        self.conn.close()

if __name__ == "__main__":
    db = DB()
    now_time = time.strftime("%Y-%m-%d %H:%M:%S")
    table_name = "sign_event"
    data = (12,"红米",2000,1,"嘿嘿嘿","2019-04-25 12:00:00",now_time)
    db.clear(table_name)
    db.insert(table_name,data)
    db.close()

3. 添加测试数据

import sys,time
sys.path.append("../db_fixture")
from db_fixture.mysql_db import DB

now_time = time.strftime("%Y-%m-%d %H:%M:%S")

#创建测试数据
datas = {
    #发布会表数据
    "data1":[(1,"红米Pro发布会",2000,1,
                   "会展中心","2019-04-15 12:00:00",now_time),
                  (2,"可参加人数为0",0,1,
                   "会展中心","2019-04-15 12:00:00",now_time),
                  (3,"当前状态为0关闭",2000,0,
                   "会展中心","2019-04-15 12:00:00",now_time),
                  (4,"发布会已结束",2000,1,
                   "会展中心","2019-04-05 12:00:00",now_time),
                  (5,"小米5发布会",2000,1,
                   "国家会议中心","2019-04-15 12:00:00",now_time),
                   ],
    "data2":[(1,"alen",13511001100,"alen@mail.com",0,now_time,1),
                  (2,"has",13511001101,"sign@mail.com",0,now_time,1),
                  (3,"tom",13511001102,"tom@mail.com",0,now_time,5),
                  ]
}
def init_data():
    """将测试数据插入表"""
    db = DB()
    for table,data in datas.items():
        db.clear(table)
        for d in data:
            db.insert(table,d)
    db.close()

if __name__ == "__main__":
    init_data()