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