1 pymysql
1.1 安装
$ (env) python3 -m pip install PyMySQL
1.2基本使用
import pymysql
1.2.1 Connection 对象
connection = pymysql.connect(host='localhost',
user='user',
password='passwd',
db='db',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
使用套路:引用外部配置
在外部的包中创建一个config.py
import pymysql
# 数据库连接配置
dbcondfig = {
"host":'localhost',
"user":'root',
"password":'123123',
"db":'test',
"charset":'utf8',
"cursorclass":pymysql.cursors.DictCursor
}
1.2.2 Cursor对象
连接、操作数据库执行SQL
Cursor
类别 | 描述 |
---|---|
Cursor | 默认,查询返回list或tuple |
DictCursor | 查询返回dict,包含字段名 |
SSCursor | 效果同Cursor。 无缓存游标 |
SSDictCursor | 效果通DictCursor 。无缓存游标 |
1.2.3执行参数
# 元组类参数
cursor.execute(query="SELECT * from jt_news where news_id=%s",args=(2,))
# 字典类参数
cursor.execute(query="SELECT * from jt_news where news_id=%(newsid)s",args={"newsid":3})
1.2.4 执行SQL
# 执行单条SQL
cursor.execute(query="insert into news(title) values(%s)",args=['测试新闻1'])
connection.commit() #由于默认提交 是 默认被pymysql关闭的,因此要commit
# 执行多条SQL
cursor.executemany(query="insert into news(title) values(%(news_title)s)"
,args=({"news_title":"'测试新闻1"},{"news_title":'测试新闻2'}))
1.3 案例
mysql 表设计
案例需求:
- 读取网页数据后,保存到文本文件。然后读取出来
- 循环插入到数据库中
- 如果发生数据改变,则要更新NAV和ACCNAV,并更新“updatetime”
from urllib.request import urlopen
from bs4 import BeautifulSoup
from common import config
import pymysql
from datetime import datetime
response = urlopen("http://fund.eastmoney.com/fund.html")
html = response.read()
html = html.decode('gb2312')
with open("./html/1.txt", 'wb') as f:
f.write(html.encode('utf8'))
f.close()
with open("./html/1.txt", "rb") as f:
html = f.read().decode("utf8")
bsObj = BeautifulSoup(html, "html.parser")
FCodes = bsObj.findAll("", {"class": "bzdm"})
res = []
for FCode in FCodes:
res.append({
"fcode": FCode.get_text(),
"fname": FCode.next_sibling.a.get_text(),
"NAV": FCode.next_sibling.next_sibling.get_text(),
"ACCNAV": FCode.next_sibling.next_sibling.next_sibling.get_text(),
"updatetime": datetime.now().isoformat(sep=' ', timespec="seconds"),
})
# 数据入库
conn = pymysql.connect(**config.dbcondfig)
# 插入
try:
with conn.cursor() as cursor:
cursor.executemany("""INSERT INTO `funddb`.`myfund`(fcode,fname,NAV,ACCNAV,updatetime)
VALUES(%(fcode)s,%(fname)s,%(NAV)s,%(ACCNAV)s,%(updatetime)s)
ON DUPLICATE KEY UPDATE `NAV`=VALUES(NAV),`ACCNAV`=VALUES(ACCNAV),`updatetime`=VALUES(updatetime);"""
,res)
conn.commit()
except Exception as e:
print(e)
finally:
conn.close()
注意事项:
executemany和ON DUPLICATE KEY UPDATE联合使用的时候不能安装sql常规模式,为软件bug,需要使用values()函数规避
参考链接
# 以下代码都会报错
cursor.executemany("""INSERT INTO `funddb`.`myfund`(fcode,fname,NAV,ACCNAV,updatetime)
VALUES(%(fcode)s,%(fname)s,%(NAV)s,%(ACCNAV)s,%(updatetime)s)
ON DUPLICATE KEY UPDATE `NAV`=%s,`ACCNAV`=%s,`updatetime`=%s;"""
,res)
cursor.executemany("""INSERT INTO `funddb`.`myfund`(fcode,fname,NAV,ACCNAV,updatetime)
VALUES(%s,%s,%s,%s,%s)
ON DUPLICATE KEY UPDATE `NAV`=%(NAV)s,`ACCNAV`=%(ACCNAV)s,`updatetime`=%(updatetime)s;"""
,res)