简单粗暴直接上代码:
- 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() 值要加入单引号或双引号哦