def process_item(self, item, spider):
code = item['code']
dfs = item['dfs']
print dfs
#DFToSQLOneByOne(session,df,code,'GDRS')
for dfItem in dfs:
df = dfItem['df']
table = dfItem['table']
if table == 'stock_gdrs_data':
df.to_sql(table,
database_engine,
if_exists='append',
index=True,
index_label=['date', 'code'],
dtype={
'code': mysql.NVARCHAR(6),
'date': mysql.DATE
})
else:
df.to_sql(table,
database_engine,
if_exists='append',
index=True,
index_label=['date', 'code', 'sid'],
dtype={
'code': mysql.NVARCHAR(6),
'date': mysql.DATE,
'sid': mysql.NVARCHAR(6)
})
class StockMonthKdjMacd(Base):
__tablename__ = 'month_kdj_macd'
date = Column('date',mysql.DATE,primary_key=True)
code = Column('code',mysql.NVARCHAR(6), primary_key=True)
open_p = Column('open',mysql.DOUBLE)
high_p = Column('high',mysql.DOUBLE)
low_p = Column('low',mysql.DOUBLE)
close_p = Column('close',mysql.DOUBLE)
dif = Column('dif',mysql.DOUBLE)
dea = Column('dea',mysql.DOUBLE)
macd = Column('macd',mysql.DOUBLE)
k = Column('k',mysql.DOUBLE)
d = Column('d',mysql.DOUBLE)
j = Column('j',mysql.DOUBLE)
j_chaomai_flag = Column('j_chaomai_flag',mysql.INTEGER)
def __init__(self, kargs):
""""""
self.date = kargs['date']
self.code = kargs['code']
self.open = kargs['open']
self.high = kargs['high']
self.low = kargs['low']
self.close = kargs['close']
self.dif = kargs['dif']
self.dea = kargs['dea']
self.macd = kargs['macd']
self.k = kargs['k']
self.d = kargs['d']
self.j = kargs['j']
self.j_chaomai_flag = kargs['j_chaomai_flag']
def get_xsg_monthly(self):
try:
df = ts.xsg_data()
df.drop_duplicates(inplace=True)
date = np.array(df['date'])
code = np.array(df['code'])
count = np.array(df['count'])
ratio = np.array(df['ratio'])
arrays = [date, code]
tuples = list(zip(*arrays))
index = pd.MultiIndex.from_tuples(tuples, names=['date', 'code'])
s = pd.Series(count, index=index)
f = s.to_frame()
f.rename(index=str, columns={0: "count"}, inplace=True)
f['ratio'] = ratio
f.to_sql('stock_xsg_data',
mysqlEngine,
if_exists='append',
index=True,
index_label=['date', 'code'],
dtype={
'code': mysql.NVARCHAR(6),
'date': mysql.DATE
})
"""
mh = MysqlHandle()
DFToSQLOneByOne(mh.session,f,'Report')
"""
except Exception, e:
print e
def get_hist_daily_trade_data(self, code, startDate, endDate):
try:
df = ts.get_h_data(code, startDate, endDate)
df.reset_index(level=0, inplace=True)
df['code'] = [code for i in range(len(df))]
date = np.array(df['date'])
code = np.array(df['code'])
open = np.array(df['open'])
high = np.array(df['high'])
close = np.array(df['close'])
low = np.array(df['low'])
volume = np.array(df['volume'])
amount = np.array(df['amount'])
arrays = [date, code]
tuples = list(zip(*arrays))
index = pd.MultiIndex.from_tuples(tuples, names=['date', 'code'])
s = pd.Series(open, index=index)
f = s.to_frame()
f.rename(index=str, columns={0: "open"}, inplace=True)
f['high'] = high
f['close'] = close
f['low'] = low
f['volume'] = volume
f.to_sql('stock_daily_kt_data',
StockHelper.engine,
if_exists='append',
index=True,
index_label=['date', 'code'],
dtype={
'code': mysql.NVARCHAR(6),
'date': mysql.DATE
})
#stocklib.DFToSQLOneByOne(self.session,f,'StockDailyKT')
except Exception, e:
print e
class SDLTGD(Base):
"""
Shi Da Liu Tong Gu Dong
"""
__tablename__ = 'stock_sdltgd_data'
date = Column('date', mysql.DATE, primary_key=True)
code = Column('code', mysql.NVARCHAR(6), primary_key=True)
sid = Column('sid', mysql.NVARCHAR(6), primary_key=True)
gdmc = Column('gdmc', mysql.NVARCHAR(200))
cgbl = Column('cgbl', mysql.DOUBLE)
def __init__(self, kargs):
""""""
self.date = kargs['date']
self.code = kargs['code']
self.sid = kargs['sid']
self.gdmc = kargs['gdmc']
self.cgbl = kargs['cgbl']
def get_stock_basics(self):
try:
df = ts.get_stock_basics()
#df.reset_index(level=0,inplace=True)
df.to_sql('stock_basics',
mysqlEngine,
if_exists='replace',
index=True,
index_label='code',
dtype={'code': mysql.NVARCHAR(6)})
except Exception, e:
print e
class FinanceData(Base):
"""
Stock Report class
"""
__tablename__ = 'stock_finance_data'
date = Column('date', mysql.DATE, primary_key=True)
code = Column('code', mysql.NVARCHAR(6), primary_key=True)
eps = Column('eps', mysql.DOUBLE)
net_profits = Column('net_profits', mysql.DOUBLE)
net_profits_yoy = Column('net_profits_yoy', mysql.DOUBLE)
kf_net_profits = Column('kf_net_profits',
mysql.DOUBLE) #kou fei net_profits
business_income = Column('business_income',
mysql.DOUBLE) #ying ye zong shou ru
business_income_yoy = Column('business_income_yoy',
mysql.DOUBLE) #ying ye zong shou ru tong bi
bvps = Column('bvps', mysql.DOUBLE) # mei gu jing zi chan
roe = Column('roe', mysql.DOUBLE) #jing zi chan shou yi lv
roe_tb = Column('roe_tb', mysql.DOUBLE) #jing zi chan shou yi lv tan bao
zc_fz_ratio = Column('zc_fz_ratio', mysql.DOUBLE) #zi chan fu zhai bi lv
reserved_per_share = Column('reserved_per_share',
mysql.DOUBLE) #mei gu zi ben gong ji jin
reserved_profits_per_share = Column(
'reserved_profits_per_share', mysql.DOUBLE) #mei gu wei fen pei li run
epcf = Column('epcf', mysql.DOUBLE) #mei gu jing ying xian jin liu
net_profit_ratio = Column('net_profit_ratio', mysql.DOUBLE) #jing li lv
gross_profit_rate = Column('gross_profit_rate',
mysql.DOUBLE) #xiao shou mao li lv
inventory_turnover = Column('inventory_turnover', mysql.DOUBLE) #mao li lv
def __init__(self, kargs):
""""""
self.date = kargs['date']
self.code = kargs['code']
self.eps = kargs['eps']
self.net_profits = kargs['net_profits']
self.net_profits_yoy = kargs['net_profits_yoy']
self.kf_net_profits = kargs['kf_net_profits']
self.business_income = kargs['business_income']
self.business_income_yoy = kargs['business_income_yoy']
self.bvps = kargs['bvps']
self.roe = kargs['roe']
self.roe_tb = kargs['roe_tb']
self.zc_fz_ratio = kargs['zc_fz_ratio']
self.reserved_per_share = kargs['reserved_per_share']
self.reserved_profits_per_share = kargs['reserved_profits_per_share']
self.epcf = kargs['epcf']
self.net_profit_ratio = kargs['net_profit_ratio']
self.gross_profit_rate = kargs['gross_profit_rate']
self.inventory_turnover = kargs['inventory_turnover']
class XSG(Base):
"""
Xian Shou Gu
"""
__tablename__ = 'stock_xsg_data'
date = Column('date', mysql.DATE, primary_key=True)
code = Column('code', mysql.NVARCHAR(6), primary_key=True)
count = Column('count', mysql.DOUBLE)
ratio = Column('ratio', mysql.DOUBLE)
def __init__(self, kargs):
""""""
self.date = kargs['date']
self.code = kargs['code']
self.count = kargs['count']
self.ratio = kargs['ratio']
class GDRS(Base):
"""
Gu Dong Ren Shu
"""
__tablename__ = 'stock_gdrs_data'
date = Column('date', mysql.DATE, primary_key=True)
code = Column('code', mysql.NVARCHAR(6), primary_key=True)
gdrs = Column('gdrs', mysql.BIGINT)
gdrs_jsqbh = Column('gdrs_jsqbh', mysql.DOUBLE)
rjltg = Column('rjltg', mysql.BIGINT)
rjltg_jsqbh = Column('rjltg_jsqbh', mysql.DOUBLE)
def __init__(self, kargs):
""""""
self.date = kargs['date']
self.code = kargs['code']
self.gdrs = kargs['gdrs']
self.gdrs_jsqbh = kargs['gdrs_jsqbh']
self.rjltg = kargs['rjltg']
self.rjltg_jsqbh = kargs['rjltg_jsqbh']
class StockDailyKT(Base):
"""
Stock daily KT class
"""
__tablename__ = 'stock_daily_kt_data'
date = Column('date', mysql.DATE, primary_key=True)
code = Column('code', mysql.NVARCHAR(6), primary_key=True)
open = Column('open', mysql.DOUBLE)
high = Column('high', mysql.DOUBLE)
close = Column('close', mysql.DOUBLE)
low = Column('low', mysql.DOUBLE)
volume = Column('volume', mysql.DOUBLE)
def __init__(self, kargs):
""""""
self.date = kargs['date']
self.code = kargs['code']
self.open = kargs['open']
self.high = kargs['high']
self.close = kargs['close']
self.low = kargs['low']
self.volume = kargs['volume']
class Forecast(Base):
"""
Ye Ji Yu Gao
"""
__tablename__ = 'stock_forecast_data'
date = Column('date', mysql.DATE, primary_key=True)
code = Column('code', mysql.NVARCHAR(6), primary_key=True)
report_date = Column('report_date', mysql.DATE)
forecast_type = Column('forecast_type', mysql.TEXT)
pre_eps = Column('pre_eps', mysql.DOUBLE)
min_range = Column('min_range', mysql.DOUBLE)
max_range = Column('max_range', mysql.DOUBLE)
def __init__(self, kargs):
""""""
self.date = kargs['date']
self.code = kargs['code']
self.report_date = kargs['report_date']
self.forecast_type = kargs['forecast_type']
self.pre_eps = kargs['pre_eps']
self.min_range = kargs['min_range']
self.max_range = kargs['max_range']
class StockBasic(Base):
__tablename__ = 'stock_basics'
code = Column('code', mysql.NVARCHAR(6), primary_key=True)
name = Column('name', mysql.TEXT)
industry = Column('industry', mysql.TEXT)
area = Column('area', mysql.TEXT)
pe = Column('pe', mysql.DOUBLE)
outstanding = Column('outstanding', mysql.DOUBLE)
totals = Column('totals', mysql.DOUBLE)
totalAssets = Column('totalAssets', mysql.DOUBLE)
liquidAssets = Column('liquidAssets', mysql.DOUBLE)
fixedAssets = Column('fixedAssets', mysql.DOUBLE)
reserved = Column('reserved', mysql.DOUBLE)
reservedPerShare = Column('reservedPerShare', mysql.DOUBLE)
esp = Column('esp', mysql.DOUBLE)
bvps = Column('bvps', mysql.DOUBLE)
pb = Column('pb', mysql.DOUBLE)
timeToMarket = Column('timeToMarket', mysql.BIGINT(20))
def __init__(self, code, name, industry, area, pe, outstanding, totals,
totalAssets, liquidAssets, fixedAssets, reserved,
reservedPerShar, esp, bvps, pb, timeToMarket):
self.code = code
self.name = name
self.industry = industry
self.area = area
self.pe = pe
self.outstanding = outstanding
self.totals = totals
self.totalAssets = totalAssets
self.liquidAssets = liquidAssets
self.fixedAssets = fixedAssets
self.reserved = reserved
self.reservedPerShar = reservedPerShar
self.esp = esp
self.bvps = bvps
self.pb = pb
self.timeToMarket = timeToMarket
def get_stockForecastData(self, year, quarter):
try:
if quarter == 2 or quarter == 3:
date = '%s-%s-%s' % (str(year), str(3 * quarter), '30')
else:
date = '%s-%s-%s' % (str(year), str(3 * quarter), '31')
print '-------------------'
print date
print '-----------------------'
df = ts.forecast_data(year, quarter)
df['date'] = np.array([date for i in range(len(df))])
f_ranges = list(df['range'])
min_range = []
max_range = []
index = 0
nanIdxs = []
print f_ranges
for f_range in f_ranges:
index = index + 1
if f_range == '0':
min_range.append(0)
max_range.append(0)
continue
try:
x = float(f_range)
if math.isnan(float(f_range)) != True:
min_range.append(x * 100)
max_range.append(x * 100)
continue
except:
ranges = f_range.split('~')
if len(ranges) == 2:
min_range.append(float(ranges[0].strip('%')))
max_range.append(float(ranges[1].strip('%')))
else:
min_range.append(float(ranges[0].strip('%')))
max_range.append(float(ranges[0].strip('%')))
continue
if math.isnan(float(f_range)) == True:
nanIdxs.append(index - 1)
for nanIdx in nanIdxs:
df['date'].pop(nanIdx)
df['code'].pop(nanIdx)
df['report_date'].pop(nanIdx)
df['type'].pop(nanIdx)
df['pre_eps'].pop(nanIdx)
new_codes = []
codes = list(df['code'])
idx = 0
dupIdxs = []
for code in codes:
if code not in new_codes:
new_codes.append(code)
else:
dupIdxs.append(idx)
idx = idx + 1
for dupIdx in dupIdxs:
df['date'].pop(dupIdx)
df['report_date'].pop(dupIdx)
df['type'].pop(dupIdx)
df['pre_eps'].pop(dupIdx)
min_range.pop(dupIdx)
max_range.pop(dupIdx)
date = np.array(df['date'])
code = np.array(new_codes)
report_date = np.array(df['report_date'])
forecast_type = np.array(df['type'])
pre_eps = np.array(df['pre_eps'])
min_range = np.array(min_range)
max_range = np.array(max_range)
arrays = [date, code]
tuples = list(zip(*arrays))
index = pd.MultiIndex.from_tuples(tuples, names=['date', 'code'])
s = pd.Series(forecast_type, index=index)
f = s.to_frame()
f.rename(index=str, columns={0: "forecast_type"}, inplace=True)
f['report_date'] = report_date
f['pre_eps'] = pre_eps
f['min_range'] = min_range
f['max_range'] = max_range
f.to_sql('stock_forecast_data',
mysqlEngine,
if_exists='append',
index=True,
index_label=['date', 'code'],
dtype={
'code': mysql.NVARCHAR(6),
'date': mysql.DATE
})
"""
mh = MysqlHandle()
DFToSQLOneByOne(mh.session,f,'Report')
"""
except Exception, e:
print e
