예제 #1
0
 def _date_columns(use_primary_key: bool = True) -> List[Column]:
     return [
         Column("jetavator_load_dt",
                DateTime(),
                nullable=True,
                primary_key=use_primary_key),
         Column(
             "jetavator_deleted_ind",
             # TODO: Loading as integer saves space in CSVs.
             #       Does this make sense for other file formats?
             #       Is there a more general solution?
             Integer(),
             nullable=True,
             default=0)
     ]
class Items(Base):
    """
    Items table
    """
    __tablename__ = 'items'
    id = Column(Integer, primary_key=True)
    name = Column(String(256))
    quantity = Column(Integer)
    description = Column(String(256))
    date_added = Column(DateTime())

    def __repr__(self):
        return '{ Item_id : %s, name:%s, quantity:%s, description:%s, date_added:%s}' % (
            self.id, self.name, self.quantity, self.description,
            self.date_added)
예제 #3
0
class Items(Base):
    """
    Items table
    """
    __tablename__ = 'items'
    id = Column(Integer, primary_key=True)
    name = Column(String(256))
    quantity = Column(Integer)
    description = Column(String(256))
    date_added = Column(DateTime())

    def __repr__(self):
        return "{'id' : %d, 'name' : '%s', 'quantity' : %d,  'description' : '%s', 'date_added' : %s}" % (
            self.id, self.name, self.quantity, self.description,
            self.date_added)
예제 #4
0
 def insert_data(self, dataframe):
     self.logger.info('create engine and connect to database')
     engine = sqlalchemy.create_engine(
         f'{self.database_type}://{self.database_login}:{self.database_password}@{self.database_url}/{self.database_name}'
     )
     self.logger.info('prepare to insert data')
     dataframe.to_sql('etl_hh',
                      schema='adhoc_parser',
                      con=engine,
                      if_exists='append',
                      index=False,
                      dtype={
                          'id': INT(),
                          'name': String(255),
                          'has_test': Boolean(),
                          'published_at': DateTime(),
                          'created_at': DateTime(),
                          'url': String(255),
                          'area_name': String(255),
                          'salary_from': INT(),
                          'salary_to': INT(),
                          'salary_currency': String(10),
                          'salary.gross': Boolean(),
                          'address.city': String(255),
                          'address.street': String(255),
                          'address_building': String(255),
                          'address_raw': String(500),
                          'metro_name': String(255),
                          'employer_id': INT(),
                          'employer_name': String(255),
                          'snippet_requirement': TEXT(),
                          'snippet_responsibility': TEXT(),
                          'contacts_name': String(255),
                          'contacts_email': String(255),
                      })
     self.logger.warning('data are inserted now')
예제 #5
0
class Items(Base):
    """
    Items table
    """
    __tablename__ = 'items'
    id = Column(Integer, primary_key=True)
    name = Column(String(256))
    quantity = Column(Integer)
    description = Column(String(256))
    date_added = Column(DateTime())

    def __repr__(self):
        return "id is {0}, name is {1}, quantity is {2}, description is {3}, date added is {4}.".format(
            self.id, self.name, self.quantity, self.description,
            self.date_added)
예제 #6
0
def test_datetime_properties(client):
    rest = UnRest(client.app, client.session, framework=client.__framework__)
    rest(
        Fruit, only=[], properties=[rest.Property('birthday', type=DateTime())]
    )
    code, json = client.fetch('/api/fruit')
    assert code == 200
    assert json['occurences'] == 5
    assert idsorted(json['objects'], 'fruit_id') == [
        {'fruit_id': 1, 'birthday': '2019-12-19T22:45:00'},
        {'fruit_id': 2, 'birthday': '2019-11-12T23:56:09.787000'},
        {'fruit_id': 3, 'birthday': '2020-01-01T00:00:00'},
        {'fruit_id': 4, 'birthday': '2019-12-31T23:20:00'},
        {'fruit_id': 5, 'birthday': '2019-12-31T21:59:59.999988'},
    ]
class User(db.Base):
    __tablename__ = "user"

    # Auto incrementing ID
    id = Column(Integer, primary_key=True)
    first_name = Column(String(256))
    last_name = Column(String(256))
    email = Column(String(256), unique=True)
    date_signed_up = Column(DateTime())
    available_units = Column(BigInteger)
    used_units = Column(BigInteger)

    # Foreign Key
    api_key_id = Column(Integer, ForeignKey(ApiKey.id))
    api_key = relationship(ApiKey)
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,
    )
예제 #9
0
class SitewidePageviews(Base):
    """Pageviews across all areas of the site."""

    __tablename__ = "traffic_aggregate"

    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", BigInteger())

    @classmethod
    @memoize_traffic(time=3600)
    def history(cls, interval):
        time_points, q = make_history_query(cls, interval)
        return fill_gaps(time_points, q, "unique_count", "pageview_count")
예제 #10
0
 def save_to_postgres(self):
     engine = create_engine('postgresql+psycopg2://{0}:{1}@{2}:5432/mimic3'.format(self.sqluser,
                                                                                   self.sqlpass,
                                                                                   self.host))
     self.sofa_within_si.rename(columns={"sofa_delta": "delta_score"}, inplace=True)
     # 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 sepsis_onset cascade")
     conn.commit()
     # now let's fill it again
     self.sofa_within_si[['hadm_id',
                          'sofa',
                          'sofaresp',
                          'sofacoag',
                          'sofaliv',
                          'sofacardio',
                          'sofagcs',
                          'sofaren',
                          'sepsis_time',
                          'sepsis_onset',
                          'delta_score',
                          'sofaresp_delta', 'sofacoag_delta', 'sofaliv_delta',
                          'sofacardio_delta', 'sofagcs_delta', 'sofaren_delta']] \
         .to_sql("sepsis_onset",
                 engine,
                 if_exists='append',
                 schema="mimic3_mrosnati",
                 dtype={"hadm_id": Integer(),
                        "sofa": Integer(),
                        'sofaresp': Integer(),
                        'sofacoag': Integer(),
                        'sofaliv': Integer(),
                        'sofacardio': Integer(),
                        'sofagcs': Integer(),
                        'sofaren': Integer(),
                        "sepsis_time": DateTime(),
                        "delta_score": Integer(),
                        'sofaresp_delta': Integer(),
                        'sofacoag_delta': Integer(),
                        'sofaliv_delta': Integer(),
                        'sofacardio_delta': Integer(),
                        'sofagcs_delta': Integer(),
                        'sofaren_delta': Integer()})
     self.create_table(sqlfile="/sofa_delta.sql")
예제 #11
0
class DbGroup(Base):
    __tablename__ = "db_dbgroup"

    id = Column(Integer, primary_key=True)

    uuid = Column(UUID(as_uuid=True), default=uuid_func)
    name = Column(String(255), index=True)

    type = Column(String(255), default="", index=True)

    time = Column(DateTime(timezone=True), default=timezone.now)
    description = Column(Text, nullable=True)

    user_id = Column(
        Integer,
        ForeignKey('db_dbuser.id',
                   ondelete='CASCADE',
                   deferrable=True,
                   initially="DEFERRED"))
    user = relationship('DbUser', backref=backref('dbgroups', cascade='merge'))

    dbnodes = relationship('DbNode',
                           secondary=table_groups_nodes,
                           backref="dbgroups",
                           lazy='dynamic')

    __table_args__ = (UniqueConstraint('name', 'type'), )

    def __str__(self):
        if self.type:
            return '<DbGroup [type: {}] "{}">'.format(self.type, self.name)
        else:
            return '<DbGroup [user-defined] "{}">'.format(self.name)

    def get_aiida_class(self):
        from aiida.orm.implementation.django.group import Group as DjangoAiidaGroup
        from aiida.backends.djsite.db.models import DbGroup as DjangoSchemaDbGroup
        dbgroup = DjangoSchemaDbGroup(
            id=self.id,
            type=self.type,
            uuid=self.uuid,
            name=self.name,
            time=self.time,
            description=self.description,
            user_id=self.user_id,
        )

        return DjangoAiidaGroup(dbgroup=dbgroup)
예제 #12
0
class Items(Base):
    """
    Items table
    """
    __tablename__ = 'items'
    id = Column(Integer, primary_key=True)
    name = Column(String(256))
    quantity = Column(Integer)
    description = Column(String(256))
    date_added = Column(DateTime())

    def print_as_string(self):
        return '{name: ' + self.name + ' quantity: ' + str(
            self.quantity
        ) + ' description: ' + self.description + ' date_added: ' + str(
            self.date_added) + '}'
예제 #13
0
파일: group.py 프로젝트: bashia/repoman
class Group(Base):
    __tablename__ = "groups"

    id = Column(Integer, primary_key=True)
    created = Column(DateTime())
    protected = Column(Boolean, default=False)
    name = Column(String(100), unique=True)

    #Permissions are linked to groups from within the Permissions object
    #Permissions will be available here by the 'permissions' backref

    def __init__(self, name):
        self.name = name

    def __repr__(self):
        return "<Group('%s')>" % (self.name)
예제 #14
0
class File(Base):
    """
    Store the parsed files for logging purpose.
    """

    __tablename__ = "tbl_files"

    filename = Column(String(200), primary_key=True)
    date = Column(DateTime(timezone=True), default=datetime.datetime.utcnow)

    # __table_args__  = (
    # {'schema': Base.metadata.schema}
    # )

    def __init__(self, filename):
        self.filename = filename
예제 #15
0
class Post(Base):
    """
    Blog post.
    """
    __tablename__ = 'posts'
    id = Column(Integer, primary_key=True)
    date = Column(DateTime(20), unique=True)
    title = Column(Unicode(50))
    content = Column(UnicodeText())
    category_id = Column(Integer, ForeignKey('categories.id'))

    category = relationship('Category', backref='posts')
    tags = relationship('Tag', backref='posts', secondary=tag_post_table)

    def __unicode__(self):
        return "%s" % self.id
예제 #16
0
class AuthorizeBatchTransaction(Base):
    """Settled transactions by settlement batch."""

    __tablename__ = 'authorize_batch_transactions'

    batch_id = NotNullColumn(BigInteger(), index=True)
    trans_id = NotNullColumn(BigInteger(), primary_key=True,
                             autoincrement=False)
    submit_time = NotNullColumn(DateTime())
    transaction_status = NotNullColumn(String(), index=True)
    invoice_number = Column(String())
    first_name = Column(String())
    last_name = Column(String())
    account_type = NotNullColumn(String())
    account_number = NotNullColumn(String())
    settle_amount = NotNullColumn(Float(precision=2, asdecimal=True))
예제 #17
0
class ProductObject(Base):

    __tablename__ = 'product_object'
    id = Column(String(256), primary_key=True)
    code = Column(String(256))
    name = Column(String(256))
    group_code = Column(String(256))
    group_name = Column(String(256))
    price = Column(String(256))
    prof1_price = Column(String(256))
    prof2_price = Column(String(256))
    is_order_allow = Column(Integer)
    producer_name = Column(String(256))
    is_universal = Column(Integer)
    onstock = Column(Integer)
    parse_date = Column(DateTime())
예제 #18
0
class Posts(Base):
    __tablename__ = 'forumposts1'
    URL = Column(String(200), primary_key=True)
    replyid = Column(Integer, primary_key=True)
    pid = Column(Integer)  # post id
    title = Column(String(500))
    category = Column(String(500))  # discussion category
    categoryURL = Column(String(500))
    uid = Column(String(20),
                 ForeignKey('forumusers1.uid',
                            onupdate="CASCADE",
                            ondelete='CASCADE'))  # user id
    replyTo = Column(Integer)  # This is the first post id of the discussion
    postTime = Column(
        DateTime(timezone=True))  # precise to hour eg. 2017-02-11 19:00:00
    body = Column(Text)
예제 #19
0
class LuxPrintServers(Base):
    __tablename__ = 'lux_print_servers'
    __table_args__ = {'schema': _schema}
    __colanderalchemy_config__ = {
        'title': _('Print server'),
        'plural': _('Print servers')
    }
    id = Column(Integer, primary_key=True, info={
        'colanderalchemy': {'widget': HiddenWidget()}
    })
    url = Column(Unicode, nullable=False, info={
        'colanderalchemy': {'title': _('Url')}
    })
    creation = Column(DateTime(timezone=False), info={
        'colanderalchemy': {'title': _('Creation date')}
    })
class Agency(Base):
    datasource = config.DATASOURCE_GTFS
    filename = 'agency.txt'

    __tablename__ = 'agency'

    #id = Column(Integer, Sequence(None, optional=True), primary_key=True, nullable=True)
    agency_id = Column(String(255), primary_key=True, index=True)
    agency_name = Column(String(255), nullable=False)
    agency_url = Column(String(255), nullable=False)
    agency_timezone = Column(String(50), nullable=False)
    agency_lang = Column(String(10))
    agency_phone = Column(String(50))
    agency_fare_url = Column(String(255))
    transport_mode = Column(String(50), primary_key=True)
    date_modified = Column(DateTime(timezone=True), primary_key=True)
예제 #21
0
class Items(Base):
    """
    Items table
    """
    __tablename__ = 'items'
    id = Column(Integer, primary_key=True)
    name = Column(String(256))
    quantity = Column(Integer)
    description = Column(String(256))
    date_added = Column(DateTime())

    def to_json(self):
        dict = self.__dict__
        if "_sa_instance_state" in dict:
            del dict["_sa_instance_state"]
        return dict
예제 #22
0
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 total_by_codename(cls, codenames):
        return total_by_codename(cls, codenames)
예제 #23
0
class StatAgentPeriodic(Base):

    __tablename__ = 'stat_agent_periodic'
    __table_args__ = (
        Index('stat_agent_periodic__idx__stat_agent_id', 'stat_agent_id'),
        Index('stat_agent_periodic__idx__time', 'time'),
    )

    id = Column(Integer, primary_key=True)
    time = Column(DateTime(timezone=True), nullable=False)
    login_time = Column(INTERVAL, nullable=False, server_default='0')
    pause_time = Column(INTERVAL, nullable=False, server_default='0')
    wrapup_time = Column(INTERVAL, nullable=False, server_default='0')
    stat_agent_id = Column(Integer, ForeignKey("stat_agent.id"))

    stat_agent = relationship(StatAgent, foreign_keys=stat_agent_id)
예제 #24
0
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)
                })
예제 #25
0
class UtcDateTime(TypeDecorator):
    impl = DateTime(timezone=True)

    def process_bind_param(self, value, dialect):
        if value is not None:
            if not isinstance(value, datetime.datetime):
                raise TypeError('expected datetime.datetime, not ' +
                                repr(value))
            elif value.tzinfo is None:
                raise ValueError('naive datetime is not allowed')
            return value.astimezone(datetime.timezone.utc)

    def process_result_value(self, value, dialect):
        if value is not None and value.tzinfo is None:
            value = value.replace(tzinfo=datetime.timezone.utc)
        return value
예제 #26
0
파일: traffic.py 프로젝트: zeantsoi/reddit
class AdserverImpressionsByCodename(Base):
    """Impressions for ads."""

    __tablename__ = "adserver_traffic_thing"

    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", BigInteger())

    @classmethod
    @memoize_traffic(time=3600)
    def history(cls, interval, codename):
        if interval != "day":
            raise NotImplementedError

        time_points, q = make_history_query(cls, interval)
        q = q.filter(cls.codename == codename)
        return fill_gaps(time_points, q, "unique_count", "pageview_count")

    @classmethod
    @memoize_traffic(time=3600)
    def promotion_history(cls, codename, start, stop):
        return promotion_history(cls, cls.pageview_count, codename, start, stop, interval="day")

    @classmethod
    @memoize_traffic(time=3600)
    def historical_totals(cls, interval):
        if interval != "day":
            raise NotImplementedError

        return totals(cls, interval)

    @classmethod
    @memoize_traffic(time=3600)
    def top_last_month(cls):
        return top_last_month(cls, "codename")

    @classmethod
    def recent_codenames(cls, fullname):
        raise NotImplementedError

    @classmethod
    @memoize_traffic(time=3600)
    def total_by_codename(cls, codename):
        return total_by_codename(cls, codename, interval="day")
예제 #27
0
class DbGroup(Base):
    __tablename__ = "db_dbgroup"

    id = Column(Integer, primary_key=True)

    uuid = Column(UUID(as_uuid=True), default=uuid_func)
    name = Column(String(255), index=True)

    type = Column(String(255), default="", index=True)

    time = Column(DateTime(timezone=True), default=timezone.now)
    description = Column(Text, nullable=True)

    user_id = Column(Integer, ForeignKey('db_dbuser.id', ondelete='CASCADE', deferrable=True, initially="DEFERRED"))
    user = relationship('DbUser', backref=backref('dbgroups', cascade='merge'))

    # dbnodes_relationship =relationship('DbNode', cascade='all,delete,delete-orphan')
    # # dbnodes_relationship = ('DbNode')
    # dbnodes = association_proxy('dbnodes_relationship', 'dbnodes',
    #                         creator=lambda perm: DbNode(perm=perm))

    # dbnodes_relationship = relationship('DbNode', secondary=table_groups_nodes,
    #                                     backref="dbgroups", lazy='dynamic')
    #
    # dbnodes = association_proxy('dbnodes_relationship', 'dbnodes',
    #                                                     creator=lambda perm: DbNode(id=id))

    dbnodes = relationship('DbNode', secondary=table_groups_nodes,
                           backref="dbgroups", lazy='dynamic')

    __table_args__ = (
        UniqueConstraint('name', 'type'),
    )

    @property
    def pk(self):
        return self.id

    def __str__(self):
        if self.type:
            return '<DbGroup [type: {}] "{}">'.format(self.type, self.name)
        else:
            return '<DbGroup [user-defined] "{}">'.format(self.name)

    def get_aiida_class(self):
        from aiida.orm.implementation.sqlalchemy.group import Group
        return Group(dbgroup=self)
예제 #28
0
class Items(Base):
    """
    Items table
    """
    __tablename__ = 'items'
    id = Column(Integer, primary_key=True)
    name = Column(String(256))
    quantity = Column(Integer)
    description = Column(String(256))
    date_added = Column(DateTime())

    def __repr__(self):
        return "name: {name}, quantity: {quantity}, description: {description}, date_added: {date_added}\n".format(
            name=self.name,
            quantity=self.quantity,
            description=self.description,
            date_added=self.date_added)
예제 #29
0
    def test_defaults(self):
        assert self._comp(Integer()) == 'INTEGER'
        assert self._comp(SmallInteger()) == 'SMALLINT'
        assert self._comp(BigInteger()) == 'BIGINT'
        assert self._comp(Numeric()) == 'NUMERIC'
        assert self._comp(Float()) == 'FLOAT'

        assert self._comp(DateTime()) == 'TIMESTAMP(6)'
        assert self._comp(Date()) == 'DATE'
        assert self._comp(Time()) == 'TIME(6)'

        assert self._comp(String()) == 'LONG VARCHAR'
        assert self._comp(Text()) == 'CLOB'
        assert self._comp(Unicode()) == 'LONG VARCHAR CHAR SET UNICODE'
        assert self._comp(UnicodeText()) == 'CLOB CHAR SET UNICODE'

        assert self._comp(Boolean()) == 'BYTEINT'
예제 #30
0
class GoldPartnerDealCode(Base):
    """Promo codes for deals from reddit gold partners."""

    __tablename__ = "reddit_gold_partner_deal_codes"

    id = Column(Integer, primary_key=True)
    deal = Column(String, nullable=False)
    code = Column(String, nullable=False)
    user = Column(Integer, nullable=True)
    date = Column(DateTime(timezone=True), nullable=True)

    @classmethod
    @with_sqlalchemy_session
    def get_codes_for_user(cls, user):
        results = Session.query(cls).filter(cls.user == user._id)
        codes = {r.deal: r.code for r in results}
        return codes

    @classmethod
    @with_sqlalchemy_session
    def claim_code(cls, user, deal):
        # check if they already have a code for this deal and return it
        try:
            result = (Session.query(cls).filter(
                and_(cls.user == user._id, cls.deal == deal)).one())
            return result.code
        except NoResultFound:
            pass

        # select an unclaimed code, assign it to the user, and return it
        try:
            claiming = (Session.query(cls).filter(
                and_(cls.deal == deal, cls.user == None,
                     func.pg_try_advisory_lock(cls.id))).limit(1).one())
        except NoResultFound:
            raise GoldPartnerCodesExhaustedError

        claiming.user = user._id
        claiming.date = datetime.now(g.tz)
        Session.add(claiming)
        Session.commit()

        # release the lock
        Session.query(func.pg_advisory_unlock_all()).all()

        return claiming.code