项目简介
(Python)爬虫 + MySQL + Redis项目.
爬取下来的数据可用于后续的数据分析(我计划将其用于我的毕业设计).
未来会将数据分析的可视化部署到服务器上, 并添加股票降价通知的功能的web接口.
爬虫
执行频率为每天1次, 爬取当天的4192支股票的统计信息. 并存放到数据库中.
并定期导出相应的表用于数据分析.
运行说明
首先要保证本机已经安装MySQL和Redis. 远程连接也可以, 但是需要修改源代码重新Build.
注意, 数据库密码和邮箱授权码已经过脱敏处理, 请务必配置成自己的密码和授权码然后再构建
爬虫部分
采用单线程Python爬虫 + MySQL. 主要文件在./PythonCrawler中
运行文件databaseInitialization.py初始化数据库和相应的表, 数据库连接默认是localhost:3306
运行文件findAllStocksCode.py获得所有4192支股票的股票代码和名称, 这时会得到StockCodes.txt文件
修改insertRecords.py等以"insertRecords"开头的文件中的股票信息路径, 修改为StockCodes.txt文件的绝对路径, crontab定时执行需要绝对路径.
with open(“/home/haiyang/StockCodes.txt”, “r”, encoding=“utf-8”) as f:
开启定时爬虫crontab -e, 如果对每次执行的时间没有限制, 推荐使用单线程爬虫insertRecords.py. 如果对时间有要求, 可以使用进程池, 线程池, 线程池+连接池, 异步IO等方法加速. 但是爬取失败的可能性会增加.
单线程爬虫insertRecords.py的参考时间为406秒
多进程爬虫insertRecordsMultiProcesses.py 的参考时间为150秒
多线程爬虫insertRecordsMultiThread.py的参考时间为146秒
多线程+数据库连接池爬虫insertRecordsMultiThreadConnectionPool.py的参考时间为143秒
程序运行截图：
核心程序代码
databaseInitialization.py
import pymysqlimport tracebackdef initializeDatabase():try:cursor = db.cursor()createDatabase = "CREATE DATABASE IF NOT EXISTS stocks"cursor.execute(createDatabase)except Exception as e:traceback.print_exc()def initializeStockTable():try:cursor = db.cursor()cursor.execute("USE stocks")sql = ''' CREATE TABLE IF NOT EXISTS stock(stock_id INT AUTO_INCREMENT,stock_code CHAR(8) NOT NULL,stock_name VARCHAR(20) NOT NULL,PRIMARY KEY(stock_id));'''cursor.execute(sql)cursor.execute("SELECT COUNT(*) FROM stock;")count = cursor.fetchone()if count[0] == 0:with open("./StockCodes.txt", "r", encoding="utf-8") as f:lines = f.readlines()for line in lines:line = line.strip()insert = "INSERT INTO stock(stock_code, stock_name) VALUES('{}','{}')" \.format(line.split(",")[0], line.split(",")[1])cursor.execute(insert)mit()else:passexcept Exception as e:traceback.print_exc()# Table Interpret 用来解释record表中各个列的含义, 以及表中的格式和现实的格式def initializeInterpretTable():cursor = db.cursor()cursor.execute("USE stocks")#sql0 = '''TRUNCATE TABLE Interpret;'''#cursor.execute(sql0);sql = '''CREATE TABLE IF NOT EXISTS Interpret(record_column CHAR(5) NOT NULL,explanation VARCHAR(20) NOT NULL,format_in_record VARCHAR(30) NOT NULL,format_in_reality VARCHAR(30) NOT NULL);'''cursor.execute(sql)sqls = []sqls.append("INSERT INTO Interpret VALUES('f43', '最新', '5471', '54.71')")sqls.append("INSERT INTO Interpret VALUES('f44', '最高', '5817', '58.17')")sqls.append("INSERT INTO Interpret VALUES('f45', '最低', '5118', '51.18')")sqls.append("INSERT INTO Interpret VALUES('f46', '今开', '5140', '51.40')")sqls.append("INSERT INTO Interpret VALUES('f47', '成交量', '836250', '83.62万手')")sqls.append("INSERT INTO Interpret VALUES('f48', '成交额', '4616348672.0', '46.16亿')")sqls.append("INSERT INTO Interpret VALUES('f50', '量比', '265', '2.65')")sqls.append("INSERT INTO Interpret VALUES('f57', '股票代码', '688981', '688981')")sqls.append("INSERT INTO Interpret VALUES('f60', '昨收', '5094', '50.94')")sqls.append("INSERT INTO Interpret VALUES('f168', '换手率', '804', '8.04%')")sqls.append("INSERT INTO Interpret VALUES('f169', '上涨', '647', '6.47')")sqls.append("INSERT INTO Interpret VALUES('f170', '上涨百分比', '1270', '12.70%')")for sql in sqls:cursor.execute(sql)mit()def initializeRecordTable():cursor = db.cursor()cursor.execute("USE stocks")sql = '''CREATE TABLE IF NOT EXISTS Record(record_id BIGINT AUTO_INCREMENT,record_time TIMESTAMP NOT NULL,f57 CHAR(8) NOT NULL,f43 INT NOT NULL,f44 INT NOT NULL,f45 INT NOT NULL,f46 INT NOT NULL,f47 BIGINT NOT NULL,f48 DECIMAL(13,1) NOT NULL,f50 SMALLINT NOT NULL,f60 INT NOT NULL,f168 SMALLINT NOT NULL,f169 INT NOT NULL,f170 INT NOT NULL,PRIMARY KEY(record_id));'''cursor.execute(sql)if __name__ == "__main__":connectParams = {"host": "localhost", "port": 3306, "user": "root", "password": "sxing86"}db = pymysql.connect(**connectParams)try:initializeDatabase()initializeStockTable()initializeInterpretTable()initializeRecordTable()except Exception as e:traceback.print_exc()finally:db.close()
完整程序代码下载地址：股票数据爬取并存入mysql完整源代码
Python爬取股票数据存入mysql数据库 获取股票（最新 最高 今开 成交量 成交额 量比 换手率 涨幅等）支持多线程+数据库连接池
