def life_cycle(self, engine, metadata, column, cascade_on_drop): __table__ = create_view(name='trivial_view', selectable=sa.select([column]), metadata=metadata, cascade_on_drop=cascade_on_drop) __table__.create(engine) __table__.drop(engine)
class DatasetView(BaseModel): """Datasets View Model.""" __tablename__ = 'v_datasets' __table__ = create_view( name=__tablename__, selectable=select([ Datasets.created_at, Datasets.updated_at, Datasets.id, Datasets.name, Datasets.title, Datasets.is_public, Datasets.start_date, Datasets.end_date, Datasets.dataset_table_name, Datasets.metadata_json, Datasets.version, Datasets.version_successor, Datasets.version_predecessor, Datasets.description, Datasets.classification_system_id.label( 'classification_system_id'), LucClassificationSystem.name.label('classification_system_name'), LucClassificationSystem.version.label( 'classification_system_version'), Datasets.user_id.label('user_id'), Users.full_name.label('user_name'), Datasets.collect_method_id.label('collect_method_id'), CollectMethod.name.label('collect_method_name') ]).where( and_( Users.id == Datasets.user_id, LucClassificationSystem.id == Datasets.classification_system_id, CollectMethod.id == Datasets.collect_method_id)), metadata=BaseModel.metadata, ) __table__.schema = Config.SAMPLEDB_SCHEMA
class ARTICLE_UNI_VIEW(Base): __table__ = create_view( name='article_university_view', selectable=sa.select( ARTICLES_COLUMNS + [ADMISSION_UNIVERSITIES.university], from_obj=(ARTICLES.__table__.join(ADMISSION_UNIVERSITIES))), metadata=Base.metadata)
class Season_Game_View(db.Base): __table__ = create_view( name="season_game_status", selectable=select( [ db.Season.id.label("id"), func.count(db.GameScrapeStatus.id).label("total_games"), func.sum(db.GameScrapeStatus.scraped_bbref_boxscore).label("total_scraped_bbref_boxscore"), func.sum(db.GameScrapeStatus.scraped_brooks_pitch_logs).label("total_scraped_brooks_pitch_logs"), func.sum(db.GameScrapeStatus.combined_data_success).label("total_combined_data_success"), func.sum(db.GameScrapeStatus.combined_data_fail).label("total_combined_data_fail"), func.sum(db.GameScrapeStatus.pitch_app_count_bbref).label("total_pitch_app_count_bbref"), func.sum(db.GameScrapeStatus.pitch_app_count_brooks).label("total_pitch_app_count_brooks"), func.sum(db.GameScrapeStatus.total_pitch_count_bbref).label("total_pitch_count_bbref"), ] ) .select_from( join( db.Season, db.GameScrapeStatus, db.Season.id == db.GameScrapeStatus.season_id, ) ) .group_by(db.Season.id), metadata=db.Base.metadata, cascade_on_drop=False, )
class ArticleView(Base): __table__ = create_view(name='article_view', selectable=sa.select( [ Article.id, Article.name, User.id.label('author_id'), User.name.label('author_name') ], from_obj=(Article.__table__.join( User, Article.author_id == User.id))), metadata=Base.metadata)
class ARTICLE_UNI_PROGRAM_VIEW(Base): __table__ = create_view( name='article_program_view', selectable=sa.select( ARTICLES_COLUMNS + [ ADMISSION_UNI_PROGRAMS.university, ADMISSION_UNI_PROGRAMS.program_level, ADMISSION_UNI_PROGRAMS.program, ADMISSION_UNI_PROGRAMS.program_type ], from_obj=(ARTICLES.__table__.join(ADMISSION_UNI_PROGRAMS))), metadata=Base.metadata)
class HistoryDatasetCollectionJobStateSummary(View): __view__ = text(AGGREGATE_STATE_QUERY).columns( column('hdca_id', Integer), column('new', Integer), column('resubmitted', Integer), column('waiting', Integer), column('queued', Integer), column('running', Integer), column('ok', Integer), column('error', Integer), column('failed', Integer), column('paused', Integer), column('deleted', Integer), column('deleted_new', Integer), column('upload', Integer), column('all_jobs', Integer)) __table__ = create_view('collection_job_state_summary_view', __view__, metadata)
class ProcessResultView(Base): __table__ = create_view(name='process_result_view', selectable=sa.select([ ProcessStatusDetail.status_id, ProcessStatusDetail.fwan_process_id, ProcessStatusDetail.file_id, ProcessStatusDetail.component_id, ProcessStatusDetail.status, ProcessStatusDetail.status_date, ResultStatus.result_code, ErrorDetail.details.label('error_details') ], from_obj=(ProcessStatusDetail.__table__.join( ResultStatus, ProcessStatusDetail.status_id == ResultStatus.status_id, isouter=True).join(ErrorDetail, ProcessStatusDetail.status_id == ErrorDetail.status_id, isouter=True))), metadata=Base.metadata)
class HistoryDatasetCollectionJobStateSummary(View): __view__ = text(""" SELECT hdca.id as hdca_id, SUM(CASE WHEN state = 'new' THEN 1 ELSE 0 END) AS new, SUM(CASE WHEN state = 'resubmitted' THEN 1 ELSE 0 END) AS resubmitted, SUM(CASE WHEN state = 'waiting' THEN 1 ELSE 0 END) AS waiting, SUM(CASE WHEN state = 'queued' THEN 1 ELSE 0 END) AS queued, SUM(CASE WHEN state = 'running' THEN 1 ELSE 0 END) AS running, SUM(CASE WHEN state = 'ok' THEN 1 ELSE 0 END) AS ok, SUM(CASE WHEN state = 'error' THEN 1 ELSE 0 END) AS error, SUM(CASE WHEN state = 'failed' THEN 1 ELSE 0 END) AS failed, SUM(CASE WHEN state = 'paused' THEN 1 ELSE 0 END) AS paused, SUM(CASE WHEN state = 'deleted' THEN 1 ELSE 0 END) AS deleted, SUM(CASE WHEN state = 'deleted_new' THEN 1 ELSE 0 END) AS deleted_new, SUM(CASE WHEN state = 'upload' THEN 1 ELSE 0 END) AS upload, SUM(CASE WHEN job.id IS NOT NULL THEN 1 ELSE 0 END) AS all_jobs FROM history_dataset_collection_association hdca LEFT JOIN implicit_collection_jobs icj ON icj.id = hdca.implicit_collection_jobs_id LEFT JOIN implicit_collection_jobs_job_association icjja ON icj.id = icjja.implicit_collection_jobs_id LEFT JOIN job ON icjja.job_id = job.id GROUP BY hdca.id """) __view__ = __view__.columns(column('hdca_id', Integer), column('new', Integer), column('resubmitted', Integer), column('waiting', Integer), column('queued', Integer), column('running', Integer), column('ok', Integer), column('error', Integer), column('failed', Integer), column('paused', Integer), column('deleted', Integer), column('deleted_new', Integer), column('upload', Integer), column('all_jobs', Integer)) __table__ = create_view('collection_job_state_summary_view', __view__, metadata)
class Season_Date_View(db.Base): __table__ = create_view( name="season_date_status", selectable=select([ db.Season.id.label("id"), func.count(db.DateScrapeStatus.id).label("total_days"), func.sum(db.DateScrapeStatus.scraped_daily_dash_bbref).label( "total_scraped_daily_dash_bbref"), func.sum(db.DateScrapeStatus.scraped_daily_dash_brooks).label( "total_scraped_daily_dash_brooks"), func.sum(db.DateScrapeStatus.game_count_bbref).label( "total_game_count_bbref"), func.sum(db.DateScrapeStatus.game_count_brooks).label( "total_game_count_brooks"), ]).select_from( join( db.Season, db.DateScrapeStatus, db.Season.id == db.DateScrapeStatus.season_id, )).group_by(db.Season.id), metadata=db.Base.metadata, cascade_on_drop=False, )
class ClassesView(BaseModel): """A LucClass View.""" __tablename__ = 'v_classes' parent_classes = aliased(LucClass) __table__ = create_view( name=__tablename__, selectable=select( [ LucClass.created_at, LucClass.updated_at, LucClass.id, LucClass.name, LucClass.description, LucClass.code, parent_classes.name.label('class_parent_name'), LucClassificationSystem.name.label('class_system_name') ], from_obj=(LucClass.__table__.join( LucClassificationSystem, LucClassificationSystem.id == LucClass.class_system_id).join( parent_classes, LucClass.class_parent_id == parent_classes.id, isouter=True))), metadata=BaseModel.metadata, ) __table__.schema = Config.LCCS_SCHEMA_NAME
from sqlalchemy import Column from sqlalchemy import String from sqlalchemy import Integer from sqlalchemy import Date from sqlalchemy import ForeignKey from sqlalchemy_utils import create_view from sqlalchemy import select # Local application imports from dbmodels.base import Base from dbmodels.product import Product from dbmodels.company import Company from dbmodels.producttype import ProductType from dbmodels.price import Price stmt = select([ Product.name.label('name'), Product.specifications, Company.name.label('company'), Price.current_price, Price.old_price, Price.date ]).select_from( Price.__table__.outerjoin(Product, Price.product_id == Product.id).outerjoin( Company, Company.id == Product.company_id)) # attaches the view to the metadata using the select statement view = create_view('price_view', stmt, Base.metadata) class PriceView(Base): __table__ = view
class Season_Game_PitchApp_View(db.Base): __table__ = create_view( name="season_game_pitch_app_status", selectable=select([ db.Season.id.label("id"), db.Season.year.label("year"), db.Season.season_type.label("season_type"), db.PitchAppScrapeStatus.scrape_status_date_id.label("date_id"), db.PitchAppScrapeStatus.scrape_status_game_id.label("game_id"), db.PitchAppScrapeStatus.bbref_game_id.label("bbref_game_id"), func.count(db.PitchAppScrapeStatus.id).label("total_pitchfx"), func.sum(db.PitchAppScrapeStatus.scraped_pitchfx).label( "total_pitchfx_scraped"), func.sum(db.PitchAppScrapeStatus.no_pitchfx_data).label( "total_no_pitchfx_data"), func.sum( db.PitchAppScrapeStatus.combined_pitchfx_bbref_data).label( "total_combined_pitchfx_bbref_data"), func.sum(db.PitchAppScrapeStatus.batters_faced_bbref).label( "total_batters_faced_bbref"), func.sum(db.PitchAppScrapeStatus.batters_faced_pitchfx).label( "total_batters_faced_pitchfx"), func.sum(db.PitchAppScrapeStatus.pitch_count_pitch_log).label( "total_pitch_count_pitch_log"), func.sum(db.PitchAppScrapeStatus.pitch_count_bbref).label( "total_pitch_count_bbref"), func.sum(db.PitchAppScrapeStatus.pitch_count_pitchfx).label( "total_pitch_count_pitchfx"), func.sum( db.PitchAppScrapeStatus.pitch_count_pitchfx_audited).label( "total_pitch_count_pitchfx_audited"), func.sum( db.PitchAppScrapeStatus.total_at_bats_pitchfx_complete).label( "total_at_bats_pitchfx_complete"), func.sum(db.PitchAppScrapeStatus.patched_pitchfx_count).label( "total_patched_pitchfx_count"), func.sum( db.PitchAppScrapeStatus.total_at_bats_patched_pitchfx).label( "total_at_bats_patched_pitchfx"), func.sum(db.PitchAppScrapeStatus.missing_pitchfx_count).label( "total_missing_pitchfx_count"), func.sum( db.PitchAppScrapeStatus.total_at_bats_missing_pitchfx).label( "total_at_bats_missing_pitchfx"), func.sum(db.PitchAppScrapeStatus.removed_pitchfx_count).label( "total_removed_pitchfx_count"), func.sum( db.PitchAppScrapeStatus.total_at_bats_removed_pitchfx).label( "total_at_bats_removed_pitchfx"), func.sum(db.PitchAppScrapeStatus.pitchfx_error).label( "total_pitchfx_error"), func.sum( db.PitchAppScrapeStatus.total_at_bats_pitchfx_error).label( "total_at_bats_pitchfx_error"), func.sum(db.PitchAppScrapeStatus.invalid_pitchfx).label( "total_invalid_pitchfx"), func.sum(db.PitchAppScrapeStatus.invalid_pitchfx_count).label( "total_invalid_pitchfx_count"), func.sum( db.PitchAppScrapeStatus.total_at_bats_invalid_pitchfx).label( "total_at_bats_invalid_pitchfx"), ]).select_from( join( db.Season, db.PitchAppScrapeStatus, db.Season.id == db.PitchAppScrapeStatus.season_id, )).group_by(db.PitchAppScrapeStatus.scrape_status_game_id), metadata=db.Base.metadata, cascade_on_drop=False, ) @classmethod def get_all_bbref_game_ids_eligible_for_audit(cls, db_engine, year, season_type=SeasonType. REGULAR_SEASON): s = select([cls.bbref_game_id]).where( and_( cls.year == year, cls.season_type == season_type, cls.total_pitchfx == cls.total_pitchfx_scraped, cls.total_pitchfx != cls.total_combined_pitchfx_bbref_data, )) results = db_engine.execute(s).fetchall() return flatten_list2d( [d.values() for d in [dict(row) for row in results]]) @classmethod def get_all_bbref_game_ids_all_pitchfx_logs_are_valid( cls, db_engine, year, season_type=SeasonType.REGULAR_SEASON): s = select([cls.bbref_game_id]).where( and_( cls.year == year, cls.season_type == season_type, cls.total_pitchfx == cls.total_combined_pitchfx_bbref_data, cls.total_pitchfx_error == 0, cls.total_invalid_pitchfx == 0, )) results = db_engine.execute(s).fetchall() return flatten_list2d( [d.values() for d in [dict(row) for row in results]]) @classmethod def get_all_bbref_game_ids_pitchfx_error(cls, db_engine, year, season_type=SeasonType. REGULAR_SEASON): s = select([cls.bbref_game_id]).where( and_( cls.year == year, cls.season_type == season_type, cls.total_pitchfx_error > 0, )) results = db_engine.execute(s).fetchall() return flatten_list2d( [d.values() for d in [dict(row) for row in results]]) @classmethod def get_all_bbref_game_ids_invalid_pitchfx(cls, db_engine, year, season_type=SeasonType. REGULAR_SEASON): s = select([cls.bbref_game_id]).where( and_( cls.year == year, cls.season_type == season_type, cls.total_invalid_pitchfx > 0, )) results = db_engine.execute(s).fetchall() return flatten_list2d( [d.values() for d in [dict(row) for row in results]]) @classmethod def get_all_bbref_game_ids_combined_no_missing_pfx(cls, db_engine, season_type=SeasonType. REGULAR_SEASON): s = select([cls.bbref_game_id]).where( and_( cls.season_type == season_type, cls.total_pitchfx == cls.total_combined_pitchfx_bbref_data, cls.total_missing_pitchfx_count == 0, cls.total_pitchfx_error == 0, cls.total_invalid_pitchfx == 0, )) results = db_engine.execute(s).fetchall() return flatten_list2d( [d.values() for d in [dict(row) for row in results]])
class Game_PitchApp_View(db.Base): __table__ = create_view( name="game_pitch_app_status", selectable=select([ db.GameScrapeStatus.id.label("id"), db.GameScrapeStatus.bbref_game_id.label("bbref_game_id"), func.count(db.PitchAppScrapeStatus.id).label("total_pitchfx"), func.sum(db.PitchAppScrapeStatus.scraped_pitchfx).label( "total_pitchfx_scraped"), func.sum(db.PitchAppScrapeStatus.no_pitchfx_data).label( "total_no_pitchfx_data"), func.sum( db.PitchAppScrapeStatus.combined_pitchfx_bbref_data).label( "total_combined_pitchfx_bbref_data"), func.sum(db.PitchAppScrapeStatus.batters_faced_bbref).label( "total_batters_faced_bbref"), func.sum(db.PitchAppScrapeStatus.batters_faced_pitchfx).label( "total_batters_faced_pitchfx"), func.sum(db.PitchAppScrapeStatus.pitch_count_pitch_log).label( "total_pitch_count_pitch_log"), func.sum(db.PitchAppScrapeStatus.pitch_count_bbref).label( "total_pitch_count_bbref"), func.sum(db.PitchAppScrapeStatus.pitch_count_pitchfx).label( "total_pitch_count_pitchfx"), func.sum( db.PitchAppScrapeStatus.pitch_count_pitchfx_audited).label( "total_pitch_count_pitchfx_audited"), func.sum( db.PitchAppScrapeStatus.total_at_bats_pitchfx_complete).label( "total_at_bats_pitchfx_complete"), func.sum(db.PitchAppScrapeStatus.patched_pitchfx_count).label( "total_patched_pitchfx_count"), func.sum( db.PitchAppScrapeStatus.total_at_bats_patched_pitchfx).label( "total_at_bats_patched_pitchfx"), func.sum(db.PitchAppScrapeStatus.missing_pitchfx_count).label( "total_missing_pitchfx_count"), func.sum( db.PitchAppScrapeStatus.total_at_bats_missing_pitchfx).label( "total_at_bats_missing_pitchfx"), func.sum(db.PitchAppScrapeStatus.removed_pitchfx_count).label( "total_removed_pitchfx_count"), func.sum( db.PitchAppScrapeStatus.total_at_bats_removed_pitchfx).label( "total_at_bats_removed_pitchfx"), func.sum(db.PitchAppScrapeStatus.pitchfx_error).label( "total_pitchfx_error"), func.sum( db.PitchAppScrapeStatus.total_at_bats_pitchfx_error).label( "total_at_bats_pitchfx_error"), func.sum(db.PitchAppScrapeStatus.invalid_pitchfx).label( "total_invalid_pitchfx"), func.sum(db.PitchAppScrapeStatus.invalid_pitchfx_count).label( "total_invalid_pitchfx_count"), func.sum( db.PitchAppScrapeStatus.total_at_bats_invalid_pitchfx).label( "total_at_bats_invalid_pitchfx"), ]).select_from( join( db.GameScrapeStatus, db.PitchAppScrapeStatus, db.GameScrapeStatus.id == db.PitchAppScrapeStatus.scrape_status_game_id, )).group_by(db.GameScrapeStatus.id), metadata=db.Base.metadata, cascade_on_drop=False, )
autoincrement=False, nullable=False), Column('song_id', Integer, ForeignKey(Songs.c.id, ondelete='CASCADE'), autoincrement=False, nullable=False), Column('rating', Integer), UniqueConstraint('user_id', 'song_id', name='songs_ratings_user_id_song_id_key')) avg_songs_ratings_s = (select([SongsRatings.c.song_id, Users.c.id.label('user_id'), func.round(func.avg(SongsRatings.c.rating)) .label('avg_rating')]) .where(SongsRatings.c.user_id != Users.c.id) .group_by(SongsRatings.c.song_id, Users.c.id)) AvgSongsRatings = create_view('avg_songs_ratings', avg_songs_ratings_s, metadata) Albums = \ Table('albums', metadata, Column('id', Integer, primary_key=True, autoincrement=True), Column('path', Text, unique=True, nullable=False)) AlbumsRatings = \ Table('albums_ratings', metadata, Column('user_id', Integer, ForeignKey(Users.c.id, ondelete='CASCADE'), autoincrement=False, nullable=False), Column('album_id', Integer, ForeignKey(Albums.c.id, ondelete='CASCADE'), autoincrement=False, nullable=False),
# Standard library imports # Third party imports from sqlalchemy import Column from sqlalchemy import String from sqlalchemy import Integer from sqlalchemy import Date from sqlalchemy import ForeignKey from sqlalchemy_utils import create_view from sqlalchemy import select # Local application imports from dbmodels.base import Base from dbmodels.product import Product from dbmodels.company import Company from dbmodels.producttype import ProductType stmt = select([ Product.name.label('name'), Product.specifications, ProductType.name.label('type'), Company.name.label('company') ]).select_from(Product.__table__.outerjoin(ProductType, Product.type_id == ProductType.id).outerjoin(Company, Product.company_id == Company.id)) # attaches the view to the metadata using the select statement view = create_view('product_view', stmt, Base.metadata) class ProductView(Base): __table__ = view
class Pitcher_PitchType_All_View(db.Base): __table__ = create_view( name="pitcher_pitchtype_all", selectable=select( [ db.PitchFx.pitcher_id.label("id"), db.PitchFx.pitcher_id_mlb.label("mlb_id"), db.PitchFx.p_throws.label("p_throws"), db.PitchFx.mlbam_pitch_name.label("pitch_type"), total_pitches, total_pa, total_at_bats, total_outs, total_hits, total_bb, total_k, func.avg(db.PitchFx.start_speed).label("avg_speed"), func.avg(db.PitchFx.pfx_x).label("avg_pfx_x"), func.avg(db.PitchFx.pfx_z).label("avg_pfx_z"), func.avg(db.PitchFx.px).label("avg_px"), func.avg(db.PitchFx.pz).label("avg_pz"), func.avg(db.PitchFx.plate_time).label("avg_plate_time"), func.avg(db.PitchFx.extension).label("avg_extension"), func.avg(db.PitchFx.break_angle).label("avg_break_angle"), func.avg(db.PitchFx.break_length).label("avg_break_length"), func.avg(db.PitchFx.break_y).label("avg_break_y"), func.avg(db.PitchFx.spin_rate).label("avg_spin_rate"), func.avg(db.PitchFx.spin_direction).label("avg_spin_direction"), avg, obp, slg, ops, iso, fly_ball_rate, ground_ball_rate, line_drive_rate, popup_rate, hard_hit_rate, medium_hit_rate, soft_hit_rate, barrel_rate, avg_launch_speed, avg_launch_angle, avg_hit_distance, bb_rate, k_rate, hr_per_fb, zone_rate, called_strike_rate, swinging_strike_rate, whiff_rate, bad_whiff_rate, csw_rate, o_swing_rate, z_swing_rate, swing_rate, o_contact_rate, z_contact_rate, contact_rate, total_swings, total_swings_made_contact, total_called_strikes, total_swinging_strikes, total_bad_whiffs, total_inside_strike_zone, total_outside_strike_zone, total_swings_inside_zone, total_swings_outside_zone, total_contact_inside_zone, total_contact_outside_zone, total_balls_in_play, total_ground_balls, total_line_drives, total_fly_balls, total_popups, total_hard_hits, total_medium_hits, total_soft_hits, total_barrels, total_singles, total_doubles, total_triples, total_homeruns, func.sum(db.PitchFx.ab_result_ibb).label("total_ibb"), total_hbp, total_errors, total_sac_hit, total_sac_fly, ] ) .where( and_( db.PitchFx.is_invalid_ibb == 0, db.PitchFx.is_out_of_sequence == 0, or_(db.PitchFx.stand == "L", db.PitchFx.stand == "R"), ) ) .select_from(db.PitchFx) .group_by(db.PitchFx.pitcher_id) .group_by(db.PitchFx.mlbam_pitch_name) .order_by(db.PitchFx.pitcher_id_mlb), metadata=db.Base.metadata, cascade_on_drop=False, ) @classmethod def get_pfx_metrics_for_career_for_pitcher(cls, db_engine, mlb_id): results = db_engine.execute(select([cls]).where(cls.mlb_id == mlb_id)).fetchall() return [dict(row) for row in results] if results else []
UserPickHistory.__table__.c.element == PLPlayersLookup.__table__.c.id ).join( PLTeamsLookup, PLPlayersLookup.__table__.c.team == PLTeamsLookup.__table__.c.id ).join( PLPlayersHistory, and_( UserPickHistory.__table__.c.element == PLPlayersHistory.__table__.c.element, UserPickHistory.__table__.c.week == PLPlayersHistory.__table__.c.round ) ) ) # attaches the view to the metadata using the select statement view_users_pick_history = create_view('users_pick_history_V', stmt_users_pick_history, Base.metadata, cascade_on_drop=False) # provides an ORM interface to the view class UserPickHistory_V(Base): __table__ = view_users_pick_history stmt_user_chip_history = select( [c.label(c.name) for c in UserChipHistory.__table__.c] + [c.label(c.name) for c in LeagueParticipants.__table__.c if c.name in ['player_name', 'entry_name']] ). \ select_from( UserChipHistory.__table__.join(LeagueParticipants, UserChipHistory.__table__.c.user_id == LeagueParticipants.__table__.c.entry)