class Node(Base): __tablename__ = 'nodes' id = Column(INTEGER(unsigned=True), Sequence('node_id_seq'), primary_key=True) mcc = Column(SMALLINT(unsigned=True), nullable=False) mnc = Column(SMALLINT(unsigned=True), nullable=False) node_id = Column(INTEGER(unsigned=True), nullable=False) lat = Column(DECIMAL(8, 6), nullable=False) lng = Column(DECIMAL(9, 6), nullable=False) mean_lat = Column(DECIMAL(8, 6)) mean_lng = Column(DECIMAL(9, 6)) samples = Column(MEDIUMINT(unsigned=True)) created = Column(INTEGER(unsigned=True)) updated = Column(INTEGER(unsigned=True)) def __repr__(self): return "<Node(mcc='%s', mnc='%s' id='%s', enb='%s')>" % (self.mcc, self.mnc, self.id, self.node_id) __table_args__ = ( Index('nodes_index', 'mcc', 'mnc', 'node_id'), UniqueConstraint('mcc', 'mnc', 'node_id', name='unique_node') )
def init_stock_price_monthly(ts_code, force=None): table_name = 'stock_price_monthly' if not need_pull_check(ts_code, table_name, force): print('need not 2 pull {} -> {}'.format(table_name, ts_code)) return else: print('start 2 pull {} -> {} .'.format(table_name, ts_code)) df = get_pro().monthly( ts_code=ts_code, fields='ts_code,trade_date,open,high,low,close,vol,amount') if not len(df): return df_add_y_m(df, 'trade_date') dtype = { 'ts_code': VARCHAR(length=10), 'trade_date': DATE(), 'y': INT, 'm': INT, 'open': DECIMAL(precision=8, scale=2), 'high': DECIMAL(precision=8, scale=2), 'low': DECIMAL(precision=8, scale=2), 'close': DECIMAL(precision=8, scale=2), 'vol': BIGINT(), 'amount': BIGINT() } df.to_sql(table_name, get_engine(), dtype=dtype, index=False, if_exists='append')
class Building(Base, Entity): __tablename__ = "Building" id = Column('id', BIGINT, primary_key=True, autoincrement=True) name = Column('name', VARCHAR(255), unique=True, nullable=False) latitude = Column('latitude', DECIMAL(10, 8), nullable=False) longitude = Column('longitude', DECIMAL(11, 8), nullable=False) def __init__(self, id: int=None, name: str=None, latitude: Decimal=None, longitude: Decimal=None): self.id = id self.name = name self.latitude = latitude self.longitude = longitude @classmethod def add(cls, name: str, latitude: Decimal, longitude: Decimal) -> 'Building': building = Building(name=name, latitude=latitude, longitude=longitude) db.session.add(building) db.session.commit() db.session.refresh(building) return building @classmethod def get_by_name(cls, name: str) -> Optional['Building']: return db.session.query(cls).filter_by(name=name).one_or_none()
class HKOddsModel(db.Model): __tablename__ = "hk_odds" id = Column(BigInteger, primary_key = True, autoincrement = True, nullable = False) # Unique ID race_date = Column("racedate", Date, nullable = False) # Race date. race_course_code = Column("racecoursecode", Unicode(2)) # Race course code. race_number = Column("racenumber", Integer) # Race number. horse_number = Column("horsenumber", Integer, nullable = False) # Horse number. update_date_time = Column("updatedate", DateTime, nullable = False) # Date and time of last update. win_odds = Column("winodds", DECIMAL(10,2)) # Odds of win. It's not float! Float for money is not acceptable! is_win_fav = Column("isWinFav", Integer) # Some times this parameter can takes value 2 and more. place_odds = Column("placeodds", DECIMAL(10,2)) # The same as Win odds. is_place_fav = Column("isPlaceFav", Integer) # The same as is_win_fav. pool = Column("pool", BigInteger) # Pool number. is_reserve = Column("isReserve", Boolean) # Reserve data. is_scratched = Column("isScratched", Boolean) # Scratched data. def __init__(self, race_date, race_course_code, race_number, horse_number, update_date_time, win_odds, is_win_fav, place_odds, is_place_fav, pool, is_reserve = False, is_scratched = False): self.race_date = race_date self.race_course_code = race_course_code self.race_number = race_number self.horse_number = horse_number self.update_date_time = update_date_time self.win_odds = win_odds self.is_win_fav = is_win_fav self.place_odds = place_odds self.is_place_fav = is_place_fav self.pool = pool self.is_reserve = is_reserve self.is_scratched = is_scratched
def save_okex_spot_kline(self,okbkline,tablename,duplicates): if len(okbkline) == 0: print("[save_okex_spot_kline]okbkline is null.........") return if(duplicates): list1 = '"{}"'.format('","'.join(str(i) for i in okbkline["time"].values.tolist())) query_sql='''SELECT * FROM {} where time in ({});'''.format(tablename,list1) #print(query_sql) df = pd.read_sql(sql=query_sql, con=self.engine) df['ts'] = df['ts'].astype('int64') temp=okbkline.append(df) temp2 = temp.append(df) diff = temp2.drop_duplicates(subset=['ts'], keep=False) else: diff=okbkline dtypedict = {'instrument_id': TEXT, 'open': DECIMAL(18, 8), 'high': DECIMAL(18, 8), 'low': DECIMAL(18, 8), 'close': DECIMAL(18, 8), 'volume': DECIMAL(18, 8), 'time': DateTime,'ts': TEXT} diff.to_sql(name=tablename, con=self.engine, chunksize=1000, if_exists='append', index=None,dtype=dtypedict)
class Sector(Base): __tablename__ = 'sectors' id = Column(INTEGER(unsigned=True), Sequence('sector_id_seq'), primary_key=True) mcc = Column(SMALLINT(unsigned=True), nullable=False) mnc = Column(SMALLINT(unsigned=True), nullable=False) node_id = Column(INTEGER(unsigned=True), nullable=False) sector_id = Column(TINYINT(unsigned=True), nullable=False) pci = Column(SMALLINT(unsigned=True), default=-1, nullable=False) lat = Column(DECIMAL(8, 6)) lng = Column(DECIMAL(9, 6)) range = Column(MEDIUMINT(unsigned=True)) samples = Column(MEDIUMINT(unsigned=True)) created = Column(INTEGER(unsigned=True)) updated = Column(INTEGER(unsigned=True)) def __repr__(self): return "<Sector(id='%s', enb='%s', sector='%s')>" % (self.id, self.node_id, self.sector_id) __table_args__ = ( Index('sectors_index', 'mcc', 'mnc', 'node_id', 'sector_id'), UniqueConstraint('mcc', 'mnc', 'node_id', 'sector_id', name='unique_sector'), # ForeignKeyConstraint(['mcc', 'mnc', 'node_id',], ['nodes.mcc', 'nodes.mnc', 'nodes.node_id']) )
class Dividends(Base): __tablename__ = 'dividends' id = Column(Integer, primary_key=True) date = Column(Date, index=True) name = Column(String(80), index=True) strategy = Column(String(80), index=True) portfolio = Column(String(80), index=True) book = Column(String(80), index=True) qty = Column(DECIMAL(40, 8)) dps = Column(DECIMAL(40, 8)) amount = Column(DECIMAL(40, 8))
class CashFlowReport(Base): __tablename__ = 'cash_flow_report' __table_args__ = {"useexisting": True} ID = Column(VARCHAR(32), primary_key=True) COMPCODE = Column(BigInteger) PUBLISHDATE = Column(DATE) ENDDATE = Column(DATE) REPORTTYPE = Column(Integer) REPORTYEAR = Column(Integer) REPORTDATETYPE = Column(Integer) MANANETR = Column(DECIMAL(19, 4)) INVNETCASHFLOW = Column(DECIMAL(19, 4)) FINNETCFLOW = Column(DECIMAL(19, 4)) CASHNETR = Column(DECIMAL(19, 4)) FINALCASHBALA = Column(DECIMAL(19, 4)) BIZNETCFLOW = Column(DECIMAL(19, 4)) CASHNETI = Column(DECIMAL(19, 4)) LABORGETCASH = Column(DECIMAL(19, 4)) MINYSHARRIGH = Column(Float(53)) INCOTAXEXPE = Column(Float(53)) TMSTAMP = Column(Integer) creat_time = Column(DATE) update_time = Column(DATE) __pit_column__ = { 'pub_date': PUBLISHDATE, 'filter_date': ENDDATE, 'index': COMPCODE }
class Balances(Base): __tablename__ = "balances" id = Column("id", Integer, primary_key=True, index=True) account_id = Column("account_id", String, ForeignKey("accounts.account_id"), nullable=False) available = Column("available", DECIMAL(precision=11, scale=2), nullable=True) current = Column("current", DECIMAL(precision=11, scale=2), nullable=False) limit = Column("limit", DECIMAL(precision=11, scale=2), nullable=True) currency = Column("currency", String, nullable=False) timestamp = Column("timestamp", DateTime, nullable=False) account = relationship("Accounts", back_populates="balances")
def run(): df = pd.read_csv('./cleanfile.csv', encoding='utf-8', sep=',') df["sentiments"] = df["content"].map( lambda c: snownlp.SnowNLP(c).sentiments) df["keywords"] = df["content"].map(getKeyWord) df["input_time"] = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S') #engine = create_engine('mysql+pymysql://root:@127.0.0.1:3306/sina') engine = create_engine('mysql+mysqlconnector://root:@127.0.0.1:3306/sina') dtypedict = { 'id': Integer(), 'mid': VARCHAR(length=50), 'content': TEXT, 'uid': VARCHAR(length=15), 'area': VARCHAR(length=15), 'nick': VARCHAR(length=50), 'ip': VARCHAR(length=15), 'newsid': VARCHAR(length=50), 'time': DATETIME(), 'sentiments': DECIMAL('10,10'), 'keywords': VARCHAR(length=100), 'input_time': DATETIME(), } df.to_sql(name='news', con=engine, chunksize=100000, if_exists='replace', index=True, index_label='id', dtype=dtypedict)
class TanseferLog(BaseModel): __tablename__ = 'transfer_log' id = Column(Integer, primary_key=True) from_user = Column(Integer, ForeignKey('user.id', ondelete='CASCADE', onupdate='CASCADE')) to_user = Column(Integer, ForeignKey('user.id', ondelete='CASCADE', onupdate='CASCADE')) amount = Column(DECIMAL(10, 2))
class Livro(db.Model): id = db.Column(db.Integer, primary_key=True) isbn = db.Column(db.Integer, unique=True) titulo = db.Column(db.String(200)) categoria = db.Column(db.String(100)) edicao = db.Column(db.Integer) ano = db.Column(db.Integer) descricao = db.Column(db.Text) id_autor = db.Column(db.Integer, db.ForeignKey(Autor.id)) id_editora = db.Column(db.Integer, db.ForeignKey(Editora.id)) preco = db.Column(DECIMAL(10, 2)) #item = db.relationship('Item', uselist = False, back_populates = 'livro') #catalogo define se o livro está ou não no catálogo #0 não está, 1 está catalogo = db.Column(db.Integer) def __init__(self, isbn, titulo, categoria, edicao, ano, descricao, id_autor, id_editora, preco, catalogo): self.isbn = isbn self.titulo = titulo self.categoria = categoria self.edicao = edicao self.ano = ano self.descricao = descricao self.id_autor = id_autor self.id_editora = id_editora self.preco = preco self.catalogo = catalogo
def run(): df = pd.read_csv('./cleanfile.csv', encoding='utf-8', sep=',') df["sentiments"] = df["content"].map( lambda c: snownlp.SnowNLP(c).sentiments) df["keywords"] = df["content"].map(getKeyWord) #engine = create_engine('mysql+pymysql://root:@127.0.0.1:3306/sina') engine = create_engine( 'mysql+mysqlconnector://root:[email protected]:3306/sina?charset=utf8&connect_timeout=10' ) dtypedict = { 'id': Integer(), 'uid': VARCHAR(length=15), 'area': VARCHAR(length=15), 'ipadd': VARCHAR(length=15), 'usertype': VARCHAR(length=10), 'agree': VARCHAR(length=10), 'cmttime': DATETIME(), 'content': TEXT, 'sentiments': DECIMAL('10,10'), 'keywords': VARCHAR(length=100), } df.to_sql(name='news', con=engine, chunksize=100000, if_exists='replace', index=True, index_label='id', dtype=dtypedict)
class Ranking(Base): __tablename__ = "ranking" id: int = Column(Integer, primary_key=True, autoincrement=True) institution_id: int = Column(Integer, ForeignKey("institution.id")) ranking_system: RankingSystemEnum = Column( Enum(RankingSystemEnum), nullable=False, index=True ) ranking_type: RankingTypeEnum = Column( Enum(RankingTypeEnum), nullable=False, index=True ) year: int = Column(Integer) field: str = Column(String(255), nullable=False) subject: str = Column(String(255), nullable=False) metric: MetricEnum = Column(Enum(MetricEnum), nullable=False, index=True) value: Decimal = Column(DECIMAL(13, 3)) value_type: ValueTypeEnum = Column(Enum(ValueTypeEnum), nullable=False) # Relationships institution: Institution = relationship( "Institution", back_populates="rankings" ) def __init__(self, **kwargs): kwargs = {k: v for k, v in kwargs.items() if k in self.__table__.c} super().__init__(**kwargs) def __repr__(self): return ( f"{self.ranking_system.name} ({self.year}) | " + f"{self.field} ({self.subject}) -> " + f"{self.metric.name}: {self.value}" )
class OrderDetailDAO(BaseModel): __tablename__ = 'order_detail' id = Column(Integer, primary_key=True) order_id = Column(Integer) food_id = Column(Integer) amount = Column(Integer) price = Column(DECIMAL(5, 2)) created_at = Column(TIMESTAMP(timezone=False), nullable=False, server_default=func.current_timestamp()) @hybrid_method def add(self, session): try: self._commit(session, self) return self except Exception as e: session.rollback() raise e @hybrid_method def food(self, session): return FoodDAO.byID(session, self.food_id) @hybrid_method def by_order_id(cls, session, order_id): query = session.query(cls).filter(cls.order_id== order_id) return query.all()
class Blotter(Base): __tablename__ = 'blotter' id = Column(Integer, primary_key=True) date = Column(Date, index=True) account = Column(String(80)) name = Column(String(80), index=True) strategy = Column(String(80), index=True) price = Column(DECIMAL(40, 8)) qty = Column(BIGINT) price_qty = Column(DECIMAL(40, 8)) action = Column(String(80)) execution_status = Column(String(80)) fees = Column(DECIMAL(40, 8)) amount = Column(DECIMAL(40, 8)) portfolio = Column(String(80), index=True) book = Column(String(80), index=True)
class MovieSerieModel(db.Model): __tablename__ = 'tb_movies_series' __table_args__ = {'extend_existing': True} id_movie = db.Column(db.Integer, primary_key=True) tconst = db.Column(db.String(10)) titleType = db.Column(db.String(12)) primaryTitle = db.Column(db.String(300)) originalTitle = db.Column(db.String(300)) isAdult = db.Column(db.Integer) startYear = db.Column(db.String(4)) endYear = db.Column(db.String(4)) runtimeMinutes = db.Column(db.String(5)) genres = db.Column(db.String(50)) averageRating = db.Column(DECIMAL(3, 1)) numVotes = db.Column(db.Integer) nombres_actores = db.Column(db.String(300)) nombres_directores = db.Column(db.String(200)) review = db.Column(db.String(25000)) plot = db.Column(db.String(2500)) keywords = db.Column(db.String(300)) def __repr__(self): return '<Movie/Serie {} {} {}>'.format(self.originalTitle, self.genre, self.averageRating)
def _init_ts(self): y_start = 1990 __pool = ThreadPoolExecutor(max_workers=MULTIPLE, thread_name_prefix="test_") fs = [] i = 0 for y_i in range(31)[::-1]: y = y_start + y_i first, last = dao.get_trade_date(y, 0) if not first: continue print("{}-{}".format(y, 0)) first = first.strftime('%Y%m%d') last = last.strftime('%Y%m%d') f1 = __pool.submit(get_pro().index_weight, index_code=self.biz_code, start_date=first, end_date=first) f2 = __pool.submit(get_pro().index_weight, index_code=self.biz_code, start_date=last, end_date=last) fs.append(f1) fs.append(f2) i += 2 if i > 197: print('198次后休息60秒') time.sleep(60) i = 0 df = None for f2 in fs: temp_df = f2.result() if len(temp_df): if df is None: df = temp_df else: df = df.append(temp_df, ignore_index=True) df_add_y_m(df, 'trade_date') dtype = { 'index_code': VARCHAR(length=10), 'con_code': VARCHAR(length=10), 'y': INT, 'm': INT, 'trade_date': DATE(), 'weight': DECIMAL(precision=10, scale=6) } df = df.reindex( columns='index_code,con_code,y,m,trade_date,weight'.split(',')) df.to_sql(self.get_table_name(), get_engine(), dtype=dtype, index=False, if_exists='append')
def addCdmMaOffer(self, contacts_with_offers_and_answers, ВхТабл): sel_offers_id=select([cast(contacts_with_offers_and_answers.c.offer_id,DECIMAL)]).\ where(contacts_with_offers_and_answers.c.offer_id!=None) cdm_ma_offer=select([getattr(ВхТабл.inp_cdm_ma_offer.c,i) for i in ['participant_id','product_cd','offer_id']]).\ where(ВхТабл.inp_cdm_ma_offer.c.offer_id.in_(sel_offers_id)).alias('cdm_ma_offer') j=outerjoin(contacts_with_offers_and_answers,cdm_ma_offer, cast(contacts_with_offers_and_answers.c.offer_id,DECIMAL(18,0))\ ==cast(cdm_ma_offer.c.offer_id,DECIMAL(18,0))) j2 = j.outerjoin( ВхТабл.ma_product_dict, j.c.cdm_ma_offer_product_cd == ВхТабл.ma_product_dict.c.product_cd) self.Таблица_запросов[2]=(select(contacts_with_offers_and_answers.c+\ [j2.c.cdm_ma_offer_product_cd,j2.c.etl_data_mart_cdm_ma_product_dict_product_name]).\ select_from(j2).alias('from_cdm'),'Таблица c подтянутыми cdm_ma_offer')
class MarketDividends(Base): __tablename__ = 'market_dividends' id = Column(Integer, primary_key=True) date = Column(Date, index=True) ex_date = Column(Date, index=True) name = Column(String(80), index=True) dividend_amount = Column(DECIMAL(40, 8)) witholding_tax = Column(BIGINT)
class AutoloanFlat(AssetBase): """ Static auto loan class. Only contains fields that do not change. """ __tablename__ = 'autoloans_flat' trustAssetNumber = Column(String(64), primary_key=True, nullable=False, unique=True) dateFirstFiling = Column(Date) trustCik = Column(Integer) assetNumber = Column(String(25)) originationDate = Column(Date) originalLoanAmount = Column(DECIMAL(20, 8)) originalLoanTerm = Column(Integer) loanMaturityDate = Column(Date) originalInterestRatePercentage = Column(DECIMAL(20, 8)) underwritingIndicator = Column(Boolean) gracePeriodNumber = Column(Integer) subvented = Column(String(255)) vehicleManufacturerName = Column(String(30)) vehicleModelName = Column(String(30)) vehicleNewUsedCode = Column(String(255)) vehicleModelYear = Column(String(4)) vehicleTypeCode = Column(String(255)) vehicleValueAmount = Column(DECIMAL(20, 8)) obligorCreditScore = Column(String(20)) obligorIncomeVerificationLevelCode = Column(String(255)) obligorEmploymentVerificationCode = Column(String(255)) coObligorIndicator = Column(Boolean) paymentToIncomePercentage = Column(DECIMAL(20, 8)) obligorGeographicLocation = Column(String(100)) zeroBalanceEffectiveDate = Column(Date) zeroBalanceCode = Column(String(255)) delinquency30Days = Column(Date) delinquency90Days = Column(Date) repossessedIndicator = Column(Boolean) repossessedDate = Column(Date) updatable_fields = [ 'zeroBalanceEffectiveDate', 'zeroBalanceCode', 'delinquency30Days', 'delinquency90Days', 'repossessedIndicator', 'repossessedDate' ]
def save_okex_spot_accounts(spotAccounts, engine): dtypedict = { 'frozen': DECIMAL(18, 8), 'hold': DECIMAL(18, 8), 'id': TEXT, 'currency': TEXT, 'balance': DECIMAL(18, 8), 'available': DECIMAL(18, 8), 'holds': DECIMAL(18, 8), 'data': DateTime, 'ts': TEXT } spotAccounts.to_sql(name='account_okex_spot', con=engine, chunksize=1000, if_exists='append', index=None, dtype=dtypedict)
def _second_process(self): table_name = self._interface + '_stat' df = self._df grouped = df.groupby('y') r = grouped[['stk_div', 'cash_div']].agg([np.sum]) r = r.reset_index() r = r.rename(columns={('stk_div', 'sum'): 'stk_div', ('cash_div', 'sum'): 'cash_div', ('y'): 'y'}) r = r.sort_values(by=['y'], ascending=False) data = {'ts_code': np.full((len(r)), self._key), 'y': r['y'], 'stk_div': r[('stk_div', 'sum')], 'cash_div': r[('cash_div', 'sum')]} df = pd.DataFrame(data) dtype = {'ts_code': VARCHAR(length=10), 'end_date': DATE(), 'y': INT(), 'stk_div': DECIMAL(precision=10, scale=8), 'cash_div': DECIMAL(precision=12, scale=8)} df.to_sql(table_name, get_engine(), dtype=dtype, index=False, if_exists='append')
def addOffersAndAnswers(self, s1, ВхТабл): #джойн с contact_x_offer j = outerjoin( s1, ВхТабл.contact_x_offer, s1.c.contact_id == cast(ВхТабл.contact_x_offer.c.contact_id, DECIMAL(18, 0))) contacts_with_offers=select([getattr(s1.c,i) for i in [i.name for i in s1.columns]]+\ [cast(ВхТабл.contact_x_offer.c.offer_id,BIGINT).label('offer_id'), ВхТабл.contact_x_offer.c.campaign_cd]).select_from(j) cols_from_customer_reactions = [ 'AGREE_RESP', 'THINK_RESP', 'DELAY_RESP', 'SOUNDED_RESP', 'DECLINE_RESP' ] cols_from_customer_reactions = [ i.lower() for i in cols_from_customer_reactions ] self.contacts_with_offers = contacts_with_offers contacts_with_offers = contacts_with_offers.alias('cwo') j = outerjoin( contacts_with_offers, ВхТабл.customer_reactions, and_( cast(contacts_with_offers.c.offer_id, DECIMAL(18, 0)) == cast(ВхТабл.customer_reactions.c.offer_id, DECIMAL(18, 0)), cast(contacts_with_offers.c.customer_id, DECIMAL(18, 0)) == cast( ВхТабл.customer_reactions.c.gold_customer_id, DECIMAL(18, 0)))) contacts_with_offers_and_answers=select(contacts_with_offers.c+[cast(getattr(ВхТабл.customer_reactions.c,i),INT).label(i) if i!='operator_office' else getattr(ВхТабл.customer_reactions.c,i) for i in cols_from_customer_reactions]).select_from(j).\ alias('c1') self.Таблица_запросов[1] = ( contacts_with_offers_and_answers, 'Таблица с подтянутыми ответами клиентов из customer_reactions и офферами' )
def write_into_db(self, customer_id, strategy_id, weight_hist, weight_dates, rebalance_hist, rebalance_dates, net_value_record): ''' 写入数据库. ''' weight_df = pd.DataFrame(list( zip(weight_dates, [json.dumps(weight) for weight in weight_hist])), columns=['trade_date', 'position_weight']) rebalance_df = pd.DataFrame(list( zip(rebalance_dates, [json.dumps(rebalance) for rebalance in rebalance_hist])), columns=['trade_date', 'rebalance']) net_value_record['customer_id'] = customer_id weight_df['customer_id'] = customer_id rebalance_df['customer_id'] = customer_id net_value_record['strategy_id'] = strategy_id weight_df['strategy_id'] = strategy_id rebalance_df['strategy_id'] = strategy_id conn_engine = create_engine( 'mssql+pymssql://DBadmin:[email protected]/GeniusBar') net_value_record.reset_index().to_sql('strategy_net_value', conn_engine, if_exists='append', index=False, dtype={ 'customer_id': BIGINT, 'strategy_id': VARCHAR(32), 'trade_date': VARCHAR(32), 'net_value': DECIMAL(20, 6) }) weight_df.to_sql('strategy_weight', conn_engine, if_exists='append', index=False, dtype={ 'customer_id': BIGINT, 'strategy_id': VARCHAR(32), 'trade_date': VARCHAR(32), 'position_weight': TEXT }) rebalance_df.to_sql('strategy_rebalance', conn_engine, if_exists='append', index=False, dtype={ 'customer_id': BIGINT, 'strategy_id': VARCHAR(32), 'trade_date': VARCHAR(32), 'rebalance': TEXT })
def save_okex_spot_fills(spot_fills, engine): dtypedict = { 'ledger_id': TEXT, 'trade_id': TEXT, 'instrument_id': TEXT, 'price': DECIMAL(18, 8), 'size': TEXT, 'order_id': TEXT, 'timestamp': DateTime, 'exec_type': TEXT, 'fee': DECIMAL(18, 8), 'side': TEXT, 'currency': TEXT } spot_fills.to_sql(name='account_okex_spot_fill', con=engine, chunksize=1000, if_exists='append', index=None, dtype=dtypedict)
class Taxrate(BASE): """ Taxrate """ __tablename__ = "taxrate" id = Column(Integer, primary_key=True) rate = Column(DECIMAL(14, 3)) type = Column(Integer) label = Column(String) created_at = Column(String) updated_at = Column(String)
class UserLocation(Base): __tablename__ = 'UserLocation' user_id = Column('user_id', BIGINT, ForeignKey('User.id'), nullable=False, primary_key=True) time = Column('time', DateTime, nullable=False, default=datetime.datetime.utcnow, primary_key=True) latitude = Column('latitude', DECIMAL(10, 8), nullable=False) longitude = Column('longitude', DECIMAL(11, 8), nullable=False) user = relationship('User', foreign_keys=[user_id], backref='user') def __init__(self, id: int=None, user: int=None, time: int=None, lat: float=None, long: float=None): self.id = id self.user_id = user self.time = time self.latitude = lat self.longitude = long @classmethod def most_recent_for_user(cls, session, id: int) -> 'UserLocation': return session.query(UserLocation)\ .filter_by(user_id=id)\ .order_by(desc('time'))\ .first()
def save_okex_asset_valuation(asset_valuation, engine): dtypedict = { 'account_type': TEXT, 'balance': DECIMAL(18, 8), 'valuation_currency': TEXT, 'timestamp': DateTime, 'ts': TEXT } asset_valuation.to_sql(name='account_okex_asset_valuation', con=engine, chunksize=1000, if_exists='append', index=None, dtype=dtypedict)
def addTelecardContacts(self, ВхТабл): lk_MOBAPP_OFFER_HISTORY = Table('cdm_mobapp_offer_history'.lower(), Base.metadata, schema='etl_data_mart', autoload=True, autoload_with=engine) s1 = select([ cast(lk_MOBAPP_OFFER_HISTORY.c.gold_customer_id, DECIMAL(18, 0)).label('gold_customer_id'), cast(lk_MOBAPP_OFFER_HISTORY.c.creation_dt, TIMESTAMP).label('creation_dt'), cast(lk_MOBAPP_OFFER_HISTORY.c.offer_id, BIGINT).label('offer_id'), lk_MOBAPP_OFFER_HISTORY.c.product_cd, lk_MOBAPP_OFFER_HISTORY.c.campaign_cd ]) s1 = s1.distinct().select_from(lk_MOBAPP_OFFER_HISTORY) s1 = s1.where( and_( lk_MOBAPP_OFFER_HISTORY.c.product_cd != 'Rejected', lk_MOBAPP_OFFER_HISTORY.c.product_cd != 'Convert')).alias('t1') j=outerjoin(s1,ВхТабл.main_client_register,ВхТабл.main_client_register.c.gold_customer_id==s1.c.gold_customer_id).\ outerjoin(ВхТабл.branch_dict,ВхТабл.main_client_register.c.branch_entity_id==ВхТабл.branch_dict.c.branch_id) mob_app = select([ text('"mob_app" AS channel_cd'), text('"SENT" AS contact_history_status_cd'), text( '10000000000+ROW_NUMBER() OVER(ORDER BY t1.gold_customer_id,creation_dt) AS contact_id' ), text('0 AS ctrl_grp_flg'), cast(s1.c.gold_customer_id, INT).label('customer_id'), text('"SENT" AS delivery_status'), text('NULL AS participant_id'), s1.c.creation_dt.label('contact_dttm'), ВхТабл.branch_dict.c.branch_id, ВхТабл.branch_dict.c.branch_nm, cast(s1.c.offer_id, INT).label('offer_id'), s1.c.campaign_cd, text('NULL AS agree_resp'), text('NULL AS think_resp'), text('NULL AS delay_resp'), text('NULL AS sounded_resp'), text('NULL AS decline_resp'), text('NULL AS operator_office'), s1.c.product_cd, s1.c.product_cd.label('product_name'), s1.c.product_cd.label('campaign_nm'), text('"No product" AS crm_prod_nm') ]).select_from(j) self.telecard_query = """INSERT INTO sbx_005.lk_del3 """ + cmp( mob_app, ress=True)