def read(self, props, is_filter=True):
view = props.get('view')
if '.' in view:
return self.read_table(props, is_filter=is_filter)
else:
clnames = self.conn[view].collection_names()
columns = list(self.conn[view][clnames[0]].find_one({}).keys())
columns.remove('_id')
[columns.remove(i) for i in columns if 'date' in i]
collection_type = self.identify(clnames)
columns_type = self.identify(columns)
data = self.read_db(props, collection_type, columns_type)
if collection_type == 'fields' and columns_type == 'symbol':
data = data.set_index(['fields', 'trade_date']).stack(0).unstack(0).reset_index()
data['symbol'] = [trans_symbol(i) for i in data['symbol']]
if data['trade_date'].dtype.name == 'int':
pass
elif data['trade_date'].dtype.name == 'str':
data['trade_date'] = data['trade_date'].astype('int')
elif data['trade_date'].dtype.name == 'object':
data['trade_date'] = data['trade_date'].astype('int')
else:
data['trade_date'] = data['trade_date'].apply(lambda x: int(datetime.strftime(x, '%Y%m%d')))
return data
def identify(string_list):
string_list = trans_symbol(string_list)
# 数字
is_num = [i.isdigit() for i in string_list]
i_is_num = [i[0].isdigit() for i in string_list]
# 字母
is_string = [i.isalpha() for i in string_list if '_' not in i]
i_is_string = [i[0].isalpha() for i in string_list if '_' not in i]
# 数字+字母
is_num_n_string = [(not (i.isalpha() or i.isdigit())) for i in string_list]
if (len(is_num) - is_num.count(True)) <= 2:
res = 'symbol'
elif (len(is_string) - is_string.count(True)) <= 2:
res = 'fields'
elif (len(is_num_n_string) - is_num_n_string.count(True)) <= 2:
res = 'symbol'
elif (len(i_is_string) - i_is_string.count(True)) <= 2:
res = 'fields'
else:
raise ValueError('cant identify')
return res
def spc_treatment(view, df):
'''
需要特殊处理的表
'''
if view == 'dbo.ASHAREEODPRICES':
df = df[~df['OBJECT_ID'].isin(['{56FBE7CC-D183-11E6-A487-6C0B84A6895D}', '{56FBE85B-D183-11E6-A487-6C0B84A6895D}'])]
if 'S_DQ_TRADESTATUS' in df.columns.values:
df['S_DQ_TRADESTATUS'] = df['S_DQ_TRADESTATUS'].apply(lambda x: x.encode('latin1').decode('gbk') if isinstance(x, str) else x)
df['S_DQ_TRADESTATUS'][df['S_DQ_TRADESTATUS'] == '交易'] = '1'
df['S_DQ_TRADESTATUS'][df['S_DQ_TRADESTATUS'] == '停牌'] = '0'
df['S_DQ_TRADESTATUS'] = df['S_DQ_TRADESTATUS'].replace('XR', '1').replace('XD', '1').replace('DR', '1')
if view == 'dbo.AINDEXEODPRICES':
if 'S_DQ_TRADESTATUS' in df.columns:
df = df.drop('S_DQ_TRADESTATUS', axis=1)
df['S_DQ_TRADESTATUS'] = '1'
if 'ZYYX' in view:
df['STOCK_CODE'] = utils.trans_symbol(list(df['STOCK_CODE'].values))
'''
df['CON_DATE'] = df['CON_DATE'].apply(
lambda x: datetime.strftime(x.to_datetime(), '%Y%m%d') if x != pd.NaT else 'NaT')
def func(x):
if len(x) == 10:
return x[:4] + x[5:7] + x[-2:]
else:
return x
for field in ['CON_OR_HISDATE', 'CON_NP_HISDATE', 'CON_EPS_HISDATE', 'ENTRYTIME', 'UPDATETIME']:
if field in df.columns:
df[field] = df[field].astype(str)
df[field] = df[field].apply(lambda x: func(x))
'''
return view, df
from datetime import datetime
from time import time
import os
import pandas as pd
from datasync.data_origin.sql_origin import OracleOrigin
conn = sqlite3.connect(r'D:\sqlite_data\ZYYXData.sqlite')
conn2 = sqlite3.connect(r'D:\sqlite_data\ZYYXData2.sqlite')
db_config = {
'addr': "172.16.55.54:1521/ORCL",
'user': "******",
'password': "******"
}
origin = OracleOrigin(db_config)
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'
start = time()
sql = 'select * from ZYYX2.CON_FORECAST_STK'
data = origin.read(sql=sql)
data['STOCK_CODE'] = trans_symbol(list(data['STOCK_CODE'].values))
data['CON_DATE'] = data['CON_DATE'].apply(lambda x: datetime.strftime(
x.to_datetime(), '%Y%m%d') if x != pd.NaT else None)
print(time() - start)
for field in [
'CON_OR_HISDATE', 'CON_NP_HISDATE', 'CON_EPS_HISDATE', 'ENTRYTIME',
'UPDATETIME'
]:
print(field)