创建存储表
1.股票基本信息表 stock_basic
-- ------------------------------ Table structure for stock_basic-- ----------------------------DROPTABLEIFEXISTS`stock_basic`;CREATETABLE`stock_basic`(`code`varchar(10)CHARACTERSETutf8mb4COLLATEutf8mb4_general_ciNOTNULLCOMMENT'股票代码（如：sh.600000）',`code_name`varchar(50)CHARACTERSETutf8mb4COLLATEutf8mb4_general_ciNOTNULLCOMMENT'股票名称',`exchange`varchar(10)CHARACTERSETutf8mb4COLLATEutf8mb4_general_ciNULLDEFAULTNULLCOMMENT'交易所（sh:上海, sz:深圳）',`stock_type`varchar(10)CHARACTERSETutf8mb4COLLATEutf8mb4_general_ciNULLDEFAULTNULLCOMMENT'股票类型（A股、B股等）',`status`varchar(10)CHARACTERSETutf8mb4COLLATEutf8mb4_general_ciNULLDEFAULTNULLCOMMENT'上市状态（上市、退市等）',`list_date`varchar(10)CHARACTERSETutf8mb4COLLATEutf8mb4_general_ciNULLDEFAULTNULLCOMMENT'上市日期',`delist_date`varchar(10)CHARACTERSETutf8mb4COLLATEutf8mb4_general_ciNULLDEFAULTNULLCOMMENT'退市日期',`create_time`datetime(0)NULLDEFAULTCURRENT_TIMESTAMP(0)COMMENT'创建时间',`update_time`datetime(0)NULLDEFAULTCURRENT_TIMESTAMP(0)ONUPDATECURRENT_TIMESTAMP(0)COMMENT'更新时间',PRIMARYKEY(`code`)USINGBTREE)ENGINE=InnoDBCHARACTERSET=utf8mb4COLLATE=utf8mb4_general_ciCOMMENT='股票基本信息表'ROW_FORMAT=Compact;SETFOREIGN_KEY_CHECKS=1;2.股票日线行情表 stock_quote_daily
-- ------------------------------ Table structure for stock_quote_daily-- ----------------------------DROPTABLEIFEXISTS`stock_quote_daily`;CREATETABLE`stock_quote_daily`(`id`bigint(20)NOTNULLAUTO_INCREMENTCOMMENT'主键ID',`code`varchar(10)CHARACTERSETutf8mb4COLLATEutf8mb4_general_ciNOTNULLCOMMENT'股票代码',`trade_date`varchar(10)CHARACTERSETutf8mb4COLLATEutf8mb4_general_ciNOTNULLCOMMENT'交易日期',`open`decimal(10,4)NULLDEFAULTNULLCOMMENT'开盘价',`high`decimal(10,4)NULLDEFAULTNULLCOMMENT'最高价',`low`decimal(10,4)NULLDEFAULTNULLCOMMENT'最低价',`close`decimal(10,4)NULLDEFAULTNULLCOMMENT'收盘价',`pre_close`decimal(10,4)NULLDEFAULTNULLCOMMENT'前收盘价',`change_amount`decimal(10,4)NULLDEFAULTNULLCOMMENT'涨跌额',`change_percent`decimal(10,4)NULLDEFAULTNULLCOMMENT'涨跌幅（%）',`volume`bigint(20)NULLDEFAULTNULLCOMMENT'成交量（股）',`amount`decimal(15,4)NULLDEFAULTNULLCOMMENT'成交额（元）',`adjustflag`varchar(10)CHARACTERSETutf8mb4COLLATEutf8mb4_general_ciNULLDEFAULTNULLCOMMENT'复权类型（1:后复权, 2:前复权, 3:不复权）',`turn`decimal(10,4)NULLDEFAULTNULLCOMMENT'换手率（%）',`tradestatus`varchar(10)CHARACTERSETutf8mb4COLLATEutf8mb4_general_ciNULLDEFAULTNULLCOMMENT'交易状态（1:正常交易, 0:停牌）',`pctChg`decimal(10,4)NULLDEFAULTNULLCOMMENT'涨跌幅（%）',`peTTM`decimal(15,4)NULLDEFAULTNULLCOMMENT'动态市盈率',`pbMRQ`decimal(15,4)NULLDEFAULTNULLCOMMENT'市净率',`psTTM`decimal(15,4)NULLDEFAULTNULLCOMMENT'市销率',`pcfNcfTTM`decimal(15,4)NULLDEFAULTNULLCOMMENT'市现率',`isST`tinyint(1)NULLDEFAULTNULLCOMMENT'是否ST股（1:是, 0:否）',`create_time`datetime(0)NULLDEFAULTCURRENT_TIMESTAMP(0)COMMENT'创建时间',`update_time`datetime(0)NULLDEFAULTCURRENT_TIMESTAMP(0)ONUPDATECURRENT_TIMESTAMP(0)COMMENT'更新时间',PRIMARYKEY(`id`)USINGBTREE,UNIQUEINDEX`idx_code_trade_date`(`code`,`trade_date`)USINGBTREECOMMENT'股票代码和交易日期的唯一索引')ENGINE=InnoDBAUTO_INCREMENT=1CHARACTERSET=utf8mb4COLLATE=utf8mb4_general_ciCOMMENT='股票日线行情表'ROW_FORMAT=Compact;使用Baostock采集
创建采集脚本 get_data.py
importbaostockasbsimportpandasaspdfromsqlalchemyimportcreate_engineimporttimefromdatetimeimportdatetime# MySQL数据库配置MYSQL_CONFIG={'host':'49.235.159.43',# 数据库地址'port':3306,# 端口'user':'adata',# 用户名'password':'wb3BRaNtesdxfydA',# 密码'db':'adata',# 数据库名'charset':'utf8mb4'}# 初始化数据库连接engine=create_engine(f"mysql+pymysql://{MYSQL_CONFIG['user']}:{MYSQL_CONFIG['password']}@{MYSQL_CONFIG['host']}:{MYSQL_CONFIG['port']}/{MYSQL_CONFIG['db']}?charset={MYSQL_CONFIG['charset']}")definit_baostock():"""初始化Baostock连接"""lg=bs.login()iflg.error_code!='0':print(f"登录失败：{lg.error_msg}")returnFalseprint("Baostock登录成功")returnTruedefget_stock_basic():"""获取所有股票基本信息并存储到数据库"""# 查询A股所有股票rs=bs.query_stock_basic(code_name="")ifrs.error_code!='0':print(f"获取股票基本信息失败：{rs.error_msg}")returndata_list=[]while(rs.error_code=='0')&rs.next():data=rs.get_row_data()data_dict={'code':data[0],'code_name':data[1],'exchange':data[2],'stock_type':data[4],# 股票类型（A股、B股等）'status':'上市'ifdata[5]=='1'else'退市',# 上市状态'list_date':data[2],'delist_date':data[3]ifdata[3]elseNone}data_list.append(data_dict)# 转换为DataFrame并写入数据库df=pd.DataFrame(data_list)ifnotdf.empty:df.to_sql(name='stock_basic',con=engine,if_exists='append',index=False,chunksize=1000)print(f"成功写入{len(df)}条股票基本信息")defget_stock_daily_data(code,start_date='1990-12-19',end_date=None):"""获取单只股票的历史日线数据"""end_date=end_dateordatetime.now().strftime('%Y-%m-%d')rs=bs.query_history_k_data_plus(code=code,fields="date,open,high,low,close,preclose,volume,amount,adjustflag,turn,tradestatus,""pctChg,peTTM,pbMRQ,psTTM,pcfNcfTTM,isST",start_date=start_date,end_date=end_date,frequency="d",adjustflag="2"# 2=前复权)ifrs.error_code!='0':print(f"获取{code}数据失败：{rs.error_msg}")returnNonedata_list=[]while(rs.error_code=='0')&rs.next():data=rs.get_row_data()data_dict={'code':code,'trade_date':data[0],'open':float(data[1])ifdata[1]elseNone,'high':float(data[2])ifdata[2]elseNone,'low':float(data[3])ifdata[3]elseNone,'close':float(data[4])ifdata[4]elseNone,'pre_close':float(data[5])ifdata[5]elseNone,'change_amount':round(float(data[4])-float(data[5]),4)ifdata[4]anddata[5]elseNone,'change_percent':float(data[11])ifdata[11]elseNone,'volume':int(data[6])ifdata[6]elseNone,'amount':float(data[7])ifdata[7]elseNone,'adjustflag':data[8],'turn':float(data[9])ifdata[9]elseNone,'tradestatus':data[10],'pctChg':float(data[11])ifdata[11]elseNone,'peTTM':float(data[12])ifdata[12]elseNone,'pbMRQ':float(data[13])ifdata[13]elseNone,'psTTM':float(data[14])ifdata[14]elseNone,'pcfNcfTTM':float(data[15])ifdata[15]elseNone,'isST':1ifdata[16]=='1'else0}data_list.append(data_dict)returnpd.DataFrame(data_list)ifdata_listelseNonedefbatch_get_daily_data():"""批量获取所有股票的日线数据"""# 从数据库读取已存在的股票代码query="SELECT code FROM stock_basic WHERE status = '上市'"codes=pd.read_sql(query,engine)['code'].tolist()print(f"共{len(codes)}只上市股票需要获取日线数据")fori,codeinenumerate(codes):print(f"正在处理第{i+1}/{len(codes)}只股票：{code}")# 查询该股票已存在的最新日期，避免重复抓取latest_date_query=f"SELECT MAX(trade_date) as latest FROM stock_quote_daily WHERE code = '{code}'"latest_date=pd.read_sql(latest_date_query,engine)['latest'].iloc[0]start_date=latest_dateiflatest_dateelse'1990-12-19'# 获取数据df=get_stock_daily_data(code,start_date=start_date)ifdfisnotNoneandnotdf.empty:# 写入数据库df.to_sql(name='stock_quote_daily',con=engine,if_exists='append',index=False,chunksize=1000)print(f"成功写入{len(df)}条日线数据")# 避免请求过于频繁time.sleep(1)if__name__=="__main__":ifinit_baostock():# 1. 获取股票基本信息#get_stock_basic()# 2. 获取日线行情数据batch_get_daily_data()# 登出Baostockbs.logout()print("数据获取完成并退出")执行采集
python get_data.py等代码执行完毕，恭喜你获得了一份大A 基本信息和历史日数据的数据
