def getProductBasicInfo():
'''
productbasicinfo表信息更新
:param dbCntInfo:
:return:
'''
dbCntInfo = dbcnt.getInitDbCntInfo()
sourceTable = "stock_basics"
getStockBasics(dbCntInfo)
destTable = "productbasicinfo"
souceDbBase = dbCntInfo.getDBCntInfoByTableName(sourceTable)
destDbBase = dbCntInfo.getDBCntInfoByTableName(destTable)
proctBaseInfoDf = pb.getAllProductBasicInfo(dbCntInfo,
ipostatus="N",
producttype='1')
productcodelist = proctBaseInfoDf["product_code"].tolist()
while (True):
sourceRetList = souceDbBase.execSelectManySql(sc.PRODUCTBASICINFO_SQL,
" order by code")
if len(sourceRetList) == 0:
break
curworkday = pb.getlastworkday()
# 数据插入到另外一个库里面
newsourceListTuple = []
for oneList in sourceRetList:
productcode = oneList["product_code"]
if productcode not in productcodelist:
print(oneList)
oneList['market_type'] = dc.DICTCONS_CODETOMARKETTYPE[
oneList['market_type']]
oneList['symbol_code'] = pb.code_to_symbol(productcode)
newsourceListTuple.append(tuple(oneList.values()))
if len(newsourceListTuple) != 0:
destDbBase.execInsertManySql(sc.PRODUCTBASICINFO_INSERTSQL,
newsourceListTuple)
proctBaseInfoDf = getDelistStockBasics(dbCntInfo)
proctBaseInfoDf = proctBaseInfoDf.set_index("symbol")
delistListTuple = []
for productcode, itemvalue in proctBaseInfoDf.iterrows():
if productcode in productcodelist:
# print("%s has delisted!" % productcode)
# print(itemvalue)
delistListTuple.append(
(itemvalue["delist_date"], pb.code_to_symbol(productcode)))
if len(delistListTuple) != 0:
# print(delistListTuple)
destDbBase.execupdatemanysql(sc.PRODUCTBASICINFO_UPDATEDELISTSQL,
delistListTuple)
def getProductFinanceInfo(dbCntInfo,sourcetable,desctable):
'''
获取产品的公司财务基础数据
:param dbCntInfo:
:param sourcetable: tusharepro数据填写到该表 histincome histcastflow histbalance
:param desctable: 移植到该正式表 company_income company_cashflow company_balance_sheet
:return:
'''
productInfoDf = pb.getAllProductBasicInfo(dbCntInfo)
if productInfoDf.empty:
print("表中无正在上市的数据，提前正常结束!")
return
pro = ts.pro_api('00f0c017db5d284d992f78f0971c73c9ecba4aa03dee2f38e71e4d9c')
sourceTable = sourcetable # 比如"histincome"
destTable = desctable # 比如"company_income"
# 获取每个产品已经获取到的最大reportdate
productReportDateDict = getmaxreportdate(dbCntInfo, destTable)
engine = dbCntInfo.getEngineByTableName(sourceTable)
# 获取tusharepro中的数据
for rowIndex in productInfoDf.index:
# if rowIndex >= 0:
# continue
if rowIndex % 200 == 0 and rowIndex != 0:
time.sleep(60)
oneProductInfo = productInfoDf.iloc[rowIndex]
productCode = oneProductInfo["product_code"]
maxreportdate = productReportDateDict[productCode]
maxreportdate = maxreportdate + 1
print("%d %s begin to get %s data from intenert..."%(rowIndex, productCode,destTable))
symbolProcuctCode = pb.code_to_symbol(productCode)
df = pd.DataFrame()
try :
if destTable in "company_income":
if maxreportdate == 1:
df = pro.income(ts_code=symbolProcuctCode)
else :
df = pro.income(ts_code=symbolProcuctCode, start_date=str(maxreportdate))
elif destTable in "company_balance_sheet":
if maxreportdate == 1:
df = pro.balancesheet(ts_code=symbolProcuctCode)
else :
df = pro.balancesheet(ts_code=symbolProcuctCode, start_date=str(maxreportdate))
elif destTable in "company_cashflow":
if maxreportdate == 1:
df = pro.cashflow(ts_code=symbolProcuctCode)
else :
df = pro.cashflow(ts_code=symbolProcuctCode, start_date=str(maxreportdate))
else :
raise Exception("destTable is exception!")
except Exception as e:
print(symbolProcuctCode + " connect time out!")
time.sleep(120)
if destTable in "company_income":
df = pro.income(ts_code=symbolProcuctCode)
elif destTable in "company_balance_sheet":
df = pro.balancesheet(ts_code=symbolProcuctCode)
elif destTable in "company_cashflow":
df = pro.cashflow(ts_code=symbolProcuctCode)
else:
raise Exception("destTable is exception!")
# 数据去重
# 数据相同删除重复的数据保留第一个数据
dfdup = df.drop_duplicates(subset=["ts_code","end_date"], keep='first', inplace=False)
realSourTable = sourceTable + productCode
basicdf = dfdup.reset_index(drop=True)
basicdf.to_sql(realSourTable, engine, if_exists="replace", index=False)
print("%s get data finish!" % (productCode))
time.sleep(1.5)
# 获取表中存在的数据。
for rowIndex in productInfoDf.index:
oneProductInfo = productInfoDf.iloc[rowIndex]
productCode = oneProductInfo["product_code"]
maxreportdate = productReportDateDict[productCode]
realSourTable = sourceTable + productCode
print("%d %s begin to insert table %s data..." % (rowIndex, realSourTable, destTable))
selectsql = sc.COMPANYFINANCE_SELECTSQL[destTable]%(realSourTable,maxreportdate)
print(selectsql)
insertsql = sc.COMPANYFINANCE_INSERTSQL[destTable]
pb.insertNormalDbByCurProductCode(dbCntInfo, sourceTable, destTable, selectsql, insertsql,productCode)
print("%s insert table %s finish!" % (realSourTable, destTable))
return
def getProfitData(dbCntInfo):
'''
在执行前需要首先用下列语句创建表
USE stocknotdealmarket;
DROP TABLE IF EXISTS histprofitdata;
CREATE TABLE histprofitdata (
ts_code text DEFAULT NULL,
end_date text DEFAULT NULL,
ann_date text DEFAULT NULL,
div_proc text DEFAULT NULL,
stk_div double DEFAULT NULL,
stk_bo_rate double DEFAULT NULL,
stk_co_rate double DEFAULT NULL,
cash_div double DEFAULT NULL,
cash_div_tax double DEFAULT NULL,
record_date text DEFAULT NULL,
ex_date text DEFAULT NULL,
pay_date text DEFAULT NULL,
div_listdate text DEFAULT NULL,
imp_ann_date text DEFAULT NULL
)
数据来源：http://f10.eastmoney.com/BonusFinancing/Index?type=web&code=sh601199
中财网:http://data.cfi.cn/cfidata.aspx?sortfd=&sortway=&curpage=1&ndk=A0A1934A1939A1957A1966A1983&xztj=&mystock=
分红产品分红数据获取
'''
# 获取产品基础信息
productBasicInfodf = pb.getAllProductBasicInfo(dbCntInfo)
if productBasicInfodf.empty:
print("表中无正在上市的数据，提前正常结束!")
return
# 获取当日日期
# finanalWorkDate = pb.getTodayDate()
sourceTable = "histprofitdata" # 分红数据
# destTable = "profitschema" # 产品分红方案
# 建立连接与tusharepro
pro = ts.pro_api('00f0c017db5d284d992f78f0971c73c9ecba4aa03dee2f38e71e4d9c')
engine = dbCntInfo.getEngineByTableName(sourceTable)
for rowIndex in productBasicInfodf.index:
oneProductTuple = productBasicInfodf.iloc[rowIndex]
productCode = oneProductTuple["product_code"] ## 产品代码
print("%d %s begin to get prifit data ..." % (rowIndex,productCode))
symbolProcuctCode = pb.code_to_symbol(productCode)
try:
df = pro.dividend(ts_code=symbolProcuctCode)
basicdf = df.reset_index(drop=True)
basicdf.to_sql(sourceTable, engine, if_exists="append", index=False)
except Exception as e:
print(productCode + " connect time out!")
time.sleep(30)
df = pro.dividend(ts_code=symbolProcuctCode)
basicdf = df.reset_index(drop=True)
basicdf.to_sql(sourceTable, engine, if_exists="append", index=False)
print(productCode + " begin to get data finish ...")
time.sleep(1)
if rowIndex % 100 == 0:
time.sleep(10)
if rowIndex % 500 == 0:
time.sleep(30)
print("all productcode profitschema finish download!")
dbCntInfo.closeAllDBConnect()
def getCurProductTradeData(productcode,dbCntInfo,engine):
'''
通过单一产品代码获取该产品的所有交易数据
:param dbCntInfo:
:param productcode:
:return:
'''
# 获取当日日期
finanalWorkDate = pb.getTodayDate()
curHour = time.localtime().tm_hour
if curHour < 17:
finanalWorkDate = pb.getYesterday()
sourceTable = "histtradedata"
destTable = "producttradedata"
# 建立连接与tusharepro
pro = ts.pro_api('00f0c017db5d284d992f78f0971c73c9ecba4aa03dee2f38e71e4d9c')
oneProductTuple = pb.getCurProductBasicInfoByProductCode(dbCntInfo,productcode)
productCode = oneProductTuple["product_code"] ## 产品代码
listedDate = oneProductTuple["listed_date"] ## 上市日期
# 获取产品的起始日期,产品可能已经存在部分行情
maxTradeDateSql = sc.PRODUCTMAXTRADEDATE_SQL % productCode
destDbBase = dbCntInfo.getDBCntInfoByTableName(tablename=destTable, productcode=productCode)
destRetList = destDbBase.execSelectSmallSql(maxTradeDateSql)
maxTradeDate = destRetList[0]['maxtradedate']
print(productCode + maxTradeDateSql)
startDate = listedDate
if maxTradeDate > listedDate:
startDate = pb.getnextnday(maxTradeDate, 1)
endDate = ((int(startDate / 10000)) + 10) * 10000 + 1231
if int(listedDate / 10000) == int(finanalWorkDate / 10000):
endDate = finanalWorkDate
if endDate > finanalWorkDate:
endDate = finanalWorkDate
if startDate > finanalWorkDate:
return
print("%s begin to get data from %d to %d ..." % (productCode, startDate, endDate))
symbolProcuctCode = pb.code_to_symbol(productCode)
try:
df = pro.daily(ts_code=symbolProcuctCode, start_date=str(startDate), end_date=str(endDate))
basicdf = df.reset_index(drop=True)
realsourcetable = sourceTable + productCode
basicdf.to_sql(realsourcetable, engine, if_exists="replace", index=False)
engine.connect().commit()
except Exception as e:
print(productCode + " connect time out!")
time.sleep(30)
df = pro.daily(ts_code=symbolProcuctCode, start_date=str(startDate), end_date=str(endDate))
basicdf = df.reset_index(drop=True)
realsourcetable = sourceTable + productCode
basicdf.to_sql(realsourcetable, engine, if_exists="replace", index=False)
while endDate < finanalWorkDate:
startDate = ((int(endDate / 10000)) + 1) * 10000 + 101
endDate = ((int(startDate / 10000)) + 10) * 10000 + 1231
if startDate > finanalWorkDate:
break
if endDate > finanalWorkDate:
endDate = finanalWorkDate
print("%s is getting data from %d to %d ..." % (productCode, startDate, endDate))
time.sleep(0.5)
try:
df = pro.daily(ts_code=symbolProcuctCode, start_date=str(startDate), end_date=str(endDate))
basicdf = df.reset_index(drop=True)
basicdf.to_sql(realsourcetable, engine, if_exists="append", index=False)
except Exception as e:
print(productCode + " connect time out!")
time.sleep(30)
df = pro.daily(ts_code=symbolProcuctCode, start_date=str(startDate), end_date=str(endDate))
basicdf = df.reset_index(drop=True)
basicdf.to_sql(realsourcetable, engine, if_exists="append", index=False)
print(productCode + " get data finish ...")
def get30tradedata():
'''
从get_hist_data中获取30min的数据然后保存到csv中，代码是以下这块，
然后从csv中读取写入到数据库中
import os
import time
df = ts.get_stock_basics()
codelist = list(df.index)
filename = 'E:\\pydevproj\\stockproj\\stockmarket\\finance\\test'
for oneindex in range(len(codelist)):
print(codelist[oneindex]+ " begin")
df = ts.get_hist_data(codelist[oneindex], ktype='30')
if df is None:
continue
realfilename = filename + "\\"+codelist[oneindex]+".csv"
if os.path.exists(realfilename):
df.to_csv(realfilename, mode='a', header=None)
else:
df.to_csv(realfilename)
print(codelist[oneindex]+ " end")
time.sleep(1)
:return:
'''
import os
import tushare as ts
import logging
import csv
from finance.servicelib.processinit import dbcnt
from finance.servicelib.processinit import stocklog
from finance.util import SqlCons as sqlcons
from finance.servicelib.public import public as pb
stocklog.initLogging()
dbCntInfo = dbcnt.createDbConnect(dbpool=False)
df = ts.get_stock_basics()
codelist = list(df.index)
filename = 'G:\\nfx\\stockproj\\stockmarket\\finance\\resource\\trade30'
for oneindex in range(len(codelist)):
print(codelist[oneindex] + " begin")
productCode = codelist[oneindex]
realfilename = filename + "\\" + productCode + ".csv"
if os.path.exists(realfilename) is not True:
logging.info("product(%s) there is no file!" % productCode)
continue
logging.info("file(%s) begin to read!" % realfilename)
sourcetable = "prod30tradedata"
tableDbBase = dbCntInfo.getDBCntInfoByTableName(
tablename=sourcetable, productcode=productCode)
firstline = 0
execlSql = sqlcons.CSV30DATAINSERTDB
with open(realfilename, 'r') as fname:
reader = csv.reader(fname)
print(reader)
for row in reader:
if firstline == 0:
firstline = firstline + 1
continue
# print(row)
# print(type(row[0]))
tradedate = int(row[0][:4] + row[0][5:7] + row[0][8:10])
tradetime = int(row[0][11:13] + row[0][14:16] + row[0][17:19])
openPrice = float(row[1])
highPrice = float(row[2])
closePrice = float(row[3])
lowPrice = float(row[4])
productVolumn = float(row[5])
productAmount = 0.0
print(tradedate)
print(tradetime)
# prod30tradedata(product_code, symbol_code, trade_date, trade_time, open_price, high_price,
# close_price, low_price, product_volume, product_amount)
symbol_code = pb.code_to_symbol(productCode)
# print(openPrice)
# print(highPrice)
# print(closePrice)
# print(lowPrice)
# print(productVolumn)
# print(symbol_code)
execlSql = execlSql + sqlcons.CSV30INSERTVAR % (
productCode, symbol_code, tradedate, tradetime, openPrice,
highPrice, closePrice, lowPrice, productVolumn,
productAmount)
firstline = firstline + 1
if firstline == 100:
excepte = tableDbBase.execNotSelectSql(execlSql[:-1])
if excepte is not None:
print(excepte)
print("执行异常，程序终止!")
break
execlSql = sqlcons.CSV30DATAINSERTDB
firstline = 1
if len(execlSql) > len(sqlcons.CSV30DATAINSERTDB):
excepte = tableDbBase.execNotSelectSql(execlSql[:-1])
if excepte is not None:
print(excepte)
print("执行异常，程序终止!")
break
logging.info("file(%s) read end!" % realfilename)
dbCntInfo.closeAllDBConnect()
continue
# print(row)
# print(type(row[0]))
tradedate = int(row[0][:4] + row[0][5:7] + row[0][8:10])
tradetime = int(row[0][11:13] + row[0][14:16] + row[0][17:19])
openPrice = float(row[1])
highPrice = float(row[2])
closePrice = float(row[3])
lowPrice = float(row[4])
productVolumn = float(row[5])
productAmount = 0.0
print(tradedate)
print(tradetime)
# prod30tradedata(product_code, symbol_code, trade_date, trade_time, open_price, high_price,
# close_price, low_price, product_volume, product_amount)
symbol_code = pb.code_to_symbol(productCode)
# print(openPrice)
# print(highPrice)
# print(closePrice)
# print(lowPrice)
# print(productVolumn)
# print(symbol_code)
execlSql = execlSql + sqlcons.CSV30INSERTVAR % (
productCode, symbol_code, tradedate, tradetime, openPrice,
highPrice, closePrice, lowPrice, productVolumn,
productAmount)
firstline = firstline + 1
if firstline == 100:
excepte = tableDbBase.execNotSelectSql(execlSql[:-1])
if excepte is not None:
print(excepte)