def show_artist(artist_id): data = {} artist = Artist.query.get(artist_id) if artist is None: flash('There is no artist with enterd ID, Try with another ID') return redirect(url_for('index')) data["id"] = artist.id data["name"] = artist.name data["city"] = artist.city data["state"] = artist.state data["phone"] = artist.phone data["facebook_link"] = artist.facebook_link data["image_link"] = artist.image_link geners = [gener for gener in Artist.query.get(artist_id).geners] data["genres"] = geners print(geners) past_shows = db.session.query( Venue.name.label('venue_name'), Venue.id.label('venue_id'), Venue.image_link.label('venue_image_link'), Shows.date.label('start_time')).join(Venue).filter( Shows.date < func.current_date(), Shows.artist_id == artist_id) upcoming_shows = db.session.query( Venue.name.label('venue_name'), Venue.id.label('venue_id'), Venue.image_link.label('venue_image_link'), Shows.date.label('start_time')).join(Venue).filter( Shows.date >= func.current_date(), Shows.artist_id == artist_id) data["past_shows"] = list(past_shows.all()) data["past_shows_count"] = past_shows.count() data["upcoming_shows"] = list(upcoming_shows.all()) data["upcoming_shows_count"] = upcoming_shows.count() return render_template('pages/show_artist.html', artist=data)
def show_venue(venue_id): data = {} venue = Venue.query.get(venue_id) if venue is None: flash('There is no venue with enterd ID, Try with another ID') return redirect(url_for('index')) data["id"] = venue.id data["name"] = venue.name data["address"] = venue.address data["city"] = venue.city data["state"] = venue.state data["phone"] = venue.phone data["facebook_link"] = venue.facebook_link data["image_link"] = venue.image_link geners = [gener for gener in Venue.query.get(venue_id).geners] data["genres"] = geners past_shows = db.session.query( Artist.name.label('artist_name'), Artist.id.label('artist_id'), Artist.image_link.label('artist_image_link'), Shows.date.label('start_time')).join(Artist).filter( Shows.date < func.current_date(), Shows.venue_id == venue_id) upcoming_shows = db.session.query( Artist.name.label('artist_name'), Artist.id.label('artist_id'), Artist.image_link.label('artist_image_link'), Shows.date.label('start_time')).join(Artist).filter( Shows.date >= func.current_date(), Shows.venue_id == venue_id) data["past_shows"] = list(past_shows.all()) data["past_shows_count"] = past_shows.count() data["upcoming_shows"] = list(upcoming_shows.all()) data["upcoming_shows_count"] = upcoming_shows.count() return render_template('pages/show_venue.html', venue=data)
def search_artists(): # TODO: implement search on artists with partial string search. Ensure it is case-insensitive. # seach for "A" should return "Guns N Petals", "Matt Quevado", and "The Wild Sax Band". # search for "band" should return "The Wild Sax Band". search_term = request.form["search_term"] print(search_term) shows_subquery = db.session.query( Shows.artist_id, func.count('*').label('num_upcoming_shows')).filter( Shows.date >= func.current_date()).group_by( Shows.artist_id).subquery() artists_data = db.session.query( Artist.name, Artist.id, shows_subquery.c.num_upcoming_shows).filter( Artist.name.ilike('%' + search_term + '%')).outerjoin( shows_subquery, Artist.id == shows_subquery.c.artist_id) response = {} artists_list = [] response["count"] = artists_data.count() for artist in artists_data.all(): artists_list.append(artist) response["data"] = artists_list # response={ # "count": 1, # "data": [{ # "id": 4, # "name": "Guns N Petals", # "num_upcoming_shows": 0, # }] # } return render_template('pages/search_artists.html', results=response, search_term=request.form.get('search_term', ''))
def search_venues(): # TODO: implement search on artists with partial string search. Ensure it is case-insensitive. # seach for Hop should return "The Musical Hop". # search for "Music" should return "The Musical Hop" and "Park Square Live Music & Coffee" search_term = request.form["search_term"] print(search_term) shows_subquery = db.session.query( Shows.venue_id, func.count('*').label('num_upcoming_shows')).filter( Shows.date >= func.current_date()).group_by( Shows.venue_id).subquery() venues_data = db.session.query( Venue.name, Venue.id, shows_subquery.c.num_upcoming_shows).filter( Venue.name.ilike('%' + search_term + '%')).outerjoin( shows_subquery, Venue.id == shows_subquery.c.venue_id) response = {} venue_list = [] response["count"] = venues_data.count() for venue in venues_data.all(): venue_list.append(venue) response["data"] = venue_list # response={ # "count": 1, # "data": [{ # "id": 2, # "name": "The Dueling Pianos Bar", # "num_upcoming_shows": 0, # }] # } return render_template('pages/search_venues.html', results=response, search_term=request.form.get('search_term', ''))
class Entry(db.Model): """Entry model class Each user has three rows on entries for a date - done, todo, blocking. """ __tablename__ = 'entries' id = db.Column(db.Integer, primary_key=True) text = db.Column(db.String) type = db.Column(db.Enum('done', 'todo', 'blocking', name='entry_types')) date = db.Column(db.Date, server_default=func.current_date()) time_created = db.Column(db.DateTime, server_default=func.current_timestamp()) time_updated = db.Column(db.DateTime, onupdate=func.current_timestamp()) user_id = db.Column(db.Integer, db.ForeignKey('users.id')) user = db.relationship('User', backref=db.backref('entries', lazy='dynamic')) __table_args__ = (db.UniqueConstraint('type', 'user_id', 'date', name='_type_user_date_uc'), ) def __init__(self, text, entry_type, user, date=None): self.text = text self.type = entry_type self.user = user if date: self.date = date def __repr__(self): return '<Entry {}:{}:{}'.format(self.user.name, self.type, self.text[:50])
def venues(): # TODO: replace with real venues data. # num_shows should be aggregated based on number of upcoming shows per venue. # using https://docs.sqlalchemy.org/en/13/orm/tutorial.html venues_cities = db.session.query(Venue.city, Venue.state).order_by( Venue.state).distinct().all() # print('-------------------------------------') # print(venues_cities) data = [] print('-------------------------------------') for city, state in venues_cities: sub_dict = {} sub_dict["city"] = city sub_dict["state"] = state venue_list = [] shows_subquery = db.session.query( Shows.venue_id, func.count('*').label('num_upcoming_shows')).filter( Shows.date >= func.current_date()).group_by( Shows.venue_id).subquery() venues_data = db.session.query( Venue.id, Venue.name, Venue.city, Venue.state, shows_subquery.c.num_upcoming_shows).outerjoin( shows_subquery, Venue.id == shows_subquery.c.venue_id).filter( Venue.city == city, Venue.state == state).order_by(Venue.state, Venue.city).all() for v in venues_data: venue_list.append(v) print(venue_list, '----------------') sub_dict["venues"] = venue_list data.append(dict(sub_dict)) # print(data) # data=[ # { # "city": "San Francisco", # "state": "CA", # "venues": [{ # "id": 1, # "name": "The Musical Hop", # "num_upcoming_shows": 0, # }, { # "id": 3, # "name": "Park Square Live Music & Coffee", # "num_upcoming_shows": 1, # }] # }, { # "city": "New York", # "state": "NY", # "venues": [{ # "id": 2, # "name": "The Dueling Pianos Bar", # "num_upcoming_shows": 0, # }] # } # ] return render_template('pages/venues.html', areas=data)
class Vacation(db.Model): id = db.Column(db.Integer, primary_key=True) vacation_date = db.Column(db.String(50), default=func.current_date()) user_id = db.Column( db.Integer, db.ForeignKey("user.id", ondelete="CASCADE"), nullable=False ) def __repr__(self): return f"Vacation day in {self.vacation_date}"
class Rate(db.Model): __table_args__ = ( UniqueConstraint('currency_id', 'date', name='_ticker__date'), ) id = db.Column(db.Integer, primary_key=True) currency_id = db.Column(db.Integer, db.ForeignKey('currency.id'), nullable=False) date = db.Column(db.Date, nullable=False, server_default=func.current_date()) rate = db.Column(db.Float(decimal_return_scale=2), nullable=False) volume = db.Column(db.Float(decimal_return_scale=2), nullable=False)
class Project(db.Model): id = db.Column(db.Integer, primary_key=True) name = db.Column(db.String(100), nullable=False, unique=True) shortcut = db.Column(db.String(50), nullable=False, unique=True) start_date = db.Column(db.String(50), nullable=False, default=func.current_date()) end_date = db.Column(db.String(50), nullable=False) phase = db.Column(db.String(50), nullable=False) active = db.Column(db.Boolean, default=True) hour = db.relationship("Hour", backref="project", passive_deletes=True) def __repr__(self): return f"Project: {self.name}"
class Hour(db.Model): id = db.Column(db.Integer, primary_key=True) amount = db.Column(db.Integer, nullable=False) work_date = db.Column(db.String(50), default=func.current_date()) project_shortcut = db.Column(db.String(50), db.ForeignKey("project.shortcut"), nullable=False) user_id = db.Column(db.Integer, db.ForeignKey("user.id", ondelete="CASCADE"), nullable=False) def __repr__(self): return f"{self.amount} hours"
class Telemetry(Base): id = Column(Integer, primary_key=True, index=True) speed = Column(Float, nullable=False) distance = Column(Float, nullable=False) engine_temp = Column(Float, nullable=False) creation_time = Column(DateTime, default=func.current_date(), nullable=False) energy_cons = Column(Float, nullable=True) rpm = Column(Integer, nullable=False) battery = Column(Integer, nullable=True) avg_speed= Column(Float, nullable=False) round_id = Column(Integer, ForeignKey("round.id")) rounds = relationship(Round, primaryjoin=round_id == Round.id) latitude = Column(Float, nullable=False) longitude = Column(Float, nullable=False)
def search_venues(): search_term = request.form["search_term"] shows_subquery = db.session.query( Shows.venue_id, func.count('*').label('num_upcoming_shows')).filter( Shows.date >= func.current_date()).group_by( Shows.venue_id).subquery() venues_data = db.session.query( Venue.name, Venue.id, shows_subquery.c.num_upcoming_shows).filter( Venue.name.ilike('%' + search_term + '%')).outerjoin( shows_subquery, Venue.id == shows_subquery.c.venue_id) response = {} response["count"] = venues_data.count() venue_list = [venue for venue in venues_data] response["data"] = venue_list return render_template('pages/search_venues.html', results=response, search_term=request.form.get('search_term', ''))
class Post(db.Model): # pylint: disable=E1101 __tableneme__ = "post" iD = db.Column(db.String, primary_key=True) created_date = db.Column(db.DateTime(timezone=False), server_default=func.now()) updated_date = db.Column(db.String, server_default=func.current_date()) author = db.Column(db.String, nullable=False) html = db.Column(db.LargeBinary, nullable=False) title = db.Column(db.String, nullable=False) s_title = db.Column(db.String, nullable=False) color = db.Column(db.String, nullable=False) date = db.Column(db.String, nullable=False) token = db.Column(db.String, nullable=False) icon = db.Column(db.LargeBinary, nullable=True) ver = db.Column(db.Integer, nullable=False, default=0) def __repr__(self): return "<post iD:{},title:{}>".format(self.iD, self.title)
def search_artists(): search_term = request.form["search_term"] shows_subquery = db.session.query( Shows.artist_id, func.count('*').label('num_upcoming_shows')).filter( Shows.date >= func.current_date()).group_by( Shows.artist_id).subquery() artists_data = db.session.query( Artist.name, Artist.id, shows_subquery.c.num_upcoming_shows).filter( Artist.name.ilike('%' + search_term + '%')).outerjoin( shows_subquery, Artist.id == shows_subquery.c.artist_id) response = {} artists_list = [] response["count"] = artists_data.count() for artist in artists_data.all(): artists_list.append(artist) response["data"] = artists_list return render_template('pages/search_artists.html', results=response, search_term=request.form.get('search_term', ''))
def venues(): venues_cities = db.session.query(Venue.city, Venue.state).order_by( Venue.state).distinct().all() data = [] for city, state in venues_cities: sub_dict = {} sub_dict["city"] = city sub_dict["state"] = state shows_subquery = db.session.query( Shows.venue_id, func.count('*').label('num_upcoming_shows')).filter( Shows.date >= func.current_date()).group_by( Shows.venue_id).subquery() venues_data = db.session.query( Venue.id, Venue.name, Venue.city, Venue.state, shows_subquery.c.num_upcoming_shows).outerjoin( shows_subquery, Venue.id == shows_subquery.c.venue_id).filter( Venue.city == city, Venue.state == state).order_by(Venue.state, Venue.city).all() venue_list = [venue for venue in venues_data] sub_dict["venues"] = venue_list data.append(dict(sub_dict)) return render_template('pages/venues.html', areas=data)
class CoreFixtures(object): # lambdas which return a tuple of ColumnElement objects. # must return at least two objects that should compare differently. # to test more varieties of "difference" additional objects can be added. fixtures = [ lambda: ( column("q"), column("x"), column("q", Integer), column("q", String), ), lambda: (~column("q", Boolean), ~column("p", Boolean)), lambda: ( table_a.c.a.label("foo"), table_a.c.a.label("bar"), table_a.c.b.label("foo"), ), lambda: ( _label_reference(table_a.c.a.desc()), _label_reference(table_a.c.a.asc()), ), lambda: (_textual_label_reference("a"), _textual_label_reference("b")), lambda: ( text("select a, b from table").columns(a=Integer, b=String), text("select a, b, c from table").columns( a=Integer, b=String, c=Integer), text("select a, b, c from table where foo=:bar").bindparams( bindparam("bar", type_=Integer)), text("select a, b, c from table where foo=:foo").bindparams( bindparam("foo", type_=Integer)), text("select a, b, c from table where foo=:bar").bindparams( bindparam("bar", type_=String)), ), lambda: ( column("q") == column("x"), column("q") == column("y"), column("z") == column("x"), column("z") + column("x"), column("z") - column("x"), column("x") - column("z"), column("z") > column("x"), column("x").in_([5, 7]), column("x").in_([10, 7, 8]), # note these two are mathematically equivalent but for now they # are considered to be different column("z") >= column("x"), column("x") <= column("z"), column("q").between(5, 6), column("q").between(5, 6, symmetric=True), column("q").like("somstr"), column("q").like("somstr", escape="\\"), column("q").like("somstr", escape="X"), ), lambda: ( table_a.c.a, table_a.c.a._annotate({"orm": True}), table_a.c.a._annotate({ "orm": True })._annotate({"bar": False}), table_a.c.a._annotate({ "orm": True, "parententity": MyEntity("a", table_a) }), table_a.c.a._annotate({ "orm": True, "parententity": MyEntity("b", table_a) }), table_a.c.a._annotate( { "orm": True, "parententity": MyEntity("b", select([table_a])) }), table_a.c.a._annotate({ "orm": True, "parententity": MyEntity("b", select([table_a]).where(table_a.c.a == 5)), }), ), lambda: ( table_a, table_a._annotate({"orm": True}), table_a._annotate({ "orm": True })._annotate({"bar": False}), table_a._annotate({ "orm": True, "parententity": MyEntity("a", table_a) }), table_a._annotate({ "orm": True, "parententity": MyEntity("b", table_a) }), table_a._annotate({ "orm": True, "parententity": MyEntity("b", select([table_a])) }), ), lambda: ( table("a", column("x"), column("y")), table("a", column("x"), column("y"))._annotate({"orm": True}), table("b", column("x"), column("y"))._annotate({"orm": True}), ), lambda: ( cast(column("q"), Integer), cast(column("q"), Float), cast(column("p"), Integer), ), lambda: ( bindparam("x"), bindparam("y"), bindparam("x", type_=Integer), bindparam("x", type_=String), bindparam(None), ), lambda: (_OffsetLimitParam("x"), _OffsetLimitParam("y")), lambda: (func.foo(), func.foo(5), func.bar()), lambda: (func.current_date(), func.current_time()), lambda: ( func.next_value(Sequence("q")), func.next_value(Sequence("p")), ), lambda: (True_(), False_()), lambda: (Null(), ), lambda: (ReturnTypeFromArgs("foo"), ReturnTypeFromArgs(5)), lambda: (FunctionElement(5), FunctionElement(5, 6)), lambda: (func.count(), func.not_count()), lambda: (func.char_length("abc"), func.char_length("def")), lambda: (GenericFunction("a", "b"), GenericFunction("a")), lambda: (CollationClause("foobar"), CollationClause("batbar")), lambda: ( type_coerce(column("q", Integer), String), type_coerce(column("q", Integer), Float), type_coerce(column("z", Integer), Float), ), lambda: (table_a.c.a, table_b.c.a), lambda: (tuple_(1, 2), tuple_(3, 4)), lambda: (func.array_agg([1, 2]), func.array_agg([3, 4])), lambda: ( func.percentile_cont(0.5).within_group(table_a.c.a), func.percentile_cont(0.5).within_group(table_a.c.b), func.percentile_cont(0.5).within_group(table_a.c.a, table_a.c.b), func.percentile_cont(0.5).within_group(table_a.c.a, table_a.c.b, column("q")), ), lambda: ( func.is_equal("a", "b").as_comparison(1, 2), func.is_equal("a", "c").as_comparison(1, 2), func.is_equal("a", "b").as_comparison(2, 1), func.is_equal("a", "b", "c").as_comparison(1, 2), func.foobar("a", "b").as_comparison(1, 2), ), lambda: ( func.row_number().over(order_by=table_a.c.a), func.row_number().over(order_by=table_a.c.a, range_=(0, 10)), func.row_number().over(order_by=table_a.c.a, range_=(None, 10)), func.row_number().over(order_by=table_a.c.a, rows=(None, 20)), func.row_number().over(order_by=table_a.c.b), func.row_number().over(order_by=table_a.c.a, partition_by=table_a.c.b), ), lambda: ( func.count(1).filter(table_a.c.a == 5), func.count(1).filter(table_a.c.a == 10), func.foob(1).filter(table_a.c.a == 10), ), lambda: ( and_(table_a.c.a == 5, table_a.c.b == table_b.c.a), and_(table_a.c.a == 5, table_a.c.a == table_b.c.a), or_(table_a.c.a == 5, table_a.c.b == table_b.c.a), ClauseList(table_a.c.a == 5, table_a.c.b == table_b.c.a), ClauseList(table_a.c.a == 5, table_a.c.b == table_a.c.a), ), lambda: ( case(whens=[(table_a.c.a == 5, 10), (table_a.c.a == 10, 20)]), case(whens=[(table_a.c.a == 18, 10), (table_a.c.a == 10, 20)]), case(whens=[(table_a.c.a == 5, 10), (table_a.c.b == 10, 20)]), case(whens=[ (table_a.c.a == 5, 10), (table_a.c.b == 10, 20), (table_a.c.a == 9, 12), ]), case( whens=[(table_a.c.a == 5, 10), (table_a.c.a == 10, 20)], else_=30, ), case({ "wendy": "W", "jack": "J" }, value=table_a.c.a, else_="E"), case({ "wendy": "W", "jack": "J" }, value=table_a.c.b, else_="E"), case({ "wendy_w": "W", "jack": "J" }, value=table_a.c.a, else_="E"), ), lambda: ( extract("foo", table_a.c.a), extract("foo", table_a.c.b), extract("bar", table_a.c.a), ), lambda: ( Slice(1, 2, 5), Slice(1, 5, 5), Slice(1, 5, 10), Slice(2, 10, 15), ), lambda: ( select([table_a.c.a]), select([table_a.c.a, table_a.c.b]), select([table_a.c.b, table_a.c.a]), select([table_a.c.b, table_a.c.a]).apply_labels(), select([table_a.c.a]).where(table_a.c.b == 5), select([table_a.c.a]).where(table_a.c.b == 5).where(table_a.c.a == 10), select([table_a.c.a]).where(table_a.c.b == 5).with_for_update(), select([table_a.c.a]).where(table_a.c.b == 5).with_for_update( nowait=True), select([table_a.c.a]).where(table_a.c.b == 5).correlate(table_b), select([table_a.c.a]).where(table_a.c.b == 5).correlate_except( table_b), ), lambda: ( future_select(table_a.c.a), future_select(table_a.c.a).join(table_b, table_a.c.a == table_b.c.a ), future_select(table_a.c.a).join_from(table_a, table_b, table_a.c.a == table_b.c.a), future_select(table_a.c.a).join_from(table_a, table_b), future_select(table_a.c.a).join_from(table_c, table_b), future_select(table_a.c.a).join( table_b, table_a.c.a == table_b.c.a).join( table_c, table_b.c.b == table_c.c.x), future_select(table_a.c.a).join(table_b), future_select(table_a.c.a).join(table_c), future_select(table_a.c.a).join(table_b, table_a.c.a == table_b.c.b ), future_select(table_a.c.a).join(table_c, table_a.c.a == table_c.c.x ), ), lambda: ( select([table_a.c.a]).cte(), select([table_a.c.a]).cte(recursive=True), select([table_a.c.a]).cte(name="some_cte", recursive=True), select([table_a.c.a]).cte(name="some_cte"), select([table_a.c.a]).cte(name="some_cte").alias("other_cte"), select([table_a.c.a]).cte(name="some_cte").union_all( select([table_a.c.a])), select([table_a.c.a]).cte(name="some_cte").union_all( select([table_a.c.b])), select([table_a.c.a]).lateral(), select([table_a.c.a]).lateral(name="bar"), table_a.tablesample(func.bernoulli(1)), table_a.tablesample(func.bernoulli(1), seed=func.random()), table_a.tablesample(func.bernoulli(1), seed=func.other_random()), table_a.tablesample(func.hoho(1)), table_a.tablesample(func.bernoulli(1), name="bar"), table_a.tablesample( func.bernoulli(1), name="bar", seed=func.random()), ), lambda: ( table_a.insert(), table_a.insert().values({})._annotate({"nocache": True}), table_b.insert(), table_b.insert().with_dialect_options(sqlite_foo="some value"), table_b.insert().from_select(["a", "b"], select([table_a])), table_b.insert().from_select(["a", "b"], select([table_a]).where(table_a.c.a > 5)), table_b.insert().from_select(["a", "b"], select([table_b])), table_b.insert().from_select(["c", "d"], select([table_a])), table_b.insert().returning(table_b.c.a), table_b.insert().returning(table_b.c.a, table_b.c.b), table_b.insert().inline(), table_b.insert().prefix_with("foo"), table_b.insert().with_hint("RUNFAST"), table_b.insert().values(a=5, b=10), table_b.insert().values(a=5), table_b.insert().values({ table_b.c.a: 5, "b": 10 })._annotate({"nocache": True}), table_b.insert().values(a=7, b=10), table_b.insert().values(a=5, b=10).inline(), table_b.insert().values([{ "a": 5, "b": 10 }, { "a": 8, "b": 12 }])._annotate({"nocache": True}), table_b.insert().values([{ "a": 9, "b": 10 }, { "a": 8, "b": 7 }])._annotate({"nocache": True}), table_b.insert().values([(5, 10), (8, 12)])._annotate({"nocache": True}), table_b.insert().values([(5, 9), (5, 12)])._annotate({"nocache": True}), ), lambda: ( table_b.update(), table_b.update().where(table_b.c.a == 5), table_b.update().where(table_b.c.b == 5), table_b.update().where(table_b.c.b == 5).with_dialect_options( mysql_limit=10), table_b.update().where(table_b.c.b == 5).with_dialect_options( mysql_limit=10, sqlite_foo="some value"), table_b.update().where(table_b.c.a == 5).values(a=5, b=10), table_b.update().where(table_b.c.a == 5).values(a=5, b=10, c=12), table_b.update().where(table_b.c.b == 5).values(a=5, b=10). _annotate({"nocache": True}), table_b.update().values(a=5, b=10), table_b.update().values({ "a": 5, table_b.c.b: 10 })._annotate({"nocache": True}), table_b.update().values(a=7, b=10), table_b.update().ordered_values(("a", 5), ("b", 10)), table_b.update().ordered_values(("b", 10), ("a", 5)), table_b.update().ordered_values((table_b.c.a, 5), ("b", 10)), ), lambda: ( table_b.delete(), table_b.delete().with_dialect_options(sqlite_foo="some value"), table_b.delete().where(table_b.c.a == 5), table_b.delete().where(table_b.c.b == 5), ), lambda: ( values( column("mykey", Integer), column("mytext", String), column("myint", Integer), name="myvalues", ).data([(1, "textA", 99), (2, "textB", 88)])._annotate({"nocache": True}), values( column("mykey", Integer), column("mytext", String), column("myint", Integer), name="myothervalues", ).data([(1, "textA", 99), (2, "textB", 88)])._annotate({"nocache": True}), values( column("mykey", Integer), column("mytext", String), column("myint", Integer), name="myvalues", ).data([(1, "textA", 89), (2, "textG", 88)])._annotate({"nocache": True}), values( column("mykey", Integer), column("mynottext", String), column("myint", Integer), name="myvalues", ).data([(1, "textA", 99), (2, "textB", 88)])._annotate({"nocache": True}), # TODO: difference in type # values( # [ # column("mykey", Integer), # column("mytext", Text), # column("myint", Integer), # ], # (1, "textA", 99), # (2, "textB", 88), # alias_name="myvalues", # ), ), lambda: ( select([table_a.c.a]), select([table_a.c.a]).prefix_with("foo"), select([table_a.c.a]).prefix_with("foo", dialect="mysql"), select([table_a.c.a]).prefix_with("foo", dialect="postgresql"), select([table_a.c.a]).prefix_with("bar"), select([table_a.c.a]).suffix_with("bar"), ), lambda: ( select([table_a_2.c.a]), select([table_a_2_fs.c.a]), select([table_a_2_bs.c.a]), ), lambda: ( select([table_a.c.a]), select([table_a.c.a]).with_hint(None, "some hint"), select([table_a.c.a]).with_hint(None, "some other hint"), select([table_a.c.a]).with_hint(table_a, "some hint"), select([table_a.c.a]).with_hint(table_a, "some hint").with_hint( None, "some other hint"), select([table_a.c.a]).with_hint(table_a, "some other hint"), select([table_a.c.a]).with_hint( table_a, "some hint", dialect_name="mysql"), select([table_a.c.a]).with_hint( table_a, "some hint", dialect_name="postgresql"), ), lambda: ( table_a.join(table_b, table_a.c.a == table_b.c.a), table_a.join(table_b, and_(table_a.c.a == table_b.c.a, table_a.c.b == 1)), table_a.outerjoin(table_b, table_a.c.a == table_b.c.a), ), lambda: ( table_a.alias("a"), table_a.alias("b"), table_a.alias(), table_b.alias("a"), select([table_a.c.a]).alias("a"), ), lambda: ( FromGrouping(table_a.alias("a")), FromGrouping(table_a.alias("b")), ), lambda: ( SelectStatementGrouping(select([table_a])), SelectStatementGrouping(select([table_b])), ), lambda: ( select([table_a.c.a]).scalar_subquery(), select([table_a.c.a]).where(table_a.c.b == 5).scalar_subquery(), ), lambda: ( exists().where(table_a.c.a == 5), exists().where(table_a.c.b == 5), ), lambda: ( union(select([table_a.c.a]), select([table_a.c.b])), union(select([table_a.c.a]), select([table_a.c.b])).order_by("a"), union_all(select([table_a.c.a]), select([table_a.c.b])), union(select([table_a.c.a])), union( select([table_a.c.a]), select([table_a.c.b]).where(table_a.c.b > 5), ), ), lambda: ( table("a", column("x"), column("y")), table("a", column("y"), column("x")), table("b", column("x"), column("y")), table("a", column("x"), column("y"), column("z")), table("a", column("x"), column("y", Integer)), table("a", column("q"), column("y", Integer)), ), lambda: (table_a, table_b), ] dont_compare_values_fixtures = [ lambda: ( # note the in_(...) all have different column names becuase # otherwise all IN expressions would compare as equivalent column("x").in_(random_choices(range(10), k=3)), column("y").in_( bindparam( "q", random_choices(range(10), k=random.randint(0, 7)), expanding=True, )), column("z").in_(random_choices(range(10), k=random.randint(0, 7))), column("x") == random.randint(1, 10), ) ] def _complex_fixtures(): def one(): a1 = table_a.alias() a2 = table_b_like_a.alias() stmt = (select([table_a.c.a, a1.c.b, a2.c.b]).where(table_a.c.b == a1.c.b).where( a1.c.b == a2.c.b).where(a1.c.a == 5)) return stmt def one_diff(): a1 = table_b_like_a.alias() a2 = table_a.alias() stmt = (select([table_a.c.a, a1.c.b, a2.c.b]).where(table_a.c.b == a1.c.b).where( a1.c.b == a2.c.b).where(a1.c.a == 5)) return stmt def two(): inner = one().subquery() stmt = select([table_b.c.a, inner.c.a, inner.c.b]).select_from( table_b.join(inner, table_b.c.b == inner.c.b)) return stmt def three(): a1 = table_a.alias() a2 = table_a.alias() ex = exists().where(table_b.c.b == a1.c.a) stmt = (select([a1.c.a, a2.c.a]).select_from( a1.join(a2, a1.c.b == a2.c.b)).where(ex)) return stmt return [one(), one_diff(), two(), three()] fixtures.append(_complex_fixtures) def _statements_w_context_options_fixtures(): return [ select([table_a])._add_context_option(opt1, True), select([table_a])._add_context_option(opt1, 5), select([table_a])._add_context_option(opt1, True)._add_context_option( opt2, True), select([table_a ])._add_context_option(opt1, True)._add_context_option(opt2, 5), select([table_a])._add_context_option(opt3, True), ] fixtures.append(_statements_w_context_options_fixtures) def _statements_w_anonymous_col_names(): def one(): c = column("q") l = c.label(None) # new case as of Id810f485c5f7ed971529489b84694e02a3356d6d subq = select([l]).subquery() # this creates a ColumnClause as a proxy to the Label() that has # an anoymous name, so the column has one too. anon_col = subq.c[0] # then when BindParameter is created, it checks the label # and doesn't double up on the anonymous name which is uncachable return anon_col > 5 def two(): c = column("p") l = c.label(None) # new case as of Id810f485c5f7ed971529489b84694e02a3356d6d subq = select([l]).subquery() # this creates a ColumnClause as a proxy to the Label() that has # an anoymous name, so the column has one too. anon_col = subq.c[0] # then when BindParameter is created, it checks the label # and doesn't double up on the anonymous name which is uncachable return anon_col > 5 def three(): l1, l2 = table_a.c.a.label(None), table_a.c.b.label(None) stmt = select([table_a.c.a, table_a.c.b, l1, l2]) subq = stmt.subquery() return select([subq]).where(subq.c[2] == 10) return ( one(), two(), three(), ) fixtures.append(_statements_w_anonymous_col_names)
class CoreFixtures(object): # lambdas which return a tuple of ColumnElement objects. # must return at least two objects that should compare differently. # to test more varieties of "difference" additional objects can be added. fixtures = [ lambda: ( column("q"), column("x"), column("q", Integer), column("q", String), ), lambda: (~column("q", Boolean), ~column("p", Boolean)), lambda: ( table_a.c.a.label("foo"), table_a.c.a.label("bar"), table_a.c.b.label("foo"), ), lambda: ( _label_reference(table_a.c.a.desc()), _label_reference(table_a.c.a.asc()), ), lambda: (_textual_label_reference("a"), _textual_label_reference("b")), lambda: ( text("select a, b from table").columns(a=Integer, b=String), text("select a, b, c from table").columns( a=Integer, b=String, c=Integer ), text("select a, b, c from table where foo=:bar").bindparams( bindparam("bar", type_=Integer) ), text("select a, b, c from table where foo=:foo").bindparams( bindparam("foo", type_=Integer) ), text("select a, b, c from table where foo=:bar").bindparams( bindparam("bar", type_=String) ), ), lambda: ( column("q") == column("x"), column("q") == column("y"), column("z") == column("x"), column("z") + column("x"), column("z") - column("x"), column("x") - column("z"), column("z") > column("x"), column("x").in_([5, 7]), column("x").in_([10, 7, 8]), # note these two are mathematically equivalent but for now they # are considered to be different column("z") >= column("x"), column("x") <= column("z"), column("q").between(5, 6), column("q").between(5, 6, symmetric=True), column("q").like("somstr"), column("q").like("somstr", escape="\\"), column("q").like("somstr", escape="X"), ), lambda: ( table_a.c.a, table_a.c.a._annotate({"orm": True}), table_a.c.a._annotate({"orm": True})._annotate({"bar": False}), table_a.c.a._annotate( {"orm": True, "parententity": MyEntity("a", table_a)} ), table_a.c.a._annotate( {"orm": True, "parententity": MyEntity("b", table_a)} ), table_a.c.a._annotate( {"orm": True, "parententity": MyEntity("b", select([table_a]))} ), ), lambda: ( cast(column("q"), Integer), cast(column("q"), Float), cast(column("p"), Integer), ), lambda: ( bindparam("x"), bindparam("y"), bindparam("x", type_=Integer), bindparam("x", type_=String), bindparam(None), ), lambda: (_OffsetLimitParam("x"), _OffsetLimitParam("y")), lambda: (func.foo(), func.foo(5), func.bar()), lambda: (func.current_date(), func.current_time()), lambda: ( func.next_value(Sequence("q")), func.next_value(Sequence("p")), ), lambda: (True_(), False_()), lambda: (Null(),), lambda: (ReturnTypeFromArgs("foo"), ReturnTypeFromArgs(5)), lambda: (FunctionElement(5), FunctionElement(5, 6)), lambda: (func.count(), func.not_count()), lambda: (func.char_length("abc"), func.char_length("def")), lambda: (GenericFunction("a", "b"), GenericFunction("a")), lambda: (CollationClause("foobar"), CollationClause("batbar")), lambda: ( type_coerce(column("q", Integer), String), type_coerce(column("q", Integer), Float), type_coerce(column("z", Integer), Float), ), lambda: (table_a.c.a, table_b.c.a), lambda: (tuple_(1, 2), tuple_(3, 4)), lambda: (func.array_agg([1, 2]), func.array_agg([3, 4])), lambda: ( func.percentile_cont(0.5).within_group(table_a.c.a), func.percentile_cont(0.5).within_group(table_a.c.b), func.percentile_cont(0.5).within_group(table_a.c.a, table_a.c.b), func.percentile_cont(0.5).within_group( table_a.c.a, table_a.c.b, column("q") ), ), lambda: ( func.is_equal("a", "b").as_comparison(1, 2), func.is_equal("a", "c").as_comparison(1, 2), func.is_equal("a", "b").as_comparison(2, 1), func.is_equal("a", "b", "c").as_comparison(1, 2), func.foobar("a", "b").as_comparison(1, 2), ), lambda: ( func.row_number().over(order_by=table_a.c.a), func.row_number().over(order_by=table_a.c.a, range_=(0, 10)), func.row_number().over(order_by=table_a.c.a, range_=(None, 10)), func.row_number().over(order_by=table_a.c.a, rows=(None, 20)), func.row_number().over(order_by=table_a.c.b), func.row_number().over( order_by=table_a.c.a, partition_by=table_a.c.b ), ), lambda: ( func.count(1).filter(table_a.c.a == 5), func.count(1).filter(table_a.c.a == 10), func.foob(1).filter(table_a.c.a == 10), ), lambda: ( and_(table_a.c.a == 5, table_a.c.b == table_b.c.a), and_(table_a.c.a == 5, table_a.c.a == table_b.c.a), or_(table_a.c.a == 5, table_a.c.b == table_b.c.a), ClauseList(table_a.c.a == 5, table_a.c.b == table_b.c.a), ClauseList(table_a.c.a == 5, table_a.c.b == table_a.c.a), ), lambda: ( case(whens=[(table_a.c.a == 5, 10), (table_a.c.a == 10, 20)]), case(whens=[(table_a.c.a == 18, 10), (table_a.c.a == 10, 20)]), case(whens=[(table_a.c.a == 5, 10), (table_a.c.b == 10, 20)]), case( whens=[ (table_a.c.a == 5, 10), (table_a.c.b == 10, 20), (table_a.c.a == 9, 12), ] ), case( whens=[(table_a.c.a == 5, 10), (table_a.c.a == 10, 20)], else_=30, ), case({"wendy": "W", "jack": "J"}, value=table_a.c.a, else_="E"), case({"wendy": "W", "jack": "J"}, value=table_a.c.b, else_="E"), case({"wendy_w": "W", "jack": "J"}, value=table_a.c.a, else_="E"), ), lambda: ( extract("foo", table_a.c.a), extract("foo", table_a.c.b), extract("bar", table_a.c.a), ), lambda: ( Slice(1, 2, 5), Slice(1, 5, 5), Slice(1, 5, 10), Slice(2, 10, 15), ), lambda: ( select([table_a.c.a]), select([table_a.c.a, table_a.c.b]), select([table_a.c.b, table_a.c.a]), select([table_a.c.a]).where(table_a.c.b == 5), select([table_a.c.a]) .where(table_a.c.b == 5) .where(table_a.c.a == 10), select([table_a.c.a]).where(table_a.c.b == 5).with_for_update(), select([table_a.c.a]) .where(table_a.c.b == 5) .with_for_update(nowait=True), select([table_a.c.a]).where(table_a.c.b == 5).correlate(table_b), select([table_a.c.a]) .where(table_a.c.b == 5) .correlate_except(table_b), ), lambda: ( select([table_a.c.a]).cte(), select([table_a.c.a]).cte(recursive=True), select([table_a.c.a]).cte(name="some_cte", recursive=True), select([table_a.c.a]).cte(name="some_cte"), select([table_a.c.a]).cte(name="some_cte").alias("other_cte"), select([table_a.c.a]) .cte(name="some_cte") .union_all(select([table_a.c.a])), select([table_a.c.a]) .cte(name="some_cte") .union_all(select([table_a.c.b])), select([table_a.c.a]).lateral(), select([table_a.c.a]).lateral(name="bar"), table_a.tablesample(func.bernoulli(1)), table_a.tablesample(func.bernoulli(1), seed=func.random()), table_a.tablesample(func.bernoulli(1), seed=func.other_random()), table_a.tablesample(func.hoho(1)), table_a.tablesample(func.bernoulli(1), name="bar"), table_a.tablesample( func.bernoulli(1), name="bar", seed=func.random() ), ), lambda: ( select([table_a.c.a]), select([table_a.c.a]).prefix_with("foo"), select([table_a.c.a]).prefix_with("foo", dialect="mysql"), select([table_a.c.a]).prefix_with("foo", dialect="postgresql"), select([table_a.c.a]).prefix_with("bar"), select([table_a.c.a]).suffix_with("bar"), ), lambda: ( select([table_a_2.c.a]), select([table_a_2_fs.c.a]), select([table_a_2_bs.c.a]), ), lambda: ( select([table_a.c.a]), select([table_a.c.a]).with_hint(None, "some hint"), select([table_a.c.a]).with_hint(None, "some other hint"), select([table_a.c.a]).with_hint(table_a, "some hint"), select([table_a.c.a]) .with_hint(table_a, "some hint") .with_hint(None, "some other hint"), select([table_a.c.a]).with_hint(table_a, "some other hint"), select([table_a.c.a]).with_hint( table_a, "some hint", dialect_name="mysql" ), select([table_a.c.a]).with_hint( table_a, "some hint", dialect_name="postgresql" ), ), lambda: ( table_a.join(table_b, table_a.c.a == table_b.c.a), table_a.join( table_b, and_(table_a.c.a == table_b.c.a, table_a.c.b == 1) ), table_a.outerjoin(table_b, table_a.c.a == table_b.c.a), ), lambda: ( table_a.alias("a"), table_a.alias("b"), table_a.alias(), table_b.alias("a"), select([table_a.c.a]).alias("a"), ), lambda: ( FromGrouping(table_a.alias("a")), FromGrouping(table_a.alias("b")), ), lambda: ( SelectStatementGrouping(select([table_a])), SelectStatementGrouping(select([table_b])), ), lambda: ( select([table_a.c.a]).scalar_subquery(), select([table_a.c.a]).where(table_a.c.b == 5).scalar_subquery(), ), lambda: ( exists().where(table_a.c.a == 5), exists().where(table_a.c.b == 5), ), lambda: ( union(select([table_a.c.a]), select([table_a.c.b])), union(select([table_a.c.a]), select([table_a.c.b])).order_by("a"), union_all(select([table_a.c.a]), select([table_a.c.b])), union(select([table_a.c.a])), union( select([table_a.c.a]), select([table_a.c.b]).where(table_a.c.b > 5), ), ), lambda: ( table("a", column("x"), column("y")), table("a", column("y"), column("x")), table("b", column("x"), column("y")), table("a", column("x"), column("y"), column("z")), table("a", column("x"), column("y", Integer)), table("a", column("q"), column("y", Integer)), ), lambda: (table_a, table_b), ] dont_compare_values_fixtures = [ lambda: ( # note the in_(...) all have different column names becuase # otherwise all IN expressions would compare as equivalent column("x").in_(random_choices(range(10), k=3)), column("y").in_( bindparam( "q", random_choices(range(10), k=random.randint(0, 7)), expanding=True, ) ), column("z").in_(random_choices(range(10), k=random.randint(0, 7))), column("x") == random.randint(1, 10), ) ] def _complex_fixtures(): def one(): a1 = table_a.alias() a2 = table_b_like_a.alias() stmt = ( select([table_a.c.a, a1.c.b, a2.c.b]) .where(table_a.c.b == a1.c.b) .where(a1.c.b == a2.c.b) .where(a1.c.a == 5) ) return stmt def one_diff(): a1 = table_b_like_a.alias() a2 = table_a.alias() stmt = ( select([table_a.c.a, a1.c.b, a2.c.b]) .where(table_a.c.b == a1.c.b) .where(a1.c.b == a2.c.b) .where(a1.c.a == 5) ) return stmt def two(): inner = one().subquery() stmt = select([table_b.c.a, inner.c.a, inner.c.b]).select_from( table_b.join(inner, table_b.c.b == inner.c.b) ) return stmt def three(): a1 = table_a.alias() a2 = table_a.alias() ex = exists().where(table_b.c.b == a1.c.a) stmt = ( select([a1.c.a, a2.c.a]) .select_from(a1.join(a2, a1.c.b == a2.c.b)) .where(ex) ) return stmt return [one(), one_diff(), two(), three()] fixtures.append(_complex_fixtures)
class CompareAndCopyTest(fixtures.TestBase): # lambdas which return a tuple of ColumnElement objects. # must return at least two objects that should compare differently. # to test more varieties of "difference" additional objects can be added. fixtures = [ lambda: ( column("q"), column("x"), column("q", Integer), column("q", String), ), lambda: (~column("q", Boolean), ~column("p", Boolean)), lambda: ( table_a.c.a.label("foo"), table_a.c.a.label("bar"), table_a.c.b.label("foo"), ), lambda: ( _label_reference(table_a.c.a.desc()), _label_reference(table_a.c.a.asc()), ), lambda: (_textual_label_reference("a"), _textual_label_reference("b")), lambda: ( text("select a, b from table").columns(a=Integer, b=String), text("select a, b, c from table").columns( a=Integer, b=String, c=Integer), ), lambda: ( column("q") == column("x"), column("q") == column("y"), column("z") == column("x"), ), lambda: ( cast(column("q"), Integer), cast(column("q"), Float), cast(column("p"), Integer), ), lambda: ( bindparam("x"), bindparam("y"), bindparam("x", type_=Integer), bindparam("x", type_=String), bindparam(None), ), lambda: (_OffsetLimitParam("x"), _OffsetLimitParam("y")), lambda: (func.foo(), func.foo(5), func.bar()), lambda: (func.current_date(), func.current_time()), lambda: ( func.next_value(Sequence("q")), func.next_value(Sequence("p")), ), lambda: (True_(), False_()), lambda: (Null(), ), lambda: (ReturnTypeFromArgs("foo"), ReturnTypeFromArgs(5)), lambda: (FunctionElement(5), FunctionElement(5, 6)), lambda: (func.count(), func.not_count()), lambda: (func.char_length("abc"), func.char_length("def")), lambda: (GenericFunction("a", "b"), GenericFunction("a")), lambda: (CollationClause("foobar"), CollationClause("batbar")), lambda: ( type_coerce(column("q", Integer), String), type_coerce(column("q", Integer), Float), type_coerce(column("z", Integer), Float), ), lambda: (table_a.c.a, table_b.c.a), lambda: (tuple_([1, 2]), tuple_([3, 4])), lambda: (func.array_agg([1, 2]), func.array_agg([3, 4])), lambda: ( func.percentile_cont(0.5).within_group(table_a.c.a), func.percentile_cont(0.5).within_group(table_a.c.b), func.percentile_cont(0.5).within_group(table_a.c.a, table_a.c.b), func.percentile_cont(0.5).within_group(table_a.c.a, table_a.c.b, column("q")), ), lambda: ( func.is_equal("a", "b").as_comparison(1, 2), func.is_equal("a", "c").as_comparison(1, 2), func.is_equal("a", "b").as_comparison(2, 1), func.is_equal("a", "b", "c").as_comparison(1, 2), func.foobar("a", "b").as_comparison(1, 2), ), lambda: ( func.row_number().over(order_by=table_a.c.a), func.row_number().over(order_by=table_a.c.a, range_=(0, 10)), func.row_number().over(order_by=table_a.c.a, range_=(None, 10)), func.row_number().over(order_by=table_a.c.a, rows=(None, 20)), func.row_number().over(order_by=table_a.c.b), func.row_number().over(order_by=table_a.c.a, partition_by=table_a.c.b), ), lambda: ( func.count(1).filter(table_a.c.a == 5), func.count(1).filter(table_a.c.a == 10), func.foob(1).filter(table_a.c.a == 10), ), lambda: ( and_(table_a.c.a == 5, table_a.c.b == table_b.c.a), and_(table_a.c.a == 5, table_a.c.a == table_b.c.a), or_(table_a.c.a == 5, table_a.c.b == table_b.c.a), ClauseList(table_a.c.a == 5, table_a.c.b == table_b.c.a), ClauseList(table_a.c.a == 5, table_a.c.b == table_a.c.a), ), lambda: ( case(whens=[(table_a.c.a == 5, 10), (table_a.c.a == 10, 20)]), case(whens=[(table_a.c.a == 18, 10), (table_a.c.a == 10, 20)]), case(whens=[(table_a.c.a == 5, 10), (table_a.c.b == 10, 20)]), case(whens=[ (table_a.c.a == 5, 10), (table_a.c.b == 10, 20), (table_a.c.a == 9, 12), ]), case( whens=[(table_a.c.a == 5, 10), (table_a.c.a == 10, 20)], else_=30, ), case({ "wendy": "W", "jack": "J" }, value=table_a.c.a, else_="E"), case({ "wendy": "W", "jack": "J" }, value=table_a.c.b, else_="E"), case({ "wendy_w": "W", "jack": "J" }, value=table_a.c.a, else_="E"), ), lambda: ( extract("foo", table_a.c.a), extract("foo", table_a.c.b), extract("bar", table_a.c.a), ), lambda: ( Slice(1, 2, 5), Slice(1, 5, 5), Slice(1, 5, 10), Slice(2, 10, 15), ), lambda: ( select([table_a.c.a]), select([table_a.c.a, table_a.c.b]), select([table_a.c.b, table_a.c.a]), select([table_a.c.a]).where(table_a.c.b == 5), select([table_a.c.a]).where(table_a.c.b == 5).where(table_a.c.a == 10), select([table_a.c.a]).where(table_a.c.b == 5).with_for_update(), select([table_a.c.a]).where(table_a.c.b == 5).with_for_update( nowait=True), select([table_a.c.a]).where(table_a.c.b == 5).correlate(table_b), select([table_a.c.a]).where(table_a.c.b == 5).correlate_except( table_b), ), lambda: ( table_a.join(table_b, table_a.c.a == table_b.c.a), table_a.join(table_b, and_(table_a.c.a == table_b.c.a, table_a.c.b == 1)), table_a.outerjoin(table_b, table_a.c.a == table_b.c.a), ), lambda: ( table_a.alias("a"), table_a.alias("b"), table_a.alias(), table_b.alias("a"), select([table_a.c.a]).alias("a"), ), lambda: ( FromGrouping(table_a.alias("a")), FromGrouping(table_a.alias("b")), ), lambda: ( select([table_a.c.a]).as_scalar(), select([table_a.c.a]).where(table_a.c.b == 5).as_scalar(), ), lambda: ( exists().where(table_a.c.a == 5), exists().where(table_a.c.b == 5), ), lambda: ( union(select([table_a.c.a]), select([table_a.c.b])), union(select([table_a.c.a]), select([table_a.c.b])).order_by("a"), union_all(select([table_a.c.a]), select([table_a.c.b])), union(select([table_a.c.a])), union( select([table_a.c.a]), select([table_a.c.b]).where(table_a.c.b > 5), ), ), lambda: ( table("a", column("x"), column("y")), table("a", column("y"), column("x")), table("b", column("x"), column("y")), table("a", column("x"), column("y"), column("z")), table("a", column("x"), column("y", Integer)), table("a", column("q"), column("y", Integer)), ), lambda: ( Table("a", MetaData(), Column("q", Integer), Column("b", String)), Table("b", MetaData(), Column("q", Integer), Column("b", String)), ), ] @classmethod def setup_class(cls): # TODO: we need to get dialects here somehow, perhaps in test_suite? [ importlib.import_module("sqlalchemy.dialects.%s" % d) for d in dialects.__all__ if not d.startswith("_") ] def test_all_present(self): need = set( cls for cls in class_hierarchy(ClauseElement) if issubclass(cls, (ColumnElement, Selectable)) and "__init__" in cls.__dict__ and not issubclass(cls, (Annotated)) and "orm" not in cls.__module__ and "crud" not in cls.__module__ and "dialects" not in cls.__module__ # TODO: dialects? ).difference({ColumnElement, UnaryExpression}) for fixture in self.fixtures: case_a = fixture() for elem in case_a: for mro in type(elem).__mro__: need.discard(mro) is_false(bool(need), "%d Remaining classes: %r" % (len(need), need)) def test_compare(self): for fixture in self.fixtures: case_a = fixture() case_b = fixture() for a, b in itertools.combinations_with_replacement( range(len(case_a)), 2): if a == b: is_true( case_a[a].compare(case_b[b], arbitrary_expression=True), "%r != %r" % (case_a[a], case_b[b]), ) else: is_false( case_a[a].compare(case_b[b], arbitrary_expression=True), "%r == %r" % (case_a[a], case_b[b]), ) def test_cache_key(self): def assert_params_append(assert_params): def append(param): if param._value_required_for_cache: assert_params.append(param) else: is_(param.value, None) return append for fixture in self.fixtures: case_a = fixture() case_b = fixture() for a, b in itertools.combinations_with_replacement( range(len(case_a)), 2): assert_a_params = [] assert_b_params = [] visitors.traverse_depthfirst( case_a[a], {}, {"bindparam": assert_params_append(assert_a_params)}, ) visitors.traverse_depthfirst( case_b[b], {}, {"bindparam": assert_params_append(assert_b_params)}, ) if assert_a_params: assert_raises_message( NotImplementedError, "bindparams collection argument required ", case_a[a]._cache_key, ) if assert_b_params: assert_raises_message( NotImplementedError, "bindparams collection argument required ", case_b[b]._cache_key, ) if not assert_a_params and not assert_b_params: if a == b: eq_(case_a[a]._cache_key(), case_b[b]._cache_key()) else: ne_(case_a[a]._cache_key(), case_b[b]._cache_key()) def test_cache_key_gather_bindparams(self): for fixture in self.fixtures: case_a = fixture() case_b = fixture() # in the "bindparams" case, the cache keys for bound parameters # with only different values will be the same, but the params # themselves are gathered into a collection. for a, b in itertools.combinations_with_replacement( range(len(case_a)), 2): a_params = {"bindparams": []} b_params = {"bindparams": []} if a == b: a_key = case_a[a]._cache_key(**a_params) b_key = case_b[b]._cache_key(**b_params) eq_(a_key, b_key) if a_params["bindparams"]: for a_param, b_param in zip(a_params["bindparams"], b_params["bindparams"]): assert a_param.compare(b_param) else: a_key = case_a[a]._cache_key(**a_params) b_key = case_b[b]._cache_key(**b_params) if a_key == b_key: for a_param, b_param in zip(a_params["bindparams"], b_params["bindparams"]): if not a_param.compare(b_param): break else: assert False, "Bound parameters are all the same" else: ne_(a_key, b_key) assert_a_params = [] assert_b_params = [] visitors.traverse_depthfirst( case_a[a], {}, {"bindparam": assert_a_params.append}) visitors.traverse_depthfirst( case_b[b], {}, {"bindparam": assert_b_params.append}) # note we're asserting the order of the params as well as # if there are dupes or not. ordering has to be deterministic # and matches what a traversal would provide. eq_(a_params["bindparams"], assert_a_params) eq_(b_params["bindparams"], assert_b_params) def test_compare_col_identity(self): stmt1 = (select([table_a.c.a, table_b.c.b ]).where(table_a.c.a == table_b.c.b).alias()) stmt1_c = (select([table_a.c.a, table_b.c.b ]).where(table_a.c.a == table_b.c.b).alias()) stmt2 = union(select([table_a]), select([table_b])) stmt3 = select([table_b]) equivalents = {table_a.c.a: [table_b.c.a]} is_false( stmt1.compare(stmt2, use_proxies=True, equivalents=equivalents)) is_true( stmt1.compare(stmt1_c, use_proxies=True, equivalents=equivalents)) is_true((table_a.c.a == table_b.c.b).compare( stmt1.c.a == stmt1.c.b, use_proxies=True, equivalents=equivalents, )) def test_copy_internals(self): for fixture in self.fixtures: case_a = fixture() case_b = fixture() assert case_a[0].compare(case_b[0]) clone = case_a[0]._clone() clone._copy_internals() assert clone.compare(case_b[0]) stack = [clone] seen = {clone} found_elements = False while stack: obj = stack.pop(0) items = [ subelem for key, elem in clone.__dict__.items() if key != "_is_clone_of" and elem is not None for subelem in util.to_list(elem) if (isinstance(subelem, (ColumnElement, ClauseList)) and subelem not in seen and not isinstance( subelem, Immutable) and subelem is not case_a[0]) ] stack.extend(items) seen.update(items) if obj is not clone: found_elements = True # ensure the element will not compare as true obj.compare = lambda other, **kw: False obj.__visit_name__ = "dont_match" if found_elements: assert not clone.compare(case_b[0]) assert case_a[0].compare(case_b[0])
def show_venue(venue_id): # shows the venue page with the given venue_id # TODO: replace with real venue data from the venues table, using venue_id data = {} venue = Venue.query.get(venue_id) if venue is None: flash('There is no venue with enterd ID, Try with another ID') return redirect(url_for('index')) data["id"] = venue.id data["name"] = venue.name data["address"] = venue.address data["city"] = venue.city data["state"] = venue.state data["phone"] = venue.phone data["website"] = venue.website data["facebook_link"] = venue.facebook_link data["seeking_talent"] = venue.seeking_talent data["seeking_description"] = venue.seeking_description data["image_link"] = venue.image_link geners = [] for g in Venue.query.get(venue_id).geners: geners.append(g.name) data["genres"] = geners past_shows = db.session.query( Artist.name.label('artist_name'), Artist.id.label('artist_id'), Artist.image_link.label('artist_image_link'), Shows.date.label('start_time')).join(Artist).filter( Shows.date < func.current_date(), Shows.venue_id == venue_id) upcoming_shows = db.session.query( Artist.name.label('artist_name'), Artist.id.label('artist_id'), Artist.image_link.label('artist_image_link'), Shows.date.label('start_time')).join(Artist).filter( Shows.date >= func.current_date(), Shows.venue_id == venue_id) data["past_shows"] = list(past_shows.all()) data["past_shows_count"] = past_shows.count() data["upcoming_shows"] = list(upcoming_shows.all()) data["upcoming_shows_count"] = upcoming_shows.count() print(data) # data1={ # "id": 1, # "name": "The Musical Hop", # "genres": ["Jazz", "Reggae", "Swing", "Classical", "Folk"], # "address": "1015 Folsom Street", # "city": "San Francisco", # "state": "CA", # "phone": "123-123-1234", # "website": "https://www.themusicalhop.com", # "facebook_link": "https://www.facebook.com/TheMusicalHop", # "seeking_talent": True, # "seeking_description": "We are on the lookout for a local artist to play every two weeks. Please call us.", # "image_link": "https://images.unsplash.com/photo-1543900694-133f37abaaa5?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=400&q=60", # "past_shows": [{ # "artist_id": 4, # "artist_name": "Guns N Petals", # "artist_image_link": "https://images.unsplash.com/photo-1549213783-8284d0336c4f?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=300&q=80", # "start_time": "2019-05-21T21:30:00.000Z" # }], # "upcoming_shows": [], # "past_shows_count": 1, # "upcoming_shows_count": 0, # } # data2={ # "id": 2, # "name": "The Dueling Pianos Bar", # "genres": ["Classical", "R&B", "Hip-Hop"], # "address": "335 Delancey Street", # "city": "New York", # "state": "NY", # "phone": "914-003-1132", # "website": "https://www.theduelingpianos.com", # "facebook_link": "https://www.facebook.com/theduelingpianos", # "seeking_talent": False, # "image_link": "https://images.unsplash.com/photo-1497032205916-ac775f0649ae?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=750&q=80", # "past_shows": [], # "upcoming_shows": [], # "past_shows_count": 0, # "upcoming_shows_count": 0, # } # data3={ # "id": 3, # "name": "Park Square Live Music & Coffee", # "genres": ["Rock n Roll", "Jazz", "Classical", "Folk"], # "address": "34 Whiskey Moore Ave", # "city": "San Francisco", # "state": "CA", # "phone": "415-000-1234", # "website": "https://www.parksquarelivemusicandcoffee.com", # "facebook_link": "https://www.facebook.com/ParkSquareLiveMusicAndCoffee", # "seeking_talent": False, # "image_link": "https://images.unsplash.com/photo-1485686531765-ba63b07845a7?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=747&q=80", # "past_shows": [{ # "artist_id": 5, # "artist_name": "Matt Quevedo", # "artist_image_link": "https://images.unsplash.com/photo-1495223153807-b916f75de8c5?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=334&q=80", # "start_time": "2019-06-15T23:00:00.000Z" # }], # "upcoming_shows": [{ # "artist_id": 6, # "artist_name": "The Wild Sax Band", # "artist_image_link": "https://images.unsplash.com/photo-1558369981-f9ca78462e61?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=794&q=80", # "start_time": "2035-04-01T20:00:00.000Z" # }, { # "artist_id": 6, # "artist_name": "The Wild Sax Band", # "artist_image_link": "https://images.unsplash.com/photo-1558369981-f9ca78462e61?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=794&q=80", # "start_time": "2035-04-08T20:00:00.000Z" # }, { # "artist_id": 6, # "artist_name": "The Wild Sax Band", # "artist_image_link": "https://images.unsplash.com/photo-1558369981-f9ca78462e61?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=794&q=80", # "start_time": "2035-04-15T20:00:00.000Z" # }], # "past_shows_count": 1, # "upcoming_shows_count": 1, # } # data = list(filter(lambda d: d['id'] == venue_id, [data1, data2, data3]))[0] return render_template('pages/show_venue.html', venue=data)
def show_artist(artist_id): # shows the venue page with the given venue_id # TODO: replace with real venue data from the venues table, using venue_id data = {} artist = Artist.query.get(artist_id) if artist is None: flash('There is no artist with enterd ID, Try with another ID') return redirect(url_for('index')) data["id"] = artist.id data["name"] = artist.name geners = [] for g in Artist.query.get(artist_id).geners: geners.append(g.name) data["genres"] = geners data["city"] = artist.city data["state"] = artist.state data["phone"] = artist.phone data["website"] = artist.website data["facebook_link"] = artist.facebook_link data["seeking_venue"] = artist.seeking_venue data["seeking_description"] = artist.seeking_description data["image_link"] = artist.image_link past_shows = db.session.query( Venue.name.label('venue_name'), Venue.id.label('venue_id'), Venue.image_link.label('venue_image_link'), Shows.date.label('start_time')).join(Venue).filter( Shows.date < func.current_date(), Shows.artist_id == artist_id) upcoming_shows = db.session.query( Venue.name.label('venue_name'), Venue.id.label('venue_id'), Venue.image_link.label('venue_image_link'), Shows.date.label('start_time')).join(Venue).filter( Shows.date >= func.current_date(), Shows.artist_id == artist_id) data["past_shows"] = list(past_shows.all()) data["past_shows_count"] = past_shows.count() data["upcoming_shows"] = list(upcoming_shows.all()) data["upcoming_shows_count"] = upcoming_shows.count() # data1={ # "id": 4, # "name": "Guns N Petals", # "genres": ["Rock n Roll"], # "city": "San Francisco", # "state": "CA", # "phone": "326-123-5000", # "website": "https://www.gunsnpetalsband.com", # "facebook_link": "https://www.facebook.com/GunsNPetals", # "seeking_venue": True, # "seeking_description": "Looking for shows to perform at in the San Francisco Bay Area!", # "image_link": "https://images.unsplash.com/photo-1549213783-8284d0336c4f?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=300&q=80", # "past_shows": [{ # "venue_id": 1, # "venue_name": "The Musical Hop", # "venue_image_link": "https://images.unsplash.com/photo-1543900694-133f37abaaa5?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=400&q=60", # "start_time": "2019-05-21T21:30:00.000Z" # }], # "upcoming_shows": [], # "past_shows_count": 1, # "upcoming_shows_count": 0, # } # data2={ # "id": 5, # "name": "Matt Quevedo", # "genres": ["Jazz"], # "city": "New York", # "state": "NY", # "phone": "300-400-5000", # "facebook_link": "https://www.facebook.com/mattquevedo923251523", # "seeking_venue": False, # "image_link": "https://images.unsplash.com/photo-1495223153807-b916f75de8c5?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=334&q=80", # "past_shows": [{ # "venue_id": 3, # "venue_name": "Park Square Live Music & Coffee", # "venue_image_link": "https://images.unsplash.com/photo-1485686531765-ba63b07845a7?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=747&q=80", # "start_time": "2019-06-15T23:00:00.000Z" # }], # "upcoming_shows": [], # "past_shows_count": 1, # "upcoming_shows_count": 0, # } # data3={ # "id": 6, # "name": "The Wild Sax Band", # "genres": ["Jazz", "Classical"], # "city": "San Francisco", # "state": "CA", # "phone": "432-325-5432", # "seeking_venue": False, # "image_link": "https://images.unsplash.com/photo-1558369981-f9ca78462e61?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=794&q=80", # "past_shows": [], # "upcoming_shows": [{ # "venue_id": 3, # "venue_name": "Park Square Live Music & Coffee", # "venue_image_link": "https://images.unsplash.com/photo-1485686531765-ba63b07845a7?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=747&q=80", # "start_time": "2035-04-01T20:00:00.000Z" # }, { # "venue_id": 3, # "venue_name": "Park Square Live Music & Coffee", # "venue_image_link": "https://images.unsplash.com/photo-1485686531765-ba63b07845a7?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=747&q=80", # "start_time": "2035-04-08T20:00:00.000Z" # }, { # "venue_id": 3, # "venue_name": "Park Square Live Music & Coffee", # "venue_image_link": "https://images.unsplash.com/photo-1485686531765-ba63b07845a7?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=747&q=80", # "start_time": "2035-04-15T20:00:00.000Z" # }], # "past_shows_count": 0, # "upcoming_shows_count": 3, # } # data = list(filter(lambda d: d['id'] == artist_id, [data1, data2, data3]))[0] return render_template('pages/show_artist.html', artist=data)
metadata = sa.MetaData() chats = sa.Table('chats', metadata, sa.Column('id', sa.Integer, primary_key=True), sa.Column('telegram_id', sa.Integer, nullable=False), sa.Column('chat_type', sa.String(50)), sa.Column('username', sa.String(200)), sa.Column('first_name', sa.String(200))) notes = sa.Table( 'notes', metadata, sa.Column('id', UUID(as_uuid=True), primary_key=True), sa.Column('chat_id', sa.ForeignKey(chats.c.telegram_id), nullable=False), sa.Column('note_text', sa.Text, nullable=False), sa.Column('note_time', sa.DateTime, default=datetime.utcnow), sa.Column('created', sa.DateTime, default=datetime.utcnow)) sermons = sa.Table('sermons', metadata, sa.Column('id', sa.Integer, primary_key=True), sa.Column('meta_info', sa.String(200)), sa.Column('title', sa.String(200)), sa.Column('sutta', sa.String(200)), sa.Column('sermon_text', sa.Text, nullable=False)) currency_rates = sa.Table( 'currency_rates', metadata, sa.Column('rates', sa.JSON, nullable=False), sa.Column('base', sa.String(10), primary_key=True), sa.Column('lookup_date', sa.Date, primary_key=True, default=func.current_date()))
class PriceHistory(db.Model): __table_args__ = (UniqueConstraint('ticker_id', 'date', name='_ticker__date'), ) id = db.Column(db.Integer, primary_key=True) ticker_id = db.Column(db.Integer, db.ForeignKey('ticker.id'), nullable=False) date = db.Column(db.Date, nullable=False, server_default=func.current_date()) open = db.Column(db.Float(decimal_return_scale=2), nullable=False) close = db.Column(db.Float(decimal_return_scale=2), nullable=False) high = db.Column(db.Float(decimal_return_scale=2), nullable=False) low = db.Column(db.Float(decimal_return_scale=2), nullable=False) volume = db.Column(db.Integer, nullable=False) @classmethod def get_or_create(cls, ticker=None, date=None, **kwargs): instance = cls.query.filter_by(ticker_id=ticker.id, date=date).first() if instance: return instance, False else: kwargs['volume'] = kwargs['volume'].replace(',', '') instance = cls(ticker=ticker, date=date, **kwargs) db.session.add(instance) return instance, True def update(self, commit=False, **kwargs): for key, value in kwargs.items(): setattr(self, key, value) if commit: db.session.commit() @classmethod def get_analytics(cls, ticker_name, date_from, date_to): PricesA = aliased(cls) PricesB = aliased(cls) result = db.session.query( label('open', func.abs(PricesA.open - PricesB.open)), label('close', func.abs(PricesA.close - PricesB.close)), label('low', func.abs(PricesA.low - PricesB.low)), label('high', func.abs(PricesA.close - PricesB.high))).join(Ticker, ).join( PricesB, and_(PricesB.ticker_id == Ticker.id)).filter( Ticker.name == ticker_name, PricesA.date == date_from, PricesB.date == date_to) return result @classmethod def get_delta(cls, ticker_name, type_price, value): sql = text( DELTA_SELECT.format(ticker_name=ticker_name, type_price=type_price, value_delta=value)) result = db.engine.execute(sql) return result.fetchall() def __repr__(self): return '<{} = {}, {}'.format(self.ticker.name, self.volume, self.date)
def query_work_day_stats( company_id, start_date=None, end_date=None, first=None, after=None, tzname="Europe/Paris", ): tz = gettz(tzname) if after: max_time, user_id_ = parse_datetime_plus_id_cursor(after) max_date = max_time.date() end_date = min(max_date, end_date) if end_date else max_date query = (Activity.query.join(Mission).join( Expenditure, and_( Activity.user_id == Expenditure.user_id, Activity.mission_id == Expenditure.mission_id, ), isouter=True, ).with_entities( Activity.id, Activity.user_id, Activity.mission_id, Mission.name, Activity.start_time, Activity.end_time, Activity.type, Expenditure.id.label("expenditure_id"), Expenditure.type.label("expenditure_type"), func.generate_series( func.date_trunc( "day", func.timezone( tzname, func.timezone("UTC", Activity.start_time), ), ), func.timezone( tzname, func.coalesce( func.timezone("UTC", Activity.end_time), func.now(), ), ), "1 day", ).label("day"), ).filter( Mission.company_id == company_id, ~Activity.is_dismissed, Activity.start_time != Activity.end_time, )) query = _apply_time_range_filters( query, to_datetime(start_date, tz_for_date=tz), to_datetime(end_date, tz_for_date=tz, convert_dates_to_end_of_day_times=True), ) has_next_page = False if first: activity_first = max(first * 5, 200) query = query.order_by(desc("day"), desc( Activity.user_id)).limit(activity_first + 1) has_next_page = query.count() > activity_first query = query.subquery() query = (db.session.query(query).group_by( query.c.user_id, query.c.day, query.c.mission_id, query.c.name).with_entities( query.c.user_id.label("user_id"), query.c.day, func.timezone("UTC", func.timezone(tzname, query.c.day)).label("utc_day_start"), query.c.mission_id.label("mission_id"), query.c.name.label("mission_name"), func.min( func.greatest( query.c.start_time, func.timezone("UTC", func.timezone(tzname, query.c.day)), )).label("start_time"), func.max( func.least( func.timezone( "UTC", func.timezone( tzname, query.c.day + func.cast("1 day", Interval)), ), func.coalesce(query.c.end_time, func.now()), )).label("end_time"), func.bool_or( and_( query.c.end_time.is_(None), query.c.day == func.current_date(), )).label("is_running"), *[ func.sum( case( [( query.c.type == a_type.value, extract( "epoch", func.least( func.timezone( "UTC", func.timezone( tzname, query.c.day + func.cast("1 day", Interval), ), ), func.coalesce(query.c.end_time, func.now()), ) - func.greatest( query.c.start_time, func.timezone( "UTC", func.timezone(tzname, query.c.day), ), ), ), )], else_=0, )).label(f"{a_type.value}_duration") for a_type in ActivityType ], func.greatest(func.count(distinct(query.c.expenditure_id)), 1).label("n_exp_dups"), func.count(distinct(query.c.id)).label("n_act_dups"), *[ func.sum( case( [(query.c.expenditure_type == e_type.value, 1)], else_=0, )).label(f"n_{e_type.value}_expenditures") for e_type in ExpenditureType ], ).subquery()) query = (db.session.query(query).group_by( query.c.user_id, query.c.day).with_entities( query.c.user_id.label("user_id"), query.c.day, func.array_agg(distinct( query.c.mission_name)).label("mission_names"), func.min(query.c.start_time).label("start_time"), func.max(query.c.end_time).label("end_time"), func.bool_or(query.c.is_running).label("is_running"), *[ func.sum( getattr(query.c, f"{a_type.value}_duration") / query.c.n_exp_dups).cast(Integer).label( f"{a_type.value}_duration") for a_type in ActivityType ], *[ func.sum( getattr(query.c, f"n_{e_type.value}_expenditures") / query.c.n_act_dups).cast(Integer).label( f"n_{e_type.value}_expenditures") for e_type in ExpenditureType ], ).order_by(desc("day"), desc("user_id")).subquery()) query = db.session.query(query).with_entities( *query.c, extract("epoch", query.c.end_time - query.c.start_time).label("service_duration"), reduce( lambda a, b: a + b, [ getattr(query.c, f"{a_type.value}_duration") for a_type in ActivityType ], ).label("total_work_duration"), ) results = query.all() if after: results = [ r for r in results if r.day.date() < max_date or ( r.day.date() == max_date and r.user_id < user_id_) ] if first: if has_next_page: # The last work day may be incomplete because we didn't fetch all the activities => remove it results = results[:-1] if len(results) > first: results = results[:first] has_next_page = True return results, has_next_page