1. Johngo学长首页
  2. 技术点详细复盘专题
  3. 大数据

python爬取股票数据并存到数据库

• 大数据 • 阅读 53

要import的库

import tushare as t1
import time
from config import *
from SQLserver import *


"""此为存放配置信息的config.py"""
tushare = {"token":"", "column": ['ts_code', 'trade_date', 'open', 'high', 'low', 'close', 'pre_close', 'change', 'pct_chg', 'vol', 'amount'],}
dbPath = r'\database.db'
Stock_list={"平安银行": "000001.SZ","万科A": "000002.SZ",
              "国华网安": "000004.SZ",
              "世纪星源": "000005.SZ",
              "深振业A": "000006.SZ",
              "全新好": "000007.SZ"}


class Fetch_Data():
    """code股票代码 sd开始日期 ed结束日期"""

    def __init__(self, code, start_data, end_data):
        self.code = code
        self.sd = start_data
        self.ed = end_data

    def web_spider(self):
        try:
            df = ts.daily(ts_code=self.code, start_date=self.sd, end_date=self.ed)

            return df
        except Exception as e:
            print(e)

当然仅有这段代码是不行的，我们还需要初始化tushare接口。初始化本写在main中，这里为了方便大家使用提到前面来说。

        t1.set_token(tushare["token"])
        ts = t1.pro_api()

2.存储到Sqlite数据库


class Sql_Data_Handle():
    def __init__(self, df, connect, curs):
        self.df = df
        self.connect = connect
        self.curs = curs

    def character(self):

        line_num = self.line_num

        ts_code = self.df.iloc[line_num]["ts_code"]
        trade_date = self.df.iloc[line_num]["trade_date"]
        open = self.df.iloc[line_num]["open"]
        high = self.df.iloc[line_num]["high"]
        low = self.df.iloc[line_num]["low"]
        close = self.df.iloc[line_num]["close"]
        pre_close = self.df.iloc[line_num]["pre_close"]
        change = self.df.iloc[line_num]["change"]
        pct_chg = self.df.iloc[line_num]["pct_chg"]
        vol = self.df.iloc[line_num]["vol"]
        amount = self.df.iloc[line_num]["amount"]

        values = (
            ts_code, trade_date, float(open), float(high), float(low), float(close), float(pre_close),
            float(change), float(pct_chg), float(vol), float(amount))
        return values
    def add_data(self):

        self.line_num = len(self.df) - 1

        while self.line_num >= 0:
            value_list = self.character()
            try:

                Sql.Sql_execution(
                    f"""insert into stock ({tushare["Column"]}) values """ + f"(\'{value_list[0]}\',\'{value_list[1]}\',\'{value_list[2]}\',\'{value_list[3]}\',\'{value_list[4]}\',\'{value_list[5]}\',\'{value_list[6]}\',\'{value_list[7]}\',\'{value_list[8]}\',\'{value_list[9]}\',\'{value_list[10]}\')")
            except Exception:

                print(f"{value_list[0]}-{value_list[1]}未被添加！")
            self.line_num -= 1
            i += 1
        print(f"The data has been added to the stock database")
        return i

3.主函数
包括了连接数据库 初始化tushare Pro接口 调用Fetch_Data，Sql_Data_Handle类 计算添加用时和添加数据条数的功能。

if __name__ == '__main__':
    count = 0
    line_count = len(Stock_list)
    print("连接数据库")
    Sql = DataServer_Sqlite3("stock")
    stock_code = Stock_list

for key in stock_code:
    try:
        column = tushare["column"]
        t1.set_token(tushare["token"])
        ts = t1.pro_api()
        print("初始化pro接口成功")
        input_code = stock_code[key]
        sd, ed = "20181231", "20201231"
        print(f"抓取{key}股票数据")
        start = time.perf_counter()
        df = Fetch_Data(input_code, sd, ed).web_spider()
        line_num = Sql_Data_Handle(df, Sql.connection, Sql.curs).add_data()
        end = time.perf_counter()

        print(f"添加{key}股票数据到数据库--成功,用时{start - end}")

        print(f"{key} 共{line_num}条数据已添加到数据库！")
    except Exception as e:
        print e
    count += 1
    print(f"还剩{line_count - count}支股票...")

4.SQLserver作者用来处理sql语句所写的类。包括了连接数据库，执行sql语句，删除数据，拿取数据，清空数据库，归零id 功能。方便使用者使用。


"""此为处理sql语句的SQLserver.py"""
import sqlite3
import config

class DataServer_Sqlite3():
    def __init__(self, db_name):
        self.curs = ''
        self.connection = ''
        self.header = ''
        self.data = ''
        self.db_name = db_name
        self.Connecting_database()

    def Connecting_database(self):
        self.connection = sqlite3.connect(config.dbPath)
        self.curs = self.connection.cursor()

    def Sql_execution(self, sql: str):
        self.curs = self.curs.execute(sql)
        self.connection.commit()

    def delete_data(self, key, data):
        self.curs = self.curs.execute(f"delete from " + key + " where trade_date=" + data)
        self.connection.commit()
        print("The data has been deleted")

    def Fetch_data(self):
        self.header = self.curs.description
        self.data = self.curs.fetchall()

    def Empty_database(self, db_name: str):
        self.curs = self.curs.execute(f"DELETE FROM \"{db_name}\"")
        self.connection.commit()

    def Zero_Id(self):
        self.curs = self.curs.execute(f"update sqlite_sequence set seq=0 where name='{self.db_name}'")
        self.connection.commit()

    def Create_table(self, name: str, input_header: str):
        """sample: input_header =int primary key,ts_code,symbol,name,area,industry,market,list_date"""
        self.Sql_execution(f"create table {name}({input_header})")

用Tushare接口爬取完整代码


 """此处为用于爬取和存储到数据库的Tushare.py"""
import tushare as t1
import time
from config import *
from SQLserver import *

class Fetch_Data():
    """code股票代码 sd开始日期 ed结束日期"""

    def __init__(self, code, start_data, end_data):
        self.code = code
        self.sd = start_data
        self.ed = end_data

    def web_spider(self):
        try:
            df = ts.daily(ts_code=self.code, start_date=self.sd, end_date=self.ed)

            return df
        except Exception as e:
            print(e)
class Sql_Data_Handle():
    def __init__(self, df, connect, curs):
        self.df = df
        self.connect = connect
        self.curs = curs

    def character(self):

        line_num = self.line_num

        ts_code = self.df.iloc[line_num]["ts_code"]
        trade_date = self.df.iloc[line_num]["trade_date"]
        open = self.df.iloc[line_num]["open"]
        high = self.df.iloc[line_num]["high"]
        low = self.df.iloc[line_num]["low"]
        close = self.df.iloc[line_num]["close"]
        pre_close = self.df.iloc[line_num]["pre_close"]
        change = self.df.iloc[line_num]["change"]
        pct_chg = self.df.iloc[line_num]["pct_chg"]
        vol = self.df.iloc[line_num]["vol"]
        amount = self.df.iloc[line_num]["amount"]

        values = (
            ts_code, trade_date, float(open), float(high), float(low), float(close), float(pre_close),
            float(change), float(pct_chg), float(vol), float(amount))
        return values
    def add_data(self):

        self.line_num = len(self.df) - 1

        while self.line_num >= 0:
            value_list = self.character()
            try:

                Sql.Sql_execution(
                    f"""insert into stock ({tushare["Column"]}) values """ + f"(\'{value_list[0]}\',\'{value_list[1]}\',\'{value_list[2]}\',\'{value_list[3]}\',\'{value_list[4]}\',\'{value_list[5]}\',\'{value_list[6]}\',\'{value_list[7]}\',\'{value_list[8]}\',\'{value_list[9]}\',\'{value_list[10]}\')")
            except Exception:

                print(f"{value_list[0]}-{value_list[1]}未被添加！")
            self.line_num -= 1
            i += 1
        print(f"The data has been added to the stock database")
        return i

if __name__ == '__main__':
    count = 0
    line_count = len(Stock_list)
    print("连接数据库")
    Sql = DataServer_Sqlite3("stock")
    stock_code = Stock_list

for key in stock_code:
    try:
        column = tushare["column"]
        t1.set_token(tushare["token"])
        ts = t1.pro_api()
        print("初始化pro接口成功")
        input_code = stock_code[key]
        sd, ed = "20181231", "20201231"
        print(f"抓取{key}股票数据")
        start = time.perf_counter()
        df = Fetch_Data(input_code, sd, ed).web_spider()
        line_num = Sql_Data_Handle(df, Sql.connection, Sql.curs).add_data()
        end = time.perf_counter()

        print(f"添加{key}股票数据到数据库--成功,用时{start - end}")

        print(f"{key} 共{line_num}条数据已添加到数据库！")
    except Exception as e:
        print e
    count += 1
    print(f"还剩{line_count - count}支股票...")

有需要股票代码和名称的私信我。

有问题可以在评论区留言。如果喜欢这篇文章记得点赞哦！！！

Original: https://blog.csdn.net/Harry_2002/article/details/115258680
Author: Harry_2002
Title: python爬取股票数据并存到数据库

原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/817321/

转载文章受原作者版权保护。转载请注明原作者出处！

赞 (0)
0

【自取】最近整理的，有需要可以领取学习：



Linux核心资料大放送~

全栈面试题汇总（持续更新&可下载）

一个提高学习100%效率的工具！

【超详细】深度学习面试题目！

LeetCode Python刷题答案下载！

LeetCode Java版刷题答案下载！

LeetCode C++ 版本，抓紧保存！

LeetCode GO语言 刷题答案下载！

大家都在看

亲爱的 Coder【最近整理，可免费获取】👉 最新必读书单  | 👏 面试题下载  | 🌎 免费的AI知识星球