可转债正股数据入库

最新推荐文章于 2024-07-13 18:10:19 发布

南昌四月鞅科技

最新推荐文章于 2024-07-13 18:10:19 发布

阅读量198

点赞数 2

文章标签：数据库 python 开发语言

本文链接：https://blog.csdn.net/zhanghuihua911zha/article/details/136996038

版权

akshare 中stock_zh_a_hist可以返回股票日线的数据，根据这些数据信息创建数据库表结构，然后把查到的数据入库

import configparser
import akshare as ak
import pandas as pd
import pymysql


class StockDataProcessor:
    def __init__(self, config_file):
        self.config_file = config_file
        self.db_host = None
        self.db_port = None
        self.db_user = None
        self.db_passwd = None
        self.db_database = None

    def load_config(self):
        config = configparser.ConfigParser()
        config.read(self.config_file)

        self.db_host = config.get('Database', 'host')
        self.db_port = int(config.get('Database', 'port'))
        self.db_user = config.get('Database', 'user')
        self.db_passwd = config.get('Database', 'passwd')
        self.db_database = config.get('Database', 'database')

    def fetch_stock_data(self, stock_code):
        stock_data = ak.stock_zh_a_hist(symbol=stock_code, period="daily",
                                        start_date="20180418",
                                        end_date="20240322",
                                        adjust="")
        df = pd.DataFrame(stock_data)
        return df

    def create_table(self, conn, stock_code):
        cursor = conn.cursor()

        # 删除已存在的表
        cursor.execute("DROP TABLE IF EXISTS stock_data")

        # 创建新表
        create_table_query = f'''
        CREATE TABLE stock_data (
            id INT PRIMARY KEY AUTO_INCREMENT,
            stock_code VARCHAR(10) COMMENT '股票代码',
            date DATE COMMENT '日期',
            open FLOAT COMMENT '开盘',
            close FLOAT COMMENT '收盘',
            high FLOAT COMMENT '最高',
            low FLOAT COMMENT '最低',
            volume FLOAT COMMENT '成交量',
            turnover FLOAT COMMENT '成交额',
            amplitude FLOAT COMMENT '振幅',
            change_rate FLOAT COMMENT '涨跌幅',
            change_amount FLOAT COMMENT '涨跌额',
            turnover_rate FLOAT COMMENT '换手率'
        )
        '''
        cursor.execute(create_table_query)

        cursor.close()

    def insert_data(self, conn, df, stock_code):
        cursor = conn.cursor()

        insert_query = '''
        INSERT INTO stock_data (stock_code, date, open, close, high, low, volume, turnover, amplitude, change_rate, change_amount, turnover_rate)
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        '''
        data = df[['日期', '开盘', '收盘', '最高', '最低', '成交量', '成交额', '振幅', '涨跌幅', '涨跌额', '换手率']].values.tolist()
        data = [[stock_code] + row for row in data]  # 在数据行前添加股票代码
        cursor.executemany(insert_query, data)

        conn.commit()
        cursor.close()

    def process_stock_data(self):
        self.load_config()

        stock_code = input("请输入股票代码: ")

        conn = pymysql.connect(
            host=self.db_host,
            port=self.db_port,
            user=self.db_user,
            passwd=self.db_passwd,
            database=self.db_database
        )

        df = self.fetch_stock_data(stock_code)

        self.create_table(conn, stock_code)
        self.insert_data(conn, df, stock_code)

        conn.close()


def main():
    config_file = 'config.ini'
    processor = StockDataProcessor(config_file)
    processor.process_stock_data()


if __name__ == '__main__':
    main()

在运行程序之前，请确保已经安装了configparser、akshare、pandas和pymysql库，并根据你的需求修改config.ini配置文件和fetch_stock_data方法中的股票数据获取参数。然后，运行main函数即可开始插入数据到已存在的数据表中。