机器学习 第一章 Python复习（9）数据入库 pymysql

1 pymysql

参考文档

SQL工具

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 表设计

案例需求：

1. 读取网页数据后，保存到文本文件。然后读取出来
2. 循环插入到数据库中
3. 如果发生数据改变，则要更新NAV和ACCNAV，并更新“updatetime”

参考文档：python中的日期

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)