Python自动化之数据驱动（转载下自己的日常练习笔记）

一.execl数据驱动

1.xlutils简要说明

导包：pip3 install xlutils

注意⚠️：xlutils在介些Execl文件的时候，只识别.xls后缀的文件，如果是.xlsx后缀的文件被解析，.xlsx后缀的文件会被破坏

2.接口自动化中对execl简单的读取

#!/user/bin/env python
#coding:utf-8
#Author:shenqiang

'''xlrd写入文件，同时清空原文件'''
import xlrd
import os

'''拿到文件的路径'''
def base_path(filename = None):
    return os.path.join(os.path.dirname(__file__),filename)

'''读取文件内容'''
work = xlrd.open_workbook(base_path('tryExeclRead.xls'))

'''以下标或者sheet名取对应的哪页'''
sheet = work.sheet_by_index(0)
# sheet = work.sheet_by_name()

'''查看文件有多少行'''
print(sheet.nrows)

'''获取单元格内容,第10行，第3列'''
print(sheet.cell_value(9,2))

3.改写execl文件的内容

#!/user/bin/env python
#coding:utf-8
#Author:shenqiang

import xlrd
import os
from xlutils.copy import copy

'''拿到文件的路径'''
def base_path(filename = None):
    return os.path.join(os.path.dirname(__file__),filename)

'''打开文件'''
work = xlrd.open_workbook(base_path('tryExeclRead.xls'))
'''把文件内存存在一个变量里'''
file_content = copy(work)
'''拿到文件需要改写的sheet页'''
file = file_content.get_sheet(0)
# print(file_content)
'''定位文件位置写入内容'''
file.write(9,3,'强强')
'''保存文件'''
file_content.save(base_path('tryExeclRead.xls'))

4.configparser配置文件的读取（服务和数据库连接）

配置文件名

config.ini

文件内容：

[linux]
ip:10.0.13.26
port:22
username:root
password:W2ynE6b58wheeFho

[mysql]
ip:10.0.13.26
port:22
username:root
password:W2ynE6b58wheeFho

文件名

tryConfigparser.py

文件内容：

#!/user/bin/env python
#coding:utf-8
#Author:shenqiang

import os
import configparser

'''拿到文件的路径'''
def base_path(filename = None):
    return os.path.join(os.path.dirname(__file__),filename)

def getConfigparser(Linux = 'linux'):
    '''实例化对象'''
    config = configparser.ConfigParser()
    '''读取文件内容'''
    config.read(base_path('config.ini'))
    
    ip = config.get(Linux,'ip')
    port = config.get(Linux,'port')
    username = config.get(Linux,'username')
    password = config.get(Linux,'password')
    return [ip,port,username,password]

  '''遍历文件内容'''
for i in range(len(getConfigparser())):
    print(getConfigparser()[i])

5.mysql常用的一些操作指令

启动MySQL服务
sudo / usr / local / mysql / support - files / mysql.server start

停止MySQL服务
sudo / usr / local / mysql / support - files / mysql.server stop

重启MySQL服务
sudo / usr / local / mysql / support - files / mysql.server restart

进入MySQL数据库
mysql - u root - p
Password: 密文传输（shen6409175）
  
'''查看数据库'''
show databases;
'''选中数据库'''
use students;
'''查看表'''
show tables;
'''创建表'''
create table student(
-> id int primary key,
-> name varchar(50),
-> age varchar(10),
-> address varchar(100)
-> );
'''查看表结构'''
desc student;
'''查看表设计'''
show create table student g;

二.MySQL数据驱动

1.数据库查询

#!/user/bin/env python
#coding:utf-8
#Author:shenqiang
import pymysql

def connectMysql():
    try:
        '''链接数据库'''
        connect = pymysql.connect(
            host='127.0.0.1',
            user='root',
            password='shen6409175',
            db='students'
        )
    except Exception as  e:
        return e.args
    else:
        '''创建游标'''
        cur = connect.cursor()
        '''SQL语句分离'''
        # sql = 'select * from student where id = %s'
        # params = (1,)
        # '''查重'''
        # cur.execute(sql,params)
        # '''单个语句的查询'''
        # data = cur.fetchone()
        # return datas
        sql = 'select * from student'
        '''查重'''
        cur.execute(sql)
        '''返回多个数据'''
        datas = cur.fetchall()
        '''方法一，遍历'''
        # for data in datas:
        #     print(data)
        '''方法二，列表推倒式'''
        db = [data for data in datas]
        print(db)
        
    finally:
        # 关闭游标和链接
        cur.close()
        connect.close()

connectMysql()

2.数据库插入数据

#!/user/bin/env python
#coding:utf-8
#Author:shenqiang

import pymysql

def connectMysql():
    try:
        '''链接数据库'''
        connect = pymysql.connect(
            host='127.0.0.1',
            user='root',
            password='shen6409175',
            db='students'
        )
    except Exception as  e:
        return e.args
    else:
        '''创建游标'''
        cur = connect.cursor()
        '''导入数据'''
        # 单条语句的插入
        # sql = 'insert into student values(%s,%s,%s,%s);'
        # params = (6,'沈～','24','南京')
        '''批量插入数据'''
        sql = 'insert into student values(%s,%s,%s,%s);'
        params = [
            (7, '沈～', '24', '南京'),
            (8, '沈～', '24', '南京')
            ]
        cur.executemany(sql,params)
        '''insert后必须要commit()'''
        connect.commit()
    finally:
        # 关闭游标和链接
        cur.close()
        connect.close()

connectMysql()

3.数据库删除数据

#!/user/bin/env python
#coding:utf-8
#Author:shenqiang

import pymysql

def connectMysql():
    try:
        '''链接数据库'''
        connect = pymysql.connect(
            host='127.0.0.1',
            user='root',
            password='shen6409175',
            db='students'
        )
    except Exception as  e:
        return e.args
    else:
        '''创建游标'''
        cur = connect.cursor()
        '''导入数据'''
        sql = 'delete from student where id = %s'
        params = (7,)
        cur.execute(sql,params)
        '''执行后必须要commit()'''
        connect.commit()
    finally:
        # 关闭游标和链接
        cur.close()
        connect.close()

connectMysql()

4.一个完整的Mysql数据驱动方式

#!/user/bin/env python
#coding:utf-8
#Author:shenqiang
import pymysql

class MysqlTry:
    '''链接数据库'''
    def connectMysql(self):
        '''尝试链接数据库'''
        try:
            connect =pymysql.connect(
                host = '127.0.0.1',
                user='root',
                password='shen6409175',
                db='students'
            )
        except Exception as e:
            print(e.args)
        return connect

    def selectMysql(self,sql,params):
        '''创建游标'''
        cur = self.connectMysql().cursor()
        '''查重'''
        cur.execute(sql,params)
        '''查询'''
        result = cur.fetchone()
        return result

def checkValid(username,age):
    opera = MysqlTry()
    sql = "select * from student where name = %s and age = %s"
    params=(username,age)
    return opera.selectMysql(sql=sql,params=params)

def checkinfo():
    username = input('请输入用户名 
')
    age = input('请输入用户年龄 
')
    result = checkValid(username,age)
    if result:
        print('该用户在数据库中，测试通过！')
    else:
        print('该用户不在数据库中，存在bug！')

if __name__ == '__main__':
    checkinfo()

三.CSV数据驱动

1.取csv文件内容的方法

#!/user/bin/env python
#coding:utf-8
#Author:shenqiang
import csv

'''读取csv文件的方法'''
def ReadCsvlist():
    '''方法一:列表方式取数据'''
#     '''通过列表提取csv文件'''
#     with open('csv.csv') as file:
#         '''reader是csv的迭代器'''
#         reader = csv.reader(file)
#         '''跳过首行'''
#         next(reader)
#         '''列表推倒式'''
#         db =  [item for item in reader]
#         return db
#
# print(ReadCsvlist(),type(ReadCsvlist()))
    '''方法二：字典方式取数据'''
    with open('csv.csv',encoding='utf-8') as file:
        reader = csv.DictReader(file)
        for item in reader:
            print(dict(item))

ReadCsvlist()

五.DDT测试驱动

注意⚠️：有相同执行步骤的测试用例可以使用ddt

1.基本介绍

为了创建数据驱动测试，需要在测试类上使用 @ddt 装饰符，在测试方法上使用 @data 装饰符。@data 装饰符可以把参数当做测试数据，参数可以是单个值，列表，元组，字典，对于列表和元组，需要使用 @unpack 装饰符把元组和列表解析成多个参数。

#!/user/bin/env python
#coding:utf-8
#Author:shenqiang
import  csv
import  requests
import  ddt
import  unittest


url='https://www.lagou.com/jobs/positionAjax.json?needAddtionalResult=false'

def getHeaders():
   headers={
      'Content-Type':'application/x-www-form-urlencoded; charset=UTF-8',
      'User-Agent':'Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.100 Safari/537.36',
      'Cookie':'_ga=GA1.2.1237290736.1534169036; user_trace_token=20180813220356-b7e42516-9f01-11e8-bb78-525400f775ce; LGUID=20180813220356-b7e428ad-9f01-11e8-bb78-525400f775ce; index_location_city=%E5%85%A8%E5%9B%BD; _gid=GA1.2.675811712.1540794503; JSESSIONID=ABAAABAAAGFABEF93F47251563A52306423D37E945D2C54; _gat=1; LGSID=20181029213144-fa3c8e13-db7e-11e8-b51c-525400f775ce; PRE_UTM=; PRE_HOST=www.bing.com; PRE_SITE=https%3A%2F%2Fwww.bing.com%2F; PRE_LAND=https%3A%2F%2Fwww.lagou.com%2F; Hm_lvt_4233e74dff0ae5bd0a3d81c6ccf756e6=1539529521,1539785285,1540794503,1540819902; SEARCH_ID=ae3ae41a58d94802a68e848d36c30711; Hm_lpvt_4233e74dff0ae5bd0a3d81c6ccf756e6=1540819909; LGRID=20181029213151-fe7324dc-db7e-11e8-b51c-525400f775ce',
      'Referer':'https://www.lagou.com/jobs/list_%E8%87%AA%E5%8A%A8%E5%8C%96%E6%B5%8B%E8%AF%95%E5%B7%A5%E7%A8%8B%E5%B8%88?labelWords=sug&fromSearch=true&suginput=%E8%87%AA%E5%8A%A8%E5%8C%96%E6%B5%8B%E8%AF%95'}
   return headers

def f():
   list1=[]
   t=[i for i in range(1,31)]
   list1.append(t)
   return list1

@ddt.ddt
class LaGou(unittest.TestCase):
   @ddt.data((1,),(2,),(3,))
   @ddt.unpack
   def test_laGou(self,page):
      positions = []
      r = requests.post(
         url=url,
         headers=getHeaders(),
         data={'first': False, 'pn': page, 'kd': '自动化测试工程师'})
      self.assertEqual(r.json()['success'],True)
      print(r.json()['content']['positionResult']['result'][0]['city'])

if __name__ == '__main__':
   unittest.main(verbosity=2)

2.DDT的简单应用

#!/user/bin/env python
#coding:utf-8
#Author:shenqiang

from ddt import data,unpack,ddt
import unittest

@ddt
class MyDdtTest(unittest.TestCase):

    @data((1,1),(2,2),(3,3))
    @unpack
    def test_ddt(self,value1,value2):
        print('实际参数{0},预期参数{1}'.format(value1,value2))
        print(self.assertEqual(value1,value2))

if __name__ == '__main__':
    unittest.main(verbosity=2)

六.测试执行的log读取

#!/user/bin/env python
#coding:utf-8
#Author:shenqiang

import  logging
from selenium import  webdriver
import  unittest

def log(log_content):
    # 定义文件
    logFile = logging.FileHandler('log.md', 'a',encoding='utf-8')
    # log格式
    fmt = logging.Formatter(fmt='%(asctime)s-%(name)s-%(levelname)s-%(module)s:%(message)s')
    logFile.setFormatter(fmt)

    # 定义日志
    logger1 = logging.Logger('', level=logging.DEBUG)
    logger1.addHandler(logFile)
    logger1.info(log_content)

class Ui(unittest.TestCase):
    def setUp(self):
        self.driver=webdriver.Chrome()
        log('初始化浏览器')

    def test_001(self):
        log('开始测试')
        pass

    def tearDown(self):
        log('测试结束')
        self.driver.quit()

if __name__ == '__main__':
    unittest.main(verbosity=2)

 

 

 

 

 

       db= [datafordataindatas]
       print(db)
       
   finally:
       # 关闭游标和链接
       cur.close()
       connect.close()
​
connectMysql()
#!/user/bin/env python
#coding:utf-8
#Author:shenqiang
​
importpymysql
​
defconnectMysql():
   try:
       '''链接数据库'''
       connect= pymysql.connect(
           host='127.0.0.1',
           user='root',
           password='shen6409175',
           db='students'
      )
   exceptExceptionas e:
       returne.args
   else:
       '''创建游标'''
       cur= connect.cursor()
       '''导入数据'''
       # 单条语句的插入
       # sql = 'insert into student values(%s,%s,%s,%s);'
       # params = (6,'沈～','24','南京')
       '''批量插入数据'''
       sql= 'insert into student values(%s,%s,%s,%s);'
       params= [
          (7, '沈～', '24', '南京'),
          (8, '沈～', '24', '南京')
          ]
       cur.executemany(sql,params)
       '''insert后必须要commit()'''
       connect.commit()
   finally:
       # 关闭游标和链接
       cur.close()
       connect.close()
​
connectMysql()
#!/user/bin/env python
#coding:utf-8
#Author:shenqiang
​
importpymysql
​
defconnectMysql():
   try:
       '''链接数据库'''
       connect= pymysql.connect(
           host='127.0.0.1',
           user='root',
           password='shen6409175',
           db='students'
      )
   exceptExceptionas e:
       returne.args
   else:
       '''创建游标'''
       cur= connect.cursor()
       '''导入数据'''
       sql= 'delete from student where id = %s'
       params= (7,)
       cur.execute(sql,params)
       '''执行后必须要commit()'''
       connect.commit()
   finally:
       # 关闭游标和链接
       cur.close()
       connect.close()
​
connectMysql()
#!/user/bin/env python
#coding:utf-8
#Author:shenqiang
importpymysql
​
classMysqlTry:
   '''链接数据库'''
   defconnectMysql(self):
       '''尝试链接数据库'''
       try:
           connect=pymysql.connect(
               host= '127.0.0.1',
               user='root',
               password='shen6409175',
               db='students'
          )
       exceptExceptionase:
           print(e.args)
       returnconnect
​
   defselectMysql(self,sql,params):
       '''创建游标'''
       cur= self.connectMysql().cursor()
       '''查重'''
       cur.execute(sql,params)
       '''查询'''
       result= cur.fetchone()
       returnresult
​
defcheckValid(username,age):
   opera= MysqlTry()
   sql= "select * from student where name = %s and age = %s"
   params=(username,age)
   returnopera.selectMysql(sql=sql,params=params)
​
defcheckinfo():
   username= input('请输入用户名 
')
   age= input('请输入用户年龄 
')
   result= checkValid(username,age)
   ifresult:
       print('该用户在数据库中，测试通过！')
   else:
       print('该用户不在数据库中，存在bug！')
​
if__name__== '__main__':
   checkinfo()
#!/user/bin/env python
#coding:utf-8
#Author:shenqiang
importcsv
​
'''读取csv文件的方法'''
defReadCsvlist():
   '''方法一:列表方式取数据'''
#     '''通过列表提取csv文件'''
#     with open('csv.csv') as file:
#         '''reader是csv的迭代器'''
#         reader = csv.reader(file)
#         '''跳过首行'''
#         next(reader)
#         '''列表推倒式'''
#         db = [item for item in reader]
#         return db
#
# print(ReadCsvlist(),type(ReadCsvlist()))
   '''方法二：字典方式取数据'''
   withopen('csv.csv',encoding='utf-8') asfile:
       reader= csv.DictReader(file)
       foriteminreader:
           print(dict(item))
​
ReadCsvlist()
#!/use/bin/env python
#coding:utf-8
#Author:shenqiang
​
import csv
import requests
​
'''通过接口测试的技术获取拉钩网平台的资料'''
url='https://www.lagou.com/jobs/positionAjax.json?needAddtionalResult=false'
​
defgetHeaders():
  headers={
     'Content-Type':'application/x-www-form-urlencoded; charset=UTF-8',
     'User-Agent':'Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.100 Safari/537.36',
     'Cookie':'_ga=GA1.2.1237290736.1534169036; user_trace_token=20180813220356-b7e42516-9f01-11e8-bb78-525400f775ce; LGUID=20180813220356-b7e428ad-9f01-11e8-bb78-525400f775ce; index_location_city=%E5%85%A8%E5%9B%BD; JSESSIONID=ABAAABAAAGFABEFB3FA180CE47D5CD2C77E64B1B975127F; Hm_lvt_4233e74dff0ae5bd0a3d81c6ccf756e6=1539433248,1539529521,1539785285,1540794503; _gid=GA1.2.675811712.1540794503; _gat=1; LGSID=20181029142824-d6b66331-db43-11e8-83f6-5254005c3644; PRE_UTM=; PRE_HOST=www.bing.com; PRE_SITE=https%3A%2F%2Fwww.bing.com%2F; PRE_LAND=https%3A%2F%2Fwww.lagou.com%2F; SEARCH_ID=5e964af2d19e41f1903c1f4f5b41e1a5; Hm_lpvt_4233e74dff0ae5bd0a3d81c6ccf756e6=1540794522; LGRID=20181029142843-e1d2a63c-db43-11e8-83f6-5254005c3644',
     'Referer':'https://www.lagou.com/jobs/list_%E8%87%AA%E5%8A%A8%E5%8C%96%E6%B5%8B%E8%AF%95%E5%B7%A5%E7%A8%8B%E5%B8%88?labelWords=sug&fromSearch=true&suginput=%E8%87%AA%E5%8A%A8%E5%8C%96%E6%B5%8B%E8%AF%95'
  }
  returnheaders
​
deflaGou(page=2):
  positions=[]
  r=requests.post(
     url=url,
     headers=getHeaders(),
     data={'first':False,'pn':page,'kd':'自动化测试工程师'},
     timeout=10)
  foriinrange(1,15):
     city=r.json()['content']['positionResult']['result'][i]['city']
     #区
     district=r.json()['content']['positionResult']['result'][i]['district']
     #教育
     education=r.json()['content']['positionResult']['result'][i]['education']
     #工作年限
     workYear=r.json()['content']['positionResult']['result'][i]['workYear']
     #薪资
     salary=r.json()['content']['positionResult']['result'][i]['salary']
     #公司名称
     companyFullName=r.json()['content']['positionResult']['result'][i]['companyFullName']
     #公司大小
     companySize=r.json()['content']['positionResult']['result'][i]['companySize']
     #公司标签
     companyLabelList=r.json()['content']['positionResult']['result'][i]['companyLabelList']
     #工作标签
     positionLables=r.json()['content']['positionResult']['result'][i]['positionLables']
     position={
        '城市':city,
        '区域':district,
        "学历":education,
        "工作年限":workYear,
        "薪资":salary,
        "公司名称":companyFullName,
        "公司大小":companySize,
        "公司标签":companyLabelList,
        "工作标签":positionLables
    }
     positions.append(position)
  headers=['城市','区域','学历','工作年限','薪资','公司名称','公司大小','公司标签','工作标签']
  withopen('lagou.csv', 'w', encoding='gbk', newline='') asf:
     writer= csv.DictWriter(f, headers)
     writer.writeheader()
     writer.writerows(positions)
  # for item in positions:
  # with open('lagou.csv','w',encoding='utf-8',newline='') as f:
  #     writer=csv.DictWriter(f,headers)
  #     writer.writeheader()
  #     writer.writerows(positions)
​
​
laGou()
#!/user/bin/env python
#coding:utf-8
#Author:shenqiang
import csv
import requests
import ddt
import unittest
​
​
url='https://www.lagou.com/jobs/positionAjax.json?needAddtionalResult=false'
​
defgetHeaders():
  headers={
     'Content-Type':'application/x-www-form-urlencoded; charset=UTF-8',
     'User-Agent':'Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/69.0.3497.100 Safari/537.36',
     'Cookie':'_ga=GA1.2.1237290736.1534169036; user_trace_token=20180813220356-b7e42516-9f01-11e8-bb78-525400f775ce; LGUID=20180813220356-b7e428ad-9f01-11e8-bb78-525400f775ce; index_location_city=%E5%85%A8%E5%9B%BD; _gid=GA1.2.675811712.1540794503; JSESSIONID=ABAAABAAAGFABEF93F47251563A52306423D37E945D2C54; _gat=1; LGSID=20181029213144-fa3c8e13-db7e-11e8-b51c-525400f775ce; PRE_UTM=; PRE_HOST=www.bing.com; PRE_SITE=https%3A%2F%2Fwww.bing.com%2F; PRE_LAND=https%3A%2F%2Fwww.lagou.com%2F; Hm_lvt_4233e74dff0ae5bd0a3d81c6ccf756e6=1539529521,1539785285,1540794503,1540819902; SEARCH_ID=ae3ae41a58d94802a68e848d36c30711; Hm_lpvt_4233e74dff0ae5bd0a3d81c6ccf756e6=1540819909; LGRID=20181029213151-fe7324dc-db7e-11e8-b51c-525400f775ce',
     'Referer':'https://www.lagou.com/jobs/list_%E8%87%AA%E5%8A%A8%E5%8C%96%E6%B5%8B%E8%AF%95%E5%B7%A5%E7%A8%8B%E5%B8%88?labelWords=sug&fromSearch=true&suginput=%E8%87%AA%E5%8A%A8%E5%8C%96%E6%B5%8B%E8%AF%95'}
  returnheaders
​
deff():
  list1=[]
  t=[iforiinrange(1,31)]
  list1.append(t)
  returnlist1
​
@ddt.ddt
classLaGou(unittest.TestCase):
  @ddt.data((1,),(2,),(3,))
  @ddt.unpack
  deftest_laGou(self,page):
     positions= []
     r= requests.post(
        url=url,
        headers=getHeaders(),
        data={'first': False, 'pn': page, 'kd': '自动化测试工程师'})
     self.assertEqual(r.json()['success'],True)
     print(r.json()['content']['positionResult']['result'][0]['city'])
​
if__name__== '__main__':
  unittest.main(verbosity=2)
#!/user/bin/env python
#coding:utf-8
#Author:shenqiang
​
fromddtimportdata,unpack,ddt
importunittest
​
@ddt
classMyDdtTest(unittest.TestCase):
​
   @data((1,1),(2,2),(3,3))
   @unpack
   deftest_ddt(self,value1,value2):
       print('实际参数{0},预期参数{1}'.format(value1,value2))
       print(self.assertEqual(value1,value2))
​
if__name__== '__main__':
   unittest.main(verbosity=2)
#!/user/bin/env python
#coding:utf-8
#Author:shenqiang
​
import logging
fromseleniumimport webdriver
import unittest
​
deflog(log_content):
   # 定义文件
   logFile= logging.FileHandler('log.md', 'a',encoding='utf-8')
   # log格式
   fmt= logging.Formatter(fmt='%(asctime)s-%(name)s-%(levelname)s-%(module)s:%(message)s')
   logFile.setFormatter(fmt)
​
   # 定义日志
   logger1= logging.Logger('', level=logging.DEBUG)
   logger1.addHandler(logFile)
   logger1.info(log_content)
​
classUi(unittest.TestCase):
   defsetUp(self):
       self.driver=webdriver.Chrome()
       log('初始化浏览器')
​
   deftest_001(self):
       log('开始测试')
       pass
​
   deftearDown(self):
       log('测试结束')
       self.driver.quit()
​
if__name__== '__main__':
   unittest.main(verbosity=2)