def import_stock_daily():
w = WindRest(WIND_REST_URL)
engine = get_db_engine()
with get_db_session(engine) as session:
# 获取每只股票最新交易日数据
sql_str = 'select wind_code, max(Trade_date) from wind_stock_daily group by wind_code'
table = session.execute(sql_str)
stock_trade_date_latest_dic = dict(table.fetchall())
# 获取市场有效交易日数据
sql_str = "select trade_date from wind_trade_date where trade_date > '2005-1-1'"
table = session.execute(sql_str)
trade_date_sorted_list = [t[0] for t in table.fetchall()]
trade_date_sorted_list.sort()
# 获取每只股票上市日期、退市日期
table = session.execute('SELECT wind_code, ipo_date, delist_date FROM wind_stock_info')
stock_date_dic = {wind_code: (ipo_date, delist_date if delist_date is None or delist_date > UN_AVAILABLE_DATE else None) for
wind_code, ipo_date, delist_date in table.fetchall()}
today_t_1 = date.today() - ONE_DAY
data_df_list = []
try:
for wind_code, date_pair in stock_date_dic.items():
date_ipo, date_delist = date_pair
# 获取 date_from
if wind_code in stock_trade_date_latest_dic:
date_latest_t1 = stock_trade_date_latest_dic[wind_code] + ONE_DAY
date_from = max([date_latest_t1, DATE_BASE, date_ipo])
else:
date_from = max([DATE_BASE, date_ipo])
date_from = get_first(trade_date_sorted_list, lambda x: x >= date_from)
# 获取 date_to
if date_delist is None:
date_to = today_t_1
else:
date_to = min([date_delist, today_t_1])
date_to = get_last(trade_date_sorted_list, lambda x: x <= date_to)
if date_from is None or date_to is None or date_from > date_to:
continue
# 获取股票量价等行情数据
wind_indictor_str = "open,high,low,close,adjfactor,volume,amt,pct_chg,maxupordown," + \
"swing,turn,free_turn,trade_status,susp_days"
data_df = w.wsd(wind_code, wind_indictor_str, date_from, date_to)
if data_df is None:
logging.warning('%s has no data during %s %s', wind_code, date_from, date_to)
continue
logging.info('%d data of %s', data_df.shape[0], wind_code)
data_df['wind_code'] = wind_code
data_df_list.append(data_df)
finally:
# 导入数据库
if len(data_df_list) > 0:
data_df_all = pd.concat(data_df_list)
data_df_all.index.rename('trade_date', inplace=True)
data_df_all.reset_index(inplace=True)
data_df_all.set_index(['wind_code', 'trade_date'], inplace=True)
data_df_all.to_sql('wind_stock_daily', engine, if_exists='append',
dtype={
'wind_code': String(20),
'trade_date': Date,
'open': Float,
'high': Float,
'low': Float,
'close': Float,
'adjfactor': Float,
'volume': Float,
'amt': Float,
'pct_chg': Float,
'maxupordown': Integer,
'swing': Float,
'turn': Float,
'free_turn': Float,
'trade_status': String(20),
'susp_days': Integer,
}
)
logging.info('%d data imported', data_df_all.shape[0])
def import_wind_stock_info(chain_param=None, refresh=False):
"""
:param chain_param: 在celery 中將前面結果做爲參數傳給後面的任務
:param refresh:获取全市场股票代码及名称
:return:
"""
table_name = 'wind_stock_info'
logging.info("更新 %s 开始", table_name)
has_table = engine_md.has_table(table_name)
wind_indicator_param_list = [
('sec_name', String(20)),
('trade_code', String(20)),
('ipo_date', Date),
('delist_date', Date),
('mkt', String(20)),
('exch_city', String(20)),
('exch_eng', String(20)),
('prename', String(2000)),
]
# 获取列属性名，以逗号进行分割 "ipo_date,trade_code,mkt,exch_city,exch_eng"
param = ",".join([key for key, _ in wind_indicator_param_list])
# 设置 dtype
dtype = {key: val for key, val in wind_indicator_param_list}
dtype['wind_code'] = String(20)
if refresh:
date_fetch = datetime.strptime('2005-1-1', STR_FORMAT_DATE).date()
else:
date_fetch = date.today()
date_end = date.today()
stock_code_set = set()
# 对date_fetch 进行一个判断，获取stock_code_set
while date_fetch < date_end:
stock_code_set_sub = get_stock_code_set(date_fetch)
if stock_code_set_sub is not None:
stock_code_set |= stock_code_set_sub
date_fetch += timedelta(days=365)
stock_code_set_sub = get_stock_code_set(date_end)
if stock_code_set_sub is not None:
stock_code_set |= stock_code_set_sub
# 获取股票对应上市日期，及摘牌日期
# w.wss("300005.SZ,300372.SZ,000003.SZ", "ipo_date,trade_code,mkt,exch_city,exch_eng")
stock_code_list = list(stock_code_set)
seg_count = 1000
stock_info_df_list = []
# 进行循环遍历获取stock_code_list_sub
for stock_code_list_sub in split_chunk(stock_code_list, seg_count):
# 尝试将 stock_code_list_sub 直接传递给wss，是否可行
stock_info_df = invoker.wss(stock_code_list_sub, param)
stock_info_df_list.append(stock_info_df)
if DEBUG:
break
# 对数据表进行规范整理.整合,索引重命名
stock_info_all_df = pd.concat(stock_info_df_list)
stock_info_all_df.index.rename('wind_code', inplace=True)
logging.info('%d data will be import', stock_info_all_df.shape[0])
stock_info_all_df.reset_index(inplace=True)
# data_list = list(stock_info_all_df.T.to_dict().values())
# 对wind_stock_info表进行数据插入
# sql_str = "REPLACE INTO {table_name} (wind_code, trade_code, sec_name, ipo_date, delist_date, mkt, exch_city, exch_eng, prename) values (:WIND_CODE, :TRADE_CODE, :SEC_NAME, :IPO_DATE, :DELIST_DATE, :MKT, :EXCH_CITY, :EXCH_ENG, :PRENAME)"
# 事物提交执行更新
# with with_db_session(engine_md) as session:
# session.execute(sql_str, data_list)
# data_count = session.execute('select count(*) from {table_name}').scalar()
data_count = bunch_insert_on_duplicate_update(stock_info_all_df, table_name, engine_md, dtype=dtype)
logging.info("更新 %s 完成 存量数据 %d 条", table_name, data_count)
if not has_table and engine_md.has_table(table_name):
alter_table_2_myisam(engine_md, [table_name])
build_primary_key([table_name])
# 更新 code_mapping 表
update_from_info_table(table_name)
class OnlineHistory(Base):
__tablename__ = 'online_history'
user_id = Column(Integer(), primary_key=True)
time = Column(Date(), primary_key=True)
new_status = Column(String())
class Atktype4(BaseModel):
__tablename__ = 'atktype4'
id = Column(Integer, primary_key=True)
date = Column(String(10), unique=True, nullable=False)
count = Column(Integer, default=0)
ftime = Column(DateTime)
ltime = Column(DateTime)
def __init__(self, date, count, ftime, ltime):
self.count = count
self.date = date
if (ftime != None):
self.ftime = ftime
else:
self.ftime = datetime.now()
if (ltime != None):
self.ltime = ltime
else:
self.ltime = datetime.now()
def __repr__(self):
return '<atktype4: %r %r>' % (self.id, self.date)
@staticmethod
def check(date):
'''
:param name:
:return: {1:'success', 2:'name repeat', 3:'name is empty'}
'''
if (date == ''):
return 3
Session = scoped_session(sessionmaker(autoflush=True, bind=engine))
query = Session.query(Atktype4)
tmp = query.filter(Atktype4.date == date).first()
Session.close()
if (tmp != None):
return 2
return 1
@staticmethod
def add(date, count=0):
'''
:param name:
:return: {1:'success', 2:'name repeat'}
'''
ret = 1
ret = Atktype4.check(date)
if (ret != 1):
return ret
ftime = datetime.now()
ltime = datetime.now()
cur = Atktype4(date, count, ftime, ltime)
Session = scoped_session(sessionmaker(autoflush=True, bind=engine))
Session.add(cur)
Session.commit()
Session.close()
return ret
@staticmethod
def updateatktype4(Date_DICT={}):
'''
:param Date_DICT:
:return: {1:'success', 2:'name is empty', 3:'failed'}
'''
try:
Session = scoped_session(sessionmaker(autoflush=True, bind=engine))
if (Date_DICT == None):
return 3
for date in Date_DICT.keys():
#print("add name: %s" %name)
ret = 1
ret = Atktype4.check(date)
if (ret == 1):
Atktype4.add(date, Date_DICT[date])
continue
elif (ret == 2):
curCount = Session.query(
Atktype4.count).filter(Atktype4.date == date).first()
if (curCount == None):
continue
else:
count = int(curCount[0]) + Date_DICT[date]
Session.query(Atktype4).filter(
Atktype4.date == date).update({
'count':
count,
'ltime':
datetime.now()
})
Session.commit()
else:
continue
except:
Session.rollback()
#raise
finally:
Session.close()
return 1
def import_stock_daily(chain_param=None, wind_code_set=None):
"""
插入股票日线数据到最近一个工作日-1。
如果超过 BASE_LINE_HOUR 时间，则获取当日的数据
:param chain_param: 在celery 中將前面結果做爲參數傳給後面的任務
:return:
"""
table_name = 'wind_stock_daily'
logging.info("更新 %s 开始", table_name)
param_list = [
('open', DOUBLE),
('high', DOUBLE),
('low', DOUBLE),
('close', DOUBLE),
('adjfactor', DOUBLE),
('volume', DOUBLE),
('amt', DOUBLE),
('pct_chg', DOUBLE),
('maxupordown', Integer),
('swing', DOUBLE),
('turn', DOUBLE),
('free_turn', DOUBLE),
('trade_status', String(30)),
('susp_days', Integer),
('total_shares', DOUBLE),
('free_float_shares', DOUBLE),
('ev2_to_ebitda', DOUBLE),
]
wind_indictor_str = ",".join([key for key, _ in param_list])
rename_col_dic = {key.upper(): key.lower() for key, _ in param_list}
has_table = engine_md.has_table(table_name)
# 进行表格判断，确定是否含有wind_stock_daily
if has_table:
sql_str = """
SELECT wind_code, date_frm, if(delist_date<end_date, delist_date, end_date) date_to
FROM
(
SELECT info.wind_code, ifnull(trade_date, ipo_date) date_frm, delist_date,
if(hour(now())<16, subdate(curdate(),1), curdate()) end_date
FROM
wind_stock_info info
LEFT OUTER JOIN
(SELECT wind_code, adddate(max(trade_date),1) trade_date FROM {table_name} GROUP BY wind_code) daily
ON info.wind_code = daily.wind_code
) tt
WHERE date_frm <= if(delist_date<end_date, delist_date, end_date)
ORDER BY wind_code""".format(table_name=table_name)
else:
sql_str = """
SELECT wind_code, date_frm, if(delist_date<end_date, delist_date, end_date) date_to
FROM
(
SELECT info.wind_code, ipo_date date_frm, delist_date,
if(hour(now())<16, subdate(curdate(),1), curdate()) end_date
FROM wind_stock_info info
) tt
WHERE date_frm <= if(delist_date<end_date, delist_date, end_date)
ORDER BY wind_code"""
logger.warning('%s 不存在，仅使用 wind_stock_info 表进行计算日期范围', table_name)
with with_db_session(engine_md) as session:
# 获取每只股票需要获取日线数据的日期区间
table = session.execute(sql_str)
# 计算每只股票需要获取日线数据的日期区间
begin_time = None
# 获取date_from,date_to，将date_from,date_to做为value值
code_date_range_dic = {
wind_code: (date_from if begin_time is None else min([date_from, begin_time]), date_to)
for wind_code, date_from, date_to in table.fetchall() if
wind_code_set is None or wind_code in wind_code_set}
# 设置 dtype
dtype = {key: val for key, val in param_list}
dtype['wind_code'] = String(20)
dtype['trade_date'] = Date
data_df_list = []
data_len = len(code_date_range_dic)
logger.info('%d stocks will been import into wind_stock_daily', data_len)
# 将data_df数据，添加到data_df_list
try:
for num, (wind_code, (date_from, date_to)) in enumerate(code_date_range_dic.items(), start=1):
logger.debug('%d/%d) %s [%s - %s]', num, data_len, wind_code, date_from, date_to)
try:
data_df = invoker.wsd(wind_code, wind_indictor_str, date_from, date_to)
except APIError as exp:
logger.exception("%d/%d) %s 执行异常", num, data_len, wind_code)
if exp.ret_dic.setdefault('error_code', 0) in (
-40520007, # 没有可用数据
-40521009, # 数据解码失败。检查输入参数是否正确，如：日期参数注意大小月月末及短二月
):
continue
else:
break
if data_df is None:
logger.warning('%d/%d) %s has no data during %s %s', num, data_len, wind_code, date_from, date_to)
continue
logger.info('%d/%d) %d data of %s between %s and %s', num, data_len, data_df.shape[0], wind_code, date_from,
date_to)
data_df['wind_code'] = wind_code
data_df_list.append(data_df)
# 仅调试使用
if DEBUG and len(data_df_list) > 2:
break
finally:
# 导入数据库
if len(data_df_list) > 0:
data_df_all = pd.concat(data_df_list)
data_df_all.index.rename('trade_date', inplace=True)
data_df_all.reset_index(inplace=True)
data_df_all.rename(columns=rename_col_dic, inplace=True)
# data_df_all.set_index(['wind_code', 'trade_date'], inplace=True)
# data_df_all.to_sql('wind_stock_daily', engine_md, if_exists='append', dtype=dtype)
# logging.info("更新 wind_stock_daily 结束 %d 条信息被更新", data_df_all.shape[0])
data_count = bunch_insert_on_duplicate_update(data_df_all, table_name, engine_md, dtype)
logging.info("更新 %s 结束 %d 条信息被更新", table_name, data_count)
if not has_table and engine_md.has_table(table_name):
alter_table_2_myisam(engine_md, [table_name])
build_primary_key([table_name])
def import_edb_monthly():
table_name = 'wind_edb_monthly'
has_table = engine_md.has_table(table_name)
PMI_FIELD_CODE_2_CN_DIC = {
"M0017126": ("PMI", date(2005, 1, 1)),
"M0017127": ("PMI:生产", date(2005, 1, 1)),
"M0017128": ("PMI:新订单", date(2005, 1, 1)),
"M0017129": ("PMI:新出口订单", date(2005, 1, 1)),
"M0017130": ("PMI:在手订单", date(2005, 1, 1)),
"M0017131": ("PMI:产成品库存", date(2005, 1, 1)),
"M0017132": ("PMI:采购量", date(2005, 1, 1)),
"M0017133": ("PMI:进口", date(2005, 1, 1)),
"M5766711": ("PMI:出厂价格", date(2005, 1, 1)),
"M0017134": ("PMI:主要原材料购进价格", date(2005, 1, 1)),
"M0017135": ("PMI:原材料库存", date(2005, 1, 1)),
"M0017136": ("PMI:从业人员", date(2005, 1, 1)),
"M0017137": ("PMI:供货商配送时间", date(2005, 1, 1)),
"M5207790": ("PMI:生产经营活动预期", date(2005, 1, 1)),
"M5206738": ("PMI:大型企业", date(2005, 1, 1)),
"M5206739": ("PMI:中型企业", date(2005, 1, 1)),
"M5206740": ("PMI:小型企业", date(2005, 1, 1)),
"M5407921": ("克强指数:当月值", date(2009, 7, 1)),
"M0000612": ("CPI:当月同比", date(1990, 1, 1)),
"M0000616": ("CPI:食品:当月同比", date(1990, 1, 1)),
"M0000613": ("CPI:非食品:当月同比", date(1990, 1, 1)),
"M0000614": ("CPI:消费品:当月同比", date(1990, 1, 1)),
"M0000615": ("CPI:服务:当月同比", date(1990, 1, 1)),
"M0000705": ("CPI:环比", date(1990, 1, 1)),
"M0000706": ("CPI:食品:环比", date(1990, 1, 1)),
"M0061581": ("CPI:非食品:环比", date(1990, 1, 1)),
"M0061583": ("CPI:消费品:环比", date(1990, 1, 1)),
"M0001227": ("PPI:全部工业品:当月同比", date(1996, 10, 1)),
"M0061585": ("PPI:全部工业品:环比", date(2002, 1, 1)),
"M0001228": ("PPI:生产资料:当月同比", date(1996, 10, 1)),
"M0066329": ("PPI:生产资料:环比", date(2011, 1, 1)),
"M0001232": ("PPI:生活资料:当月同比", date(1996, 10, 1)),
"M0066333": ("PPI:生活资料:环比", date(2011, 1, 1)),
}
# 设置表属性类型
param_list = [
('field_name', String(45)),
('trade_date', Date),
('val', DOUBLE),
]
dtype = {key: val for key, val in param_list}
dtype['field_code'] = String(20)
data_len = len(PMI_FIELD_CODE_2_CN_DIC)
if has_table:
sql_str = """select field_code, max(trade_date) trade_date_max from wind_edb_monthly group by field_code"""
# 获取数据库中最大日期
with with_db_session(engine_md) as session:
table = session.execute(sql_str)
field_date_dic = {row[0]: row[1] for row in table.fetchall()}
else:
sql_str = f"""
CREATE TABLE {table_name} (
`field_code` varchar(20) NOT NULL,
`field_name` varchar(45) DEFAULT NULL,
`trade_date` date NOT NULL,
`val` double DEFAULT NULL,
PRIMARY KEY (`field_code`,`trade_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='保存wind edb 宏观经济数据';"""
# 循环更新
for data_num, (wind_code,
(field_name,
date_from)) in enumerate(PMI_FIELD_CODE_2_CN_DIC.items(),
start=1):
if wind_code in field_date_dic:
date_from = field_date_dic[wind_code] + timedelta(days=1)
date_to = date.today() - timedelta(days=1)
logger.info('%d/%d) %s %s [%s %s]', data_num, data_len, wind_code,
field_name, date_from, date_to)
try:
data_df = invoker.edb(wind_code, date_from, date_to,
"Fill=Previous")
except APIError as exp:
logger.exception("%d/%d) %s 执行异常", data_num, data_len, wind_code)
if exp.ret_dic.setdefault('error_code', 0) in (
-40520007, # 没有可用数据
-40521009, # 数据解码失败。检查输入参数是否正确，如：日期参数注意大小月月末及短二月
):
continue
else:
break
if data_df is None or data_df.shape[0] == 0:
continue
trade_date_max = str_2_date(max(data_df.index))
if trade_date_max <= date_from:
continue
data_df.index.rename('trade_date', inplace=True)
data_df.reset_index(inplace=True)
data_df.rename(columns={wind_code.upper(): 'val'}, inplace=True)
data_df['field_code'] = wind_code
data_df['field_name'] = field_name
# data_df.to_sql('wind_edb_monthly', engine_md, if_exists='append', index=False)
bunch_insert_on_duplicate_update(data_df,
table_name,
engine_md,
dtype=dtype)
if not has_table and engine_md.has_table(table_name):
alter_table_2_myisam(engine_md, [table_name])
build_primary_key([table_name])
class Page(BaseModel):
__tablename__ = 'page'
id = Column(Integer, primary_key=True)
name = Column(String(128))
content = Column(Text)
class User(BaseObject):
'''
User class used for authentication/autorization
'''
_username = Column(Unicode(64), unique=True, nullable=False)
username = synonym(
'_username',
descriptor=property(
lambda self: self._username, lambda self, username: setattr(
self, '_username', self.__class__._filter_string(username))))
_locked = Column(Boolean, default=True)
jobs = relationship("Job",
backref=backref("User", lazy="joined"),
cascade="all, delete-orphan")
permissions = relationship("Permission",
backref=backref("User", lazy="joined"),
cascade="all, delete-orphan")
salt = Column(String(16),
unique=True,
nullable=False,
default=lambda: urandom(8).encode('hex'))
_password = Column('password', Unicode(64))
password = synonym(
'_password',
descriptor=property(
lambda self: self._password, lambda self, password: setattr(
self, '_password',
self.__class__._hash_password(password, self.salt))))
@classmethod
def by_id(cls, user_id):
''' Return the user object whose user id is user_id '''
return dbsession.query(cls).filter_by(id=user_id).first()
@classmethod
def by_uuid(cls, user_uuid):
''' Return the user object whose user uuid is user_uuid '''
return dbsession.query(cls).filter_by(uuid=unicode(user_uuid)).first()
@classmethod
def get_unapproved(cls):
''' Return all unapproved user objects '''
return dbsession.query(cls).filter_by(approved=False).all()
@classmethod
def get_approved(cls):
''' Return all approved user objects '''
return dbsession.query(cls).filter_by(approved=True).all()
@classmethod
def all(cls):
''' Return all non-admin user objects '''
return dbsession.query(cls).all()
@classmethod
def all_users(cls):
''' Return all non-admin user objects '''
return filter(lambda user: not user.has_permission(ADMIN_PERMISSION),
cls.all())
@classmethod
def by_username(cls, user_name):
''' Return the user object whose user name is 'user_name' '''
return dbsession.query(cls).filter_by(
username=unicode(user_name)).first()
@classmethod
def _filter_string(cls, string, extra_chars=""):
''' Remove any non-white listed chars from a string '''
char_white_list = ascii_letters + digits + extra_chars
return filter(lambda char: char in char_white_list, string)
@classmethod
def _hash_password(cls, password, salt):
''' PBKDF2; return unicode string '''
hashed = PBKDF2(password, salt,
iterations=ITERATE).read(32).encode('hex')
return unicode(hashed)
@property
def queued_jobs(self):
''' Jobs owned by the user that have not been completed '''
return filter(lambda job: (job.status != u"COMPLETED"), self.jobs)
@property
def completed_jobs(self):
''' Jobs owned by the user that have been completed '''
return filter(lambda job: (job.status == u"COMPLETED"), self.jobs)
@property
def permissions(self):
''' Return a list with all permissions granted to the user '''
return dbsession.query(Permission).filter_by(user_id=self.id)
@property
def permissions_names(self):
''' Return a list with all permissions names granted to the user '''
return [permission.permission_name for permission in self.permissions]
@property
def locked(self):
'''
Determines if an admin has locked an account, accounts with
administrative permissions cannot be locked.
'''
if self.has_permission(ADMIN_PERMISSION):
return False # Admin accounts cannot be locked
else:
return self._locked
@locked.setter
def locked(self, value):
''' Setter method for _lock '''
assert isinstance(value, bool)
if not self.has_permission(ADMIN_PERMISSION):
self._locked = value
def has_permission(self, permission):
''' Return True if 'permission' is in permissions_names '''
return True if permission in self.permissions_names else False
def validate_password(self, attempt):
''' Check the password against existing credentials '''
return self.password == self._hash_password(attempt, self.salt)
def __repr__(self):
return '<User - user_name: %s, jobs: %d>' % (self.user_name,
len(self.jobs))
def __str__(self):
return self.username
class IpAddress(BaseObject):
''' IP Address definition '''
box_id = Column(Integer, ForeignKey('box.id'), nullable=False)
uuid = Column(String(36),
unique=True,
nullable=False,
default=lambda: str(uuid4()))
_v4 = Column(Unicode(16), unique=True)
v4 = synonym('_v4',
descriptor=property(
lambda self: self._v4, lambda self, v4: setattr(
self, '_v4', self.__class__.format_v4(v4))))
_v6 = Column(Unicode(40), unique=True)
v6 = synonym('_v6',
descriptor=property(
lambda self: self._v6, lambda self, v6: setattr(
self, '_v6', self.__class__.format_v6(v6))))
@classmethod
def all(cls):
''' Returns a list of all objects in the database '''
return dbsession.query(cls).all()
@classmethod
def by_id(cls, identifier):
''' Returns a the object with id of identifier '''
return dbsession.query(cls).filter_by(id=identifier).first()
@classmethod
def by_uuid(cls, uuid):
''' Return and object based on a uuid '''
return dbsession.query(cls).filter_by(uuid=unicode(uuid)).first()
@classmethod
def by_address(cls, addr):
''' Return and object based on an address '''
ip = dbsession.query(cls).filter_by(v4=unicode(addr)).first()
if ip is None:
ip = dbsession.query(cls).filter_by(v6=unicode(addr)).first()
return ip
@classmethod
def format_v4(cls, ip_address):
''' Checks the format of the string to confirm its a valid ipv4 address '''
regex = re.compile(
r"\b(?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\b"
)
if not regex.match(ip_address):
raise ValueError("Invalid Ipv4 Address: '%s'" % str(ip_address))
else:
return ip_address
@classmethod
def format_v6(cls, ip_address):
'''
Checks the format of the string to confirm its a valid ipv6 address
May the PEP8 gods forgive what I am about to do...
'''
regex = re.compile(
r"/^\s*((([0-9A-Fa-f]{1,4}:){7}([0-9A-Fa-f]{1,4}|:))|(([0-9A-Fa-f]{1,4}:){6}(:[0-9A-Fa-f]{1,4}|((25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)(\.(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)){3})|:))|(([0-9A-Fa-f]{1,4}:){5}(((:[0-9A-Fa-f]{1,4}){1,2})|:((25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)(\.(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)){3})|:))|(([0-9A-Fa-f]{1,4}:){4}(((:[0-9A-Fa-f]{1,4}){1,3})|((:[0-9A-Fa-f]{1,4})?:((25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)(\.(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)){3}))|:))|(([0-9A-Fa-f]{1,4}:){3}(((:[0-9A-Fa-f]{1,4}){1,4})|((:[0-9A-Fa-f]{1,4}){0,2}:((25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)(\.(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)){3}))|:))|(([0-9A-Fa-f]{1,4}:){2}(((:[0-9A-Fa-f]{1,4}){1,5})|((:[0-9A-Fa-f]{1,4}){0,3}:((25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)(\.(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)){3}))|:))|(([0-9A-Fa-f]{1,4}:){1}(((:[0-9A-Fa-f]{1,4}){1,6})|((:[0-9A-Fa-f]{1,4}){0,4}:((25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)(\.(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)){3}))|:))|(:(((:[0-9A-Fa-f]{1,4}){1,7})|((:[0-9A-Fa-f]{1,4}){0,5}:((25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)(\.(25[0-5]|2[0-4]\d|1\d\d|[1-9]?\d)){3}))|:)))(%.+)?\s*$/"
)
if not regex.match(ip_address):
raise ValueError("Invalid Ipv6 Address: '%s'" % str(ip_address))
else:
return ip_address
def is_v4(self):
return bool(self.v4 is not None)
def is_v6(self):
return bool(self.v6 is not None)
def __repr__(self):
return "<IpAddress - v4: %s, v6: %s>" % (str(self.v4), str(self.v6))
def __str__(self):
if self.v6 is not None:
return self.v6
else:
return self.v4
def __eq__(self, other):
if self.v6 is not None:
return self.v6 == other.v6
else:
return self.v4 == other.v4
from tasks.backend import engine_md
from tasks.merge.code_mapping import update_from_info_table
from tasks.utils.db_utils import with_db_session, add_col_2_table, alter_table_2_myisam, \
bunch_insert_on_duplicate_update
from tasks.tushare.ts_pro_api import pro
DEBUG = False
logger = logging.getLogger()
DATE_BASE = datetime.strptime('2005-01-01', STR_FORMAT_DATE).date()
ONE_DAY = timedelta(days=1)
# 标示每天几点以后下载当日行情数据
BASE_LINE_HOUR = 16
STR_FORMAT_DATE_TS = '%Y%m%d'
INDICATOR_PARAM_LIST_TUSHARE_STOCK_INDEX_BASIC = [
('ts_code', String(30)),
('name', String(100)),
('fullname', String(200)),
('market', String(100)),
('publisher', String(100)),
('index_type', String(100)),
('category', String(50)),
('base_date', Date),
('base_point', DOUBLE),
('list_date', Date),
('weight_rule', String(200)),
('desc', Text),
('exp_date', Date),
]
# 设置 dtype
DTYPE_TUSHARE_STOCK_INDEX_BASIC = {
def update_df_2_db(instrument_type, table_name, data_df):
"""将 DataFrame 数据保存到 数据库对应的表中"""
dtype = {
'trade_date': Date,
'Contract': String(20),
'ContractNext': String(20),
'Close': DOUBLE,
'CloseNext': DOUBLE,
'TermStructure': DOUBLE,
'Volume': DOUBLE,
'VolumeNext': DOUBLE,
'OI': DOUBLE,
'OINext': DOUBLE,
'Open': DOUBLE,
'OpenNext': DOUBLE,
'High': DOUBLE,
'HighNext': DOUBLE,
'Low': DOUBLE,
'LowNext': DOUBLE,
'WarehouseWarrant': DOUBLE,
'WarehouseWarrantNext': DOUBLE,
'adj_factor_main': DOUBLE,
'adj_factor_secondary': DOUBLE,
'instrument_type': String(20),
}
# 为了解决 AttributeError: 'numpy.float64' object has no attribute 'translate' 错误，需要将数据类型转换成 float
data_df["Close"] = data_df["Close"].apply(str_2_float)
data_df["CloseNext"] = data_df["CloseNext"].apply(str_2_float)
data_df["TermStructure"] = data_df["TermStructure"].apply(str_2_float)
data_df["Volume"] = data_df["Volume"].apply(str_2_float)
data_df["VolumeNext"] = data_df["VolumeNext"].apply(str_2_float)
data_df["OI"] = data_df["OI"].apply(str_2_float)
data_df["OINext"] = data_df["OINext"].apply(str_2_float)
data_df["Open"] = data_df["Open"].apply(str_2_float)
data_df["OpenNext"] = data_df["OpenNext"].apply(str_2_float)
data_df["High"] = data_df["High"].apply(str_2_float)
data_df["HighNext"] = data_df["HighNext"].apply(str_2_float)
data_df["Low"] = data_df["Low"].apply(str_2_float)
data_df["LowNext"] = data_df["LowNext"].apply(str_2_float)
data_df["WarehouseWarrant"] = data_df["WarehouseWarrant"].apply(
str_2_float)
data_df["WarehouseWarrantNext"] = data_df["WarehouseWarrantNext"].apply(
str_2_float)
data_df["adj_factor_main"] = data_df["adj_factor_main"].apply(str_2_float)
data_df["adj_factor_secondary"] = data_df["adj_factor_secondary"].apply(
str_2_float)
# 清理历史记录
with with_db_session(engine_md) as session:
sql_str = """SELECT table_name FROM information_schema.TABLES
WHERE table_name = :table_name and TABLE_SCHEMA=(select database())"""
# 复权数据表
is_existed = session.execute(sql_str,
params={
"table_name": table_name
}).fetchone()
if is_existed is not None:
session.execute(
"delete from %s where instrument_type = :instrument_type" %
table_name,
params={"instrument_type": instrument_type})
logger.debug("删除 %s 中的 %s 历史数据", table_name, instrument_type)
# 插入数据库
# pd.DataFrame.to_sql(data_df, table_name, engine_md, if_exists='append', index=False, dtype=dtype)
bunch_insert_on_duplicate_update(data_df,
table_name,
engine_md,
dtype=dtype,
myisam_if_create_table=True,
primary_keys=['trade_date', 'Contract'],
schema=config.DB_SCHEMA_MD)
class DbComputer(Base):
__tablename__ = "db_dbcomputer"
id = Column(Integer, primary_key=True)
uuid = Column(UUID(as_uuid=True), default=uuid_func)
name = Column(String(255), unique=True, nullable=False)
hostname = Column(String(255))
description = Column(Text, nullable=True)
enabled = Column(Boolean)
transport_type = Column(String(255))
scheduler_type = Column(String(255))
transport_params = Column(JSONB)
_metadata = Column('metadata', JSONB)
dbnodes_q = relationship('DbNode', lazy='dynamic')
def __init__(self, *args, **kwargs):
self.enabled = True
self._metadata = {}
self.transport_params = {}
# TODO SP: it's supposed to be nullable, but there is a NOT NULL
# constraint inside the DB.
self.description = ""
super(DbComputer, self).__init__(*args, **kwargs)
@classmethod
def get_dbcomputer(cls, computer):
"""
Return a DbComputer from its name (or from another Computer or DbComputer instance)
"""
from aiida.orm.computer import Computer
if isinstance(computer, basestring):
try:
dbcomputer = cls.session.query(cls).filter(
cls.name == computer).one()
except NoResultFound:
raise NotExistent(
"No computer found in the table of computers with "
"the given name '{}'".format(computer))
except MultipleResultsFound:
raise DbContentError(
"There is more than one computer with name '{}', "
"pass a Computer instance".format(computer))
elif isinstance(computer, int):
try:
dbcomputer = cls.session.query(cls).filter(
cls.id == computer).one()
except NoResultFound:
raise NotExistent(
"No computer found in the table of computers with "
"the given pk '{}'".format(computer))
elif isinstance(computer, DbComputer):
if computer.id is None:
raise ValueError(
"The computer instance you are passing has not been stored yet"
)
dbcomputer = computer
elif isinstance(computer, Computer):
if computer.dbcomputer.id is None:
raise ValueError(
"The computer instance you are passing has not been stored yet"
)
dbcomputer = computer.dbcomputer
else:
raise TypeError(
"Pass either a computer name, a DbComputer django instance or a Computer object"
)
return dbcomputer
def get_aiida_class(self):
from aiida.orm.computer import Computer
return Computer(dbcomputer=self)
def get_workdir(self):
try:
return self._metadata['workdir']
except KeyError:
raise ConfigurationError(
'No workdir found for DbComputer {} '.format(self.name))
def __str__(self):
if self.enabled:
return "{} ({})".format(self.name, self.hostname)
else:
return "{} ({}) [DISABLED]".format(self.name, self.hostname)
root = os.path.abspath(os.curdir)
path_of_data = os.path.join(root, 'data/listings_clean.csv')
df = pd.read_csv(path_of_data)
# rename columns
column_mapping = {
'accommodates': 'guest_count',
'beds': 'bed_count',
'LGA': 'lga'
}
df = df.rename(column_mapping, axis=1)
# Set column type
dtype_mapping = {
'id': Integer(),
'lga': String(),
'bed_count': Integer(),
'guest_count': Integer(),
'property_type': String(),
'room_type': String(),
'price': Integer(),
'rating': Integer(),
}
# Dump DF
df.to_sql('properties_df',
con=Engine,
if_exists='replace',
dtype=dtype_mapping,
index_label='id')
class DbNode(Base):
__tablename__ = "db_dbnode"
aiida_query = _QueryProperty(_AiidaQuery)
id = Column(Integer, primary_key=True)
uuid = Column(UUID(as_uuid=True), default=uuid_func)
type = Column(String(255), index=True)
label = Column(String(255), index=True, nullable=True,
default="") # Does it make sense to be nullable and have a default?
description = Column(Text(), nullable=True, default="")
ctime = Column(DateTime(timezone=True), default=timezone.now)
mtime = Column(DateTime(timezone=True), default=timezone.now)
nodeversion = Column(Integer, default=1)
public = Column(Boolean, default=False)
attributes = Column(JSONB)
extras = Column(JSONB)
dbcomputer_id = Column(
Integer,
ForeignKey('db_dbcomputer.id', deferrable=True, initially="DEFERRED", ondelete="RESTRICT"),
nullable=True
)
# This should have the same ondelet behaviour as db_computer_id, right?
user_id = Column(
Integer,
ForeignKey(
'db_dbuser.id', deferrable=True, initially="DEFERRED", ondelete="restrict"
),
nullable=False
)
# TODO SP: The 'passive_deletes=all' argument here means that SQLAlchemy
# won't take care of automatic deleting in the DbLink table. This still
# isn't exactly the same behaviour than with Django. The solution to
# this is probably a ON DELETE inside the DB. On removing node with id=x,
# we would remove all link with x as an output.
######### RELATIONSSHIPS ################
dbcomputer = relationship(
'DbComputer',
backref=backref('dbnodes', passive_deletes='all', cascade='merge')
)
# User
user = relationship(
'DbUser',
backref=backref('dbnodes', passive_deletes='all', cascade='merge',)
)
# outputs via db_dblink table
outputs_q = relationship(
"DbNode", secondary="db_dblink",
primaryjoin="DbNode.id == DbLink.input_id",
secondaryjoin="DbNode.id == DbLink.output_id",
backref=backref("inputs_q", passive_deletes=True, lazy='dynamic'),
lazy='dynamic',
passive_deletes=True
)
@property
def outputs(self):
return self.outputs_q.all()
@property
def inputs(self):
return self.inputs_q.all()
def __init__(self, *args, **kwargs):
super(DbNode, self).__init__(*args, **kwargs)
if self.attributes is None:
self.attributes = dict()
if self.extras is None:
self.extras = dict()
# XXX repetition between django/sqlalchemy here.
def get_aiida_class(self):
"""
Return the corresponding aiida instance of class aiida.orm.Node or a
appropriate subclass.
"""
from aiida.common.old_pluginloader import from_type_to_pluginclassname
from aiida.orm.node import Node
try:
pluginclassname = from_type_to_pluginclassname(self.type)
except DbContentError:
raise DbContentError("The type name of node with pk= {} is "
"not valid: '{}'".format(self.pk, self.type))
try:
PluginClass = load_plugin(Node, 'aiida.orm', pluginclassname)
except MissingPluginError:
aiidalogger.error("Unable to find plugin for type '{}' (node= {}), "
"will use base Node class".format(self.type, self.pk))
PluginClass = Node
return PluginClass(dbnode=self)
def get_simple_name(self, invalid_result=None):
"""
Return a string with the last part of the type name.
If the type is empty, use 'Node'.
If the type is invalid, return the content of the input variable
``invalid_result``.
:param invalid_result: The value to be returned if the node type is
not recognized.
"""
thistype = self.type
# Fix for base class
if thistype == "":
thistype = "node.Node."
if not thistype.endswith("."):
return invalid_result
else:
thistype = thistype[:-1] # Strip final dot
return thistype.rpartition('.')[2]
def set_attr(self, key, value):
DbNode._set_attr(self.attributes, key, value)
flag_modified(self, "attributes")
self.save()
def set_extra(self, key, value):
DbNode._set_attr(self.extras, key, value)
flag_modified(self, "extras")
self.save()
def reset_extras(self, new_extras):
self.extras.clear()
self.extras.update(new_extras)
flag_modified(self, "extras")
self.save()
def del_attr(self, key):
DbNode._del_attr(self.attributes, key)
flag_modified(self, "attributes")
self.save()
def del_extra(self, key):
DbNode._del_attr(self.extras, key)
flag_modified(self, "extras")
self.save()
@staticmethod
def _set_attr(d, key, value):
if '.' in key:
raise ValueError(
"We don't know how to treat key with dot in it yet")
d[key] = value
@staticmethod
def _del_attr(d, key):
if '.' in key:
raise ValueError("We don't know how to treat key with dot in it yet")
if key not in d:
raise ValueError("Key {} does not exists".format(key))
del d[key]
@property
def pk(self):
return self.id
def __str__(self):
simplename = self.get_simple_name(invalid_result="Unknown")
# node pk + type
if self.label:
return "{} node [{}]: {}".format(simplename, self.pk, self.label)
else:
return "{} node [{}]".format(simplename, self.pk)
# User email
@hybrid_property
def user_email(self):
"""
Returns: the email of the user
"""
return self.user.email
@user_email.expression
def user_email(cls):
"""
Returns: the email of the user at a class level (i.e. in the database)
"""
return select([DbUser.email]).where(DbUser.id == cls.user_id).label(
'user_email')
# Computer name
@hybrid_property
def computer_name(self):
"""
Returns: the of the computer
"""
return self.dbcomputer.name
@computer_name.expression
def computer_name(cls):
"""
Returns: the name of the computer at a class level (i.e. in the
database)
"""
return select([DbComputer.name]).where(DbComputer.id ==
cls.dbcomputer_id).label(
'computer_name')
@hybrid_property
def state(self):
"""
Return the most recent state from DbCalcState
"""
if not self.id:
return None
all_states = DbCalcState.query.filter(DbCalcState.dbnode_id == self.id).all()
if all_states:
#return max((st.time, st.state) for st in all_states)[1]
return sort_states(((dbcalcstate.state, dbcalcstate.state.value)
for dbcalcstate in all_states),
use_key=True)[0]
else:
return None
@state.expression
def state(cls):
"""
Return the expression to get the 'latest' state from DbCalcState,
to be used in queries, where 'latest' is defined using the state order
defined in _sorted_datastates.
"""
# Sort first the latest states
whens = {
v: idx for idx, v
in enumerate(_sorted_datastates[::-1], start=1)}
custom_sort_order = case(value=DbCalcState.state,
whens=whens,
else_=100) # else: high value to put it at the bottom
# Add numerical state to string, to allow to sort them
states_with_num = select([
DbCalcState.id.label('id'),
DbCalcState.dbnode_id.label('dbnode_id'),
DbCalcState.state.label('state_string'),
custom_sort_order.label('num_state')
]).select_from(DbCalcState).alias()
# Get the most 'recent' state (using the state ordering, and the min function) for
# each calc
calc_state_num = select([
states_with_num.c.dbnode_id.label('dbnode_id'),
func.min(states_with_num.c.num_state).label('recent_state')
]).group_by(states_with_num.c.dbnode_id).alias()
# Join the most-recent-state table with the DbCalcState table
all_states_q = select([
DbCalcState.dbnode_id.label('dbnode_id'),
DbCalcState.state.label('state_string'),
calc_state_num.c.recent_state.label('recent_state'),
custom_sort_order.label('num_state'),
]).select_from(#DbCalcState).alias().join(
join(DbCalcState, calc_state_num, DbCalcState.dbnode_id == calc_state_num.c.dbnode_id)).alias()
# Get the association between each calc and only its corresponding most-recent-state row
subq = select([
all_states_q.c.dbnode_id.label('dbnode_id'),
all_states_q.c.state_string.label('state')
]).select_from(all_states_q).where(all_states_q.c.num_state == all_states_q.c.recent_state).alias()
# Final filtering for the actual query
return select([subq.c.state]).\
where(
subq.c.dbnode_id == cls.id,
).\
label('laststate')
class A( self.Entity ):
name = Field(String(60), colname='strName')
class Domain(BaseModel):
__tablename__ = 'domain'
id = Column(Integer, primary_key=True)
name = Column(String(80), unique=True, nullable=False)
count = Column(Integer, default=0)
ftime = Column(DateTime)
ltime = Column(DateTime)
def __init__(self, name, count, ftime, ltime):
self.name = name
self.count = count
if (ftime is not None):
self.ftime = ftime
else:
self.ftime = datetime.now()
if (ltime is not None):
self.ltime = ltime
else:
self.ltime = datetime.now()
def __repr__(self):
return '<domain: %r %r>' % (self.id, self.name)
@staticmethod
def info(num):
ret = {}
ret['data'] = []
query = session.query(Domain)
pureData = query.filter().order_by(desc(Domain.count)).limit(num).all()
for tmpData in pureData:
tmp = {}
tmp['name'] = tmpData.name
tmp['count'] = tmpData.count
ret['data'].append(tmp)
return ret
@staticmethod
def check(name):
'''
:param name:
:return: {1:'success', 2:'name repeat', 3:'name is empty'}
'''
if (name == ''):
return 3
Session = scoped_session(sessionmaker(autoflush=True, bind=engine))
query = Session.query(Domain)
tmpDomain = query.filter(Domain.name == name).first()
Session.close()
if (tmpDomain is not None):
return 2
return 1
@staticmethod
def add(name, count=0):
'''
:param name:
:return: {1:'success', 2:'name repeat'}
'''
ret = 1
ret = Domain.check(name)
if (ret != 1):
return ret
if (count == 0):
count = 1
ftime = datetime.now()
ltime = datetime.now()
curDomain = Domain(name, count, ftime, ltime)
Session = scoped_session(sessionmaker(autoflush=True, bind=engine))
Session.add(curDomain)
Session.commit()
Session.close()
# session.add(curDomain)
# session.commit()
return ret
@staticmethod
def updateDomain(Name_DICT={}):
'''
:param Name_DICT:
:return: {1:'success', 2:'name is empty', 3:'failed'}
'''
try:
Session = scoped_session(sessionmaker(autoflush=True, bind=engine))
if (Name_DICT is None):
return 3
for name in Name_DICT.keys():
#print("add name: %s" %name)
ret = 1
ret = Domain.check(name)
if (ret == 1):
Domain.add(name, Name_DICT[name])
continue
elif (ret == 2):
curCount = Session.query(
Domain.count).filter(Domain.name == name).first()
if (curCount is None):
continue
else:
count = int(curCount[0]) + Name_DICT[name]
Session.query(Domain).filter(
Domain.name == name).update({
'count': count,
'ltime': datetime.now()
})
Session.commit()
else:
continue
except BaseException:
Session.rollback()
# raise
finally:
Session.close()
return 1
class Table3( self.Entity ):
t3id = Field(Integer, primary_key=True)
name = Field(String(30))
tbl1 = ManyToOne(Table1)
class Station(BaseModel):
__tablename__='station'
id=Column(Integer, primary_key=True)
name=Column(String(80), unique=True, nullable=False)
number=Column(String(80), unique=True, nullable=False)
supervisor=Column(String(80))
phone=Column(String(80))
time=Column(DateTime)
valid=Column(Integer, default=1)
def __init__(self, name, number, supervisor, phone, time):
self.name=name
self.number=number
self.supervisor=supervisor
self.phone=phone
self.time=time
def __repr__(self):
return '<station: %d %r>' % (self.id, self.name)
@staticmethod
def check(name, number):
'''
1: 'success',
2: 'name repeat'
3: 'number repeat'
4: 'name is empty'
5: 'number is empty'
'''
ret=1
if(name==''):
ret=4
return ret
if(number==''):
ret=5
return ret
query=session.query(Station)
tmpStation=query.filter(or_(Station.name==name, Station.number==number), Station.valid == 1).all()
for tStation in tmpStation:
if(tStation.name==name):
ret=2
return ret
if(tStation.number==number):
ret=3
return ret
return ret
@staticmethod
def add(name, number, supervisor, phone):
'''
:param name:
:param number:
:param supervisor:
:param phont:
:return: {1:'success', 2:'name repeat', 3:'number repeat', 4:'name is empty', 5:'number is empty'}
'''
ret=1
if(name==''):
ret=4
return ret
if(number==''):
ret=5
return ret
ret=Station.check(name, number)
if(ret!=1):
return ret
curStation=Station(name, number, supervisor, phone, datetime.now())
session.add(curStation)
session.commit()
return ret
@staticmethod
def eqpNum():
ret={}
ret['data']=[]
allData=session.execute('select station_name ,count(1) as nums from (select station.name as station_name from station join sensor on station.id=sensor.stationId join eqp on sensor.mac=eqp.sensorMac) as total group by station_name order by nums desc limit 5;').fetchall();
for tmpData in allData:
tmp={}
tmp['name']=tmpData.station_name
tmp['count']=tmpData.nums
ret['data'].append(tmp)
return ret
@staticmethod
def select(inData):
ret={}
ret['status']=1
ret['data']=[]
ret['pureData']=[]
query=session.query(Station)
allStation=None
if('id' in inData):
allStation=query.filter(Station.id==inData['id'], Station.valid == 1).all()
elif('number' in inData):
allStation=query.filter(Station.number==inData['number'], Station.valid == 1).all()
elif('name' in inData):
allStation=query.filter(Station.name==inData['name'], Station.valid == 1).all()
else:
allStation=query.filter(Station.valid == 1).all()
for tmpData in allStation:
tmp={}
tmp['id']=tmpData.id
tmp['name']=tmpData.name
tmp['number']=tmpData.number
tmp['supervisor']=tmpData.supervisor
tmp['phone']=tmpData.phone
tmp['time']=tmpData.time
ret['data'].append(tmp)
ret['pureData'].append(tmpData)
return ret
@staticmethod
def updateStation(inData):
'''
:param: inData
:return: {1:'success', 2:'name repeat', 3:'permission denied', 4:'param wrong', 5:'number repeat'}
'''
from user import User
if(('UserId' in inData) and ('StationId' in inData) and (('Name' in inData) or ('Number' in inData) or ('Supervisor' in inData) or ('Phone' in inData))):
updateUserLevel = session.query(User.level).filter(User.id == inData['UserId'], User.valid == 1).first()
if(updateUserLevel == None or int(updateUserLevel[0] != 0)):
return 3
else:
if('Name' in inData and inData['Name'] != ''):
tmpStation=session.query(Station).filter(Station.name==inData['Name'], Station.valid == 1).first()
if(tmpStation != None):
return 2
session.query(Station).filter(Station.id == inData['StationId'], Station.valid == 1).update({'name':inData['Name'], 'time':datetime.now()})
if('Number' in inData and inData['Number'] != ''):
tmpStation=session.query(Station).filter(Station.number==inData['Number'], Station.valid == 1).first()
if(tmpStation != None):
return 5
session.query(Station).filter(Station.id == inData['StationId'], Station.valid == 1).update({'number':inData['Number'], 'time':datetime.now()})
if('Supervisor' in inData and inData['Supervisor'] != ''):
session.query(Station).filter(Station.id == inData['StationId'], Station.valid == 1).update({'supervisor':inData['Supervisor'], 'time':datetime.now()})
if('Phone' in inData and inData['Phone'] != ''):
session.query(Station).filter(Station.id == inData['StationId'], Station.valid == 1).update({'phone':inData['Phone'], 'time':datetime.now()})
session.commit()
return 1
else:
return 4
@staticmethod
def delStation(inData):
'''
:param: inData
:return: {1:'success', 2:'failed', 3:'permission denied', 4:'param wrong'}
'''
from user import User
from sensor import Sensor
if(('UserId' in inData) and ('StationId' in inData)):
updateUserLevel = session.query(User.level).filter(User.id == inData['UserId'], User.valid == 1).first()
if(updateUserLevel == None or int(updateUserLevel[0] != 0)):
return 3
else:
session.query(Station).filter(Station.id == inData['StationId'], Station.valid == 1).update({'valid':0, 'time':datetime.now()})
delSensorId = session.query(Sensor.id).filter(Sensor.stationId == inData['StationId'], Sensor.valid == 1).all()
for sensorId in delSensorId:
Sensor.delSensor({'UserId':inData['UserId'], 'SensorId':int(sensorId[0])})
session.commit()
return 1
else:
return 4
@staticmethod
def update_station2(inData):
'''
:param inData:
:return: {0:'errror', 1:'success', 2:'no this id', 3:'iData has no key id', 4:'name input is none', 5:'number is none', 6:'supervisor is none'},
7:'phone is none'}
'''
ret=1
if(not inData.has_key('id')):
ret=3
return ret
query=session.query(Station)
tmpStation=query.filter(Station.id==inData['id']).first()
if(tmpStation==None):
ret=2
return ret
if(inData.has_key('name')):
if(inData['name']!=''):
tmpStation.name=inData['name']
else:
ret=4
if(inData.has_key('number')):
if(inData['number']!=''):
tmpStation.number=inData['number']
else:
ret=5
if(inData.has_key('supervisor')):
tmpStation.supervisor=inData['supervisor']
# if(inData['supervisor']!=''):
# tmpStation.supervisor=inData['supervisor']
# else:
# ret=6
if(inData.has_key('phone')):
tmpStation.phone=inData['phone']
# if(inData['phone']!=''):
# tmpStation.phone=inData['phone']
# else:
# ret=7
session.commit()
return ret
@staticmethod
def delete_station2(inData):
'''
:param inData:
:return:{
2:'there is no id in inData',
3:'there is no such id'
}
'''
ret=0
if(not inData.has_key('id')):
ret=2
return ret
query=session.query(Station)
tmpStation=query.filter(Station.id==inData['id']).first()
if(tmpStation==None):
ret=3
return ret
else:
from sensor import Sensor
tmpAllSensor=Sensor.select({'stationId': tmpStation.id})
for tmpSensor in tmpAllSensor['pureData']:
tmpSensor.stationId=0
session.delete(tmpStation)
session.commit()
ret=1
return ret
init_db()
class Level(BaseModel):
__tablename__ = 'level'
id = Column(Integer, primary_key=True)
level = Column(SmallInteger, nullable=False)
step = Column(SmallInteger, nullable=False) # 0: fake page
type = Column(SmallInteger, nullable=False) # 0: common, 1: puretext, 2: redirect
dirname = Column(String(64), nullable=False)
pagename = Column(String(64), nullable=False)
title = Column(String(256), nullable=False)
imgname = Column(String(128))
imgalt = Column(String(256))
otherimgs = Column(String(256)) # name of other images, ',' seperate
audioname = Column(String(128))
otheraudios = Column(String(256)) # name of other audios, ',' seperate
hint = Column(String(512))
hiddencode = Column(Text)
puretexth1 = Column(String(256))
puretextp = Column(Text)
redirectto = Column(String(128))
redirecttime = Column(SmallInteger)
class PassengerCount(Base):
'''
This is the mapping from the csv file to tabel in the DB
IdReportRow --> passenger_count_id
Direction --> direction
DateKey --> date
HourKey --> actual_time
PlannedMissionTime --> planned_time
StationId --> stop_id
station_order --> stop_order
TripId --> trip_id
DoorsOpenTime --> door_open_time
DoorsCloseTime --> door_close_time
PassengersDown_rounded_sofi --> passengers_down
PassengersUp_rounded_sofi --> passengers_up
PassengersContinue_rounded_sofi --> passengers_continue
'''
filename = 'passenger_count.csv'
__tablename__ = 'passenger_count'
id = Column(Integer,
Sequence(None, optional=True),
primary_key=True,
nullable=True)
passenger_count_id = Column(String(20), index=True, nullable=False)
direction = Column(String(1))
date = Column(String(10))
actual_time = Column(String(10))
planned_time = Column(String(10))
stop_id = Column(String(20), index=True, nullable=False)
stop_sequence = Column(Integer, default=-1)
trip_id = Column(String(20), index=True, nullable=False)
door_open_time = Column(String(20))
door_close_time = Column(String(20))
passengers_down = Column(Integer, default=-1)
passengers_up = Column(Integer, default=-1)
passengers_continue = Column(Integer, default=-1)
stop = relationship('Stop',
primaryjoin='Stop.stop_id==PassengerCount.stop_id',
foreign_keys='(PassengerCount.stop_id)',
uselist=False,
viewonly=True)
trip = relationship('Trip',
primaryjoin='Trip.trip_id==PassengerCount.trip_id',
foreign_keys='(PassengerCount.trip_id)',
uselist=False,
viewonly=True)
def __init__(self, *args, **kwargs):
super(PassengerCount, self).__init__(*args, **kwargs)
@classmethod
def get_mapping(self):
mapping = {
'passenger_count_id': 'IdReportRow',
'direction': 'Direction',
'date': 'DateKey',
'actual_time': 'HourKey',
'planned_time': 'PlannedMissionTime',
'stop_id': 'StationId',
'stop_sequence': 'station_order',
'trip_id': 'TripId',
'door_open_time': 'DoorsOpenTime',
'door_close_time': 'DoorsCloseTime',
'passengers_down': 'PassengersDown_rounded_sofi',
'passengers_up': 'PassengersUp_rounded_sofi',
'passengers_continue': 'PassengersContinue_rounded_sofi'
}
return mapping
@classmethod
def get_csv_table_columns(self):
return self.__table__.columns.keys()
@classmethod
def get_csv_table_index(self):
return "id"
@classmethod
def get_mapping_to_csv(self, column):
mapping = self.get_mapping()
return mapping[column]
@classmethod
def transform_data(self, df):
print("started preprocess")
df = df[list(self.get_mapping().values())]
new_df = pd.DataFrame(columns=self.__table__.columns.keys())
for column in list(self.__table__.columns.keys())[1:-1]:
new_df[column] = df[self.get_mapping_to_csv(column)]
new_df[self.get_csv_table_index()] = list(
range(new_df[list(self.__table__.columns.keys())[2]].count()))
print("finished preprocess")
return new_df
@classmethod
def load(cls, db: Database, passenger_count_file: str) -> StatusCode:
"""This function creates Database instance for the server (only schemas i.e. no data).
:param db: db instance.
:type db: Databse.
:param gtfs_directory: the GTFS temporary folder path.
:type gtfs_directory: str.
:returns: StatusCode -- StatusCode.
"""
log = logging.getLogger(cls.__module__)
start_time = time.time()
if os.path.exists(passenger_count_file):
cls.load_table_db(db, passenger_count_file)
process_time = time.time() - start_time
print('{0}.load ({1:.0f} seconds)'.format(cls.__name__, process_time))
log.debug('{0}.load ({1:.0f} seconds)'.format(cls.__name__,
process_time))
return StatusCode.OK
class Setting(BaseModel):
__tablename__ = 'setting'
id = Column(Integer, primary_key=True)
name = Column(String(32), unique=True)
value = Column(Text())
from tasks.merge.code_mapping import update_from_info_table
from ibats_utils.db import with_db_session, add_col_2_table, alter_table_2_myisam, \
bunch_insert_on_duplicate_update
from tasks.tushare.ts_pro_api import pro
from tasks.config import config
DEBUG = False
logger = logging.getLogger()
DATE_BASE = datetime.strptime('2005-01-01', STR_FORMAT_DATE).date()
ONE_DAY = timedelta(days=1)
# 标示每天几点以后下载当日行情数据
BASE_LINE_HOUR = 16
STR_FORMAT_DATE_TS = '%Y%m%d'
INDICATOR_PARAM_LIST_TUSHARE_STOCK_CASHFLOW = [
('ts_code', String(20)),
('ann_date', Date),
('f_ann_date', Date),
('end_date', Date),
('report_type', DOUBLE),
('comp_type', DOUBLE),
('net_profit', DOUBLE),
('finan_exp', DOUBLE),
('c_fr_sale_sg', DOUBLE),
('recp_tax_rends', DOUBLE),
('n_depos_incr_fi', DOUBLE),
('n_incr_loans_cb', DOUBLE),
('n_inc_borr_oth_fi', DOUBLE),
('prem_fr_orig_contr', DOUBLE),
('n_incr_insured_dep', DOUBLE),
('n_reinsur_prem', DOUBLE),
df = pd.read_excel('Employee.xlsx')
print(df.values)
df_products = pd.read_excel('Products.xlsx')
df_sales_period_data = pd.read_excel('Sales_Period_Data.xlsx')
df_price = pd.read_excel('Price.xlsx')
df_sales_quantities = pd.read_excel('Sales Quantities.xlsx')
from sqlalchemy.types import String, SmallInteger
df.to_sql('Employee',
con=engine,
if_exists='append',
index=False,
dtype={
'EmpID': String(length=255),
'Sales Team Lead': String(length=255),
'PayGrade': String(length=255),
'Region': String(length=255),
'IsFound': String(length=255)
})
print(df_products)
df_products.to_sql('products',
con=engine,
if_exists='append',
index=False,
dtype={
'PROD_CODE': String(length=255),
'PROD_NAME': String(length=1024),
def import_cb_daily(chain_param=None, wind_code_set: set = None, begin_time=None):
"""
导入可转债日线数据
需要补充 转股价格
:param chain_param: 在celery 中將前面結果做爲參數傳給後面的任務
:return:
"""
table_name = "wind_convertible_bond_daily"
info_table_name = "wind_convertible_bond_info"
has_table = engine_md.has_table(table_name)
col_name_param_list = [
('outstandingbalance', DOUBLE),
('clause_conversion2_bondlot', DOUBLE),
('clause_conversion2_bondproportion', DOUBLE),
('clause_conversion2_swapshareprice', DOUBLE),
('clause_conversion2_conversionproportion', DOUBLE),
('convpremium', DOUBLE),
('convpremiumratio', DOUBLE),
('convvalue', DOUBLE),
('convpe', DOUBLE),
('convpb', DOUBLE),
('underlyingpe', DOUBLE),
('underlyingpb', DOUBLE),
('diluterate', DOUBLE),
('ldiluterate', DOUBLE),
('open', DOUBLE),
('high', DOUBLE),
('low', DOUBLE),
('close', DOUBLE),
('volume', DOUBLE),
]
wind_indictor_str = ",".join(col_name for col_name, _ in col_name_param_list)
logging.info("更新 %s 开始", table_name)
if has_table:
sql_str = """
SELECT wind_code, date_frm, if(delist_date<end_date, delist_date, end_date) date_to
FROM
(
SELECT info.wind_code,
ifnull(trade_date, ipo_date) date_frm, clause_conversion_2_swapshareenddate delist_date,
if(hour(now())<16, subdate(curdate(),1), curdate()) end_date
FROM
wind_convertible_bond_info info
LEFT OUTER JOIN
(SELECT wind_code, adddate(max(trade_date),1) trade_date FROM {table_name} GROUP BY wind_code) daily
ON info.wind_code = daily.wind_code
) tt
WHERE date_frm <= if(delist_date<end_date, delist_date, end_date)
ORDER BY wind_code""".format(table_name=table_name)
else:
logger.warning('%s 不存在，仅使用 %s 表进行计算日期范围', table_name, info_table_name)
sql_str = """
SELECT wind_code, date_frm, if(delist_date<end_date, delist_date, end_date) date_to
FROM
(
SELECT info.wind_code,
ipo_date date_frm, clause_conversion_2_swapshareenddate delist_date,
if(hour(now())<16, subdate(curdate(),1), curdate()) end_date
FROM wind_convertible_bond_info info
) tt
WHERE date_frm <= if(delist_date<end_date, delist_date, end_date)
ORDER BY wind_code"""
with with_db_session(engine_md) as session:
# 获取每只股票需要获取日线数据的日期区间
table = session.execute(sql_str)
# 计算每只股票需要获取日线数据的日期区间
# 获取date_from,date_to，将date_from,date_to做为value值
code_date_range_dic = {
wind_code: (date_from if begin_time is None else min([date_from, begin_time]), date_to)
for wind_code, date_from, date_to in table.fetchall() if
wind_code_set is None or wind_code in wind_code_set}
# 设置dtype
dtype = {key: val for key, val in col_name_param_list}
dtype['wind_code'] = String(20)
dtype['trade_date'] = Date
data_df_list, data_count, tot_data_count, code_count = [], 0, 0, len(code_date_range_dic)
# 获取股票量价等行情数据
try:
for num, (wind_code, (begin_time, end_time)) in enumerate(code_date_range_dic.items(), start=1):
logger.debug('%d/%d) %s [%s - %s]', num, code_count, wind_code, begin_time, end_time)
try:
data_df = invoker.wsd(wind_code, wind_indictor_str, begin_time, end_time, "unit=1")
except APIError as exp:
if exp.ret_dic.setdefault('error_code', 0) in (
-40520007, # 没有可用数据
-40521009, # 数据解码失败。检查输入参数是否正确，如：日期参数注意大小月月末及短二月
):
logger.warning("%d/%d) %s 执行异常 %s", num, code_count, wind_code, exp.ret_dic)
continue
else:
logger.exception("%d/%d) %s 执行异常", num, code_count, wind_code)
break
if data_df is None:
logger.warning('%d/%d) %s has no data during %s %s',
num, code_count, wind_code, begin_time, end_time)
continue
if data_df is not None and data_df.shape[0] > 0:
data_df['wind_code'] = wind_code
data_df.index.rename('trade_date', inplace=True)
data_df.reset_index(inplace=True)
data_df.rename(columns={col: col.lower() for col in data_df.columns}, inplace=True)
data_df_list.append(data_df)
data_count += data_df.shape[0]
# 大于阀值有开始插入
if data_count >= 10000:
data_df_all = pd.concat(data_df_list)
data_count = bunch_insert_on_duplicate_update(data_df_all, table_name, engine_md, dtype)
logging.info("更新 %s %d 条信息", table_name, data_count)
tot_data_count += data_count
data_df_list, data_count = [], 0
# 仅调试使用
if DEBUG and len(data_df_list) > 1:
break
finally:
# 导入数据库
if len(data_df_list) > 0:
data_df_all = pd.concat(data_df_list)
data_count = bunch_insert_on_duplicate_update(data_df_all, table_name, engine_md, dtype)
logging.info("更新 %s 结束 %d 条信息被更新", table_name, data_count)
if not has_table and engine_md.has_table(table_name):
alter_table_2_myisam(engine_md, [table_name])
build_primary_key([table_name])
def add_data_2_ckdvp(col_name, param, wind_code_set: set = None, begin_time=None):
"""判断表格是否存在，存在则进行表格的关联查询
:param col_name: 增加的列属性名
:param param: 参数
:param wind_code_set: 默认为None
:param begin_time: 默认为None
:return:
"""
table_name = 'wind_ckdvp_stock'
all_finished = False
has_table = engine_md.has_table('wind_ckdvp_stock')
if has_table:
# 执行语句，表格数据联立
sql_str = """
select wind_code, date_frm, if(delist_date<end_date, delist_date, end_date) date_to
FROM
(
select info.wind_code,
(ipo_date) date_frm, delist_date,
if(hour(now())<16, subdate(curdate(),1), curdate()) end_date
from wind_stock_info info
left outer join
(select wind_code, adddate(max(time),1) from wind_ckdvp_stock
where wind_ckdvp_stock.key='{0}' and param='{1}' group by wind_code
) daily
on info.wind_code = daily.wind_code
) tt
where date_frm <= if(delist_date<end_date,delist_date, end_date)
order by wind_code""".format(col_name, param)
else:
logger.warning('wind_ckdvp_stock 不存在，仅使用 wind_stock_info 表进行计算日期范围')
sql_str = """
SELECT wind_code, date_frm,
if(delist_date<end_date,delist_date, end_date) date_to
FROM
(
SELECT info.wind_code,ipo_date date_frm, delist_date,
if(hour(now())<16, subdate(curdate(),1), curdate()) end_date
FROM wind_stock_info info
) tt
WHERE date_frm <= if(delist_date<end_date, delist_date, end_date)
ORDER BY wind_code"""
with with_db_session(engine_md) as session:
# 获取每只股票需要获取日线数据的日期区间
table = session.execute(sql_str)
code_date_range_dic = {
wind_code: (date_from if begin_time is None else min([date_from, begin_time]), date_to)
for wind_code, date_from, date_to in table.fetchall() if
wind_code_set is None or wind_code in wind_code_set}
# 设置 dtype
dtype = {
'wind_code': String(20),
'key': String(80),
'time': Date,
'value': String(80),
'param': String(80),
}
data_df_list, data_count, tot_data_count, code_count = [], 0, 0, len(code_date_range_dic)
try:
for num, (wind_code, (date_from, date_to)) in enumerate(code_date_range_dic.items(), start=1):
logger.debug('%d/%d) %s [%s - %s]', num, code_count, wind_code, date_from, date_to)
data_df = invoker.wsd(
wind_code,
col_name,
date_from,
date_to,
param
)
if data_df is not None and data_df.shape[0] > 0:
# 对我们的表格进行规范整理,整理我们的列名，索引更改
data_df['key'] = col_name
data_df['param'] = param
data_df['wind_code'] = wind_code
data_df.rename(columns={col_name.upper(): 'value'}, inplace=True)
data_df.index.rename('time', inplace=True)
data_df.reset_index(inplace=True)
data_count += data_df.shape[0]
data_df_list.append(data_df)
# 大于阀值有开始插入
if data_count >= 10000:
tot_data_df = pd.concat(data_df_list)
# tot_data_df.to_sql(table_name, engine_md, if_exists='append', index=False, dtype=dtype)
data_count = bunch_insert_on_duplicate_update(tot_data_df, table_name, engine_md, dtype)
tot_data_count += data_count
data_df_list, data_count = [], 0
# 仅调试使用
if DEBUG and len(data_df_list) > 1:
break
all_finished = True
finally:
if data_count > 0:
tot_data_df = pd.concat(data_df_list)
# tot_data_df.to_sql(table_name, engine_md, if_exists='append', index=False, dtype=dtype)
data_count = bunch_insert_on_duplicate_update(tot_data_df, table_name, engine_md, dtype)
tot_data_count += data_count
if not has_table and engine_md.has_table(table_name):
create_pk_str = """ALTER TABLE {table_name}
CHANGE COLUMN `wind_code` `wind_code` VARCHAR(20) NOT NULL ,
CHANGE COLUMN `time` `time` DATE NOT NULL ,
CHANGE COLUMN `key` `key` VARCHAR(80) NOT NULL ,
CHANGE COLUMN `param` `param` VARCHAR(80) NOT NULL ,
ADD PRIMARY KEY (`wind_code`, `time`, `key`, `param`)""".format(table_name=table_name)
with with_db_session(engine_md) as session:
session.execute(create_pk_str)
logging.info("更新 %s 完成 新增数据 %d 条", table_name, tot_data_count)
if not has_table and engine_md.has_table(table_name):
alter_table_2_myisam(engine_md, [table_name])
build_primary_key([table_name])
return all_finished
def import_cb_info(chain_param=None, first_time=False):
"""
获取全市场可转债数据
:param chain_param: 在celery 中將前面結果做爲參數傳給後面的任務
:param first_time: 第一次执行时将从 1999 年开始查找全部基本信息
:return:
"""
table_name = 'wind_convertible_bond_info'
has_table = engine_md.has_table(table_name)
name_param_list = [
('trade_code', DOUBLE),
('fullname', String(45)),
('sec_name', String(45)),
('issue_announcement', Date),
('ipo_date', Date),
('clause_conversion_2_swapsharestartdate', Date),
('clause_conversion_2_swapshareenddate', Date),
('clause_conversion_code', DOUBLE),
('clause_interest_5', String(8)),
('clause_interest_8', String(8)),
('clause_interest_6', String(200)),
('clause_interest_compensationinterest', DOUBLE),
('clause_interest_compensationinterest', DOUBLE),
('issueamount', DOUBLE),
('term', DOUBLE),
('underlyingcode', String(20)),
('underlyingname', String(20)),
('redemption_beginning', Date),
]
param = ",".join([key for key, _ in name_param_list])
# 设置dtype类型
dtype = {key: val for key, val in name_param_list}
dtype['wind_code'] = String(20)
#
if first_time:
date_since = datetime.strptime('1999-01-01', STR_FORMAT_DATE).date()
date_list = []
one_year = timedelta(days=365)
while date_since < date.today() - ONE_DAY:
date_list.append(date_since)
date_since += one_year
else:
date_list.append(date.today() - ONE_DAY)
else:
date_list = [date.today() - ONE_DAY]
# 获取 wind_code 集合
wind_code_set = set()
for fetch_date in date_list:
data_set = get_cb_set(fetch_date)
if data_set is not None:
wind_code_set |= data_set
# 获取股票对应上市日期，及摘牌日期
# w.wss("300005.SZ,300372.SZ,000003.SZ", "ipo_date,trade_code,mkt,exch_city,exch_eng")
wind_code_list = list(wind_code_set)
# wind_code_count = len(wind_code_list)
# seg_count = 1000
# loop_count = math.ceil(float(wind_code_count) / seg_count)
data_info_df_list = []
try:
for sub_list in split_chunk(wind_code_list, 1000):
# num_start = n * seg_count
# num_end = (n + 1) * seg_count
# # num_end = num_end if num_end <= wind_code_count else wind_code_count
# sub_list = wind_code_list[n:(n + seg_count)]
# 尝试将 stock_code_list_sub 直接传递给wss，是否可行
data_df = invoker.wss(sub_list, param, "unit=1")
if data_df is not None and data_df.shape[0] > 0:
data_info_df_list.append(data_df)
# 仅仅调试时使用
if DEBUG and len(data_info_df_list) > 1:
break
finally:
if len(data_info_df_list) > 0:
data_info_all_df = pd.concat(data_info_df_list)
data_info_all_df.index.rename('wind_code', inplace=True)
data_info_all_df.rename(columns={col: col.lower() for col in data_info_all_df.columns}, inplace=True)
logging.info('%d data will be import', data_info_all_df.shape[0])
data_info_all_df.reset_index(inplace=True)
data_count = bunch_insert_on_duplicate_update(data_info_all_df, table_name, engine_md, dtype=dtype)
# logging.info("%d stocks have been in %s", len(data_info_all_df), table_name)
logging.info("更新 %s 完成 新增数据 %d 条", table_name, data_count)
if not has_table and engine_md.has_table(table_name):
alter_table_2_myisam(engine_md, [table_name])
build_primary_key([table_name])
# 更新 code_mapping 表
if engine_md.has_table(table_name):
update_from_info_table(table_name)
def import_stock_quertarly(chain_param=None, wind_code_set=None):
"""
插入股票日线数据到最近一个工作日-1
:param chain_param: 在celery 中將前面結果做爲參數傳給後面的任務
:return:
"""
logging.info("更新 wind_stock_quertarly 开始")
table_name = 'wind_stock_quertarly'
has_table = engine_md.has_table(table_name)
if has_table:
sql_str = """
SELECT wind_code, date_frm, if(delist_date<end_date, delist_date, end_date) date_to
FROM
(
SELECT info.wind_code, ifnull(trade_date, ipo_date) date_frm, delist_date,
if(hour(now())<16, subdate(curdate(),1), curdate()) end_date
FROM
wind_stock_info info
LEFT OUTER JOIN
(SELECT wind_code, adddate(max(trade_date),1) trade_date FROM {table_name} GROUP BY wind_code) quertarly
ON info.wind_code = quertarly.wind_code
) tt
WHERE date_frm <= if(delist_date<end_date, delist_date, end_date)
ORDER BY wind_code;""".format(table_name=table_name)
else:
logger.warning('wind_stock_quertarly 不存在，仅使用 wind_stock_info 表进行计算日期范围')
sql_str = """
SELECT wind_code, date_frm, if(delist_date<end_date, delist_date, end_date) date_to
FROM
(
SELECT info.wind_code, ipo_date date_frm, delist_date,
if(hour(now())<16, subdate(curdate(),1), curdate()) end_date
FROM wind_stock_info info
) tt
WHERE date_frm <= if(delist_date<end_date, delist_date, end_date)
ORDER BY wind_code"""
with with_db_session(engine_md) as session:
# 获取每只股票需要获取日线数据的日期区间
table = session.execute(sql_str)
# 计算每只股票需要获取日线数据的日期区间
begin_time = None
# 获取date_from,date_to，将date_from,date_to做为value值
stock_date_dic = {
wind_code: (date_from if begin_time is None else min([date_from, begin_time]), date_to)
for wind_code, date_from, date_to in table.fetchall() if
wind_code_set is None or wind_code in wind_code_set}
# 获取股票量价等行情数据
param_list = [
('roic_ttm', String(20)),
('yoyprofit', String(20)),
('ebit', String(20)),
('ebit2', String(20)),
('ebit2_ttm', String(20)),
('surpluscapitalps', String(20)),
('undistributedps', String(20)),
('stm_issuingdate', String(20)),
]
# 获取参数列表
wind_indictor_str = ",".join(key for key, _ in param_list)
dtype = {key: val for key, val in param_list}
dtype['wind_code'] = String(20)
dtype['trade_date'] = Date
# # 标示每天几点以后下载当日行情数据
# BASE_LINE_HOUR = 16
# with with_db_session(engine_md) as session:
# # 获取每只股票最新交易日数据
# sql_str = 'select wind_code, max(Trade_date) from wind_stock_quertarly group by wind_code'
# table = session.execute(sql_str)
# stock_trade_date_latest_dic = dict(table.fetchall())
# # 获取市场有效交易日数据
# sql_str = "select trade_date from wind_trade_date where trade_date > '2005-1-1'"
# table = session.execute(sql_str)
# trade_date_sorted_list = [t[0] for t in table.fetchall()]
# trade_date_sorted_list.sort()
# # 获取每只股票上市日期、退市日期
# table = session.execute('SELECT wind_code, ipo_date, delist_date FROM wind_stock_info')
# stock_date_dic = {wind_code: (ipo_date, delist_date if delist_date is None or delist_date > UN_AVAILABLE_DATE else None) for
# wind_code, ipo_date, delist_date in table.fetchall()}
# date_ending = date.today() - ONE_DAY if datetime.now().hour < BASE_LINE_HOUR else date.today()
# data_df_list = []
# logger.info('%d stocks will been import into wind_stock_quertarly', len(stock_date_dic))
data_df_list = []
logger.info('%d stocks will been import into wind_stock_quertarly', len(stock_date_dic))
try:
for stock_num, (wind_code, (date_from, date_to)) in enumerate(stock_date_dic.items()):
# 获取股票量价等行情数据
# w.wsd("002122.SZ", "roic_ttm,yoyprofit,ebit,ebit2,ebit2_ttm,surpluscapitalps,undistributedps,stm_issuingdate", "2012-12-31", "2017-12-06", "unit=1;rptType=1;Period=Q")
data_df = invoker.wsd(wind_code, wind_indictor_str, date_from, date_to, "unit=1;rptType=1;Period=Q")
if data_df is None:
logger.warning('%d) %s has no data during %s %s', stock_num, wind_code, date_from, date_to)
continue
data_df.rename(columns={c: str(c).lower() for c in data_df.columns}, inplace=True)
# 清理掉期间全空的行
for trade_date in list(data_df.index[:10]):
is_all_none = data_df.loc[trade_date].apply(lambda x: x is None).all()
if is_all_none:
logger.warning("%s %s 数据全部为空", wind_code, trade_date)
data_df.drop(trade_date, inplace=True)
logger.info('%d) %d data of %s between %s and %s', stock_num, data_df.shape[0], wind_code, date_from,
date_to)
data_df['wind_code'] = wind_code
data_df.index.rename('trade_date', inplace=True)
data_df.reset_index(inplace=True)
data_df_list.append(data_df)
if DEBUG and len(data_df_list) > 10:
break
finally:
# 导入数据库
if len(data_df_list) > 0:
data_df_all = pd.concat(data_df_list)
bunch_insert_on_duplicate_update(data_df_all, table_name, engine_md, dtype=dtype)
logging.info("更新 wind_stock_quertarly 结束 %d 条信息被更新", data_df_all.shape[0])
if not has_table and engine_md.has_table(table_name):
alter_table_2_myisam(engine_md, [table_name])
build_primary_key([table_name])
class Table1( self.Entity ):
t1id = Field(Integer, primary_key=True)
name = Field(String(30))
tbl2s = OneToMany('Table2')
tbl3 = OneToOne('Table3')
class LastOnline(Base):
__tablename__ = 'last_online'
user_id = Column(Integer(), primary_key=True)
time = Column(Date(), primary_key=True)
last_status = Column(String())
class RouteStop(Base):
datasource = config.DATASOURCE_DERIVED
__tablename__ = 'route_stops'
route_id = Column(String(255),
primary_key=True,
index=True,
nullable=False)
direction_id = Column(Integer,
primary_key=True,
index=True,
nullable=False)
stop_id = Column(String(255), primary_key=True, index=True, nullable=False)
order = Column(Integer, index=True, nullable=False)
start_date = Column(Date, index=True, nullable=False)
end_date = Column(Date, index=True, nullable=False)
route = relationship('Route',
primaryjoin='RouteStop.route_id==Route.route_id',
foreign_keys='(RouteStop.route_id)',
uselist=False,
viewonly=True,
lazy='joined')
stop = relationship('Stop',
primaryjoin='RouteStop.stop_id==Stop.stop_id',
foreign_keys='(RouteStop.stop_id)',
uselist=False,
viewonly=True,
lazy='joined')
direction = relationship(
'RouteDirection',
primaryjoin=
'RouteStop.route_id==RouteDirection.route_id and RouteStop.direction_id==RouteDirection.direction_id',
foreign_keys='(RouteStop.route_id, RouteStop.direction_id)',
uselist=False,
viewonly=True,
lazy='joined')
start_calendar = relationship(
'UniversalCalendar',
primaryjoin='RouteStop.start_date==UniversalCalendar.date',
foreign_keys='(RouteStop.start_date)',
uselist=True,
viewonly=True)
end_calendar = relationship(
'UniversalCalendar',
primaryjoin='RouteStop.end_date==UniversalCalendar.date',
foreign_keys='(RouteStop.end_date)',
uselist=True,
viewonly=True)
def is_active(self, date=None):
""" :return False whenever we see that the route_stop's start and end date are
outside the input date (where the input date defaults to 'today')
"""
_is_active = False
if self.start_date and self.end_date:
if date is None:
date = datetime.date.today()
if self.start_date <= date <= self.end_date:
_is_active = True
return _is_active
@classmethod
def is_stop_active(cls, session, stop_id, agency_id=None, date=None):
""" returns boolean whether given stop id is active for a given date
"""
ret_val = False
# step 1: default date
if date is None or not isinstance(date, datetime.date):
date = datetime.date.today()
# step 2: get RouteStop object
rs = RouteStop.query_by_stop(session, stop_id, agency_id, date, 1)
if rs and len(rs) > 0:
ret_val = True
return ret_val
@classmethod
def query_by_stop(cls,
session,
stop_id,
agency_id=None,
date=None,
count=None,
sort=False):
""" get all route stop records by looking for a given stop_id.
further filtering can be had by providing an active date and agency id
"""
#import pdb; pdb.set_trace()
# step 1: query all route stops by stop id (and maybe agency)
q = session.query(RouteStop).filter(RouteStop.stop_id == stop_id)
if agency_id is not None:
q = q.filter(RouteStop.agency_id == agency_id)
# step 2: filter based on date
if date:
q = q.filter(RouteStop.start_date <= date).filter(
date <= RouteStop.end_date)
# step 3: limit the number of objects returned by query
if count:
q = q.limit(count)
# step 4: sort the results based on order column
if sort:
q = q.order_by(RouteStop.order)
ret_val = q.all()
return ret_val
@classmethod
def unique_routes_at_stop(cls,
session,
stop_id,
agency_id=None,
date=None,
route_name_filter=False):
""" get a unique set of route records by looking for a given stop_id.
further filtering can be had by providing an active date and agency id, and route name
"""
ret_val = []
route_ids = []
route_names = []
route_stops = RouteStop.query_by_stop(session,
stop_id,
agency_id,
date,
sort=True)
for rs in route_stops:
# step 1: filter(s) check
if rs.route_id in route_ids: continue
if route_name_filter and rs.route.route_name in route_names:
continue
route_ids.append(rs.route_id)
route_names.append(rs.route.route_name)
# step 2: append route object to results
ret_val.append(rs.route)
return ret_val
@classmethod
def active_unique_routes_at_stop(cls,
session,
stop_id,
agency_id=None,
date=None,
route_name_filter=False):
""" to filter active routes, just provide a date to the above unique_routes_at_stop method
"""
# make sure date is not null...
if date is None or not isinstance(date, datetime.date):
date = datetime.date.today()
return cls.unique_routes_at_stop(session, stop_id, agency_id, date,
route_name_filter)
@classmethod
def active_stops(cls,
session,
route_id,
direction_id=None,
agency_id=None,
date=None):
""" returns list of routes that are seen as 'active' based on dates and filters
"""
# step 1: default date
if date is None or not isinstance(date, datetime.date):
date = datetime.date.today()
# step 2a: query all route stops by route (and maybe direction and agency
q = session.query(RouteStop).filter(RouteStop.route_id == route_id)
if direction_id is not None:
q = q.filter(RouteStop.direction_id == direction_id)
if agency_id is not None:
q = q.filter(RouteStop.agency_id == agency_id)
# step 2b: filter based on date
q = q.filter(RouteStop.start_date <= date).filter(
date <= RouteStop.end_date)
# step 2c: add some stop order
q = q.order_by(RouteStop.order)
route_stops = q.all()
return route_stops
@classmethod
def load(cls, db, **kwargs):
log.debug('{0}.load (loaded later in post_process)'.format(
cls.__name__))
pass
@classmethod
def post_process(cls, db, **kwargs):
log.debug('{0}.post_process'.format(cls.__name__))
cls.populate(db.session)
@classmethod
def is_arrival(cls, session, trip_id, stop_id):
""" :return True if it looks like this Trip / Stop pair is an arrival only
NOTE: this routine might be EXPENSIVE since it is
Further, this routine isn't well thought out...not sure block.is_arrival() works
"""
_is_arrival = False
from gtfsdb import Block
blocks = Block.blocks_by_trip_stop(session, trip_id, stop_id)
if blocks:
for b in blocks:
if b.is_arrival():
#import pdb; pdb.set_trace()
_is_arrival = True
break
return _is_arrival
@classmethod
def populate(cls, session):
""" for each route/direction, find list of stop_ids for route/direction pairs
the load is a two part process, where part A finds a list of unique stop ids, and
part B creates the RouteStop (and potentially RouteDirections ... if not in GTFS) records
"""
from gtfsdb import Route, RouteDirection
start_time = time.time()
routes = session.query(Route).all()
for r in routes:
# step 0: figure out some info about the route
create_directions = False
if r.directions is None or len(r.directions) == 0:
create_directions = True
# step 1a: filter the list of trips down to only a trip with a unique pattern
trips = []
shape_id_filter = []
for t in r.trips:
# a bit of a speedup to filter trips that have the same shape
if t.shape_id and t.shape_id in shape_id_filter:
continue
# store our trips
shape_id_filter.append(t.shape_id)
trips.append(t)
# step 1b: sort our list of trips by length (note: for trips with two directions, ...)
trips = sorted(trips, key=lambda t: t.trip_len, reverse=True)
# step 2: get a hash table of route stops with effective start and end dates
stop_effective_dates = cls._find_route_stop_effective_dates(
session, r.route_id)
# PART A: we're going to just collect a list of unique stop ids for this route / directions
for d in [0, 1]:
unique_stops = []
# step 3: loop through all our trips and their stop times, pulling out a unique set of stops
for t in trips:
if t.direction_id == d:
# step 4: loop through this trip's stop times, and find any/all stops that are in our stop list already
# further, let's try to find the best position of that stop (e.g., look for where the stop patterns breaks)
last_pos = None
for i, st in enumerate(t.stop_times):
# step 5a: make sure this stop that customers can actually board...
if st.is_boarding_stop():
# step 5b: don't want arrival trips to influence route stop list
if st.stop_id in unique_stops:
last_pos = unique_stops.index(st.stop_id)
else:
# step 5b: add ths stop id to our unique list ... either in position, or appended to the end of the list
if last_pos:
last_pos += 1
unique_stops.insert(
last_pos, st.stop_id)
else:
unique_stops.append(st.stop_id)
print unique_stops
# PART B: add records to the database ...
if len(unique_stops) > 0:
# step 6: if an entry for the direction doesn't exist, create a new
# RouteDirection record and add it to this route
if create_directions:
rd = RouteDirection()
rd.route_id = r.route_id
rd.direction_id = d
rd.direction_name = "Outbound" if d == 0 else "Inbound"
session.add(rd)
# step 7: create new RouteStop records
for k, stop_id in enumerate(unique_stops):
# step 7: create a RouteStop record
rs = RouteStop()
rs.route_id = r.route_id
rs.direction_id = d
rs.stop_id = stop_id
rs.order = k + 1
rs.start_date = stop_effective_dates[stop_id][1]
rs.end_date = stop_effective_dates[stop_id][2]
session.add(rs)
# step 8: commit the new records to the db for this route...
sys.stdout.write('*')
session.commit()
# step 9: final commit for any stragglers
session.commit()
session.flush()
session.close()
processing_time = time.time() - start_time
log.debug('{0}.post_process ({1:.0f} seconds)'.format(
cls.__name__, processing_time))
@classmethod
def _find_route_stop_effective_dates(cls, session, route_id):
""" find effective start date and end date for all stops of the input route, when
queried against the trip and stop time tables. Below are a couple of pure SQL queries that
perform what I'm doing to get said start and end dates:
# query all route stops with start & end dates
SELECT t.route_id, st.stop_id, min(date), max(date)
FROM ott.universal_calendar u, ott.trips t, ott.stop_times st
WHERE t.service_id = u.service_id
AND t.trip_id = st.trip_id
GROUP BY t.route_id, st.stop_id
# query all stops start & end dates for a given route (used below in SQLAlchemy)
SELECT st.stop_id, min(date), max(date)
FROM ott.universal_calendar u, ott.trips t, ott.stop_times st
WHERE t.service_id = u.service_id
AND t.trip_id = st.trip_id
AND st.stop_id = '1'
GROUP BY st.stop_id
@:return hash table with stop_id as key, and tuple of (stop_id, start_date, end_date) for all route stops
"""
#import pdb; pdb.set_trace()
ret_val = {}
# step 1: query the route/stop start and end dates, based on stop time table
from gtfsdb import UniversalCalendar, StopTime, Trip
q = session.query(StopTime.stop_id, func.min(UniversalCalendar.date),
func.max(UniversalCalendar.date))
q = q.filter(UniversalCalendar.service_id == Trip.service_id)
q = q.filter(Trip.trip_id == StopTime.trip_id)
q = q.filter(Trip.route_id == route_id)
q = q.group_by(StopTime.stop_id)
stop_dates = q.all()
# step 2: make a hash of these dates with the stop id as the key
for d in stop_dates:
ret_val[d[0]] = d
return ret_val