# -*- coding: utf-8 -*-
import os
import xlwings as xw
import sys
import pymysql
import xlwings as xw
import pandas as pd
import numpy as np
import datetime
from sqlalchemy import create_engine
import fund_keystock

#定义链接数据库函数
def connection_base(dbname: str)->pymysql.connect:
    _servername = '172.20.60.4'
    _user = 'zxdb_read'
    _pwd = 'Zxdb_read'
    _port = 3306
    _conn = pymysql.connect(host=_servername, user=_user, password=_pwd, database=dbname, port=_port, charset='utf8')
    return _conn

def stock_list(begindate,enddate,reportdate):

    _db = 'jydb'
    _conn = connection_base(_db)

    #取数据
    try:
        #查除科创外A股市值
        _sqlstr = "select InnerCode,TradingDay,TotalMV,NegotiableMV FROM lc_dindicesforvaluation where TradingDay ='%s' AND\
        InnerCode in (select InnerCode from SecuMain where ListedDate < '%s'  and (SecuCategory = 1 or SecuCategory = 41 ) AND\
        (SecuMarket=83 OR secumarket=90) AND (ListedSector=1 OR ListedSector=2 or ListedSector=6) and ListedState=1)" %(enddate, begindate) 
        Latest_MV = pd.read_sql_query(_sqlstr, _conn)
    #查科创A股市值
        _sqlstr= "select InnerCode,TradingDay,TotalMV,NegotiableMV FROM LC_STIBDIndiForValue where TradingDay ='%s' AND\
        InnerCode in (select InnerCode from SecuMain where ListedDate < '%s'  and (SecuCategory = 1 or SecuCategory = 41 ) AND\
        (SecuMarket=83 OR secumarket=90) AND ListedSector=7 and ListedState=1)"%(enddate, begindate) 
        Latest_MV2= pd.read_sql_query(_sqlstr, _conn)
    #查除科创外A股净资产
        _sqlstr = "select distinct CompanyCode,SEWithoutMI,IntangibleAssets,GoodWill,InfoPublDate FROM LC_BalanceSheetAll where Enddate ='%s' and\
        IfMerged = 1 AND CompanyCode in (select CompanyCode from SecuMain where ListedDate < '%s' and (SecuCategory = 1 or SecuCategory = 41 )\
        AND (SecuMarket=83 OR secumarket=90) AND (ListedSector=1 OR ListedSector=2 or ListedSector=6) and ListedState=1)"%(reportdate,begindate)
        Latest_BV = pd.read_sql_query(_sqlstr, _conn)
    #查科创A股净资产
        _sqlstr = "select distinct CompanyCode,SEWithoutMI,IntangibleAssets,GoodWill,InfoPublDate FROM LC_STIBBalanceSheet where Enddate ='%s' and\
        IfMerged = 1 AND CompanyCode in (select CompanyCode from SecuMain where ListedDate < '%s' and (SecuCategory = 1 or SecuCategory = 41 )\
        AND (SecuMarket=83 OR secumarket=90) AND ListedSector=7 and ListedState=1)"%(reportdate,begindate)
        Latest_BV2 = pd.read_sql_query(_sqlstr, _conn)
    #查除科创外A股交易数据
        _sqlstr ="select InnerCode,ChangePCTRM,TurnoverRateRM,TurnoverValuePDayRY,YearVolatilityByDay from QT_StockPerformance where TradingDay='%s' AND\
        InnerCode in (select InnerCode from SecuMain where ListedDate < '%s' and (SecuCategory = 1 or SecuCategory = 41 ) AND\
        (SecuMarket=83 OR secumarket=90) AND (ListedSector=1 OR ListedSector=2 or ListedSector=6) and ListedState=1)"%(enddate,begindate)
        Latest_Factor=pd.read_sql_query(_sqlstr, _conn)
    #查科创A股交易数据
        _sqlstr ="select InnerCode,ChangePCTRM,TurnoverRateRM,TurnoverValuePDayRY,YVolatility from LC_STIBPerformance where TradingDay='%s' AND\
        InnerCode in (select InnerCode from SecuMain where ListedDate < '%s' and (SecuCategory = 1 or SecuCategory = 41 ) AND\
        (SecuMarket=83 OR secumarket=90) AND ListedSector=7 and ListedState=1)"%(enddate,begindate)
        Latest_Factor2=pd.read_sql_query(_sqlstr, _conn)
    #统一波动率对应列名
        Latest_Factor2.rename(columns={'YVolatility':'YearVolatilityByDay'},inplace=True)
    #查聚源公司代码、内部代码、股票代码的字典
        _sqlstr = "select InnerCode,CompanyCode,SecuCode from SecuMain where ListedDate < '%s' and\
        (SecuCategory = 1 or SecuCategory = 41 ) AND (SecuMarket=83 OR secumarket=90) AND (ListedSector=1 OR ListedSector=2 or\
        ListedSector=6 or ListedSector=7)and ListedState=1 ORDER BY Secucode"%(begindate)
        Dict=pd.read_sql_query(_sqlstr, _conn)
    
    finally:
        _conn.close()
#拼接市值表
    Latest_MV3=pd.concat([Latest_MV,Latest_MV2],ignore_index= True)
#拼接净资产表
    Latest_BV3=pd.concat([Latest_BV,Latest_BV2],ignore_index= True)
#净资产表数据库中Null替换为零
    Latest_BV3=Latest_BV3.replace(np.nan,0)
#只保留将净资产表中财报更新的结果，去除前值
    Latest_BV3=Latest_BV3.sort_values('InfoPublDate',ascending=False).drop_duplicates(subset=['CompanyCode'],keep='first')
#拼接交易表
    Latest_Factor3=pd.concat([Latest_Factor,Latest_Factor2],ignore_index= True)
#构造调整后净资产
    Latest_BV3['AdjustedBV']=Latest_BV3['SEWithoutMI']-Latest_BV3['IntangibleAssets']-Latest_BV3['GoodWill']
#将三张表利用关键字与字典相连
    Latest_MV3=Latest_MV3.merge(Dict,on='InnerCode',how='left')
    Latest_BV3=Latest_BV3.merge(Dict,on='CompanyCode',how='left')
    Latest_Factor3=Latest_Factor3.merge(Dict,on='InnerCode',how='left')
#将三张表利用统一的关键字SecuCode相连
    Allstock=Latest_MV3.merge(Latest_BV3,on='SecuCode',how='left')
    Allstock=Allstock.merge(Latest_Factor3,on='SecuCode',how='left')
#删除冗余列
    Allstock.drop(Allstock.columns[[0,4,6,7,8,9,10,12,13,18]], axis=1, inplace=True)
#删除调整净资产为负的行
    Allstock.drop(Allstock[Allstock.AdjustedBV<0].index,inplace=True)
#按照流通市值排序
    Allstock=Allstock.sort_values('NegotiableMV')
#剔除日均换手率排名后5%股票
    q=Allstock.quantile(q=0.05, axis=0, numeric_only=True, interpolation='linear')
    Allstock.drop(Allstock[Allstock.TurnoverValuePDayRY<q[5]].index,inplace=True)
#计算全市场流通市值
    x=Allstock['NegotiableMV'].sum()
#计算累计加总占比全市场4%的股票排名
    summv=0
    i=0
    while summv < x*0.04:
        summv=summv+Allstock.iat[i,1]
        i = i+1
#截断排名以下的股票行
    Allstock.drop(Allstock.tail(len(Allstock)-i).index,inplace=True)
#构造调整后PB指标
    Allstock['Prc2bk']=Allstock['TotalMV']/Allstock['AdjustedBV']

#方法一 剔除期间涨幅过大过小的股票
    q1=Allstock.quantile(q=0.2, axis=0, numeric_only=True, interpolation='linear')
    q2=Allstock.quantile(q=0.8, axis=0, numeric_only=True, interpolation='linear')
    Allstock.drop(Allstock[Allstock.ChangePCTRM<q1[3]].index,inplace=True)
    Allstock.drop(Allstock[Allstock.ChangePCTRM>q2[3]].index,inplace=True)

    Allstock.drop(Allstock.columns[[1,2,4,5,7]], axis=1, inplace=True)
#计算因子值
    Allstock['RankTO']=Allstock['TurnoverRateRM'].rank(ascending=False,method='max')
    Allstock['RankVol']=Allstock['YearVolatilityByDay'].rank(ascending=False,method='max')
    Allstock['RankPB']=Allstock['Prc2bk'].rank(ascending=False,method='max')
    Allstock['Score']=Allstock['RankTO']+Allstock['RankVol']+Allstock['RankPB']
    Allstock=Allstock.sort_values('Score',ascending=False)
    Allstock.drop(Allstock.tail(len(Allstock)-200).index,inplace=True)
    return Allstock[['TradingDay','SecuCode','TurnoverRateRM','YearVolatilityByDay','Prc2bk']]
这是我写的一个函数 现在我有一个excel文件，在sheet1的第一列到第三列，对应着stock_list()函数的三个参数，请帮我写一段代码 循环调用这个excel文件里每一行的三个参数，然后把return的结果Allstock写进本地的accdb数据库里