def register_sim(pid: int, force=False) -> Pk_sim: try: new_sim = Pk_sim(pid=pid, sim_date=func.current_date(), sim_time=func.current_time()) session.add(new_sim) session.commit() except: if force: session.rollback() del_pksimid = session.query( Pk_sim.pksimid).filter(Pk_sim.pid == pid) to_del = session.query(Pk).filter(Pk.pksimid == del_pksimid) to_del.delete(synchronize_session=False) to_del = session.query(Pk_sim).filter(Pk_sim.pid == pid) to_del.delete(synchronize_session=False) session.commit() new_sim = Pk_sim(pid=pid, sim_date=func.current_date(), sim_time=func.current_time()) session.add(new_sim) session.commit() else: logging.error("The protein has already been calculated") exit() return new_sim
def test_as_from(self, connection): # TODO: shouldn't this work on oracle too ? x = connection.execute(func.current_date()).scalar() y = connection.execute(func.current_date().select()).scalar() z = connection.scalar(func.current_date()) w = connection.scalar(select("*").select_from(func.current_date())) assert x == y == z == w
def test_as_from(self): # TODO: shouldn't this work on oracle too ? x = func.current_date(bind=testing.db).execute().scalar() y = func.current_date(bind=testing.db).select().execute().scalar() z = func.current_date(bind=testing.db).scalar() w = select(['*'], from_obj=[func.current_date(bind=testing.db)]).\ scalar() assert x == y == z == w
def test_as_from(self, connection): # TODO: shouldn't this work on oracle too ? x = connection.execute(func.current_date(bind=testing.db)).scalar() y = connection.execute( func.current_date(bind=testing.db).select()).scalar() z = connection.scalar(func.current_date(bind=testing.db)) w = connection.scalar( select(["*"], from_obj=[func.current_date(bind=testing.db)])) assert x == y == z == w
def test_native_datetime(self): dbapi = testing.db.dialect.dbapi connect_args = { 'detect_types': dbapi.PARSE_DECLTYPES | dbapi.PARSE_COLNAMES } engine = engines.testing_engine(options={ 'connect_args': connect_args, 'native_datetime': True }) t = Table('datetest', MetaData(), Column('id', Integer, primary_key=True), Column('d1', Date), Column('d2', sqltypes.TIMESTAMP)) t.create(engine) try: engine.execute( t.insert(), { 'd1': datetime.date(2010, 5, 10), 'd2': datetime.datetime(2010, 5, 10, 12, 15, 25) }) row = engine.execute(t.select()).first() eq_(row, (1, datetime.date( 2010, 5, 10), datetime.datetime(2010, 5, 10, 12, 15, 25))) r = engine.execute(func.current_date()).scalar() assert isinstance(r, util.string_types) finally: t.drop(engine) engine.dispose()
def test_text_in_select_nonfrom(self): generate_series = text( "generate_series(:x, :y, :z) as s(a)").bindparams(x=None, y=None, z=None) s = select(( func.current_date() + literal_column("s.a")).label("dates")).select_from(generate_series) self.assert_compile( s, "SELECT CURRENT_DATE + s.a AS dates FROM " "generate_series(:x, :y, :z) as s(a)", checkparams={ "y": None, "x": None, "z": None }, ) self.assert_compile( s.params(x=5, y=6, z=7), "SELECT CURRENT_DATE + s.a AS dates FROM " "generate_series(:x, :y, :z) as s(a)", checkparams={ "y": 6, "x": 5, "z": 7 }, )
def test_native_datetime(self): dbapi = testing.db.dialect.dbapi connect_args = { 'detect_types': dbapi.PARSE_DECLTYPES | dbapi.PARSE_COLNAMES} engine = engines.testing_engine( options={'connect_args': connect_args, 'native_datetime': True}) t = Table( 'datetest', MetaData(), Column('id', Integer, primary_key=True), Column('d1', Date), Column('d2', sqltypes.TIMESTAMP)) t.create(engine) try: engine.execute(t.insert(), { 'd1': datetime.date(2010, 5, 10), 'd2': datetime.datetime(2010, 5, 10, 12, 15, 25) }) row = engine.execute(t.select()).first() eq_( row, (1, datetime.date(2010, 5, 10), datetime.datetime(2010, 5, 10, 12, 15, 25))) r = engine.execute(func.current_date()).scalar() assert isinstance(r, util.string_types) finally: t.drop(engine) engine.dispose()
class A(object): __tablename__ = "a" id = Column(Integer, primary_key=True) _date = Column(Date, default=func.current_date()) b_id = Column(Integer, ForeignKey("b.id")) b = relationship("B")
def newCatalogItem(catalog_header_id): print "In new items" if 'username' not in login_session: return redirect('/login') catalog_header = session.query(CatalogHeader).filter_by( id=catalog_header_id).one() print catalog_header.user_id, login_session['user_id'] if login_session['user_id'] != catalog_header.user_id: return "<script>function myFunction() {alert('You are not authorized to add catalogitems items to this catalog. Please create your own catalog in order to add items.');}</script><body onload='myFunction()''>" if request.method == 'POST': print "In post" newItem = CatalogItem(name=request.form['name'], description=request.form['description'], price=request.form['price'], section=request.form['section'], catalog_header_id=catalog_header_id, user_id=catalog_header.user_id, first_stock_date=func.current_date()) session.add(newItem) session.commit() flash('New Catalog Item %s Item Successfully Created' % (newItem.name)) return redirect( url_for('showCatalogItems', catalog_header_id=catalog_header_id)) else: print "In login not same" return render_template('newcatalogitem.html', catalog_header_id=catalog_header_id)
def test_as_from(self): # TODO: shouldn't this work on oracle too ? x = func.current_date(bind=testing.db).execute().scalar() y = func.current_date(bind=testing.db).select().execute().scalar() z = func.current_date(bind=testing.db).scalar() w = select(['*'], from_obj=[func.current_date(bind=testing.db)]).\ scalar() # construct a column-based FROM object out of a function, # like in [ticket:172] s = select([sql.column('date', type_=DateTime)], from_obj=[func.current_date(bind=testing.db)]) q = s.execute().first()[s.c.date] r = s.alias('datequery').select().scalar() assert x == y == z == w == q == r
def has_access_to_node_id(node_id, accesstype, user=None, ip=None, date=None): # XXX: the database-independent code could move to core.node from core import db from core.users import get_guest_user if user is None: user = get_guest_user() if user.is_admin: return True if ip is None: ip = IPv4Address("0.0.0.0") if date is None: date = func.current_date() accessfunc = access_funcs[accesstype] group_ids = user.group_ids access = accessfunc(node_id, group_ids, ip, date) return db.session.execute(select([access])).scalar()
class Donation(db.Model): __tablename__ = "donation" donor_id = db.Column(db.Integer, db.ForeignKey(Donor.id), primary_key=True) bloodbank_id = db.Column(db.Integer, db.ForeignKey(Bloodbank.id), primary_key=True) date = db.Column(db.Date, nullable=False, default=func.current_date(), primary_key=True) units = db.Column(db.Integer, nullable=False, default=1) bloodbanks = db.relationship("Bloodbank", back_populates="donors") donors = db.relationship("Donor", back_populates="bloodbanks")
def test_conn_execute(self, connection): from sqlalchemy.sql.expression import FunctionElement from sqlalchemy.ext.compiler import compiles class myfunc(FunctionElement): type = Date() @compiles(myfunc) def compile_(elem, compiler, **kw): return compiler.process(func.current_date()) x = connection.execute(func.current_date()).scalar() y = connection.execute(func.current_date().select()).scalar() z = connection.scalar(func.current_date()) q = connection.scalar(myfunc()) assert (x == y == z == q) is True
def retrieve_merchant_payments(merchant_id: str): merchant = Merchant.query.filter(Merchant.merchant_id == merchant_id).first() if merchant: result = Payments.query.filter(db.and_(Payments.payment_date >= func.current_date() - 7, Payments.merchant_fk == merchant.merchant_pk)).count() return jsonify(payments_count=result) else: return jsonify("Merchant with " + merchant_id + ' id does not exists'), 404
def past_shows(self): shows = db.session.query(Show).filter(Show.venue_id == self.id).filter( Show.start_time < func.current_date()).all() for s in shows: artist = Artist.query.get(s.artist_id) s.artist_name = artist.name s.artist_image_link = artist.image_link s.start_time = str(s.start_time) return shows
def retrieve_merchant_amount(merchant_id: str): merchant = Merchant.query.filter(Merchant.merchant_id == merchant_id).first() if merchant: result = db.session.query(func.sum(Payments.payment_amount)).filter( db.and_(Payments.payment_date >= func.current_date() - 7, Payments.merchant_fk == merchant.merchant_pk)).first()[0] return jsonify(payments_amount=str(0 if result is None else result)) else: return jsonify("Merchant with " + merchant_id + ' id does not exists'), 404
def current_date_by_all_minute_commit_count(self, form_id): self.db \ .session \ .query(func.date_format(Value.created_at, "%Y-%m-%d %H:%i:00").label('minute_time'), func.count().label('commit_count')) \ .filter(Value.form_id == form_id, Value.created_at > func.current_date()) \ .group_by(Value.minute_time) \ .order_by(Value.minute_time) \ .all()
def retrieve_merchant_rejects(merchant_id: str): merchant = Merchant.query.filter(Merchant.merchant_id == merchant_id).first() if merchant: rejected = PaymentStatus.query.filter(PaymentStatus.payment_status_name == 'Rejected').first().payment_status_pk result = Payments.query.filter( db.and_(Payments.payment_status_fk == rejected, Payments.payment_date >= func.current_date() - 7 , Payments.merchant_fk == merchant.merchant_pk)).count() return jsonify(rejected_payments_count=result) else: return jsonify("Merchant with " + merchant_id + ' id does not exists'), 404
class Donor(db.Model): __tablename__ = "donor" id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(30), nullable=False) email = db.Column(db.String(120), unique=True, nullable=False) contact_no = db.Column(db.String(15), unique=True, nullable=False) # address = db.Column(db.String(1000), nullable=False) blood_group = db.Column(db.String(5), nullable=False) last_donation = db.Column(db.Date, nullable=False, default=func.current_date()) bloodbanks = db.relationship("Donation", back_populates="donors")
def upcoming_shows(self): shows = db.session.query(Show).filter( Show.artist_id == self.id).filter( Show.start_time >= func.current_date()).all() for s in shows: venue = Venue.query.get(s.venue_id) s.venue_name = venue.name s.venue_image_link = venue.image_link s.start_time = str(s.start_time) return shows
def daily_amount_select(): return select([ func.cast( func.sum(SalesOrderLine.unit_price * SalesOrderLine.quantity) / func.greatest( func.cast( func.date_part( 'DAY', func.current_date() - Product.create_date), Integer), 1), Numeric) ]).as_scalar()
def test_conn_execute(self): from sqlalchemy.sql.expression import FunctionElement from sqlalchemy.ext.compiler import compiles class myfunc(FunctionElement): type = Date() @compiles(myfunc) def compile(elem, compiler, **kw): return compiler.process(func.current_date()) conn = testing.db.connect() try: x = conn.execute(func.current_date()).scalar() y = conn.execute(func.current_date().select()).scalar() z = conn.scalar(func.current_date()) q = conn.scalar(myfunc()) finally: conn.close() assert (x == y == z == q) is True
def retrieve_merchant_rejected_amount(merchant_id: str): merchant = Merchant.query.filter(Merchant.merchant_id == merchant_id).first() if merchant: rejected = PaymentStatus.query.filter(PaymentStatus.payment_status_name == 'Rejected').first().payment_status_pk result = \ db.session.query(func.sum(Payments.payment_amount)) \ .filter(db.and_(Payments.payment_status_fk == rejected, Payments.payment_date >= func.current_date() - 7, Payments.merchant_fk == merchant.merchant_pk)).first()[0] return jsonify(rejected_payments_amount=str(0 if result is None else result)) else: return jsonify("Merchant with " + merchant_id + ' id does not exists'), 404
def __init__(self, body: T.AnyStr, files: T.List[str], author: FabUser, parent: DesignPost): self.body = body self.author = author self.parent_post = parent self.files = files self._files_json = json.dumps({'files': self.files}) self.is_accepted_response = False self.date_created = func.current_date() self.resp_id = uuid.uuid4().hex
def __init__(self, title: T.AnyStr, body: T.AnyStr, files: T.List[str], author: AbstractUser, date: Date): self.title = title self.body = body self.author = author self.files = files self._files_json = json.dumps({'files': self.files}) self.post_id = uuid.uuid4().hex self.has_accepted_response = False self.date_created = func.current_date() self.date_needed = date
def send_mess(message): if message.text in locations.keys(): global loc loc = locations[message.text] print(loc) if register == 0: bot.send_message(message.chat.id, "Thanks, and now send me your favorite category", reply_markup=keyboard_1) else: bot.send_message(message.chat.id, "Get it") try: session.query(Resources).filter( Resources.user_id == message.chat.id).update( {Resources.location_: loc.upper()}) session.commit() except Exception as e: print(e) elif message.text in category: global cat cat = message.text print(cat) if register == 0: bot.send_message( message.chat.id, "You have finished, congrats! \nNow, whenever you need news \ntype /news" ) try: new_user = User(user_id=message.chat.id, user_name=message.chat.first_name, user_date=func.current_date()) new_user_res = Resources(user_id=message.chat.id, category_=cat, location_=loc) for i in [new_user, new_user_res]: session.add(i) session.commit() except Exception as e: print(e) else: bot.send_message(message.chat.id, 'Get it') try: session.query(Resources).filter( Resources.user_id == message.chat.id).update( {Resources.category_: cat}) session.commit() except Exception as e: print(e) else: bot.send_message(message.chat.id, "Probably you /help or /news")
def get_or_create(user): user_valid_tokens = UserReadToken.query.filter( UserReadToken.user_id == user.id, UserReadToken.valid_until >= datetime.now(), UserReadToken.creation_day == func.current_date(), ).all() valid_until = datetime.now() + app.config["USER_READ_TOKEN_EXPIRATION"] if user_valid_tokens: token = user_valid_tokens[0] token.valid_until = valid_until else: token = UserReadToken(user=user, valid_until=valid_until) db.session.add(token) db.session.commit() return token
def showCatalogHeaders(): catalog_headers = session.query(CatalogHeader).order_by( asc(CatalogHeader.name)) items_with_category = session.query(CatalogHeader, CatalogItem).filter( CatalogHeader.id == CatalogItem.catalog_header_id).filter( CatalogItem.first_stock_date == func.current_date()).all() if 'username' not in login_session: return render_template('publiccatalog.html', catalog_headers=catalog_headers, items_with_category=items_with_category) else: return render_template('catalog.html', catalog_headers=catalog_headers, items_with_category=items_with_category)
def get_album_image_cant_crawl(artistuuid: list): album_image_cant_crawl = (db_session.query(Album.title, Album.uuid, func.json_extract(Crawlingtask.taskdetail, "$.url").label( "image_url"), Crawlingtask.status ) .select_from(Crawlingtask) .join(Album, Album.uuid == Crawlingtask.objectid) .filter(func.DATE(Crawlingtask.created_at) == func.current_date(), Crawlingtask.actionid == 'OA9CPKSUT6PBGI1ZHPLQUPQCGVYQ71S9', Crawlingtask.objectid.in_(artistuuid) ) .order_by(Crawlingtask.objectid, Crawlingtask.created_at.desc()) ) return album_image_cant_crawl
def get_crawl_E5_06_status(ituneid: list): crawlingtask_alias = aliased(Crawlingtask, name='crawlingtask_alias') crawl_E5_06_status = ( db_session.query(func.json_unquote(func.json_extract(Crawlingtask.taskdetail, "$.album_id")).label("ituneid"), Crawlingtask.status.label('06_status'), crawlingtask_alias.status.label('E5_status') ) .select_from(Crawlingtask) .outerjoin(crawlingtask_alias, text("crawlingtask_alias.id = Crawlingtasks.ext ->> '$.itunes_track_task_id'")) .filter(func.DATE(Crawlingtask.created_at) == func.current_date(), Crawlingtask.actionid == '9C8473C36E57472281A1C7936108FC06', func.json_extract(Crawlingtask.taskdetail, "$.album_id").in_(ituneid) ) ) return crawl_E5_06_status
def req_has_access_to_node_id(node_id, accesstype, req=None, date=func.current_date()): # XXX: the database-independent code could move to core.node from core.transition import request from core.users import user_from_session if req is None: req = request user = user_from_session(req.session) # XXX: like in mysql version, what's the real solution? try: ip = IPv4Address(req.remote_addr) except AddressValueError: logg.warn("illegal IP address %s, refusing IP-based access", req.remote_addr) ip = None return Node.has_access_to_node_id(node_id, accesstype, user, ip, date)
class Connections(Base, Helper): __tablename__ = 'connections' id = Column(Integer, primary_key=True) src_iata = Column(String(10), ForeignKey('airport.iata'), nullable=False) dst_iata = Column(String(10), nullable=False) carrierCode = Column(String(10),ForeignKey('airline.carrierCode')) updated = Column(Date, nullable=False, default = func.current_date()) flightDetails = relationship("FlightDetails", back_populates="connection") def __str__(self): return "Connection: ID: {0}, from: {1}, to: {2}, airline code: {3}, updated: {4}".format(self.id, self.src_iata, self.dst_iata, self.carrierCode, self.updated) def __eq__(self, other): return self.src_iata==other.src_iata and self.dst_iata==other.dst_iata
class Weather_info(Base, Base_do): """[summary] [description] Extends: Base Base_do Variables: info_id {[type]} -- [description] city_alias {[type]} -- [description] city_id {[type]} -- [description] city_name {[type]} -- [description] today_temperature_high {[type]} -- [description] today_temperature_low {[type]} -- [description] tomorrow_temperature_high {[type]} -- [description] tomorrow_temperature_low {[type]} -- [description] text_day {[type]} -- [description] text_night {[type]} -- [description] info_date {[type]} -- [description] created_at {[type]} -- [description] updated_at {[type]} -- [description] """ # 表的名字: __tablename__ = 'weather_info' # 表的结构: info_id = Column("id", INT, primary_key=True) city_alias = Column("city_alias", Text) city_id = Column("city_id", Text) city_name = Column("city_name", Text) today_temperature_high = Column("today_temperature_high", INT) today_temperature_low = Column("today_temperature_low", INT) tomorrow_temperature_high = Column("tomorrow_temperature_high", INT) tomorrow_temperature_low = Column("tomorrow_temperature_low", INT) text_day = Column("text_day", Text) text_night = Column("text_night", Text) info_date = Column("info_date", DATE, default=func.current_date()) created_at = Column("created_at", TIMESTAMP, default=func.now()) updated_at = Column("updated_at", TIMESTAMP, default=func.now())
def list(page_nr=1, search=None): if not ModuleAPI.can_read('vacancy'): return abort(403) # Order the vacancies in such a way that vacancies that are new # or almost expired, end up on top. order = func.abs( (100 * (func.datediff(Vacancy.start_date, func.current_date()) / func.datediff(Vacancy.start_date, Vacancy.end_date))) - 50) if search is not None: vacancies = Vacancy.query.join(Company). \ filter(or_(Vacancy.title.like('%' + search + '%'), Company.name.like('%' + search + '%'), Vacancy.workload.like('%' + search + '%'), Vacancy.contract_of_service.like('%' + search + '%'))) \ .order_by(order.desc()) if not ModuleAPI.can_write('vacancy'): vacancies = vacancies.filter( and_(Vacancy.start_date < datetime.utcnow(), Vacancy.end_date > datetime.utcnow())) vacancies = vacancies.paginate(page_nr, 15, False) return render_template('vacancy/list.htm', vacancies=vacancies, search=search, path=FILE_FOLDER, title="Vacatures") if ModuleAPI.can_write('vacancy'): vacancies = Vacancy.query.join(Company).order_by(order.desc()) else: vacancies = Vacancy.query.order_by(order.desc()) \ .filter(and_(Vacancy.start_date < datetime.utcnow(), Vacancy.end_date > datetime.utcnow())) vacancies = vacancies.paginate(page_nr, 15, False) return render_template('vacancy/list.htm', vacancies=vacancies, search="", path=FILE_FOLDER, title="Vacatures")
def has_access_to_node_id(node_id, accesstype, user=None, ip=None, date=None): # XXX: the database-independent code could move to core.node from core import db from core.users import get_guest_user if user is None: user = get_guest_user() if user.is_admin: return True if ip is None: ip = IPv4Address("0.0.0.0") if date is None: date = func.current_date() accessfunc = access_funcs[accesstype] group_ids = user.group_ids access = accessfunc(node_id, group_ids, ip, date) return db.session.execute(select([access])).scalar()
def returned_on(self, date=None): """ Marks the loan as returned and clears loaned flag in related movies. """ if date is None: date = func.current_date() # note that SQLAlchemy will convert YYYYMMDD strings to datetime, no need to touch it if self.return_date: # already returned, just update the date self.return_date = date return True session = object_session(self) if self.collection_id: self.collection.loaned = False # will update the loaned flag in all associated movies as well if self.volume_id: self.volume.loaned = False # will update the loaned flag in all associated movies as well if self.movie_id: self.movie.loaned = False self.return_date = date
def test_text_in_select_nonfrom(self): generate_series = text("generate_series(:x, :y, :z) as s(a)").\ bindparams(x=None, y=None, z=None) s = select([ (func.current_date() + literal_column("s.a")).label("dates") ]).select_from(generate_series) self.assert_compile( s, "SELECT CURRENT_DATE + s.a AS dates FROM " "generate_series(:x, :y, :z) as s(a)", checkparams={'y': None, 'x': None, 'z': None} ) self.assert_compile( s.params(x=5, y=6, z=7), "SELECT CURRENT_DATE + s.a AS dates FROM " "generate_series(:x, :y, :z) as s(a)", checkparams={'y': 6, 'x': 5, 'z': 7} )
def build_accessfunc_arguments(user=None, ip=None, date=None, req=None): """Build the expected arguments for the DB permission procedures has_*_access_to_node() IP and date are returned unchanged when passed to this function. For missing arguments, default values are set from request information or current date. :returns: 3-tuple of group_ids, ip and date For admin users, it returns (None, None, None) which means: ignore all access checks. Users can test for this and skip permission checks completely. """ from core.users import get_guest_user if user is None and ip is None: if req is None: req = request from core.users import user_from_session user = user_from_session(req.session) # XXX: like in mysql version, what's the real solution? try: ip = IPv4Address(req.remote_addr) except AddressValueError: logg.warn("illegal IP address %s, refusing IP-based access", req.remote_addr) ip = None if user is None: user = get_guest_user() # admin sees everything ;) if user.is_admin: return (None, None, None) if ip is None: ip = IPv4Address("0.0.0.0") if date is None: date = sqlfunc.current_date() return user.group_ids, ip, date
def newCatalogItem(catalog_header_id): print "In new items" if 'username' not in login_session: return redirect('/login') catalog_header = session.query(CatalogHeader).filter_by(id=catalog_header_id).one() print catalog_header.user_id, login_session['user_id'] if login_session['user_id'] != catalog_header.user_id: return "<script>function myFunction() {alert('You are not authorized to add catalogitems items to this catalog. Please create your own catalog in order to add items.');}</script><body onload='myFunction()''>" if request.method == 'POST': print "In post" newItem = CatalogItem(name=request.form['name'], description=request.form['description'], price=request.form[ 'price'], section=request.form['section'], catalog_header_id=catalog_header_id, user_id=catalog_header.user_id, first_stock_date=func.current_date()) session.add(newItem) session.commit() flash('New Catalog Item %s Item Successfully Created' % (newItem.name)) return redirect(url_for('showCatalogItems', catalog_header_id=catalog_header_id)) else: print "In login not same" return render_template('newcatalogitem.html', catalog_header_id=catalog_header_id)
# Create dummy user User1 = User(name="Guru Shetti", email="*****@*****.**", picture='https://pbs.twimg.com/profile_images/2671170543/18debd694829ed78203a5a36dd364160_400x400.png') session.add(User1) session.commit() # Menu for UrbanBurger catalog_header1 = CatalogHeader(user_id=1, name="Soccer") session.add(catalog_header1) session.commit() catalogItem2 = CatalogItem(user_id=1, name="Soccer Cleats", description="Really Fast", price="$7.50", section="Women", first_stock_date= func.current_date(), image="shoes.jpg", catalog_header=catalog_header1) session.add(catalogItem2) session.commit() catalogItem1 = CatalogItem(user_id=1, name="Shin Guards", description="Max Protection", price="$2.99", section="Men", first_stock_date= datetime.date(2015,06,15), image="shoes.jpg", catalog_header=catalog_header1) session.add(catalogItem1) session.commit() catalogItem2 = CatalogItem(user_id=1, name="Shirt", description="Dry Fit", price="$5.50", section="Women", first_stock_date= datetime.date(2015,06,15), image="swimtrunk.jpg", catalog_header=catalog_header1) session.add(catalogItem2)
def showCatalogHeaders(): catalog_headers = session.query(CatalogHeader).order_by(asc(CatalogHeader.name)) items_with_category = session.query(CatalogHeader, CatalogItem).filter(CatalogHeader.id == CatalogItem.catalog_header_id).filter(CatalogItem.first_stock_date == func.current_date()).all() if 'username' not in login_session: return render_template('publiccatalog.html', catalog_headers=catalog_headers, items_with_category=items_with_category) else: return render_template('catalog.html', catalog_headers=catalog_headers, items_with_category=items_with_category)
Column('image', Unicode(128)), # XXX: deprecated ) people = Table('people', metadata, Column('person_id', Integer, primary_key=True), Column('name', Unicode(256), nullable=False, unique=True), Column('email', Unicode(128)), Column('phone', Unicode(64))) loans = Table('loans', metadata, Column('loan_id', Integer, primary_key=True), Column('person_id', ForeignKey(people.c.person_id), nullable=False), Column('movie_id', ForeignKey(movies.c.movie_id), nullable=False), Column('volume_id', ForeignKey(volumes.c.volume_id)), Column('collection_id', ForeignKey(collections.c.collection_id)), Column('date', Date, nullable=False, default=func.current_date()), Column('return_date', Date, nullable=True)) languages = Table('languages', metadata, Column('lang_id', Integer, primary_key=True), Column('name', Unicode(64), nullable=False, unique=True)) acodecs = Table('acodecs', metadata, Column('acodec_id', Integer, primary_key=True), Column('name', Unicode(64), nullable=False, unique=True)) achannels = Table('achannels', metadata, Column('achannel_id', Integer, primary_key=True), Column('name', Unicode(64), nullable=False, unique=True)) subformats = Table('subformats', metadata,
def list(request): events = Event.query.filter(cast(Event.start_time, Date) >= func.current_date()).order_by(Event.start_time.asc()) return {'events': events}
def setup_class(cls): global t, f, f2, ts, currenttime, metadata, default_generator db = testing.db metadata = MetaData(db) default_generator = {"x": 50} def mydefault(): default_generator["x"] += 1 return default_generator["x"] def myupdate_with_ctx(ctx): conn = ctx.connection return conn.execute(sa.select([sa.text("13")])).scalar() def mydefault_using_connection(ctx): conn = ctx.connection try: return conn.execute(sa.select([sa.text("12")])).scalar() finally: # ensure a "close()" on this connection does nothing, # since its a "branched" connection conn.close() use_function_defaults = testing.against("postgresql", "mssql", "maxdb") is_oracle = testing.against("oracle") # select "count(1)" returns different results on different DBs also # correct for "current_date" compatible as column default, value # differences currenttime = func.current_date(type_=sa.Date, bind=db) if is_oracle: ts = db.scalar( sa.select([func.trunc(func.sysdate(), sa.literal_column("'DAY'"), type_=sa.Date).label("today")]) ) assert isinstance(ts, datetime.date) and not isinstance(ts, datetime.datetime) f = sa.select([func.length("abcdef")], bind=db).scalar() f2 = sa.select([func.length("abcdefghijk")], bind=db).scalar() # TODO: engine propigation across nested functions not working currenttime = func.trunc(currenttime, sa.literal_column("'DAY'"), bind=db, type_=sa.Date) def1 = currenttime def2 = func.trunc(sa.text("sysdate"), sa.literal_column("'DAY'"), type_=sa.Date) deftype = sa.Date elif use_function_defaults: f = sa.select([func.length("abcdef")], bind=db).scalar() f2 = sa.select([func.length("abcdefghijk")], bind=db).scalar() def1 = currenttime deftype = sa.Date if testing.against("maxdb"): def2 = sa.text("curdate") elif testing.against("mssql"): def2 = sa.text("getdate()") else: def2 = sa.text("current_date") ts = db.scalar(func.current_date()) else: f = len("abcdef") f2 = len("abcdefghijk") def1 = def2 = "3" ts = 3 deftype = Integer t = Table( "default_test1", metadata, # python function Column("col1", Integer, primary_key=True, default=mydefault), # python literal Column("col2", String(20), default="imthedefault", onupdate="im the update"), # preexecute expression Column("col3", Integer, default=func.length("abcdef"), onupdate=func.length("abcdefghijk")), # SQL-side default from sql expression Column("col4", deftype, server_default=def1), # SQL-side default from literal expression Column("col5", deftype, server_default=def2), # preexecute + update timestamp Column("col6", sa.Date, default=currenttime, onupdate=currenttime), Column("boolcol1", sa.Boolean, default=True), Column("boolcol2", sa.Boolean, default=False), # python function which uses ExecutionContext Column("col7", Integer, default=mydefault_using_connection, onupdate=myupdate_with_ctx), # python builtin Column("col8", sa.Date, default=datetime.date.today, onupdate=datetime.date.today), # combo Column("col9", String(20), default="py", server_default="ddl"), ) t.create()
def get_where(current_sel): return current_sel.where(SalesOrder.order_date > func.current_date() - text("INTERVAL '1 DAY'"))
def daily_profit_select(): return select([func.cast(func.sum((SalesOrderLine.unit_price - Product.purchase_price) * SalesOrderLine.quantity) / func.greatest(func.cast(func.date_part('DAY', func.current_date() - Product.create_date), Integer), 1), Numeric)]).as_scalar()
def setup_class(cls): global t, f, f2, ts, currenttime, metadata, default_generator db = testing.db metadata = MetaData(db) default_generator = {'x': 50} def mydefault(): default_generator['x'] += 1 return default_generator['x'] def myupdate_with_ctx(ctx): conn = ctx.connection return conn.execute(sa.select([sa.text('13')])).scalar() def mydefault_using_connection(ctx): conn = ctx.connection try: return conn.execute(sa.select([sa.text('12')])).scalar() finally: # ensure a "close()" on this connection does nothing, # since its a "branched" connection conn.close() use_function_defaults = testing.against('postgresql', 'mssql') is_oracle = testing.against('oracle') class MyClass(object): @classmethod def gen_default(cls, ctx): return "hi" # select "count(1)" returns different results on different DBs also # correct for "current_date" compatible as column default, value # differences currenttime = func.current_date(type_=sa.Date, bind=db) if is_oracle: ts = db.scalar(sa.select([func.trunc(func.sysdate(), sa.literal_column("'DAY'"), type_=sa.Date).label('today')])) assert isinstance(ts, datetime.date) and not isinstance(ts, datetime.datetime) f = sa.select([func.length('abcdef')], bind=db).scalar() f2 = sa.select([func.length('abcdefghijk')], bind=db).scalar() # TODO: engine propigation across nested functions not working currenttime = func.trunc(currenttime, sa.literal_column("'DAY'"), bind=db, type_=sa.Date) def1 = currenttime def2 = func.trunc(sa.text("sysdate"), sa.literal_column("'DAY'"), type_=sa.Date) deftype = sa.Date elif use_function_defaults: f = sa.select([func.length('abcdef')], bind=db).scalar() f2 = sa.select([func.length('abcdefghijk')], bind=db).scalar() def1 = currenttime deftype = sa.Date if testing.against('mssql'): def2 = sa.text("getdate()") else: def2 = sa.text("current_date") ts = db.scalar(func.current_date()) else: f = len('abcdef') f2 = len('abcdefghijk') def1 = def2 = "3" ts = 3 deftype = Integer t = Table('default_test1', metadata, # python function Column('col1', Integer, primary_key=True, default=mydefault), # python literal Column('col2', String(20), default="imthedefault", onupdate="im the update"), # preexecute expression Column('col3', Integer, default=func.length('abcdef'), onupdate=func.length('abcdefghijk')), # SQL-side default from sql expression Column('col4', deftype, server_default=def1), # SQL-side default from literal expression Column('col5', deftype, server_default=def2), # preexecute + update timestamp Column('col6', sa.Date, default=currenttime, onupdate=currenttime), Column('boolcol1', sa.Boolean, default=True), Column('boolcol2', sa.Boolean, default=False), # python function which uses ExecutionContext Column('col7', Integer, default=mydefault_using_connection, onupdate=myupdate_with_ctx), # python builtin Column('col8', sa.Date, default=datetime.date.today, onupdate=datetime.date.today), # combo Column('col9', String(20), default='py', server_default='ddl'), # python method w/ context Column('col10', String(20), default=MyClass.gen_default) ) t.create()
def get_where(current_sel): return (current_sel .where(SalesOrder.order_date > func.date_trunc('week', func.current_date()) - text("INTERVAL '7 DAYS'")) .where(SalesOrder.order_date < func.date_trunc('week', func.current_date())))
def get_where(current_sel): return current_sel.where(SalesOrder.order_date >= func.current_date())
def get_where(current_sel): return current_sel.where(SalesOrder.order_date >= func.date_trunc('month', func.current_date()))
def daily_amount_select(): return select([func.cast(func.sum(SalesOrderLine.unit_price * SalesOrderLine.quantity) /func.greatest(func.cast(func.date_part('DAY', func.current_date() - Supplier.create_date),Integer), 1), Integer)]).as_scalar()
def compile(elem, compiler, **kw): return compiler.process(func.current_date())
def __init__(self, idP, ti, name, lastname, gen, birth,phone, age): self.id=idP ; self.ti=ti ; self.nombres=name self.apellidos=lastname ; self.genero=gen self.fechaNacimiento=birth ; self.telefono=phone self.edad = age ; self.fechaIngreso=func.current_date()
def test_function_overrides(self): self.assert_compile(func.current_date(), "GETDATE()") self.assert_compile(func.length(3), "LEN(:length_1)")
def __init__(self, diag, paciente): self.fecha= func.current_date() self.comentarios= diag self.paciente = paciente