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