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)
