数据驱动实战

1.数据驱动操作elcel文件的操作方法

import xlrd
import os
from xlutils.copy import copy

def base_dir(filename):
    return os.path.join(os.path.dirname(__file__),filename)
# '''elcel文件的操作'''
# work = xlrd.open_workbook(base_dir("data.xls"))
# sheet = work.sheet_by_index(0)
# #查看文件有多少行
# print(sheet.nrows)
# #获取单元格的内容
# print(sheet.cell_value(2,2))

'''excel文件内容的修改'''
work = xlrd.open_workbook(base_dir("data.xls"))
old_content=copy(work)
ws = old_content.get_sheet(0)
ws.write(4,2,"321")
old_content.save(base_dir("data1.xls"))

2.数据驱动实战configparser的应用

import os
import configparser

def base_dir(filename=None):
    return os.path.join(os.path.dirname(__file__),filename)

def getLinux():
    list1=[]
    config = configparser.ConfigParser()
    config.read(base_dir("config.ini"))
    ip = config.get("linux","IP")
    port = config.get("linux","PORT")
    username = config.get("linux","USERNAME")
    password = config.get("linux","PASSWORD")
    list1.append(ip)
    list1.append(port)
    list1.append(username)
    list1.append(password)
    return list1
print(getLinux()[0])

 

import os
import configparser

def base_dir(filename=None):
    return os.path.join(os.path.dirname(__file__),filename)

def getLinux(linux="linux"):
    list1=[]
    config = configparser.ConfigParser()
    config.read(base_dir("config.ini"))
    ip = config.get(linux,"IP")
    port = config.get(linux,"PORT")
    username = config.get(linux,"USERNAME")
    password = config.get(linux,"PASSWORD")
    list1.append(ip)
    list1.append(port)
    list1.append(username)
    list1.append(password)
    return list1
print(getLinux())

3.数据驱动实战MySQL安装和pymysql的安装

windows下安装pymssql

安装方法一： Pycharm
安装python3.6以上的Pycharm直接安装pymssql是安装失败的，因为在提供的库里面最新版本不对应python版本。

例如：python3.6以上，pycharm里的库地址是 https://pypi.python.org/simple ，此库中最新版只支持到python3.5。

安装方法二：pip
3.6以上直接 pip install pymssql是安装不了的，去https://www.lfd.uci.edu/~gohlke/pythonlibs/ 这里下载

pymssql‑2.1.4.dev5‑cp37‑cp37m‑win32.whl
pymssql‑2.1.4.dev5‑cp37‑cp37m‑win_amd64.whl

我没找到这个网站下的pymssql‑2.1.4.dev5‑cp37‑cp37m‑win_amd64.whl，从其他网站下载了一个可以用的。

可以在这个里面找到资源：https://download.csdn.net/download/yinshuilan/10540005


根据python版本下载后 ，执行 pip install pymssql‑2.1.4.dev5‑cp37‑cp37m‑win_amd64.whl 安装就行了（记得直接在pymssql‑2.1.4.dev5‑cp37‑cp37m‑win_amd64.whl 所在的文件夹打开CMD，或者指定文件路径）

4.数据驱动实战MySQL查询的应用

import pymysql

def connMySQL():
   try:
      conn = pymysql.connect(
         host="localhost",
         user="root",
         passwd="123456",
         db="xuhongchaoa"
      )
   except Exception as e:
      return e.args
   else:
      cur = conn.cursor()
      sql = "select * from userinfo where username=%s"
      params = ("xuxuchao1")
      cur.execute(sql,params)
      data = cur.fetchall()
      # for item in data:
      #  print(item)
      db = [item for item in data]
      print(db)
   finally:
      cur.close()
      conn.close()
if __name__ == '__main__':
    connMySQL()

5.数据驱动MySQL插入数据的应用

import pymysql

def insertMysql():
    try:
        conn = pymysql.connect(
            host = "localhost",
            user = "root",
            password = "123456",
            db = "xuhongchaoa"
        )
    except Exception as e:
        return e.args
    else:
        cur = conn.cursor()
        # 单条语句的插入
        # sql='insert into userinfo values (%s,%s,%s,%s) '
        # params=(3,'weike',18,'xian')
        # 批量插入
        sql = "insert into userinfo values (%s,%s,%s,%s)"
        params = [
            (10,"xuxuchao221","2312321","dswer@qq.com"),
            (11, "xuxuchao451","2312321","ds47wer@qq.com")
        ]
        cur.executemany(sql,params)
        conn.commit()
    finally:
        cur.close()
        conn.close()

if __name__ == '__main__':
    insertMysql()

6.数据驱动实战MySQL删除的应用

import pymysql

def deleteMysql():
    try:
        conn=pymysql.connect(
            host="127.0.0.1",
            user="root",
            password="123456",
            db="xuhongchaoa"
        )
    except Exception as e:
        return e.args
    else:
        cur=conn.cursor()
        sql="delete from userinfo where id=%s"
        params=(3)
        cur.execute(sql,params)
        conn.commit()
    finally:
        cur.close()
        conn.close()
if __name__ == '__main__':
    deleteMysql()

 

7.数据驱动实战MySQL分层的应用

import pymysql

class MysqlHelper():
    def conn(self):
        con = pymysql.connect(
            host="127.0.0.1",
            user="root",
            passwd="123456",
            db="xuhongchaoa"
        )
        return con
    def get_one(self,sql,params):
        cur = self.conn().cursor()
        data = cur.execute(sql,params)
        result = cur.fetchone()
        return result

def checkValid(username,password):
    opera = MysqlHelper()
    sql = "select * from userinfo where username=%s and password=%s"
    params = (username,password)
    return opera.get_one(sql=sql,params=params)

def info():
    username=input("请输入用户名：")
    password=input("请输入密码：")
    result=checkValid(username,password)
    if result:
        print("登录成功，昵称{0}".format(username))
    else:
        print("登录失败")

if __name__ == '__main__':
    info()

8.数据驱动实战CSV文件的读取（按列表和字典）

 

import csv

def readCsvList():
    with open("csv.csv","r") as f:
        reader = csv.reader(f)
        next(reader)
        for item in reader:
            print(item)
readCsvList()

def readCsvDict():
    with open("csv.csv","r") as f:
        reader = csv.DictReader(f)
        for item in reader:
            print(dict(item))
readCsvDict()

9.获取招聘信息的数据

import  csv
import  requests

def readCsvList():
    with open('csv.csv','r') as f:
        reader=csv.reader(f)
        next(reader)
        for x in reader:
            print(x)

def readCsvDict():
    with open('csv.csv','r') as f:
        reader=csv.DictReader(f)
        for x in reader:
            print(dict(x) )



def getHeaders():
    headers={
        'Content-Type':'',
        'User-Agent':'',
        'Cookie':'',
        'Referer':''}
    return headers


def laGou(url='https://www.lagou.com/jobs/positionAjax.json?needAddtionalResult=false',page=2):
    positions = []
    r = requests.post(
        url=url,
        headers=getHeaders(),
        data={'first': False, 'pn': page, 'kd': '自动化测试工程师'})
    for i in range(15):
        city = r.json()['content']['positionResult']['result'][i]['city']
        education = r.json()['content']['positionResult']['result'][i]['education']
        workYear = r.json()['content']['positionResult']['result'][i]['workYear']
        positionAdvantage = r.json()['content']['positionResult']['result'][i]['positionAdvantage']
        salary = r.json()['content']['positionResult']['result'][i]['salary']
        companyFullName = r.json()['content']['positionResult']['result'][i]['companyFullName']
        positionLables = r.json()['content']['positionResult']['result'][i]['positionLables']
        position = {
            '公司名称': companyFullName,
            '城市': city,
            '学历': education,
            '工作年限': workYear,
            '薪资': salary,
            '工作标签': positionLables,
            '福利': positionAdvantage
        }
        positions.append(position)
    for item in positions:
        print(item)

if __name__ == '__main__':
    for item in range(1, 31):
        laGou(page=item)

10.招聘信息的数据写入到CSV文件中

 def writeCsv():

    headers={'公司名称','城市','学历','工作年限','薪资','工作标签','福利'}
    for item in range(1, 31):
        positions=laGou(page=item)
        with open('lagou.csv','a',newline='',encoding='gbk') as f:
            writer=csv.DictWriter(f,headers)
            writer.writeheader()
            writer.writerows(positions)
writeCsv()

11.ddt库在接口测试中的实战和应用

import  csv
import  requests
import  ddt
import  unittest


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

def getHeaders():
    headers={
        'Content-Type':'',
        'User-Agent':'',
        'Cookie':'',
        'Referer':''}
    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)

12.Python对Xml文件的操作

import  xml.dom.minidom
import  os

def getXml(value=None):
    '''获取单节点的数据内容'''
    xmlFile=xml.dom.minidom.parse('data.xml')
    db=xmlFile.documentElement
    itemList=db.getElementsByTagName(value)
    item=itemList[0]
    return item.firstChild.data


def getUser(parent='WuYA',child=None):
    '''获取单节点的数据内容'''
    xmlFile=xml.dom.minidom.parse('data.xml')
    db=xmlFile.documentElement
    itemList=db.getElementsByTagName(parent)
    item=itemList[0]
    return item.getAttribute(child)

print(getUser(child='address'))

13.Python对日志文件的操作    /    14.演示日志文件再接口测试中的应用

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)