def insertCode():
conn = mysql_login()
sql = '''
select * from ext_data_stock.stock_base_info where stock_code like 'sh.6%' and out_date is NULL;;
'''
code_yest = pd.read_sql(sql=sql, con=conn)
conn.close()
codenum_yest = len(code_yest)
code_curr = getStockInfo()
code_sh = code_curr[code_curr['stock_code'].str.startswith('sh.6')]
code_sh_curr = code_sh[code_sh['out_date'].isnull()]
codenum_curr = len(code_sh_curr)
# 股票基本信息插入mysql数据库
dtype_dict = {
"stock_code": sqlalchemy.types.NVARCHAR(length=20),
"stock_name": sqlalchemy.types.NVARCHAR(length=100),
"ipo_date": sqlalchemy.types.DateTime(),
"out_date": sqlalchemy.types.DateTime(),
"stock_type": sqlalchemy.types.NVARCHAR(length=5),
"stock_status": sqlalchemy.types.NVARCHAR(length=5),
}
df2mysql(df=code_curr,
schema_name='ext_data_stock',
table_name='stock_base_info',
if_exists='replace',
dtype=dtype_dict,
index=True,
chunksize=5000)
return codenum_curr, codenum_yest
import os
import numpy as np
import pandas as pd
from common.db_operation import mysql_login, df2mysql
datapath = os.path.join(os.path.dirname(os.path.dirname(__file__)),
'data_set/')
dataname = os.path.join(datapath, 'data_000001_2010-2019.csv')
df = pd.read_csv(dataname, encoding='unicode_escape')
df['DateTime'] = pd.to_datetime(df['DateTime'], format="%Y-%m-%d")
# print(df['DateTime'])
#数据插入mysql数据库
df2mysql(df=df,
schema_name='ext_data_stock',
table_name='stock_quotation_his',
if_exists='replace')
def getIndexQuot(start_date, end_date, index_dict = {},):
#### 登陆系统 ####
lg = bstk.login()
# 显示登陆返回信息
print('login respond error_msg:' + lg.error_msg)
#### 获取沪深A股历史K线数据 ####
data_list = []
for stock_code in index_dict.keys():
rs = bstk.query_history_k_data_plus(stock_code,
"date,code,open,high,low,close,preclose,volume,amount,pctChg",
start_date=start_date,
end_date=end_date,
frequency="d",
adjustflag="3"
)
while (rs.error_code == '0') & rs.next():
# 获取一条记录，将记录合并在一起
data_list.append(rs.get_row_data())
result = pd.DataFrame(data_list, columns=rs.fields)
result.columns = [
'trade_date',
'index_code',
'open_price',
'high_price',
'low_price',
'close_price',
'pre_close_price',
'trade_vol',
'trade_amt',
'pct_chg',
]
index_df = pd.DataFrame({'index_code': list(index_dict.keys()),
'index_name': list(index_dict.values())})
df = pd.merge(result, index_df, left_on='index_code', right_on='index_code', how='left')
df_stock_name = df.pop('index_name')
df.insert(2, 'index_name', df_stock_name)
dtype = {
'trade_date': sqlalchemy.types.DateTime(),
'index_code': sqlalchemy.types.NVARCHAR(length=20),
'index_name': sqlalchemy.types.NVARCHAR(length=20),
'open_price': sqlalchemy.types.Numeric(20, 3),
'high_price': sqlalchemy.types.Numeric(20, 3),
'low_price': sqlalchemy.types.Numeric(20, 3),
'close_price': sqlalchemy.types.Numeric(20, 3),
'pre_close_price': sqlalchemy.types.Numeric(20, 3),
'trade_vol': sqlalchemy.types.Numeric(20, 0),
'trade_amt': sqlalchemy.types.Numeric(20, 4),
'pct_chg': sqlalchemy.types.NVARCHAR(length=20),
}
## 结果集插入mysql ####
df2mysql(df=df
, schema_name='ext_data_stock'
, table_name='index_quotation_info'
, if_exists='append'
, chunksize=5000
, dtype=dtype
, index=False
)
#### 登出系统 ####
bstk.logout()
return len(df)
def getStockQuot(start_date, end_date, stockCode,):
stock_list = getStockCode(stockCode)
#### 登陆系统 ####
lg = bstk.login()
# 显示登陆返回信息
print('login respond error_msg:' + lg.error_msg)
#### 获取沪深A股历史K线数据 ####
data_list = []
for stock_code in stock_list['stock_code']:
rs = bstk.query_history_k_data_plus(stock_code,
"date,code,open,high,low,close,preclose,volume,amount,pctChg,adjustflag,turn,tradestatus,isST",
start_date=start_date,
end_date=end_date,
frequency="d",
)
while (rs.error_code == '0') & rs.next():
# 获取一条记录，将记录合并在一起
data_list.append(rs.get_row_data())
result = pd.DataFrame(data_list, columns=rs.fields)
result.columns = [
'trade_date',
'stock_code',
'open_price',
'high_price',
'low_price',
'close_price',
'pre_close_price',
'trade_vol',
'trade_amt',
'pct_chg',
'adjust_flag',
'turn',
'trade_status',
'is_st',
]
df = pd.merge(result, stock_list, on='stock_code', how='left')
df_stock_name = df.pop('stock_name')
df.insert(2, 'stock_name', df_stock_name)
dtype = {
'trade_date':sqlalchemy.types.DateTime(),
'stock_code':sqlalchemy.types.NVARCHAR(length=20),
'stock_name':sqlalchemy.types.NVARCHAR(length=20),
'open_price':sqlalchemy.types.Numeric(20,2),
'high_price':sqlalchemy.types.Numeric(20,2),
'low_price':sqlalchemy.types.Numeric(20,2),
'close_price':sqlalchemy.types.Numeric(20,2),
'pre_close_price':sqlalchemy.types.Numeric(20,2),
'trade_vol':sqlalchemy.types.Numeric(20,0),
'trade_amt':sqlalchemy.types.Numeric(20,0),
'pct_chg':sqlalchemy.types.NVARCHAR(length=20),
'adjust_flag':sqlalchemy.types.NVARCHAR(length=10),
'turn':sqlalchemy.types.NVARCHAR(length=20),
'trade_status':sqlalchemy.types.NVARCHAR(length=10),
'is_st':sqlalchemy.types.NVARCHAR(length=10),
}
## 结果集插入mysql ####
df2mysql(df=df
, schema_name='ext_data_stock'
, table_name='stock_quotation_info'
, if_exists='append'
, chunksize=5000
, dtype=dtype
, index=False
)
#### 登出系统 ####
bstk.logout()
return len(df)