akshare下载期货日数据并存入mysql

import pymysql
import akshare as ak
import pandas as pd
from sqlalchemy import create_engine,text

    
class ConnMysql(object):

    def __init__(self):
        """初始化数据库连接"""
        self._conn = self.get_conn()
        self._cur = None
        if self._conn is not None:
            self._cur = self._conn.cursor()

    def get_conn(self):
        """
        连接数据库
        :return:
        """
        conn = pymysql.connect(
            host="localhost",  # 数据库地址
            user="root",  # 用户名
            password="XXX",  # 密码
            database="futures_daily_data",  # 数据库名
        )

        return conn

    def list_table(self):
        """
        列出所有的表
        :return:
        """
        self._cur.execute("show tables")
        table_list = [tuple[0] for tuple in self._cur.fetchall()]
        self._cur.close()
        return table_list

    def list_col(self, tabls_name):
        """
        查询所有字段
        :param tabls_name:
        :return:
        """
        self._cur.execute("show create table %s" % tabls_name)
        col_name_list = [table for table in self._cur]
        self._cur.close()
        return col_name_list[0]
    
    def create_table(self,table_name):
# 设置表格的字段
        sql = "CREATE TABLE IF NOT EXISTS  " + table_name + '  (id INT AUTO_INCREMENT PRIMARY KEY,\
        symbol VARCHAR(10) NOT NULL,\
        trade_date DATE NOT NULL, \
        open_price DECIMAL(10, 2)  NOT NULL,\
        high_price DECIMAL(10, 2)  NOT NULL, \
        low_price DECIMAL(10, 2)  NOT NULL, \
        close_price DECIMAL(10, 2)  NOT NULL, \
        volume BIGINT NOT NULL, \
        open_interest BIGINT NOT NULL,\
        turnover DECIMAL(20, 2) NOT NULL,\
        settle BIGINT NOT NULL, \
        pre_settle BIGINT NOT NULL,\
        variety VARCHAR(50) NOT NULL,\
        INDEX idx_symbol_trade_date (symbol, trade_date))\
        ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;'
        self._cur.execute(sql)

engine = create_engine("mysql+pymysql://root:Normal403198@localhost/futures_daily_data")

get_futures_daily_df_shfe = ak.get_futures_daily(start_date = '20230414',end_date = '20230414',market = 'SHFE')
variety =get_futures_daily_df_shfe.variety.drop_duplicates().copy()
get_futures_daily_df_shfe.rename(columns={
    'date': "trade_date",
    'open': "open_price",
    'close': "close_price",
    'high': "high_price",
    'low': "low_price"}, inplace=True)
#get_futures_daily_df.set_index('trade_date',inplace = True)
#get_futures_daily_df.reset_index(inplace = True)
get_futures_daily_df_shfe.replace('',0,inplace = True)
get_futures_daily_df_shfe.variety = get_futures_daily_df_shfe.variety.str.lower()
for var in variety.values:
    print(var)
group = dict(list(get_futures_daily_df_shfe.groupby('variety')))
symbol = list(group.keys())
  
for k,value in group.items():
    print(value)
    value.to_sql(k,index = False,con = engine,if_exists='append')

df = pd.read_sql_query(text("SELECT * FROM au where symbol = 'AU2212' "), con=engine)