def edge_val_recent_join(wheres=None):
hirev = select(
[
table['edge_val'].c.graph,
table['edge_val'].c.nodeA,
table['edge_val'].c.nodeB,
table['edge_val'].c.idx,
table['edge_val'].c.key,
table['edge_val'].c.branch,
func.MAX(table['edge_val'].c.rev).label('rev')
]
)
if wheres:
hirev = hirev.where(
and_(*wheres)
)
hirev = hirev.group_by(
table['edge_val'].c.graph,
table['edge_val'].c.nodeA,
table['edge_val'].c.nodeB,
table['edge_val'].c.idx,
table['edge_val'].c.key,
table['edge_val'].c.branch
).alias('hirev')
return table['edge_val'].join(
hirev,
and_(
table['edge_val'].c.graph == hirev.c.graph,
table['edge_val'].c.nodeA == hirev.c.nodeA,
table['edge_val'].c.nodeB == hirev.c.nodeB,
table['edge_val'].c.idx == hirev.c.idx,
table['edge_val'].c.branch == hirev.c.branch,
table['edge_val'].c.rev == hirev.c.rev
)
)
def node_val_hirev_join(wheres):
hirev = select(
[
table['node_val'].c.graph,
table['node_val'].c.node,
table['node_val'].c.branch,
table['node_val'].c.key,
func.MAX(table['node_val'].c.rev).label('rev')
]
).where(and_(*wheres)).group_by(
table['node_val'].c.graph,
table['node_val'].c.node,
table['node_val'].c.branch,
table['node_val'].c.key
).alias('hirev')
return table['node_val'].join(
hirev,
and_(
table['node_val'].c.graph == hirev.c.graph,
table['node_val'].c.node == hirev.c.node,
table['node_val'].c.key == hirev.c.key,
table['node_val'].c.branch == hirev.c.branch,
table['node_val'].c.rev == hirev.c.rev
)
)
def load_company_share(company_ids=None, date=None):
engine = database.connection('caihui')
metadata = MetaData(bind=engine)
t = Table('tq_sk_sharestruchg', metadata, autoload=True)
columns = [
t.c.COMPCODE.label('company_id'),
t.c.TOTALSHARE.label('total_share'),
t.c.FCIRCAAMT.label('free_float_share')
]
s = select(columns)
if company_ids is not None:
s = s.where(t.c.COMPCODE.in_(company_ids))
if date is None:
columns2 = [
t.c.COMPCODE,
func.MAX(t.c.BEGINDATE)
]
s2 = select(columns2).group_by(t.c.COMPCODE)
s = s.where(tuple_(t.c.COMPCODE, t.c.BEGINDATE).in_(s2))
else:
s = s.where(t.c.BEGINDATE<=date)
s = s.where(or_(t.c.ENDDATE>=date, t.c.ENDDATE=='19000101'))
df = pd.read_sql(s, engine, index_col=['company_id'])
return df
def get_transaction(self, session):
if self.current_transaction is None:
trn_id = session.query(func.MAX(Transaction.id)).one()[0]
if trn_id is None:
trn_id = 0
else:
trn_id += 1
self.current_transaction = Transaction(trn_id)
session.add(self.current_transaction)
return self.current_transaction
def top_contributors_by_month(self):
grouped_sigs = self.session.query(
Signature.signer_key.label('key'),
Signature.signer_name.label('name'),
Signature.signer_email.label('email'),
func.strftime('%Y-%m', Signature.sign_date).label('sign_month'),
func.COUNT(Signature.id).label('num_sigs')).join(Key).filter(
Signature.signer_key != self.ca_key).group_by(
'key', 'sign_month').subquery()
return self.session.query(
grouped_sigs.c.key,
grouped_sigs.c.name,
grouped_sigs.c.email,
grouped_sigs.c.sign_month,
grouped_sigs.c.num_sigs
).group_by(grouped_sigs.c.sign_month).having(grouped_sigs.c.num_sigs==func.MAX(grouped_sigs.c.num_sigs)).\
order_by(desc(grouped_sigs.c.sign_month)).limit(24).all()
def desc_temps_2(date1, date2):
return session.query(func.MAX(Measurement.tobs), func.MIN(Measurement.tobs), func.AVG(Measurement.tobs)).\
filter(Measurement.date >= date1).filter(Measurement.date <= date2).all()