数据驱动实战
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)
