class Watch(Base): """Watch target and user.""" __tablename__ = 'toranoana_watch' id = Column(Integer, primary_key=True) print_target_id = Column(String, nullable=False) genre_id = Column(Integer, ForeignKey(Genre.id)) genre = relationship(Genre) male = Column( ChoiceType(Target, impl=Integer()), nullable=False, ) female = Column( ChoiceType(Target, impl=Integer()), nullable=False, ) @hybrid_property def male_text(self) -> str: return TARGET_LABEL[self.male] @hybrid_property def female_text(self) -> str: return TARGET_LABEL[self.female]
def upgrade(): ### commands auto generated by Alembic - please adjust! ### op.create_table( 'flight_path_chunks', sa.Column('id', Integer(), nullable=False), sa.Column('time_created', DateTime(), nullable=False), sa.Column('time_modified', DateTime(), nullable=False), sa.Column('timestamps', postgresql.ARRAY(DateTime()), nullable=False), sa.Column('locations', Geometry(geometry_type='LINESTRING', srid=4326), nullable=False), sa.Column('start_time', DateTime(), nullable=False), sa.Column('end_time', DateTime(), nullable=False), sa.Column('flight_id', Integer(), nullable=False), sa.ForeignKeyConstraint(['flight_id'], ['flights.id'], ondelete='CASCADE'), sa.PrimaryKeyConstraint('id')) op.create_index('ix_flight_path_chunks_end_time', 'flight_path_chunks', ['end_time'], unique=False) op.create_index('ix_flight_path_chunks_start_time', 'flight_path_chunks', ['start_time'], unique=False) op.create_index('ix_flight_path_chunks_flight_id', 'flight_path_chunks', ['flight_id'], unique=False)
def put_scripts(scripts): engine = helpers.db_engine() conn = engine.connect() if not scripts.empty: try: scripts.to_sql('scripts', if_exists='replace', con=engine, index=False, chunksize=100, dtype={ 'id': Integer(), 'phone': String(50), 'time_pref': String(50), 'time_pref_label': String(50), 'amount': Integer(), 'transact_date': String(50), 'rejection_reason': String(50), 'day1': String(50), 'file_name_s3': String(50), 'file_upload_to_s3_date': String(50), 'insert_date': String(50) }) except Exception as e: er.handle_error(error_code='23', data={}) sys.exit() return
def save_app_details(): dic_app_details = {} config = yaml.safe_load(open('config.yaml')) db_username = config['mysql']['username'] db_password = config['mysql']['password'] db_endpoint = config['mysql']['endpoint'] db_database = config['mysql']['database'] engine = create_engine( 'mysql+pymysql://{}:{}@{}/{}?charset=utf8mb4'.format( db_username, db_password, db_endpoint, db_database)) with open('../data/steam_app_details.txt', 'r') as f: for i in f.readlines(): try: for app_id, dic_response in json.loads(i).items(): if dic_response.get('success'): dic_app_details[app_id] = parse_steam_app_details( dic_response.get('data', {})) except: pass df_steam_app = pd.DataFrame.from_dict(dic_app_details, 'index') df_steam_app.index.name = 'app_id' df_steam_app.reset_index(inplace=True) df_steam_app.to_sql('game_steam_app', engine, if_exists='replace', index=False, chunksize=10000, dtype={ 'app_id': Integer(), 'required_age': Integer() })
class ClickthroughsByCodename(Base): __tablename__ = "traffic_click" codename = Column("fullname", String(), nullable=False, primary_key=True) date = Column(DateTime(), nullable=False, primary_key=True) interval = Column(String(), nullable=False, primary_key=True) unique_count = Column("unique", Integer()) pageview_count = Column("total", Integer()) @classmethod @memoize_traffic(time=3600) def history(cls, interval, codename): start_time, stop_time, q = make_history_query(cls, interval) q = q.filter(cls.codename == codename) return fill_gaps(interval, start_time, stop_time, q, "unique_count", "pageview_count") @classmethod @memoize_traffic(time=3600) def promotion_history(cls, codename, start, stop): return promotion_history(cls, codename, start, stop) @classmethod @memoize_traffic(time=3600) def historical_totals(cls, interval): return totals(cls, interval)
def test_warning_for_ignored_foreign_keys(self): metadata = MetaData() table1 = Table( "Table1", metadata, Column("primary_key_column", Integer(), primary_key=True), Column("foreign_key_column1", Integer()), Column("foreign_key_column2", Integer()), ForeignKeyConstraint( ("foreign_key_column1", "foreign_key_column2"), ("Table2.primary_key_column1", "Table2.primary_key_column2"), ), ) table2 = Table( "Table2", metadata, Column("primary_key_column1", Integer, primary_key=True), Column("primary_key_column2", Integer, primary_key=True), ) vertex_name_to_table = { "TableWithForeignKey": table1, "TableWithReferencedPrimaryKey": table2, } with pytest.warns(Warning): direct_edge_descriptors = generate_direct_edge_descriptors_from_foreign_keys( vertex_name_to_table) self.assertEqual(direct_edge_descriptors, set())
class TargetedImpressionsByCodename(Base): """Impressions for ads, correlated by ad campaign.""" __tablename__ = "traffic_thingtarget" codename = Column("fullname", String(), nullable=False, primary_key=True) subreddit = Column(String(), nullable=False, primary_key=True) date = Column(DateTime(), nullable=False, primary_key=True) interval = Column(String(), nullable=False, primary_key=True) unique_count = Column("unique", Integer()) pageview_count = Column("total", Integer()) @classmethod @memoize_traffic(time=3600) def promotion_history(cls, codename, start, stop): return promotion_history(cls, cls.pageview_count, codename, start, stop) @classmethod @memoize_traffic(time=3600) def total_by_codename(cls, codenames): return total_by_codename(cls, codenames) @classmethod def campaign_history(cls, codenames, start, stop): return campaign_history(cls, codenames, start, stop)
class Rating(BaseModel): """ Rating table. """ __tablename__ = "ratings" #{ Columns rating_id = Column(Integer(), autoincrement=True, primary_key=True) rater_id = Column(Integer(6), ForeignKey("users.user_id")) movie_id = Column(Integer(5), ForeignKey("movies.movie_id")) rating_date = Column(Date(), nullable=True) rating_value = Column(Integer(length=1)) #{ Relationships rater = relation(User, backref="rated_movies") movie = relation(Movie, backref="ratings") #} @classmethod def get_average_global_rating(cls, db_session): """Return the average global rating.""" query = db_session.query(func.avg(cls.rating_value)).one() return query[0]
def test_edge_generation_from_foreign_keys(self): metadata = MetaData() table1 = Table( "Table1", metadata, Column("primary_key_column", Integer(), primary_key=True), Column("foreign_key_column", Integer(), ForeignKey("Table2.primary_key_column")), ) table2 = Table("Table2", metadata, Column("primary_key_column", Integer, primary_key=True)) vertex_name_to_table = { "TableWithForeignKey": table1, "TableWithReferencedPrimaryKey": table2, } direct_edge_descriptors = generate_direct_edge_descriptors_from_foreign_keys( vertex_name_to_table) self.assertEqual( direct_edge_descriptors, { DirectEdgeDescriptor( from_vertex="TableWithForeignKey", from_column="foreign_key_column", to_vertex="TableWithReferencedPrimaryKey", to_column="primary_key_column", ), }, )
def put_new_trainees(new_df): engine = helpers.db_engine() conn = engine.connect() if not new_df.empty: try: new_df.to_sql('enrolment_record', if_exists = 'append', con = engine, index = False, chunksize = 100, dtype = {'id': Integer(), 'phone': String(50), 'jcn': String(50), 'jc_status': Integer(), 'time_pref': String(50), 'time_pref_label': String(50), 'file_name_s3': String(50), 'file_upload_to_s3_date': String(50), 'breastfeeding': String(50), 'pregnant': String(50), 'children_under6': String(50), 'teenage_girls': String(50), 'nocategory': String(50), 'health_category': String(50), 'insert_date': String(50), 'enrolment_date': String(50), 'pilot': TINYINT(2)}) except Exception as e: er.handle_error(error_code ='23', data = {}) sys.exit() return
class Priority(TableBase): u"""Priority a package can have.""" __tablename__ = 'priorities' ident = Column( Unicode(), primary_key=True, nullable=False, doc=u"Machine-friendly name") name = Column( Unicode(), nullable=False, doc=u"Display name") abbrev = Column( Unicode(), nullable=False, doc=u"Abbreviation for reports") color = Column( Unicode(), nullable=False, doc=u"Color for reports (RRGGBB)") order = Column( Integer(), nullable=False, doc=u"Index for sorting") weight = Column( Integer(), nullable=False, doc=u"Weight for sorting packages") term = Column( Unicode(), nullable=False, doc=u"Terminal representation") def __repr__(self): return '<{} {}>'.format(type(self).__qualname__, self.ident) def __str__(self): return '{} priority'.format(self.name)
class AdserverTargetedSpentPenniesByCodename(Base): """Spend for ads, correlated by ad campaign.""" __tablename__ = "adserver_traffic_spentpenniestarget" codename = Column("fullname", String(), nullable=False, primary_key=True) subreddit = Column(String(), nullable=False, primary_key=True) date = Column(DateTime(), nullable=False, primary_key=True) interval = Column(String(), nullable=False, primary_key=True) unique_count = Column("unique", Integer()) pageview_count = Column("total", Integer()) @classmethod @memoize_traffic(time=3600) def promotion_history(cls, codename, start, stop): return promotion_history(cls, cls.unique_count, codename, start, stop, interval="day") @classmethod @memoize_traffic(time=3600) def total_by_codename(cls, codenames): return total_by_codename(cls, codenames, interval="day") @classmethod def campaign_history(cls, codenames, start, stop): return campaign_history(cls, codenames, start, stop, interval="day")
def save_owned_games(engine): dic_owned_games = {} with open('data/steam_owned_games.txt', 'r') as f: for raw_string in tqdm(f.readlines(), desc='Process Owned Games'): user_id, lst_inventory = list(json.loads(raw_string).items())[0] if lst_inventory: for i in lst_inventory: app_id = i.get('appid') playtime_forever = i.get('playtime_forever', 0) if playtime_forever > 0: dic_owned_games.update({ (user_id, app_id): { 'user_id': user_id, 'app_id': app_id, 'playtime_forever': playtime_forever } }) df_owned_games = pd.DataFrame.from_dict(dic_owned_games, 'index') df_owned_games.to_sql('steam_owned_games', engine, if_exists='replace', index=False, dtype={ 'user_id': BigInteger(), 'app_id': Integer(), 'playtime_forever': Integer() }, chunksize=10000)
class PageviewsBySubreddit(Base): """Pageviews within a subreddit (i.e. /r/something/...).""" __tablename__ = "traffic_subreddits" subreddit = Column(String(), nullable=False, primary_key=True) date = Column(DateTime(), nullable=False, primary_key=True) interval = Column(String(), nullable=False, primary_key=True) unique_count = Column("unique", Integer()) pageview_count = Column("total", Integer()) @classmethod @memoize_traffic(time=3600) def history(cls, interval, subreddit): time_points, q = make_history_query(cls, interval) q = q.filter(cls.subreddit == subreddit) return fill_gaps(time_points, q, "unique_count", "pageview_count") @classmethod @memoize_traffic(time=3600 * 6) def top_last_month(cls, num=None): return top_last_month(cls, "subreddit", num=num) @classmethod @memoize_traffic(time=3600 * 6) def last_month(cls, srs): ids = [sr.name for sr in srs] return top_last_month(cls, "subreddit", ids=ids)
class BlockBase(Base): __tablename__ = 'blocks' # User id. This field is auto generated. id = Column('id', Integer(), primary_key=True) user_id = Column('user_id', Integer(), nullable=False) person_id = Column('person_id', Integer(), nullable=False)
class PageviewsBySubredditAndPath(Base): __tablename__ = "traffic_srpaths" srpath = Column(String(), nullable=False, primary_key=True) date = Column(DateTime(), nullable=False, primary_key=True) interval = Column(String(), nullable=False, primary_key=True) unique_count = Column("unique", Integer()) pageview_count = Column("total", Integer())
class Person(Base): __tablename__ = "people" id = Column(Integer(), primary_key = True) given_name = Column(String(255)) family_name = Column(String(255)) maiden_name = Column(String(255)) suffix = Column(String(255)) gender = Column(String(1)) birthday = Column(DateTime()) birthplace_id = Column(Integer(), ForeignKey("places.id"), index=True) birthplace = relationship("Place", primaryjoin = "(Person.birthplace_id == Place.id)", uselist = False, ) deathday = Column(DateTime()) deathplace_id = Column(Integer(), ForeignKey("places.id"), index=True) deathplace = relationship("Place", primaryjoin = "(Person.deathplace_id == Place.id)", uselist = False, ) father_id = Column(Integer(), ForeignKey("people.id"), index=True) father = relationship("Person", remote_side = [id], primaryjoin = "(Person.father_id == Person.id)", uselist = False, ) mother_id = Column(Integer(), ForeignKey("people.id"), index=True) mother = relationship("Person", remote_side = [id], primaryjoin = "(Person.mother_id == Person.id)", uselist = False, ) children = relationship("Person", remote_side = [mother_id, father_id], primaryjoin = "or_(Person.id == Person.mother_id, " \ +"Person.id == Person.father_id)", ) """ marriages = relationship("Marriage", remote_side = [id], primaryjoin = "or_(Person.id == Marriage.person1_id, " \ +"Person.id == Marriage.person2_id)", ) """ addresses = relationship("Place", secondary = people_addresses, backref="people") emails = relationship("Email", secondary = people_emails, backref="people") files = relationship("File", secondary = people_files, backref="people") phones = relationship("Phone", secondary = people_phones, backref="people") places = relationship("Place", secondary = people_places, backref="people") tags = relationship("Tag", secondary = people_tags, backref="people")
def test_divide_columns_into_type_of_filters(): column_types_dict = { "col_1": Integer(), "col_2": Text(), "col_3": Float(), "col_4": DateTime(), "col_5": ARRAY("string"), "col_6": Boolean(), "col_7": Integer(), "col_8": Float(), "col_9": DateTime(), "col_10": Text(), "col_11": Boolean(), "col_12": ARRAY("string"), } unique_entries = { "col_1": [1 for i in range(MAX_ENTRIES_FOR_FILTER_SELECTOR + 1)], "col_2": ["Dream" for i in range(MAX_ENTRIES_FOR_FILTER_SELECTOR - 1)], "col_3": [2.5 for i in range(MAX_ENTRIES_FOR_FILTER_SELECTOR + 1)], "col_4": ["11/10/2013" for i in range(MAX_ENTRIES_FOR_FILTER_SELECTOR + 1)], "col_5": [[1, 4, 5] for i in range(MAX_ENTRIES_FOR_FILTER_SELECTOR - 1)], "col_6": [True for i in range(MAX_ENTRIES_FOR_FILTER_SELECTOR - 1)], "col_7": [1 for i in range(MAX_ENTRIES_FOR_FILTER_SELECTOR - 1)], "col_8": [3.5 for i in range(MAX_ENTRIES_FOR_FILTER_SELECTOR - 1)], "col_9": ["11/10/2014" for i in range(MAX_ENTRIES_FOR_FILTER_SELECTOR - 1)], "col_10": ["Dream" for i in range(MAX_ENTRIES_FOR_FILTER_SELECTOR + 1)], "col_11": [True for i in range(MAX_ENTRIES_FOR_FILTER_SELECTOR + 1)], "col_12": [[1, 4, 5] for i in range(MAX_ENTRIES_FOR_FILTER_SELECTOR + 1)], } ( filter_column_names, numerical_filter_column_names, unique_entries_dict, ) = divide_columns_into_type_of_filters(unique_entries, column_types_dict) assert set(numerical_filter_column_names) == { "col_1", "col_3", "col_4", "col_7", "col_8", "col_9", } filter_column_names_set = { "col_2", "col_5", "col_6", "col_7", "col_8", "col_9" } assert set(filter_column_names) == filter_column_names_set assert set(unique_entries_dict.keys()) == filter_column_names_set assert unique_entries_dict["col_2"] == unique_entries["col_2"] assert unique_entries_dict["col_5"] == unique_entries["col_5"] assert unique_entries_dict["col_6"] == unique_entries["col_6"] assert unique_entries_dict["col_7"] == unique_entries["col_7"] assert unique_entries_dict["col_8"] == unique_entries["col_8"] assert unique_entries_dict["col_9"] == unique_entries["col_9"]
def load_bls_oes_to_sql(start_year: int = 2017, end_year: int = 2019, db: str = "", table_name: str = "bls_oes", soc_table_name: str = "soc_list"): """ Load BLS OES data from 2019 to the specified table_name. If no table_name is specified, return a dict. # TODO: Check OES data from prior years for formatting # TODO: Clean/combine SOC codes from datasets to include latest data on SOC codes from transitions data """ log.info( "Loading BLS wage and employment data to Postgres if a table_name is specified" ) engine = create_sqlalchemyengine(db=db) bls_oes_data = download_multi_year_oes(start_year=start_year, end_year=end_year) if table_name: log.info("Successfully read OES data. Writing to the {} table".format( table_name)) bls_oes_data.to_sql(table_name, engine, if_exists="replace", index=True, index_label="id", dtype={ "soc_decimal_code": String(), "hourly_mean_wage": Numeric(), "annual_mean_wage": Numeric(), "total_employment": Integer(), "soc_code": String(), "soc_title": String(), "file_year": Integer() }) log.info("Successfully loaded BLS data to Postgres!") # Unique SOC-codes --> occupation descriptions if soc_table_name: log.info("Saving unique SOC codes/descriptions to {}".format( soc_table_name)) unique_soc_codes = (bls_oes_data[["soc_code", "soc_title"]].drop_duplicates()) unique_soc_codes.to_sql(soc_table_name, engine, if_exists="replace", index=True, index_label="id", dtype={ "soc_code": String(), "soc_title": String() }) log.info("Unique SOC codes/descriptions saved!") engine.dispose() return bls_oes_data
class AccountDeletionBase(Base): __tablename__ = 'account_deletions' # User id. This field is auto generated. id = Column('id', Integer(), primary_key=True) diaspora_handle = Column('diaspora_handle', String(255), nullable=False) person_id = Column('person_id', Integer(), nullable=True) completed_at = Column('completed_at', TIMESTAMP(), nullable=False)
class TagBase(Base): __tablename__ = 'tags' # User id. This field is auto generated. id = Column('id', Integer(), primary_key=True) name = Column('name', String(255), nullable=False) taggings_count = Column('taggings_count', Integer(), DefaultClause('0'), nullable=False)
def initialise(metadata): #=============================================================================== from sqlalchemy import Table, Column, ForeignKey from sqlalchemy.types import Integer, Float, Unicode, Date, Enum, BLOB Table( 'Chapter', metadata, Column('oid', Integer(), primary_key=True), Column('title', Unicode(), nullable=False), ) Table( 'Question', metadata, Column('oid', Integer(), primary_key=True), Column('content', BLOB(), nullable=False), Column('format', Enum('PNG', 'TIFF', 'GIF', 'JPEG'), nullable=False), Column('width', Integer(), nullable=False), Column('height', Integer(), nullable=False), Column('chapter', ForeignKey('Chapter.oid'), nullable=False), Column('answers', Unicode(), nullable=False), Column('correct', Unicode(1), nullable=False), ) Table( 'Questionnaire', metadata, Column('oid', Integer(), primary_key=True), Column('date', Date(), nullable=False), Column('title', Unicode(), nullable=False), ) Table( 'QuestionnaireDefn', metadata, Column('oid', Integer(), primary_key=True), Column('questionnaire', ForeignKey('Questionnaire.oid'), nullable=False), Column('question', ForeignKey('Question.oid'), nullable=False), Column('rank', Integer(), nullable=False), Column('page', Integer(), nullable=False), Column('offset', Float(), nullable=False), ) Table( 'Paper', metadata, Column('oid', Integer(), primary_key=True), Column('student', Unicode(), nullable=False), Column('questionnaire', ForeignKey('Questionnaire.oid'), nullable=False), ) Table( 'PaperAnswer', metadata, Column('oid', Integer(), primary_key=True), Column('paper', ForeignKey('Paper.oid'), nullable=False), Column('answer', Unicode(1), nullable=False), )
class EngineBase(Base): __tablename__ = 'engines' id = Column('id', Integer(), primary_key=True) host = Column('diaspora_handle', String(255), nullable=False) priority = Column('person_id', Integer(), nullable=False) created_at = Column('created_at', TIMESTAMP(), nullable=False) updated_at = Column('updated_at', TIMESTAMP(), nullable=False)
class TargetedImpressionsByCodename(Base): __tablename__ = "traffic_thingtarget" codename = Column("fullname", String(), nullable=False, primary_key=True) subreddit = Column(String(), nullable=False, primary_key=True) date = Column(DateTime(), nullable=False, primary_key=True) interval = Column(String(), nullable=False, primary_key=True) unique_count = Column("unique", Integer()) pageview_count = Column("total", Integer())
class AspectMembershipBase(Base): __tablename__ = 'aspect_memberships' # User id. This field is auto generated. id = Column('id', Integer(), primary_key=True) aspect_id = Column('aspect_id', Integer(), nullable=True) contact_id = Column('contact_id', Integer(), nullable=True) created_at = Column('created_at', TIMESTAMP(), nullable=False) updated_at = Column('updated_at', TIMESTAMP(), nullable=False)
class UserPreferenceBase(Base): __tablename__ = 'user_preferences' # User id. This field is auto generated. id = Column('id', Integer(), primary_key=True) email_type = Column('email_type', String(255), nullable=True) user_id = Column('user_id', Integer(), ForeignKey('users.id'), nullable=True) created_at = Column('created_at', TIMESTAMP(), nullable=False) updated_at = Column('updated_at', TIMESTAMP(), nullable=False)
class Image(bm): __tablename__="image" id = Column(Integer,primary_key=True) name = Column(String(20)) system = Column(String(10)) size = Column(Integer(10)) version = Column(String(20)) description = Column(String(20)) status = Column(Integer(10))
class Game(BaseModel): ''' 游戏表,英文代号唯一 ''' __tablename__ = 'game' id = Column(Integer, primary_key=True) name = Column(String(30)) prefix = Column(String(30), nullable=False, unique=True) types = Column(Integer(), nullable=False) is_delete = Column(Integer(), nullable=False, default=0)
class Cabinet(BaseModel): ''' 机柜表,它隶属于idc表,用于idc运维使用 ''' __tablename__ = 'cabinet' id = Column(Integer, primary_key=True) name = Column(String(30), nullable=False) prefix = Column(String(30), nullable=False) idc_id = Column(Integer(), ForeignKey('idc.id'), nullable=False) is_delete = Column(Integer(), nullable=False, default=0)
class Dist(BaseModel): ''' 区组表,它隶属于游戏表,用于根游戏相关的数据查询用 ''' __tablename__ = 'dist' id = Column(Integer, primary_key=True) name = Column(String(30), nullable=False) prefix = Column(String(30), nullable=False) game_id = Column(Integer(), ForeignKey('game.id'), nullable=False) is_delete = Column(Integer(), nullable=False, default=0)