Python操作Mysql（新浪微博实战）
1、安装命令：
pip3 install pymysql Python 操作第三方Mysql库
2、mysql -h localhost -u root -p 进入本地mysql
create database testDev; 创建testDev数据库
create table `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`first_name` varchar(20) DEFAULT NULL,
`last_name` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT '18',
`sex` varchar(6) DEFAULT NULL,
`salary` float DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4; 创建表user
3、表中插入单条信息
import pymysql from utils.jsonutils import readJson from utils.yamlUtils import readYaml def insertOne(): try: conn=pymysql.connect(host=readJson()["mysql"]["host"],user=readJson()["mysql"]["user"], passwd=readJson()["mysql"]["passwd"],db=readJson()["mysql"]["db"]) except Exception as e: raise e.args else: # "获取游标" cur=conn.cursor() sql='insert into user values (%s,%s,%s,%s,%s,%s)' params=(1,'hi','hi name',19,'boy',1008) cur.execute(sql,params) conn.commit() finally: cur.close() conn.close() insertOne()
表中插入多条信息
def insertMany(): try: conn=pymysql.connect(host='127.0.0.1',user='root',passwd='wuyikai521',db='testDev') except Exception as e: raise e.args else: cur=conn.cursor() sql='insert into user values (%s,%s,%s,%s,%s,%s)' params=[ (2, 'hi', 'hi name', 19, 'boy', 1010), (3, 'hi', 'hi name', 19, 'boy', 1019), (4, 'hi', 'hi name', 19, 'boy', 1020) ] cur.executemany(sql,params)
#commit提交 conn.commit() finally: cur.close()
#关闭游标 conn.close()
#关闭链接 insertMany()
4、查询单条数据 利用YAML文件分离IP 等等
def queryOne(): try: conn=pymysql.connect(host=readYaml()["mysql"]["host"],user=readYaml()["mysql"]["user"], passwd=readYaml()["mysql"]["passwd"],db=readYaml()["mysql"]["db"]) except Exception as e: raise e.args else: cur=conn.cursor() sql="select * from user where id=%s;" params=(3,) cur.execute(sql,params) """获取查询的信息""" data=cur.fetchone() print(data) finally: cur.close() conn.close() queryOne()
5、修改ID为1的first_name
def updateOne(): try: conn=pymysql.connect(host='127.0.0.1',user='root',passwd='wuyikai521',db='testDev') except Exception as e: raise e.args else: cur=conn.cursor() sql="update user set first_name=%s where id=%s;" params=('HA',1) cur.execute(sql,params) conn.commit() finally: cur.close() conn.close() updateOne()
6、单条删除
def deleteOne(): try: conn=pymysql.connect(host='127.0.0.1',user='root',passwd='wuyikai521',db='testDev') except Exception as e: raise e.args else: cur=conn.cursor() sql="delete from user where id=%s;" params=(4,) cur.execute(sql,params) conn.commit() finally: cur.close() conn.close() deleteOne()
7、查询全部
def queryMany(): try: conn=pymysql.connect(host=readJson()["mysql"]["host"],user=readJson()["mysql"]["user"], passwd=readJson()["mysql"]["passwd"],db=readJson()["mysql"]["db"]) except Exception as e: raise e.args else: cur=conn.cursor() sql="select * from user;" cur.execute(sql) """获取查询的信息""" data=cur.fetchall() for item in data: print(item) finally: cur.close() conn.close() queryMany()
8、新浪微博登录
from selenium.webdriver.common.by import By from base.base import WebUI import time as t class Weibo(WebUI): username_loc=(By.ID,'loginname') password_loc=(By.NAME,'password') error_loc=(By.XPATH,'/html/body/div[3]/div/p/span[2]') clickLogin_loc=(By.XPATH,'//*[@id="pl_login_form"]/div/div[3]/div[6]/a') duanxin_loc=(By.ID,'messageCheck') receive_loc=(By.XPATH,'/html/body/div/div/div/div[1]/div/div[2]/div[3]/div[1]/div[2]/div/a') def inputUsername(self,username): """输入帐号""" self.findElement(*self.username_loc).send_keys(username) t.sleep(3) def inputPassword(self,password): """输入密码""" self.findElement(*self.password_loc).send_keys(password) t.sleep(3) @property def clickLogin(self): """点击登录""" self.findElement(*self.clickLogin_loc).click() t.sleep(3) def login(self,username='15730996037',password='521125'): self.inputUsername(username=username) self.inputPassword(password=password) self.clickLogin t.sleep(30) def getError(self): """获取错误信息""" return self.findElement(*self.error_loc).text t.sleep(3) def clickDuanxin(self): """点击短息登录""" self.findElement(*self.duanxin_loc).click() t.sleep(3) def receiveYanzheng(self): """点击接受验证码""" self.findElement(*self.receive_loc).click() t.sleep(60)
对象层的继承 三代继承 子继承父 父继承祖父
from selenium.webdriver.common.by import By from base.base import WebUI from page.weibo import Weibo import time as t class WeiboProfile(Weibo): profile_loc=(By.XPATH,'//*[@id="skin_cover_s"]/div/a/img') concern_loc=(By.XPATH,'//*[@id="Pl_Core_T8CustomTriColumn__3"]/div/div/div/table/tbody/tr/td[1]/a/strong') fans_loc=(By.XPATH,'//*[@id="Pl_Core_T8CustomTriColumn__3"]/div/div/div/table/tbody/tr/td[2]/a/strong') number_loc=(By.XPATH,'//*[@id="Pl_Core_T8CustomTriColumn__3"]/div/div/div/table/tbody/tr/td[3]/a/strong') nick_loc=(By.XPATH,'//*[@id="Pl_Official_Headerv6__1"]/div[1]/div/div[2]/div[2]/h1') signature_loc=(By.XPATH,'//*[@id="Pl_Official_Headerv6__1"]/div[1]/div/div[2]/div[3]') @property def clickProfile(self): """点击首页""" self.findElement(*self.profile_loc).click() t.sleep(3) def homePage(self): self.login() self.clickProfile @property def getConcern(self): """获取关注数""" return int(self.findElement(*self.concern_loc).text) t.sleep(3) @property def getFans(self): """获取粉丝数""" return int(self.findElement(*self.fans_loc).text) t.sleep(3) @property def getNumber(self): """获取微博数""" return int(self.findElement(*self.number_loc).text) t.sleep(3) @property def getNick(self): """获取昵称""" return self.findElement(*self.nick_loc).text t.sleep(3) @property def getSig(self): """获取签名""" return self.findElement(*self.signature_loc).text t.sleep(3)
登录之后 点击首页 进行测试
from page.weiboprofile import WeiboProfile from page.init import InitWeibo from utils.jsonutils import readJson import unittest class WeiboTest(InitWeibo,WeiboProfile): def test_weiboProfile_001(self): """校验关注数""" self.homePage() self.assertEqual(self.getConcern,276) def test_weiboProfile_002(self): """校验粉丝数""" self.homePage() self.assertEqual(self.getFans,327) def test_weiboProfile_003(self): """校验微博数""" self.homePage() self.assertEqual(self.getNumber,1313) def test_weiboProfile_004(self): """校验昵称""" self.homePage() self.assertEqual(self.getNick,'吃不胖的瘦子121') def test_weiboProfile_005(self): """校验签名""" self.homePage() self.assertEqual(self.getSig,'爱玩，单身青年。')
