def newly_added_trials(self, cutoff_days):
studies = self.studies()
conditions = self.conditions()
keywords = self.keywords()
terms = [
'cancer', 'neoplasm', 'tumor', 'tumour', 'malignan', 'carcinoma',
'metast'
]
fields = [studies.c.official_title, conditions.c.name, keywords.c.name]
term_clauses = [
field.ilike('%' + term + '%') for field in fields for term in terms
]
return select([distinct(studies.c.nct_id)]).select_from(
studies.outerjoin(
conditions, conditions.c.nct_id == studies.c.nct_id).outerjoin(
keywords, keywords.c.nct_id == studies.c.nct_id)).where(
and_(
studies.c.created_at >=
func.date(func.current_date() - cutoff_days),
studies.c.study_first_posted_date >= '2017-02-01',
studies.c.study_type == 'Interventional',
studies.c.overall_status.in_([
'Recruiting', 'Enrolling by invitation',
'Not yet recruiting', 'Available'
]), or_(*term_clauses)))
def add_newly_updated_condition(self, query, cutoff_days, stub_ids):
studies = self.studies()
return query.where(
or_(
studies.c.updated_at >=
func.date(func.current_date() - cutoff_days),
studies.c.nct_id.in_(stub_ids)))
def get_all_oferts(self,
ofert_id=None,
shop_id=None,
entry_point_id=None,
scan_date=None):
o = db.session.query(
Ofert.id,
func.lower(Ofert.title).label('title'), Ofert.url, Ofert.image,
Ofert.price, Ofert.currency, Ofert.manufacturer,
Category.id.label('category_id'),
Category.name.label('category_name'), Shop.id.label('shop_id'),
Shop.url.label('shop_url'), Image.control_sum,
Ofert.creation_date.label('product_date'), Image.dimension,
Image.size.label('img_size'), Image.orientation, Image.main_color,
Shop.is_brand_shop,
Image.size).join(EntryPoint,
EntryPoint.id == Ofert.entry_point_id).join(
Category,
Category.id == EntryPoint.category_id).join(
Shop, Shop.id == EntryPoint.shop_id).join(
Image, Image.image == Ofert.image)
o = o.filter(Ofert.creation_date.cast(Date) == func.current_date())
if ofert_id:
o = o.filter(Ofert.id == ofert_id)
if shop_id:
o = o.filter(Shop.id == shop_id)
if entry_point_id:
o = o.filter(Ofert.entry_point_id == entry_point_id)
if scan_date:
o = o.filter(Ofert.creation_date.cast(Date) == scan_date)
o = o.order_by(Shop.is_brand_shop.asc(), Ofert.manufacturer.asc())
return o.all()
def get_product_by_product_def_id(self, product_def_id):
return db.session.query(
TagOfert.tag_product_def_id.label('product_id'),
Ofert.id.label('ofert_id'), Ofert.url, Ofert.title, Image.image,
Ofert.price, Ofert.currency, Image.control_sum,
Brand.name.label('brand_name'),
func.string_agg(Tag.value, ';').label('tags')).join(
Ofert, Ofert.id == TagOfert.ofert_id
).join(Image, Image.image == Ofert.image).join(
TagProductDef,
TagProductDef.id == TagOfert.tag_product_def_id,
).join(TagProduct,
TagProduct.tag_product_def_id == TagProductDef.id).join(
Tag, Tag.id == TagProduct.tag_id, isouter=True).join(
Brand,
Brand.id == TagProductDef.brand_id,
isouter=True).filter(
and_(
TagOfert.tag_product_def_id ==
product_def_id,
TagOfert.creation_date.cast(Date) ==
func.current_date())).group_by(
TagOfert.tag_product_def_id, Ofert.id,
Ofert.url, Ofert.title, Image.image,
Ofert.price, Ofert.currency,
Image.control_sum, Brand.name).order_by(
asc(Ofert.price)).all()
async def daily_new_registries():
databaseConnectionString = Database.create_psql_connection_string(
username=os.environ["DB_USERNAME"],
password=os.environ["DB_PASSWORD"],
host=os.environ["DB_HOST"],
port=os.environ["DB_PORT"],
name=os.environ["DB_NAME"])
database = Database(connectionString=databaseConnectionString)
await database.connect()
query = TokenTransfersTable.select()
query = query.where(
TokenTransfersTable.c.registryAddress.in_(
TokenTransfersTable.select().with_only_columns([
TokenTransfersTable.c.registryAddress
]).group_by(TokenTransfersTable.c.registryAddress).having(
sqlalchemyfunc.count(TokenTransfersTable.c.registryAddress) ==
1)))
query = query.where(
sqlalchemyfunc.date(TokenTransfersTable.c.blockDate) ==
sqlalchemyfunc.current_date())
rows = await database.fetch_all(query)
for row in rows:
logging.info(
f'New Tokens: registry address {row[2]} and tokenId {row[5]}')
await database.disconnect()
def parase_all_images(self):
# oferts = Ofert.query.all()
oferts = db.session.query(Ofert.image).outerjoin(
Image, Ofert.image == Image.image).filter(
and_(
Image.id == None, # noqa E711
Ofert.image.isnot(None),
Ofert.creation_date.cast(Date) == func.current_date()))
for lp, o in enumerate(oferts):
log.info('{} -> {} '.format(lp, o.image))
self.add_image(o.image)
def get_product_by_tag(self, tag):
products = db.session.query(
Tag.id, TagProductDef.id.label('product_id'),
Ofert.id.label('ofert_id'), Ofert.title, Ofert.url, Ofert.price,
Ofert.currency, Image.image, Image.control_sum,
func.coalesce(Ofert.manufacturer, '').label('brand_name'),
Category.name.label('category'), Tag.value.label('tags')).join(
TagProduct, TagProduct.tag_id == Tag.id).join(
TagProductDef,
TagProductDef.id == TagProduct.tag_product_def_id).join(
TagOfert, TagOfert.tag_product_def_id ==
TagProduct.tag_product_def_id).join(
Ofert, Ofert.id == TagOfert.ofert_id).join(
Image, Image.image == Ofert.image).join(
Category,
Category.id == TagProductDef.category_id,
isouter=True).filter(
and_(
Tag.value == tag,
Ofert.creation_date.cast(Date) ==
func.current_date())).order_by(
Ofert.price.asc()).cte(
'products')
subcategory = db.bindparam('str_tworzacy', '').label('subcategory')
colortags = db.bindparam('str_tworzacy', '').label('colortags')
return db.session.query(
products.c.product_id, products.c.ofert_id, products.c.title,
products.c.url, products.c.price, products.c.currency,
products.c.image, products.c.control_sum, products.c.brand_name,
products.c.category, products.c.tags,
func.string_agg(Tag.value, ';').label('all_tags'), subcategory,
colortags).join(
TagProduct,
TagProduct.tag_product_def_id == products.c.product_id,
isouter=True).join(Tag,
Tag.id == TagProduct.tag_id,
isouter=True).group_by(
products.c.product_id,
products.c.ofert_id,
products.c.title,
products.c.url,
products.c.price,
products.c.currency,
products.c.image,
products.c.control_sum,
products.c.brand_name,
products.c.category,
products.c.tags,
).order_by(products.c.price.asc()).all()
class Expense(Base):
"""Expenses maded in current period (with dates)."""
__tablename__ = "expenses"
id = Column(Integer, primary_key=True)
name = Column(String(500))
amount = Column(Float(), nullable=False)
date = Column(DateTime(), nullable=False, default=func.current_date())
period_id = Column(Integer, ForeignKey('periods.id'), nullable=False, default=1)
def __init__(self, name, amount, date=None):
self.name = name
self.amount = amount
if date:
self.date = date
),
DrugInfo(
id="9999999999",
validity=DateRange(
lower="1970-01-01",
upper="9999-12-31",
),
name="Crestor",
description="20mg capsule",
),
])
session.flush()
drugs = (
session.query(DrugInfo)
.filter(DrugInfo.validity.op("@>")(func.current_date()))
)
print("filtering with range types, current time")
print_table(drugs)
print("")
drugs = (
session.query(DrugInfo)
.filter(DrugInfo.validity.op("@>")(datetime.date(2019, 1, 1)))
)
print("filtering with range types, explicit date")
print_table(drugs)
print("")
description="20mg Tablet",
),
model.DrugInfo(
id="9999999999",
validity=DateRange(
lower="1970-01-01",
upper="9999-12-31",
),
name="Crestor",
description="20mg capsule",
),
])
meta.session.flush()
drugs = (meta.session.query(model.DrugInfo).filter(
model.DrugInfo.validity.op("@>")(func.current_date())))
print("filtering with range types, current time")
print_table(drugs)
print("")
drugs = (meta.session.query(model.DrugInfo).filter(
model.DrugInfo.validity.op("@>")(datetime.date(2019, 1, 1))))
print("filtering with range types, explicit date")
print_table(drugs)
print("")
meta.session.add_all([
model.DrugPrice(
id="1234567890",
class Period(Base):
"""Period for savings. E.g. month or business trip."""
__tablename__ = 'periods'
id = Column(Integer, primary_key=True)
name = Column(String(500))
start = Column(DateTime(), nullable=False, default=func.current_date())
end = Column(DateTime(), nullable=False, default=func.current_date())
active = Column(Boolean(), default=False)
def __init__(self, name, start, end):
self.name = name
self.start = start
self.end = end
@classmethod
def get(cls, name):
return DBSession().query(cls).filter_by(name=name).one()
@classmethod
def get_by_id(cls, period_id):
return DBSession().query(cls).filter_by(id=int(period_id)).one()
def make_active(self):
""" Make new period as active, old as inactive """
try:
current_active = DBSession().query(Period).filter_by(active=True).one()
current_active.active = False
except:
pass
self.active = True
def get_expenses(self, date=None):
"""Getting sum of all expanses in this period.
If date is not None, then return sum of all expanses during requested day.
"""
dbsession = DBSession()
if date:
expenses = dbsession.query(Expense).\
filter(Expense.period_id==self.id).\
filter(Expense.date==date).all()
return sum([expense.amount for expense in expenses])
periodic_expenses = sum([expense.amount for expense in dbsession.query(PeriodicExpense).\
filter(PeriodicExpense.period_id==self.id).all()])
expenses = sum([expense.amount for expense in dbsession.query(Expense).\
filter(Expense.period_id==self.id).all()])
return expenses + periodic_expenses
def get_incomes(self):
"""Getting sum of all incomes in this period."""
dbsession = DBSession()
return sum([income.amount for income in dbsession.query(Income).\
filter(Income.period_id==self.id).all()])
def money_left(self):
"""Money left to spend until end of period."""
return self.get_incomes() - self.get_expenses()
def to_spend(self, date=None):
"""Money left to spend average or given date."""
if date:
#convert date to datetime
date = datetime.datetime.combine(date, datetime.time())
money_left = self.money_left()+self.get_expenses(date)
days_left = (self.end-date).days
return round(money_left/days_left, 2)
return round(self.money_left()/self.period(), 2)
def period(self):
"""Get length of period."""
return (self.end - self.start).days