class Payday(db.Model):
__tablename__ = 'paydays'
__table_args__ = (UniqueConstraint('ts_end', name='paydays_ts_end_key'), )
# TODO: Move this to a different module?
EPOCH = datetime.datetime(1970, 1, 1, tzinfo=pytz.utc)
id = Column(Integer, nullable=False, primary_key=True)
ts_start = Column(TIMESTAMP(timezone=True),
nullable=False,
default="now()")
ts_end = Column(TIMESTAMP(timezone=True), nullable=False, default=EPOCH)
nparticipants = Column(Integer, default=0)
ntippers = Column(Integer, default=0)
ntips = Column(Integer, default=0)
ntransfers = Column(Integer, default=0)
transfer_volume = Column(Numeric(precision=35, scale=2), default=0.0)
ncc_failing = Column(Integer, default=0)
ncc_missing = Column(Integer, default=0)
ncharges = Column(Integer, default=0)
charge_volume = Column(Numeric(precision=35, scale=2), default=0.0)
charge_fees_volume = Column(Numeric(precision=35, scale=2), default=0.0)
nachs = Column(Integer, default=0)
ach_volume = Column(Numeric(precision=35, scale=2), default=0.0)
ach_fees_volume = Column(Numeric(precision=35, scale=2), default=0.0)
nach_failing = Column(Integer, default=0)
class HistoricQuote(Model):
__tablename__ = 'historic_quotes'
id = Column(Integer, primary_key=True, nullable=False)
product_id = Column(Integer, ForeignKey('products.id'), nullable=True)
latest_value = Column(Numeric(36, 8), nullable=True)
open = Column(Numeric(36, 8), nullable=True)
high = Column(Numeric(36, 8), nullable=True)
low = Column(Numeric(36, 8), nullable=True)
close = Column(Numeric(36, 8), nullable=True)
quote_date = Column(DateTime, nullable=True)
effective_from = Column(DateTime, nullable=True)
effective_to = Column(DateTime, nullable=True)
product = relationship("Products")
def serialize(self):
return {
'id': self.id,
'product_id': self.product_id,
'latest_value': self.latest_value,
'open': self.open,
'high': self.high,
'low': self.latest_value,
'close': self.close,
'quote_date': self.quote_date,
'effective_from': self.effective_from,
'effective_to': self.effective_to
}
def __repr__(self):
return self.name
class PitcherSummary(Base, Stamped):
__tablename__ = 'pitcher_summary'
player_id = Column(Integer, ForeignKey('player.id'))
player = relationship(Player)
year = Column(Integer, default=0)
games = Column(Integer, default=0)
innings = Column(Numeric(precision=3, scale=1))
whole_innings = Column(Integer, default=0)
partial_innings = Column(Integer, default=0)
outs = Column(Integer, default=0)
hits = Column(Integer, default=0)
runs = Column(Integer, default=0)
earned_runs = Column(Integer, default=0)
walks = Column(Integer, default=0)
strikeouts = Column(Integer, default=0)
home_runs = Column(Integer, default=0)
pitches = Column(Integer, default=0)
strikes = Column(Integer, default=0)
ground_balls = Column(Integer, default=0)
fly_balls = Column(Integer, default=0)
batters = Column(Integer, default=0)
wins = Column(Integer, default=0)
losses = Column(Integer, default=0)
holds = Column(Integer, default=0)
saves = Column(Integer, default=0)
blown = Column(Integer, default=0)
era = Column(Numeric(precision=6, scale=2))
whip = Column(Numeric(precision=6, scale=2))
def load_bls_oes_to_sql(file_to_load=""):
"""
Load BLS OES data from 2019 to Postgres.
# 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")
engine = create_sqlalchemyengine(
username=USERNAME, password=PASSWORD, port=PORT, host=HOST
)
if file_to_load == "":
bls_oes_data = OESDataDownloader().download_oes_data("2019")
else:
bls_oes_data = pd.read_excel(file_to_load)
# TODO: Abstract into data cleaning step once we finalize format
# O*Net SOC codes generally append .00 to the original SOC codes
bls_oes_data = (
bls_oes_data[
["area_title", "occ_code", "occ_title", "h_mean", "a_mean", "tot_emp"]
]
.assign(
soc_decimal_code=bls_oes_data["occ_code"].apply(
lambda x: "{}.00".format(x)
),
h_mean=bls_oes_data["h_mean"].apply(lambda x: to_float(x)),
a_mean=bls_oes_data["a_mean"].apply(lambda x: to_float(x)),
tot_emp=bls_oes_data["tot_emp"].apply(lambda x: to_int(x)),
)
.rename(
{
"occ_code": "soc_code",
"occ_title": "soc_title",
"h_mean": "hourly_mean_wage",
"a_mean": "annual_mean_wage",
"tot_emp": "total_employment",
},
axis=1,
)
)
bls_oes_data.to_sql(
"bls_oes",
engine,
if_exists="replace",
index=False,
dtype={
"soc_decimal_code": String(),
"hourly_mean_wage": Numeric(),
"annual_mean_wage": Numeric(),
"total_employment": Integer(),
"soc_code": String(),
"soc_title": String(),
},
)
log.info("Successfully loaded BLS data to Postgres!")
return bls_oes_data
def step3_match_controls_to_sql(self):
path = os.path.join(head, 'data', 'interim')
file = "q13_matched_controls.csv"
t_print("reading csv..")
mc = pd.read_csv(path + file)
t_print("read")
print_time()
types = {
"icustay_id": Integer(),
"hadm_id": Integer(),
"intime": DateTime(),
"outtime": DateTime(),
"length_of_stay": Numeric(),
"control_onset_hour": Numeric(),
"control_onset_time": DateTime(),
"matched_case_icustay_id": Integer()
}
t_print("saving to SQL...")
# somehow we cannot overwrite tables directly with "to_sql" so let's do that before
conn = psycopg2.connect(dbname=self.dbname,
user=self.sqluser,
password=self.sqlpass,
host=self.host)
cur = conn.cursor()
cur.execute(self.query_schema +
"drop table IF EXISTS matched_controls_hourly cascade")
conn.commit()
mc[mc.columns].to_sql("matched_controls_hourly",
self.engine,
if_exists='append',
schema="mimic3_mrosnati",
dtype=types)
t_print("saved")
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 Prestataire(base):
__tablename__ = 'contractor'
id = Column(Integer, primary_key=True, autoincrement=True)
contact_id = Column(Integer, ForeignKey('contact.id'))
company_id = Column(Integer, ForeignKey('company.id'))
invoice_id = Column(Integer, ForeignKey('invoice.id'))
expensess = Column(Numeric(5, 2))
contractor_payment_ht = Column(Numeric(5, 2))
contractor_payment_ttc = Column(Numeric(5, 2))
class Amount(base):
__tablename__ = 'amount'
id = Column(Integer, primary_key=True, autoincrement=True)
quote_id = Column(Integer, ForeignKey('quote.id'))
ht_amount = Column(Numeric(5, 2))
ttc_amount = Column(Numeric(5, 2))
payment_status = Column(ENUM('Payé', 'En attente', name='payment_status'))
paid_at = Column(DateTime)
invoice_id = Column(Integer, ForeignKey('invoice.id'))
class Market(Base, PrimaryKeyPairMixin):
current_price = Column(
Numeric(36, 18),
nullable=False,
default=decimal.Decimal(0),
)
maker_fee = Column(Numeric(36, 18), nullable=False)
taker_fee = Column(Numeric(36, 18), nullable=False)
minimum_order_amount = Column(Numeric(36, 18), nullable=False)
__tablename__ = 'market'
class Exchange(db.Model):
__tablename__ = 'exchanges'
id = Column(Integer, nullable=False, primary_key=True)
timestamp = Column(TIMESTAMP(timezone=True), nullable=False,
default="now()")
amount = Column(Numeric(precision=35, scale=2), nullable=False)
fee = Column(Numeric(precision=35, scale=2), nullable=False)
participant = Column(Text, ForeignKey("participants.username",
onupdate="CASCADE", ondelete="RESTRICT"),
nullable=False)
class CirculatorRidership(Base):
"""Table holding the ridership data for the circulator"""
__tablename__ = 'ccc_ridership'
vehicle = Column(String(length=15), primary_key=True)
route = Column(String(length=20), primary_key=True)
stop = Column(String(length=70), primary_key=True)
latitude = Column(Numeric(precision=9, scale=6))
longitude = Column(Numeric(precision=9, scale=6))
datetime = Column(DateTime, primary_key=True)
boardings = Column(Integer)
alightings = Column(Integer)
def upgrade():
op.create_table(
'trade', Column('id', UUID(), nullable=False),
Column('created_at', DateTime(timezone=True), nullable=False),
Column('buy_order_id', UUID(), nullable=False),
Column('sell_order_id', UUID(), nullable=False),
Column(
'side',
Enum('buy', 'sell', name='order_side', create_type=False),
nullable=False,
), Column('volume', Numeric(precision=36, scale=18), nullable=False),
Column('price', Numeric(precision=36, scale=18), nullable=False),
Column('index', Integer(), nullable=False),
Column('base_currency', Unicode(), nullable=False),
Column('quote_currency', Unicode(), nullable=False),
CheckConstraint('volume > 0', name='ck_trade_volume'),
CheckConstraint('price > 0', name='ck_trade_price'),
CheckConstraint('index >= 0', name='ck_trade_index'),
ForeignKeyConstraint(
['base_currency'],
['currency.id'],
),
ForeignKeyConstraint(
['buy_order_id', 'base_currency', 'quote_currency'],
['order.id', 'order.base_currency', 'order.quote_currency'],
), ForeignKeyConstraint(
['buy_order_id'],
['order.id'],
), ForeignKeyConstraint(
['quote_currency'],
['currency.id'],
),
ForeignKeyConstraint(
['sell_order_id', 'base_currency', 'quote_currency'],
['order.id', 'order.base_currency', 'order.quote_currency'],
), ForeignKeyConstraint(
['sell_order_id'],
['order.id'],
), PrimaryKeyConstraint('id'))
op.create_index(
op.f('ix_trade_created_at'),
'trade',
['created_at'],
unique=False,
)
op.create_index(
op.f('ix_trade_sort'),
'trade',
['base_currency', 'quote_currency', 'created_at', 'index'],
unique=False,
)
def product_out():
data.to_sql(table_name,
conn,
if_exists='replace',
chunksize=500,
index=True,
index_label='Id',
dtype={
'waktu': DateTime(),
'sku': String(1000),
'nama_barang': String(1000),
'jumlah_keluar': Integer,
'harga_jual': Numeric(),
'total': Numeric(),
'catatan': String(1000)
})
class Invocation(Base):
"""."""
__tablename__ = "invocation"
invocation_id = Column("invocation_id", KeyInteger, primary_key=True)
wf_id = Column(
"wf_id",
KeyInteger,
ForeignKey(Workflow.wf_id, ondelete="CASCADE"),
nullable=False,
)
job_instance_id = Column(
"job_instance_id",
KeyInteger,
ForeignKey(JobInstance.job_instance_id, ondelete="CASCADE"),
nullable=False,
)
task_submit_seq = Column("task_submit_seq", Integer, nullable=False)
start_time = Column("start_time",
TimestampType,
nullable=False,
default=time.time())
remote_duration = Column("remote_duration", DurationType, nullable=False)
remote_cpu_time = Column("remote_cpu_time", DurationType)
exitcode = Column("exitcode", Integer, nullable=False)
transformation = Column("transformation", Text, nullable=False)
executable = Column("executable", Text, nullable=False)
argv = Column("argv", Text)
abs_task_id = Column("abs_task_id", String(255))
maxrss = Column("maxrss", Integer)
avg_cpu = Column("avg_cpu", Numeric(precision=16, scale=6))
class Event(Base):
__tablename__ = 'events'
__table_args__ = (
UniqueConstraint('name', 'venue_id', 'start_time', name='name_venue_date_uc'),
)
id = Column(Integer, primary_key=True)
name = Column(String(255), nullable=False)
start_time = Column(DateTime, nullable=False, index=True)
end_time = Column(DateTime)
est_end_time = Column(DateTime)
eventful_popularity = Column(Integer)
songkick_popularity = Column(Numeric(precision=7, scale=6))
eventful_id = Column(String(255))
songkick_id = Column(String(255))
eventbrite_id = Column(String(255))
tickets_sold = Column(Integer)
image_url = Column(String(255))
venue_id = Column(Integer, ForeignKey('venues.id'))
venue = relationship('Venue', back_populates='events', uselist=False)
categories = relationship('Category', secondary=association_table, back_populates='events')
uber_prices = relationship('UberPrice', back_populates='event')
lyft_prices = relationship('LyftPrice', back_populates='event')
def __repr__(self):
return "{} @ {}".format(self.name, self.venue.name)
class RoomBandModifierPrice(Base):
""" Band prices for a given year """
__tablename__ = 'room_band_modifier_prices'
modifier_id = Column(Integer,
ForeignKey(RoomBandModifier.id),
primary_key=True,
nullable=False)
season_id = Column(Integer,
ForeignKey(BallotSeason.year),
primary_key=True,
nullable=False)
season = relationship(lambda: BallotSeason, backref="modifier_prices")
modifier = relationship(lambda: RoomBandModifier, backref="prices")
# this property is only used for the side effects
_modifier = relationship(
lambda: RoomBandModifier,
backref=backref(
"price_for",
collection_class=attribute_mapped_collection('season')))
discount = Column(Numeric(6, 2), nullable=False)
def __repr__(self):
return "RoomBandModifierPrice({!r}, discount={})".format(
self.modifier.name, self.discount)
class FareAttribute(Base):
datasource = config.DATASOURCE_GTFS
filename = 'fare_attributes.txt'
__tablename__ = 'fare_attributes'
fare_id = Column(String(255), primary_key=True)
price = Column(Numeric(10, 2), nullable=False)
currency_type = Column(String(255), nullable=False)
payment_method = Column(Integer, nullable=False)
transfers = Column(Integer)
transfer_duration = Column(Integer)
agency_id = Column(String(255))
def to_dict(self):
fields = {
'fare_id': self.fare_id,
'price': self.price,
'currency_type': self.currency_type,
'payment_method': self.payment_method,
'transfers': self.transfers,
'transfer_duration': self.transfer_duration,
'agency_id': self.agency_id
}
return fields
def to_json(self):
return json.dumps(self.to_dict())
class Account(db.Model):
__tablename__ = 'account'
__bind_key__ = 'hss'
account = Column(String(16), primary_key=True, nullable=False)
name = Column(String(255), nullable=False)
finance_balance = Column(Numeric(5, 2), nullable=False, default=0)
traffic_balance = Column(BigInteger, nullable=False, default=10000000000)
access_id = Column('access', Integer, ForeignKey('access.id'))
ips = relationship('IP')
macs = relationship('Mac')
traffic_log = relationship('TrafficLog')
traffic_quota_id = Column('traffic_quota', Integer, ForeignKey('traffic_quota.id'))
# traffic_quota is available using a backref
properties = relationship('AccountProperty', uselist=False)
fees = relationship('AccountFeeRelation')
transactions = relationship('AccountStatementLog')
@property
def combined_transactions(self):
return sorted([
*(TransactionTuple(value=-f.fee_object.amount, datum=f.fee_object.timestamp)
for f in self.fees),
*(TransactionTuple(value=t.amount, datum=t.timestamp)
for t in self.transactions),
], key=attrgetter('datum'))
class Cupons(Base):
id = Column(Integer, nullable=False, primary_key=True)
cupon_uuid = Column(String)
cupon_name = Column(String)
cupon_fee = Column(Numeric(10, 2))
qty = Column(Integer)
active = Column(Boolean)
class VoucherMaster(Base): __tablename__ = "voucher_master" vouchercode = Column(Integer, primary_key=True) reference = Column(String(40), nullable=False) voucherdate = Column(TIMESTAMP, nullable=False) reffdate = Column(TIMESTAMP) vouchertype = Column(String(40)) flag = Column(Integer) projectcode = Column(Integer) narration = Column(Text, nullable=False) pono = Column(Text) podate = Column(TIMESTAMP) poamt = Column(Numeric(13,2)) def __init__(self, vouchercode, reference, voucherdate, reffdate, vouchertype, flag, projectcode, narration,pono ,podate , poamt): self.vouchercode = vouchercode self.reference = reference self.voucherdate = voucherdate self.reffdate = reffdate self.vouchertype = vouchertype self.flag = flag self.projectcode = projectcode self.narration = narration self.pono = pono self.podate = podate self.poamt = poamt
class PitcherGame(Base, Stamped):
__tablename__ = 'pitcher_game'
player_id = Column(Integer, ForeignKey('player.id'))
player = relationship(Player)
team_id = Column(Integer, ForeignKey('team.id'))
team = relationship(Team)
game_id = Column(Integer, ForeignKey('game.id'))
game = relationship(Game)
spot = Column(Integer, default=0) # can't use 'order'
innings = Column(Numeric(precision=3, scale=1))
whole_innings = Column(Integer, default=0)
partial_innings = Column(Integer, default=0)
outs = Column(Integer, default=0)
hits = Column(Integer, default=0)
runs = Column(Integer, default=0)
earned_runs = Column(Integer, default=0)
walks = Column(Integer, default=0)
strikeouts = Column(Integer, default=0)
home_runs = Column(Integer, default=0)
pitches = Column(Integer, default=0)
strikes = Column(Integer, default=0)
ground_balls = Column(Integer, default=0)
fly_balls = Column(Integer, default=0)
batters = Column(Integer, default=0)
win = Column(Integer, default=0)
loss = Column(Integer, default=0)
hold = Column(Integer, default=0)
sv = Column(Integer, default=0) # can't overload 'save'
blown = Column(Integer, default=0)
start = Column(Boolean, default=False)
relief = Column(Boolean, default=False)
class Transaction(Base):
id = Column(UUIDType, primary_key=True, default=uuid.uuid4)
created_at = Column(UtcDateTime, nullable=False, default=utcnow())
type = Column(
EnumType(TransactionType, name='transaction_type'),
nullable=False,
index=True,
)
user_id = Column(
UUIDType,
ForeignKey('user.id'),
index=True,
nullable=False,
)
user = relationship('User')
currency = Column(
Unicode,
ForeignKey('currency.id'),
index=True,
nullable=False,
)
amount = Column(Numeric(36, 18), nullable=False)
__tablename__ = 'transaction'
__table_args__ = (CheckConstraint(amount != 0, 'ck_transaction_amount'), )
__mapper_args__ = {
'polymorphic_on': type,
'polymorphic_identity': None,
'with_polymorphic': '*',
}
class Supply(BaseModel):
'充装记录'
__tablename__ = 'supply'
id = Column(BigInteger, primary_key=True, autoincrement=True)
serial_number = Column(String(64),
server_default='',
nullable=False,
index=True)
medium = Column(String(64), server_default='', nullable=False, index=True)
cpu_id = Column(String(64), server_default='', nullable=False, index=True)
create = Column(BigInteger, server_default='0', nullable=False, index=True)
before = Column(Numeric(9, 2), server_default='0', nullable=False)
after = Column(Numeric(9, 2), server_default='0', nullable=False)
add = Column(Numeric(9, 2), server_default='0', nullable=False)
adjust = Column(Numeric(9, 2), server_default='0', nullable=False)
class LiftIncrement(Model):
__tablename__ = 'lift_increments'
id = Column(Integer, primary_key=True)
lift = Column(Text, ForeignKey('lifts.name'))
amount = Column(Numeric(10,2), nullable=False)
class FeeInfo(db.Model):
__tablename__ = 'fee_info'
__bind_key__ = 'hss'
id = Column(Integer, primary_key=True, nullable=False)
amount = Column(Numeric(5, 2), nullable=False)
description = Column(String(255), nullable=False)
timestamp = Column(TIMESTAMP, nullable=False)
class LyftPrice(Base):
__tablename__ = 'lyft_prices'
id = Column(Integer, primary_key=True)
event_id = Column(Integer, ForeignKey('events.id'), nullable=False)
event = relationship('Event', back_populates='lyft_prices', uselist=False)
primetime = Column(Numeric(precision=3, scale=1), nullable=False)
time = Column(DateTime, nullable=False)
class PurchaseMaster(Base): __tablename__ = "purchasemaster" pbillid = Column(Integer, primary_key=True) pbillno = Column(Text, nullable=False) pbilldate = Column(TIMESTAMP, nullable=False) reffdate = Column(TIMESTAMP) suppliername = Column(Text) pckfwd = Column(Numeric(6, 2)) tax = Column(Numeric(8, 2)) def __init__(self, pbillid, pbillno, pbilldate, reffdate, suppliername, pckfwd, tax): self.pbillid = pbillid self.pbillno = pbillno self.pbilldate = pbilldate self.reffdate = reffdate self.suppliername = suppliername self.pckfwd = pckfwd self.tax = tax
class AtvesCamLocations(Base):
"""The camera location database looks like this"""
__tablename__ = 'atves_cam_locations'
location_code = Column(String(length=100), primary_key=True)
locationdescription = Column(String(length=100), nullable=False)
lat = Column(Numeric(precision=6, scale=4))
long = Column(Numeric(precision=6, scale=4))
cam_type = Column(String(length=2), nullable=False)
effective_date = Column(Date)
last_record = Column(Date)
days_active = Column(Integer)
speed_limit = Column(Integer)
status = Column(Boolean)
TrafficCounts = relationship('AtvesTrafficCounts')
AmberTimeRejects = relationship('AtvesAmberTimeRejects')
class Course(Model):
__tablename__ = "course"
id = Column(Integer, primary_key=True)
name = Column(String(255), nullable=False)
# These are for better model form testing
cost = Column(Numeric(5, 2), nullable=False)
description = Column(Text, nullable=False)
level = Column(Enum('Primary', 'Secondary'))
class Venue(Base):
__tablename__ = 'venues'
id = Column(Integer, primary_key=True)
name = Column(String(255), nullable=False, index=True)
address = Column(String(255), nullable=False)
lat = Column(Numeric(precision=7, scale=4), nullable=False)
long = Column(Numeric(precision=7, scale=4), nullable=False)
capacity = Column(Integer)
eventful_id = Column(String(255))
songkick_id = Column(String(255))
eventbrite_id = Column(String(255))
image_url = Column(String(255))
events = relationship('Event', back_populates='venue')
def __repr__(self):
return "{} @ {}".format(self.name, self.address)
