def loan_rate(**kwargs):
df = ts.get_loan_rate()
df["Date"] = pd.to_datetime(df.date, format="%Y-%m-%d")
df["rate"] = df[[
"rate"
]].applymap(lambda x: float(x) if x != "--" else None)["rate"]
return df[["rate", "loan_type"]].set_index(df["Date"])
def get_loan_rate():
try:
df = ts.get_loan_rate()
engine = create_engine('mysql://*****:*****@127.0.0.1/stock?charset=utf8')
df.to_sql('loan_rate', engine, if_exists='append')
print "message"
except Exception, e:
e.message
def get_loan_rate_info():
# 贷款利率
df = ts.get_loan_rate()
if df is not None:
res = df.to_sql(microE_loan_rate, engine, if_exists='replace')
msg = 'ok' if res is None else res
print('获取贷款利率: ' + msg + '\n')
else:
print('获取贷款利率: ' + 'None' + '\n')
def stat_all(tmp_datetime):
# 存款利率
data = ts.get_deposit_rate()
common.insert_db(data, "ts_deposit_rate", False, "`date`,`deposit_type`")
# 贷款利率
data = ts.get_loan_rate()
common.insert_db(data, "ts_loan_rate", False, "`date`,`loan_type`")
# 存款准备金率
data = ts.get_rrr()
common.insert_db(data, "ts_rrr", False, "`date`")
# 货币供应量
data = ts.get_money_supply()
common.insert_db(data, "ts_money_supply", False, "`month`")
# 货币供应量(年底余额)
data = ts.get_money_supply_bal()
common.insert_db(data, "ts_money_supply_bal", False, "`year`")
# 国内生产总值(年度)
data = ts.get_gdp_year()
common.insert_db(data, "ts_gdp_year", False, "`year`")
# 国内生产总值(季度)
data = ts.get_gdp_quarter()
common.insert_db(data, "ts_get_gdp_quarter", False, "`quarter`")
# 三大需求对GDP贡献
data = ts.get_gdp_for()
common.insert_db(data, "ts_gdp_for", False, "`year`")
# 三大产业对GDP拉动
data = ts.get_gdp_pull()
common.insert_db(data, "ts_gdp_pull", False, "`year`")
# 三大产业贡献率
data = ts.get_gdp_contrib()
common.insert_db(data, "ts_gdp_contrib", False, "`year`")
# 居民消费价格指数
data = ts.get_cpi()
common.insert_db(data, "ts_cpi", False, "`month`")
# 工业品出厂价格指数
data = ts.get_ppi()
common.insert_db(data, "ts_ppi", False, "`month`")
#############################基本面数据 http://tushare.org/fundamental.html
# 股票列表
data = ts.get_stock_basics()
print(data.index)
common.insert_db(data, "ts_stock_basics", True, "`code`")
def test_get_loan_rate(self):
# ------------------------------------------------
# 贷款利率
# 返回值说明：
#
# date: 执行日期
# loan_type: 存款种类
# rate: 利率（ % ）
# ------------------------------------------------
mongo = MongoClient("127.0.0.1", 27017)
df = ts.get_loan_rate()
print(df)
insert_string = df.to_json(orient='records')
items = json.loads(insert_string)
mongo.macro.loan_rate.insert(items)
self.core_function("gdp_year")
def get_macro():
Macro={}
Macro['Depo']=ts.get_deposit_rate()
Macro['Loan']=ts.get_loan_rate()
Macro['RRR']=ts.get_rrr()
Macro['MoneySupply']=ts.get_money_supply()
Macro['MoneyBalance']=ts.get_money_supply_bal()
Macro['GDPYOY']=ts.get_gdp_year()
Macro['GDPQOQ']=ts.get_gdp_quarter()
Macro['GDPFOR']=ts.get_gdp_for()
Macro['GDPPULL']=ts.get_gdp_pull()
Macro['GDPCON']=ts.get_gdp_contrib()
Macro['CPI']=ts.get_cpi()
Macro['PPI']=ts.get_ppi()
Macro['SHIBO']=ts.shibor_data()
return Macro
def get_loan_rate():
"""得到贷款利率"""
logger.info('Begin get LoanRate.')
try:
data_df = ts.get_loan_rate()
except Exception as e:
logger.exception('Error get LoanRate.')
return None
else:
data_dicts = []
if data_df.empty:
logger.warn('Empty get LoanRate.')
else:
data_dicts = [{'date': row[0], 'loan_type': row[1], 'rate': row[2]} for row in data_df.values]
logger.info('Success get LoanRate.')
return data_dicts
def return_loan_rate(self):
'''
贷款利率
'''
df = ts.get_loan_rate()
labels = [
u'贴现(贴现)',
u'个人住房公积金贷款(五年以上)',
u'个人住房商业贷款(五年以上)',
u'短期贷款(六个月至一年)',
u'中长期贷款(一至三年)',
u'中长期贷款(三至五年)',
u'中长期贷款(五年以上)',
]
detail = {"date": [],
"datasets": []}
detail['date'] = sorted(set(df[df['loan_type'].isin(labels)]['date'].values.tolist()))
for label in labels:
df2 = df[df['loan_type'] == label]
rate = df2['rate'].values.tolist()
rate.reverse()
date = df2['date'].values.tolist()
date.reverse()
if date == detail['date']:
item = {
'label': label,
'data': rate,
}
else:
tmp_dict = {}
for idx in xrange(0, len(date)):
tmp_dict[date[idx]] = rate[idx]
for idx in xrange(0, len(detail['date'])):
k = detail['date'][idx]
if k not in tmp_dict:
tmp_dict[k] = None
item = {
'label': label,
'data': [tmp_dict[k] for k in detail['date']]
}
detail['datasets'].append(item)
self.reply(detail=detail)
def get_interest_rate():
deposit = ts.get_deposit_rate()
loan = ts.get_loan_rate()
data = deposit[[x.startswith('定期存款整存整取') for x in deposit['deposit_type']
]].pivot_table(values="rate",
index=['date'],
columns='deposit_type',
aggfunc=sum)
data1 = loan[[
x.startswith('短期贷款') or x.startswith('中长期贷款')
for x in loan['loan_type']
]].pivot_table(values="rate",
index=['date'],
columns='loan_type',
aggfunc=sum)
data.columns = [
x.strip().replace('''定期存款整存整取''', '').replace('''(''',
'').replace(''')''', '')
for x in data.columns
]
data.columns = ['DOYEAR' if x == '一年' else x for x in data.columns]
data.columns = ['DTMON' if x == '三个月' else x for x in data.columns]
data.columns = ['DTYEAR' if x == '三年' else x for x in data.columns]
data.columns = ['DSYEAR' if x == '二年' else x for x in data.columns]
data.columns = ['DFYEAR' if x == '五年' else x for x in data.columns]
data.columns = ['DHYEAR' if x == '半年' else x for x in data.columns]
data1.columns = [
x.strip().replace('''短期贷款''',
'').replace('''中长期贷款''',
'').replace('''(''',
'').replace(''')''', '')
for x in data1.columns
]
data1.columns = ['SYEAR' if x == '一至三年' else x for x in data1.columns]
data1.columns = ['LTMON' if x == '三至五年' else x for x in data1.columns]
data1.columns = ['LFYEAR' if x == '五年以上' else x for x in data1.columns]
data1.columns = ['LHYEAR' if x == '六个月以内' else x for x in data1.columns]
data1.columns = ['LOYEAR' if x == '六个月至一年' else x for x in data1.columns]
res = pd.concat([data1, data], axis=1).reset_index().fillna(method='ffill')
res.columns = ['date' if x == 'index' else x for x in res.columns]
res['crawl_date'] = QA_util_today_str()
return (res)
def __call__(self, conns):
self.base = Base()
self.financial_data = conns['financial_data']
'''存款利率'''
deposit_rate = ts.get_deposit_rate()
self.base.batchwri(deposit_rate, 'deposit_rate', self.financial_data)
'''贷款利率'''
loan_rate = ts.get_loan_rate()
self.base.batchwri(loan_rate, 'loan_rate', self.financial_data)
'''存款准备金率'''
rrr = ts.get_rrr()
self.base.batchwri(rrr, 'RatioOfDeposit', self.financial_data)
'''货币供应量'''
money_supply = ts.get_money_supply()
self.base.batchwri(money_supply, 'money_supply', self.financial_data)
'''货币供应量（年底余额)'''
money_supply_bal = ts.get_money_supply_bal()
self.base.batchwri(money_supply_bal, 'money_supply_bal',
self.financial_data)
'''国内生产总值(年度）'''
gdp_year = ts.get_gdp_year()
self.base.batchwri(gdp_year, 'gdp_year', self.financial_data)
'''国内生产总值（季度）'''
gdp_quarter = ts.get_gdp_quarter()
self.base.batchwri(gdp_quarter, 'gdp_quarter', self.financial_data)
'''三大需求对GDP贡献'''
gdp_for = ts.get_gdp_for()
self.base.batchwri(gdp_for, 'gdp_for', self.financial_data)
'''三大产业对GDP拉动'''
gdp_pull = ts.get_gdp_pull()
self.base.batchwri(gdp_pull, 'gdp_pull', self.financial_data)
'''三大产业贡献率'''
gdp_contrib = ts.get_gdp_contrib()
self.base.batchwri(gdp_contrib, 'gdp_contrib', self.financial_data)
'''居民消费价格指数'''
cpi = ts.get_cpi()
self.base.batchwri(cpi, 'cpi', self.financial_data)
'''工业品出场价格指数'''
ppi = ts.get_ppi()
self.base.batchwri(ppi, 'ppi', self.financial_data)
def deposit_vs_loan(self):
dat_loan=ts.get_loan_rate()
dat_deposit=ts.get_deposit_rate()
#dat_loan = dat_loan[dat_loan['loan_type']=="中长期贷款(一至三年)"]
dat_loan = dat_loan[dat_loan['loan_type']=="短期贷款(六个月以内)"]
dat_deposit=dat_deposit[dat_deposit['deposit_type']=="活期存款(不定期)"]
dat_loan.index=pd.to_datetime(dat_loan['date'])
dat_deposit.index=pd.to_datetime(dat_deposit['date'])
dat_loan=dat_loan.drop(['date','loan_type'],axis=1)
dat_deposit=dat_deposit.drop(['date','deposit_type'],axis=1)
dat_loan=dat_loan['2015':'2000']
dat_deposit=dat_deposit['2015':'2000']
dat=dat_loan
dat['deposit']=dat_deposit['rate']
dat['loan']=dat['rate']
dat=dat.drop(['rate'],axis=1)
dat=dat.dropna()
dat=dat.astype(dtype='float64')
dat['loan-deposit']=dat['loan']-dat['deposit']
print(dat)
dat.plot()
plt.show()
def macrodata(start=None, end=None):
"""macroeconomics data : Shibor | Reserve Ratio | M2 | GDP | CPI | Loan Rate.
the data start from 2006-10-08. Cause it is when the shibor data start on Tushare.
parameters:
---------
start: a string present a date indicates the return data start from. for example: '2011-01-22'
end : refer to start
return:
--------
pandas.DataFrame
"""
T = __T
T.rename(columns={'calendarDate': 'date'}, inplace=True)
today = dt.today()
today = dt.strftime(today, '%Y-%m-%d')
T = T[T.date > '2006-04-01']
T = T[T.date <= today]
loan = ts.get_loan_rate() #date loan_type rate
loan = loan[loan.date > '2006-06-01']
rate = pd.DataFrame()
for i in range(len(loan)):
if loan.iloc[i].loan_type == '短期贷款(六个月以内)':
rate = rate.append(loan.iloc[i][['date', 'rate']])
rrr = ts.get_rrr()[['date', 'now']] # 准备金率
rrr.rename(columns={'now': 'rrr'}, inplace=True)
rrr = rrr[rrr.date > '2006-08-01']
dataScale = ['15', '09']
dataindex = 0
def makeDate(raw_date):
raw_date = str(raw_date)
raw_date = raw_date.split('.')
if int(raw_date[1]) <= 9:
raw_date[1] = '0' + raw_date[1]
return raw_date[0] + '-' + raw_date[1] + '-' + dataScale[dataindex]
def makeQuarter(raw_date):
raw_date = str(raw_date)
raw_date = raw_date.split('.')
p = raw_date[1]
q = {'1': '01', '2': '04', '3': '07', '4': '10'}
return raw_date[0] + '-' + q[p] + '-' + '15'
supply = ts.get_money_supply()[['month', 'm2_yoy']] #15th.
supply.rename(columns={'month': 'date', 'm2_yoy': 'm2'}, inplace=True)
if type(supply.iloc[1]['date']) == type(''):
supply = supply[supply.date > '2006.6']
else:
supply = supply[supply.date > 2006.6]
supply['date'] = supply['date'].apply(makeDate)
dataindex = dataindex + 1
m2 = supply
gdp = ts.get_gdp_quarter()[['quarter',
'gdp_yoy']] # gdp will available at 15th.
gdp.rename(columns={'quarter': 'date', 'gdp_yoy': 'gdp'}, inplace=True)
if type(gdp.iloc[1]['date']) == type(''):
gdp = gdp[gdp.date > '2006.1']
else:
gdp = gdp[gdp.date > 2006.1]
gdp['date'] = gdp['date'].apply(makeQuarter)
cpi = ts.get_cpi() #month , cpi, available at 9th.
cpi.rename(columns={'month': 'date'}, inplace=True)
if type(cpi.iloc[1]['date']) == type(''):
cpi = cpi[cpi.date > '2006.6']
else:
cpi = cpi[cpi.date > 2006.6]
cpi['date'] = cpi['date'].apply(makeDate)
lastvalue = 0.0
def setValue(v):
nonlocal lastvalue
if pd.isnull(v) or v == 'None':
return lastvalue
else:
lastvalue = v
return lastvalue
T = T.merge(cpi, on='date', how='left')
lastvalue = cpi.iloc[-1]['cpi']
T['cpi'] = T['cpi'].apply(setValue)
T = T.merge(gdp, on='date', how='left')
lastvalue = gdp.iloc[-1]['gdp']
T['gdp'] = T['gdp'].apply(setValue)
T = T.merge(m2, on='date', how='left')
lastvalue = m2.iloc[-1]['m2']
T['m2'] = T['m2'].apply(setValue)
T = T.merge(rrr, on='date', how='left')
lastvalue = rrr.iloc[-1]['rrr']
T['rrr'] = T['rrr'].apply(setValue)
T = T.merge(rate, on='date', how='left')
lastvalue = rate.iloc[-1]['rate']
T['rate'] = T['rate'].apply(setValue)
# T=shibor.merge(T,on='date',how='left')
T = T[T.isOpen > 0.5]
T = T[T.date >= '2006-10-08']
del T['isOpen']
if start and end:
T = T[T.date >= start]
T = T[T.date <= end]
T[['rrr']] = T[['rrr']].astype(float)
T[['m2']] = T[['m2']].astype(float)
T[['rate']] = T[['rate']].astype(float)
T[['gdp']] = T[['gdp']].astype(float)
T[['cpi']] = T[['cpi']].astype(float)
T = T.reset_index()
del T['index']
return T
def getLoanRate(self):
file_name = 'loan_rate.csv'
path = self.index + self.index_loan_rate + file_name
data = ts.get_loan_rate()
data.to_csv(path, encoding='utf-8')
print(file_name)
'''
i didn't multiprocess this one since the performance seems acceptable
'''
import tushare as ts
import pandas as pd
if __name__ =='__main__':
deposit_rate=ts.get_deposit_rate()
deposit_rate .to_csv('D:\\ts\\macro\\deposit_rate.csv',encoding='gbk')
loan_rate=ts.get_loan_rate()
loan_rate.to_csv('D:\\ts\\macro\\loan_rate.csv', encoding='gbk')
rrr=ts.get_rrr()
rrr.to_csv('D:\\ts\\macro\\rrr.csv', encoding='gbk')
money_supply=ts.get_money_supply()
money_supply.to_csv('D:\\ts\\macro\\money_supply.csv', encoding='gbk')
money_supply_bal=ts.get_money_supply_bal()
money_supply_bal.to_csv('D:\\ts\\macro\\money_supply_bal.csv', encoding='gbk')
gdp_year=ts.get_gdp_year()
gdp_year.to_csv('D:\\ts\\macro\\gdp_year.csv', encoding='gbk')
gdp_quater=ts.get_gdp_quarter()
gdp_quater.to_csv('D:\\ts\\macro\\gdp_quater.csv', encoding='gbk')
gdp_for=ts.get_gdp_for()
def macro_type(macros_type):
if macros_type == 'deposit_rate':
deposit_rate = ts.get_deposit_rate()
if deposit_rate is not None:
deposit_rate.to_sql('macros_deposit_rate',
engine,
flavor='mysql',
if_exists='replace')
elif macros_type == 'loan_rate':
loan_rate = ts.get_loan_rate()
if loan_rate is not None:
loan_rate.to_sql('macros_loan_rate',
engine,
flavor='mysql',
if_exists='replace')
elif macros_type == 'rrr':
rrr = ts.get_rrr()
if rrr is not None:
rrr.to_sql('macros_rrr',
engine,
flavor='mysql',
if_exists='replace')
elif macros_type == 'money_supply':
money_supply = ts.get_money_supply()
if money_supply is not None:
money_supply.to_sql('macros_money_supply',
engine,
flavor='mysql',
if_exists='replace')
elif macros_type == 'money_supply_bal':
money_supply_bal = ts.get_money_supply_bal()
if money_supply_bal is not None:
money_supply_bal.to_sql('macros_money_supply_bal',
engine,
flavor='mysql',
if_exists='replace')
elif macros_type == 'gdp_year':
gdp_year = ts.get_gdp_year()
if gdp_year is not None:
gdp_year.to_sql('macros_gdp_year',
engine,
flavor='mysql',
if_exists='replace')
elif macros_type == 'gdp_quater':
gdp_quater = ts.get_gdp_quarter()
if gdp_quater is not None:
gdp_quater.to_sql('macros_gdp_quater',
engine,
flavor='mysql',
if_exists='replace')
elif macros_type == 'gdp_for':
gdp_for = ts.get_gdp_for()
if gdp_for is not None:
gdp_for.to_sql('macros_gdp_for',
engine,
flavor='mysql',
if_exists='replace')
elif macros_type == 'gdp_pull':
gdp_pull = ts.get_gdp_pull()
if gdp_pull is not None:
gdp_pull.to_sql('macros_gdp_pull',
engine,
flavor='mysql',
if_exists='replace')
elif macros_type == 'gdp_contrib':
gdp_contrib = ts.get_gdp_contrib()
if gdp_contrib is not None:
gdp_contrib.to_sql('macros_gdp_contrib',
engine,
flavor='mysql',
if_exists='replace')
elif macros_type == 'cpi':
cpi = ts.get_cpi()
if cpi is not None:
cpi.to_sql('macros_cpi',
engine,
flavor='mysql',
if_exists='replace')
elif macros_type == 'ppi':
ppi = ts.get_ppi()
if ppi is not None:
ppi.to_sql('macros_ppi',
engine,
flavor='mysql',
if_exists='replace')
def init(engine, session): tbl = "macro_deposit" tsl.log(tbl + " start...") df = ts.get_deposit_rate() df.to_sql(tbl,engine,if_exists='replace') tsl.log(tbl + " done") tbl = "macro_loan" tsl.log(tbl + " start...") df = ts.get_loan_rate() df.to_sql(tbl,engine,if_exists='replace') tsl.log(tbl + " done") tbl = "macro_rrr" tsl.log(tbl + " start...") df = ts.get_rrr() df.to_sql(tbl,engine,if_exists='replace') tsl.log(tbl + " done") tbl = "macro_money_supply" tsl.log(tbl + " start...") df = ts.get_money_supply() df.to_sql(tbl,engine,if_exists='replace') tsl.log(tbl + " done") tbl = "macro_money_supply_year" tsl.log(tbl + " start...") df = ts.get_money_supply_bal() df.to_sql(tbl,engine,if_exists='replace') tsl.log(tbl + " done") tbl = "macro_gdp_year" tsl.log(tbl + " start...") df = ts.get_gdp_year() df.to_sql(tbl,engine,if_exists='replace') tsl.log(tbl + " done") tbl = "macro_gdp_quarter" tsl.log(tbl + " start...") df = ts.get_gdp_quarter() df.to_sql(tbl,engine,if_exists='replace') tsl.log(tbl + " done") tbl = "macro_gdp_for" tsl.log(tbl + " start...") df = ts.get_gdp_for() df.to_sql(tbl,engine,if_exists='replace') tsl.log(tbl + " done") tbl = "macro_gdp_pull" tsl.log(tbl + " start...") df = ts.get_gdp_pull() df.to_sql(tbl,engine,if_exists='replace') tsl.log(tbl + " done") tbl = "macro_gdp_contrib" tsl.log(tbl + " start...") df = ts.get_gdp_contrib() df.to_sql(tbl,engine,if_exists='replace') tsl.log(tbl + " done") tbl = "macro_cpi" tsl.log(tbl + " start...") df = ts.get_cpi() df.to_sql(tbl,engine,if_exists='replace') tsl.log(tbl + " done") tbl = "macro_ppi" tsl.log(tbl + " start...") df = ts.get_ppi() df.to_sql(tbl,engine,if_exists='replace') tsl.log(tbl + " done") tbl = "gold_and_foreign_reserves" tsl.log(tbl + " start...") df = ts.get_gold_and_foreign_reserves() df.to_sql(tbl,engine,if_exists='replace') tsl.log(tbl + " done")
def job_5():
try:
print("I'm working......宏观经济数据")
# 存款利率
deposit_rate = ts.get_deposit_rate()
data = pd.DataFrame(deposit_rate)
data.to_sql('deposit_rate',engine,index=True,if_exists='replace')
print("存款利率......done")
# 贷款利率
loan_rate = ts.get_loan_rate()
data = pd.DataFrame(loan_rate)
data.to_sql('loan_rate',engine,index=True,if_exists='replace')
print("贷款利率......done")
# 存款准备金率
rrr = ts.get_rrr()
data = pd.DataFrame(rrr)
data.to_sql('rrr',engine,index=True,if_exists='replace')
print("存款准备金率......done")
# 货币供应量
money_supply = ts.get_money_supply()
data = pd.DataFrame(money_supply)
data.to_sql('money_supply',engine,index=True,if_exists='replace')
print("货币供应量......done")
# 货币供应量(年底余额)
money_supply_bal = ts.get_money_supply_bal()
data = pd.DataFrame(money_supply_bal)
data.to_sql('money_supply_bal',engine,index=True,if_exists='replace')
print("货币供应量(年底余额)......done")
# 国内生产总值(年度)
gdp_year = ts.get_gdp_year()
data = pd.DataFrame(gdp_year)
data.to_sql('gdp_year',engine,index=True,if_exists='replace')
print("国内生产总值(年度)......done")
# 国内生产总值(季度)
gdp_quarter = ts.get_gdp_quarter()
data = pd.DataFrame(gdp_quarter)
data.to_sql('gdp_quarter',engine,index=True,if_exists='replace')
print("国内生产总值(季度)......done")
# 三大需求对GDP贡献
gdp_for = ts.get_gdp_for()
data = pd.DataFrame(gdp_for)
data.to_sql('gdp_for',engine,index=True,if_exists='replace')
print("三大需求对GDP贡献......done")
# 三大产业对GDP拉动
gdp_pull = ts.get_gdp_pull()
data = pd.DataFrame(gdp_pull)
data.to_sql('gdp_pull',engine,index=True,if_exists='replace')
print("三大产业对GDP拉动......done")
# 三大产业贡献率
gdp_contrib = ts.get_gdp_contrib()
data = pd.DataFrame(gdp_contrib)
data.to_sql('gdp_contrib',engine,index=True,if_exists='replace')
print("三大产业贡献率......done")
# 居民消费价格指数
cpi = ts.get_cpi()
data = pd.DataFrame(cpi)
data.to_sql('cpi',engine,index=True,if_exists='replace')
print("居民消费价格指数......done")
# 工业品出厂价格指数
ppi = ts.get_ppi()
data = pd.DataFrame(ppi)
data.to_sql('ppi',engine,index=True,if_exists='replace')
print("工业品出厂价格指数......done")
except Exception as e:
print(e)
def loan():
return ts.get_loan_rate()
# -*- coding:UTF-8 -*-
import tushare as ts
import pymongo
import json
# ts.get_cash_flow : get specialed Code Cash_flow in the History .
# 600547:山东黄金
# 600362:江西铜业
# 600312:平高电气
# 600499：科达洁能
# 603993：洛阳钼业
db = "MacroData"
coll = "LoanRate"
conn = pymongo.MongoClient('127.0.0.1', port=27017)
df = ts.get_loan_rate()
# index data columns(X columns)
dicIndex = json.loads(df.to_json(orient='split'))
for i, ind in enumerate(dicIndex['index']):
# Note: pandas has some transformation between date,timestampe and string.
#d = pd.to_datetime(dicIndex['data'][i][0], unit='ms').strftime('%Y-%m-%d')
d = dicIndex['data'][i][0]
jsonstr = {
'_id': d + "-" + dicIndex['data'][i][1],
dicIndex['columns'][0]: d,
dicIndex['columns'][1]: dicIndex['data'][i][1],
dicIndex['columns'][2]: dicIndex['data'][i][2]
}
try:
conn[db][coll].insert(jsonstr)
except:
def getloanratedb():
loanrate = ts.get_loan_rate()
loanrate.to_sql('loanrate_data',ENGINE,if_exists='append')
def setLoanRate(self, isSave=False, tableName=MACROECONOMIC_LOAN_RATE):
df = ts.get_loan_rate()
if isSave is True:
df.to_sql(tableName, self.engine_sql, if_exists='append')
return df
def Get_loan_rate(self):
dt = ts.get_loan_rate()
dt.to_csv('贷款利率.csv')
print(dt)
def load_macro_economy():
# 下载存款利率
try:
rs = ts.get_deposit_rate()
pd.DataFrame.to_sql(rs, "deposit_rate", con=conn_macro_economy, flavor="mysql", if_exists="replace", index=True)
print("下载存款利率ok")
except:
print("下载存款利率出错")
# 下载贷款利率
try:
rs = ts.get_loan_rate()
pd.DataFrame.to_sql(rs, "loan_rate", con=conn_macro_economy, flavor="mysql", if_exists="replace", index=True)
print("下载贷款利率ok")
except:
print("下载贷款利率出错")
# 下载存款准备金率
try:
rs = ts.get_rrr()
pd.DataFrame.to_sql(rs, "rrr", con=conn_macro_economy, flavor="mysql", if_exists="replace", index=True)
print("下载存款准备金率ok")
except:
print("下载存款准备金率出错")
# 下载货币供应量
try:
rs = ts.get_money_supply()
pd.DataFrame.to_sql(rs, "money_supply", con=conn_macro_economy, flavor="mysql", if_exists="replace", index=True)
print("下载货币供应量ok")
except:
print("下载货币供应量出错")
# 下载货币供应量(年底余额)
try:
rs = ts.get_money_supply_bal()
pd.DataFrame.to_sql(
rs, "money_supply_bal", con=conn_macro_economy, flavor="mysql", if_exists="replace", index=True
)
print("下载货币供应量(年底余额)ok")
except:
print("下载货币供应量(年底余额)出错")
# 下载国内生产总值(年度)
try:
rs = ts.get_gdp_year()
pd.DataFrame.to_sql(rs, "gdp_year", con=conn_macro_economy, flavor="mysql", if_exists="replace", index=True)
print("下载国内生产总值(年度)ok")
except:
print("下载国内生产总值(年度)出错")
# 下载国内生产总值(季度)
try:
rs = ts.get_gdp_quarter()
pd.DataFrame.to_sql(rs, "gdp_quarter", con=conn_macro_economy, flavor="mysql", if_exists="replace", index=True)
print("下载国内生产总值(季度)ok")
except:
print("下载国内生产总值(季度)出错")
# 下载三大需求对GDP贡献
try:
rs = ts.get_gdp_for()
pd.DataFrame.to_sql(rs, "gdp_for", con=conn_macro_economy, flavor="mysql", if_exists="replace", index=True)
print("下载三大需求对GDP贡献ok")
except:
print("下载三大需求对GDP贡献出错")
# 下载三大产业对GDP拉动
try:
rs = ts.get_gdp_pull()
pd.DataFrame.to_sql(rs, "gdp_pull", con=conn_macro_economy, flavor="mysql", if_exists="replace", index=True)
print("下载三大产业对GDP拉动ok")
except:
print("下载三大产业对GDP拉动出错")
# 下载三大产业贡献率
try:
rs = ts.get_gdp_contrib()
pd.DataFrame.to_sql(rs, "gdp_contrib", con=conn_macro_economy, flavor="mysql", if_exists="replace", index=True)
print("下载三大产业贡献率ok")
except:
print("下载三大产业贡献率出错")
# 下载居民消费价格指数
try:
rs = ts.get_cpi()
pd.DataFrame.to_sql(rs, "gdp_cpi", con=conn_macro_economy, flavor="mysql", if_exists="replace", index=True)
print("下载居民消费价格指数ok")
except:
print("下载居民消费价格指数出错")
# 下载工业品出厂价格指数
try:
rs = ts.get_ppi()
pd.DataFrame.to_sql(rs, "gdp_ppi", con=conn_macro_economy, flavor="mysql", if_exists="replace", index=True)
print("下载工业品出厂价格指数ok")
except:
print("下载工业品出厂价格指数出错")