python使用pymysql把数据写入mysql

简单粗暴直接上代码:

import pymysql
import requests
from lxml import etree


headers = {"User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/78.0.3904.108 Safari/537.36"}

def connect():
    # 连接本地数据库
    db = pymysql.connect(host='localhost',
            user='root',
            password='bbqbbq',
            database='stock',
            charset='utf8',
            cursorclass=pymysql.cursors.DictCursor)

    try:
        # 使用cursor()创建游标对象cursor
        cursor = db.cursor()
        # 使用execute()方法执行SQL DROP: 如果表存在则删除
        cursor.execute("drop table if exists stockinfo")
        # 创建表语句
        # ENGINE=InnoDB使用innodb引擎,为MySQL AB发布binary的标准之一.
        # DEFAULT CHARSET=utf8 数据库默认编码为utf-8
        sql = """
                create table stockinfo(
                id int(11) not null primary key auto_increment,
                stockid int(11) not null,
                stockname varchar(64) not null)
                ENGINE=InnoDB DEFAULT CHARSET=utf8;

            """
        # 执行sql语句
        cursor.execute(sql)

        # 插入数据
        for stock in get_stock():
            stockid = stock.replace("(",",").replace(")","").split(",")[1].strip()
            stockname = stock.replace("(",",").replace(")","").split(",")[0].strip()
            print(stockid, stockname)

            sql_stock = """insert into stockinfo (stockid, stockname) values('%s', '%s')""" %(stockid, stockname)

            # 执行sql语句
            cursor.execute(sql_stock)

        # 提交代码并保存变化
        db.commit()
        print("代码执行完毕!")

    except pymysql.Error as err:
        print(err)
    finally:
        cursor.close()
        db.close()


def get_stock():
    # 所有A股代码列表
    url = "http://quote.eastmoney.com/stock_list.html"
    response = requests.get(url, headers=headers)
    response.encoding = "gbk"
    e = etree.HTML(response.text)
    stock_list = e.xpath( '//div[@class="quotebody"]//ul/li/a/text()')
    return stock_list



if __name__ == "__main__":
    connect()

注意:

sql_stock = """insert into stockinfo (stockid, stockname) values('%s', '%s')""" %(stockid, stockname)

这里的values() 值要加入单引号或双引号哦