def __insert(self,table_name,df):
if(df.empty):
return
conn = self.get_conn();
cur = conn.cursor();
try:
# print(df.columns)
sql = 'INSERT INTO ' + table_name + '(' + self.__parse_fields__(df.columns) + ') values ';
chip = ''
# for i in np.arange(df.shape[0]):
for i in df.index:
chip = '(' + self.__parse_values__(df.loc[i, :].values) + ')';
if (i != df.index[-1]):
chip = chip + ',';
sql = sql + chip;
logger.debug('insert sql record count:' + str(df.shape[0]));
cur.execute(sql);
conn.commit();
finally:
cur.close();
conn.close();
def _run(self, code, start, end):
ss = requests.session()
code_d = 'sh' + code if code[0] == '6' else 'sz' + code
f_data_list = []
# 得到日期
url = 'http://emweb.securities.eastmoney.com/PC_HSF10/ShareholderResearch/ShareholderResearchAjax?code=' + code_d
ctx = ss.get(url=url, headers=header_dict)
if (ctx.content != None):
f_data_list = json.loads(ctx.content)['zlcc_rz']
logger.info('funds date:' + ",".join(f_data_list))
df = pd.DataFrame()
for idx, f_d in enumerate(f_data_list):
url = 'http://emweb.securities.eastmoney.com/PC_HSF10/ShareholderResearch/MainPositionsHodlerAjax?date=' + str(
f_d) + '&code=' + code_d
logger.debug(url)
ctx = ss.get(url=url, headers=header_dict)
df.loc[idx, 'code'] = code
df.loc[idx, 'fin_year'] = DateUtil.getYear(f_d)
df.loc[idx, 'fin_season'] = DateUtil.getSeason(f_d)
df.loc[idx, 'fin_type'] = 1
if (ctx.content != None):
data_list = json.loads(ctx.content)
for d in data_list:
if (d['jglx'] == u'基金'):
df.loc[idx, 'fund_holding'] = StrUtil.parse_field(
d['zltgbl'])
if (d['jglx'] == u'社保基金'):
df.loc[idx,
'sb_holding'] = StrUtil.parse_field(d['zltgbl'])
return df
def query_any(self, sql, index_col=None):
conn = self.get_conn();
try:
logger.debug('select sql:' + sql)
df = pd.read_sql_query(sql, conn);
if (index_col != None):
df.set_index(index_col, inplace=True)
return df;
finally:
conn.close();
def __get_data(self, dt):
if (dt == None):
dt = datetime.datetime.now().strftime('%Y-%m-%d')
params_dict[
'filter'] = "(MARKET in ('001','003'))(HDDATE=^" + dt + "^)"
logger.debug('dt:' + params_dict['filter'])
# 构造session ，用于存储browser和server交互之间的cookie
self.session = requests.session()
# 任意相关url
init_url = 'http://dcfm.eastmoney.com//em_mutisvcexpandinterface/api/js/get'
ctx = self.session.get(url=init_url,
params=params_dict,
headers=header_dict)
# print 'ctx:'+ctx.content;
# print(ctx.headers)
# print(ctx.cookies)
tp = self.__parse(ctx)
logger.debug('%s|%d' % (ctx.url, tp[0]))
df = pd.DataFrame()
i = 0
for p in range(1, tp[0], 1):
params_dict['p'] = p
ctx = self.session.get(url=init_url,
params=params_dict,
headers=header_dict)
tp = self.__parse(ctx)
lst = tp[1]
#print(lst)
for obj in lst:
df.loc[i, Config.code] = obj['SCODE']
df.loc[i, Config.db_date] = dt
df.loc[
i,
Config.hk_holding_amount] = obj['SHAREHOLDPRICE'] / 10000.0
df.loc[i, Config.hk_holding_ratio] = obj['SHARESRATE']
i = i + 1
if (i == 0):
return df
df.sort_values(by=[Config.hk_holding_ratio],
inplace=True,
ascending=False)
return df
def query(self,table_name,where=None,index_col=None):
conn=self.get_conn();
try:
sql='select * from '+table_name ;
if(where!=None):
sql=sql+' where '+where;
logger.debug('select sql:'+sql)
df = pd.read_sql_query(sql, conn);
if(index_col!=None):
df.set_index(index_col,inplace=True)
return df;
finally:
conn.close();
def update(self, table_name, df, primaryKeys):
if (df.empty):
logger.info('no date to update')
return
if (primaryKeys == '' or primaryKeys == None):
raise Exception('primaryKeys is None');
conn = self.get_conn();
cur = conn.cursor();
try:
sql_prefix = 'update ' + table_name + ' set ';
sql='';
#for i in np.arange(df.shape[0]):
for i in df.index:
chip = '';
for vi, val in enumerate(df.columns):
if(val not in primaryKeys):
chip =chip+ self.__parse_field__(val) + '=' + self.__parse_value__(df.loc[i, val]);
if (vi < len(df.columns) - 1):
chip = chip + ','
if(chip==''):
raise Exception('updated columns is None');
chip += ' where ';
for vi, val in enumerate(primaryKeys):
chip += self.__parse_field__(val) + '=' + self.__parse_value__(df.loc[i, val]);
if (vi < len(primaryKeys) - 1):
chip = chip + ' and ';
sql = sql_prefix + chip;
cur.execute(sql);
logger.debug('update sql count:'+str(df.shape[0]));
conn.commit();
except BaseException as be:
conn.rollback();
logger.exception('updat db error')
raise be;
finally:
cur.close();
conn.close();