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)