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)