import pandas as pd
import matplotlib.pyplot as plt
import mplcursors
import pyodbc
import matplotlib.ticker as ticker

# Setup connection to the database
conn_str = r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\Users\LIQI\Documents\microcapstrategy.accdb"
try:
    conn = pyodbc.connect(conn_str)
            
    # 执行查询
    query = "SELECT TradingDay, SecuCode, TurnoverRateRM, YearVolatilityByDay, Prc2bk FROM Portfolio"
    df = pd.read_sql(query, conn)
    # 关闭连接
finally:
    conn.close()
    
    # 将相关列转换为float类型
    df['TurnoverRateRM'] = df['TurnoverRateRM'].astype(float)
    df['YearVolatilityByDay'] = df['YearVolatilityByDay'].astype(float)
    df['Prc2bk'] = df['Prc2bk'].astype(float)
            
            
    # 统计不同tradingdate对应的每个stock ticker的因子值
    statistics = df.groupby('TradingDay').agg({'TurnoverRateRM': ['mean', 'median', lambda x: x.quantile(0.1), lambda x: x.quantile(0.25), \
                lambda x: x.quantile(0.75), lambda x: x.quantile(0.9)],'YearVolatilityByDay': ['mean', 'median', lambda x: x.quantile(0.1),\
                lambda x: x.quantile(0.25), lambda x: x.quantile(0.75), lambda x: x.quantile(0.9)],'Prc2bk': ['mean', 'median',\
                lambda x: x.quantile(0.1), lambda x: x.quantile(0.25), lambda x: x.quantile(0.75),lambda x: x.quantile(0.9)]})

    # 绘制时间序列折线图
    dates = statistics.index.tolist()

    plt.figure(figsize=(12, 12))

    # 绘制因子A的时间序列折线图
    ax1 =plt.subplot(3, 1, 1)
    ax1.plot(dates, statistics[('TurnoverRateRM', 'mean')].values, label='Mean')
    ax1.plot(dates, statistics[('TurnoverRateRM', 'median')].values, label='Median')
    ax1.plot(dates, statistics[('TurnoverRateRM', '<lambda_0>')].values, label='10% Quantile')
    ax1.plot(dates, statistics[('TurnoverRateRM', '<lambda_1>')].values, label='25% Quantile')
    ax1.plot(dates, statistics[('TurnoverRateRM', '<lambda_2>')].values, label='75% Quantile')
    ax1.plot(dates, statistics[('TurnoverRateRM', '<lambda_3>')].values, label='90% Quantile')
    ax1.set_ylabel('近月换手率')
    ax1.legend()
    ax1.xaxis.set_tick_params(rotation=45)
    ax1.xaxis.set_major_locator(ticker.MultipleLocator(3))  # 设置刻度间隔为3

    # 绘制因子B的时间序列折线图
    ax2=plt.subplot(3, 1, 2)
    ax2.plot(dates, statistics[('YearVolatilityByDay', 'mean')].values, label='Mean')
    ax2.plot(dates, statistics[('YearVolatilityByDay', 'median')].values, label='Median')
    ax2.plot(dates, statistics[('YearVolatilityByDay', '<lambda_0>')].values, label='10% Quantile')
    ax2.plot(dates, statistics[('YearVolatilityByDay', '<lambda_1>')].values, label='25% Quantile')
    ax2.plot(dates, statistics[('YearVolatilityByDay', '<lambda_2>')].values, label='75% Quantile')
    ax2.plot(dates, statistics[('YearVolatilityByDay', '<lambda_3>')].values, label='90% Quantile')
    ax2.set_ylabel('年化波动率')
    ax2.legend()
    ax2.xaxis.set_tick_params(rotation=45)
    ax2.xaxis.set_major_locator(ticker.MultipleLocator(3))  # 设置刻度间隔为3

    # 绘制因子C的时间序列折线图
    ax3=plt.subplot(3, 1, 3)
    ax3.plot(dates, statistics[('Prc2bk', 'mean')].values, label='Mean')
    ax3.plot(dates, statistics[('Prc2bk', 'median')].values, label='Median')
    ax3.plot(dates, statistics[('Prc2bk', '<lambda_0>')].values, label='10% Quantile')
    ax3.plot(dates, statistics[('Prc2bk', '<lambda_1>')].values, label='25% Quantile')
    ax3.plot(dates, statistics[('Prc2bk', '<lambda_2>')].values, label='75% Quantile')
    ax3.plot(dates, statistics[('Prc2bk', '<lambda_3>')].values, label='90% Quantile')
    ax3.set_ylabel('改良市净率')
    ax3.legend()
    ax3.xaxis.set_tick_params(rotation=45)
    ax3.xaxis.set_major_locator(ticker.MultipleLocator(3))  # 设置刻度间隔为3
    
    plt.xlabel('建仓日')
    plt.tight_layout()
    
    # 使用mplcursors库实现鼠标悬停显示数据
    mplcursors.cursor([ax1, ax2, ax3])
    
    plt.show()    不知道为啥 我没办法通过把鼠标放在图标上获得对应的数据 请帮我检查下哪里有问题