예제 #1
0
class RedfinHouseGrade(db.Model):
    __tablename__ = "redfin_house_grade"
    redfin_house_grade_id = db.Column(UUID(as_uuid=True), primary_key=True, server_default=sa_text("uuid_generate_v4()"))
    redfin_house_id = db.Column(UUID(as_uuid=True))
    grade = db.Column(db.Float())
    created_at = db.Column(db.DateTime(),server_default=sa_text("now()"))
    updated_at = db.Column(db.DateTime(),server_default=sa_text("now()"))
예제 #2
0
class RedfinHouse(db.Model):
    __tablename__ = "redfin_house"

    redfin_house_id = db.Column(UUID(as_uuid=True), primary_key=True, server_default=sa_text("uuid_generate_v4()"))
    address_1 = db.Column(db.String(256))
    city = db.Column(db.String(128))
    state = db.Column(db.String(8))
    zip = db.Column(db.String(16))
    latitude = db.Column(db.Float())
    longitude = db.Column(db.Float())
    price = db.Column(db.Float())
    sale_type = db.Column(db.String(128))
    sold_date = db.Column(db.DateTime())
    property_type = db.Column(db.String(128))
    year_built = db.Column(db.Integer())
    beds = db.Column(db.Integer())
    bath = db.Column(db.Float())
    location = db.Column(db.String(128))
    square_feet = db.Column(db.Float())
    lot_size = db.Column(db.Float())
    days_on_market = db.Column(db.Integer())
    price_square_foot = db.Column(db.Float())
    hoa_month = db.Column(db.Float())
    house_url = db.Column(db.String(256))
    source = db.Column(db.String(128))
    mls_no = db.Column(db.String(16))
    status = db.Column(db.String(32))
    next_open_house_start_time = db.Column(db.DateTime())
    next_open_house_end_time =  db.Column(db.DateTime())
    favorite = db.Column(db.Boolean())
    interested = db.Column(db.Boolean()) 
    created_at = db.Column(db.DateTime(),server_default=sa_text("now()"))
    updated_at = db.Column(db.DateTime(),server_default=sa_text("now()"))
예제 #3
0
class RedfinHouseIngestChecksum(db.Model):
    __tablename__ = "redfin_house_ingest_checksum"
    checksum_id = db.Column(UUID(as_uuid=True), primary_key=True, server_default=sa_text("uuid_generate_v4()"))
    checksum = db.Column(db.String(64))
    filename = db.Column(db.String(256))
    created_at = db.Column(db.DateTime(),server_default=sa_text("now()"))
    updated_at = db.Column(db.DateTime(),server_default=sa_text("now()"))
예제 #4
0
class RedfinHousePriceEstimate(db.Model):
    __tablename__ = "redfin_house_price_estimate"
    price_estimate_id = db.Column(UUID(as_uuid=True), primary_key=True, server_default=sa_text("uuid_generate_v4()"))
    house_id = db.Column(UUID(as_uuid=True))
    price = db.Column(db.Float())
    predicted_price = db.Column(db.Float())
    created_at = db.Column(db.DateTime(),server_default=sa_text("now()"))
    updated_at = db.Column(db.DateTime(),server_default=sa_text("now()"))
예제 #5
0
def push_data(data_in_dict):
    if not data_in_dict or len(data_in_dict) == 0:
        raise Exception('Empty_data_from_HA')
    connection_str = f'mysql+pymysql://{SETTINGS["Database"]["username"]}:{SETTINGS["Database"]["password"]}@{SETTINGS["Database"]["ip"]}:{SETTINGS["Database"]["port"]}/{SETTINGS["Database"]["base"]}'
    engine = create_engine(connection_str,
                           echo=SETTINGS['Database']['echo_debug'])
    Base.metadata.create_all(bind=engine)
    Session = sessionmaker(bind=engine, autocommit=False)
    session = Session()
    session.execute(sa_text('''Truncate table HAhosts'''))
    counter_insert = 0
    for h in data_in_dict:
        if h['svname'] in ('BACKEND', 'FRONTEND'):
            continue
        if h['pxname'] not in SETTINGS['HAproxy']['pxname_filter']:
            continue
        host1 = Host()
        host1.pxname = h['pxname']
        host1.svname = h['svname']
        host1.status = h['status']
        host1.scur = h['scur']
        host1.addr, host1.port = h['addr'].split(':')
        host1.algo = h['algo']
        session.add(host1)
        counter_insert += 1
    session.commit()
    logging.info(f'Inserted {counter_insert} lines')
    session.close()
예제 #6
0
class User(db.Model):
    __tablename__ = 'users'
    __table_args__ = {'extend_existing': True}

    id = db.Column(UUID(as_uuid=True),
                   primary_key=True,
                   server_default=sa_text("uuid_generate_v4()"))
    email = db.Column(db.String(254), unique=True, nullable=False)
    fname = db.Column(db.String)
    lname = db.Column(db.String)
    password = db.Column(db.String, nullable=False)
    salt = db.Column(db.String)
    hash_version = db.Column(db.Integer)
    roleid = db.Column(db.Integer,
                       db.ForeignKey('roles.id'),
                       nullable=False,
                       default=2)
    role = db.relationship('Roles', backref=db.backref('roles', lazy=True))
    preferences = db.Column(db.String)

    def __repr__(self):
        return '<User %r>' % self.email

    def serialize(self):
        return {
            'id': self.id,
            'email': self.email,
            'fname': self.fname,
            'lname': self.lname,
            'role': self.role.name,
            'preferences': self.preferences
        }
예제 #7
0
class BaseMixin:
    uuid: UUID = Column(
        UUID2STR, primary_key=True, unique=True, nullable=False, server_default=sa_text("uuid_generate_v4()")
    )
    created: datetime = Column(UTCDatetime, default=datetime.now)

    @db_handler
    def add(self):
        SESSION.add(self)
        SESSION.commit()

    # pylint: disable=R0201
    @db_handler
    def update(self):
        SESSION.commit()

    # pylint: enable=R0201

    @db_handler
    def delete(self):
        SESSION.delete(self)
        SESSION.commit()

    @staticmethod
    def rollback():
        SESSION.rollback()
예제 #8
0
파일: db.py 프로젝트: srittau/dbupgrade
def _try_fetching_version_info_for_schema(
        engine: Engine, schema: str) -> Optional[Tuple[int, int]]:
    sql = SQL_SELECT_VERSIONS.format(quote=_quote_char(engine))
    query = sa_text(sql)
    result = engine.execute(query, col=schema)
    rows = result.fetchall()  # type: List[Tuple[int, int]]
    return rows[0] if len(rows) == 1 else None
예제 #9
0
class RedfinHouse(db.Model):
    __tablename__ = "redfin_house"

    redfin_house_id = db.Column(UUID(as_uuid=True),
                                primary_key=True,
                                server_default=sa_text("uuid_generate_v4()"))
    address_1 = db.Column(db.String(256))
예제 #10
0
class Follower(db.Model):
    __tablename__ = "followers"

    id = db.Column(db.Integer, primary_key=True)
    uuid = db.Column(UUID(as_uuid=True),
                     server_default=sa_text("uuid_generate_v4()"),
                     unique=True)
    actor_id = db.Column(db.Integer, db.ForeignKey("actor.id"))
    target_id = db.Column(db.Integer, db.ForeignKey("actor.id"))
    activity_url = db.Column(URLType(), unique=True, nullable=True)
    creation_date = db.Column(db.DateTime(timezone=False),
                              default=datetime.datetime.utcnow)
    modification_date = db.Column(db.DateTime(timezone=False),
                                  onupdate=datetime.datetime.now)

    actor = db.relationship("Actor", foreign_keys=[actor_id])
    target = db.relationship("Actor", foreign_keys=[target_id])

    __table_args__ = (UniqueConstraint("actor_id",
                                       "target_id",
                                       name="unique_following"), )

    def __repr__(self):
        return f"<Follower(id='{self.id}', actor_id='{self.actor_id}', target_id='{self.target_id}')>"

    def follow_back(self):
        f = (db.session.query(Follower.id).filter(
            Follower.actor_id == self.target_id,
            Follower.target_id == self.actor_id).first())
        return f
예제 #11
0
class Registry(ModelMixin):

    __tablename__ = 'registries'

    id = db.Column(UUID(as_uuid=True),
                   primary_key=True,
                   server_default=sa_text("uuid_generate_v4()"))
    name = db.Column(db.String, nullable=False, unique=True)
    date_created = db.Column(db.DateTime, default=db.func.current_timestamp())
예제 #12
0
class RentalZipInfo(db.Model):
    __tablename__ = "rental_zip_info"
    zip = db.Column(db.String(16), primary_key=True)
    state_name = db.Column(db.String(64))
    state_abbr = db.Column(db.String(8))
    county_name = db.Column(db.String(64))
    area_name = db.Column(db.String(128))
    area_token = db.Column(db.String(128))
    metro = db.Column(db.Integer())
    fmr_type = db.Column(db.Integer())
    fmr0 = db.Column(db.Float())
    fmr1 = db.Column(db.Float())
    fmr2 = db.Column(db.Float())
    fmr3 = db.Column(db.Float())
    fmr4 = db.Column(db.Float())
    overall_rank = db.Column(db.Integer())
    overall_percentile = db.Column(db.Float())
    year = db.Column(db.Integer())
    created_at = db.Column(db.DateTime(),server_default=sa_text("now()"))
    updated_at = db.Column(db.DateTime(),server_default=sa_text("now()"))
예제 #13
0
class RentalProperty(db.Model):
    __tablename__ = "rental_property"
    rental_property_id = db.Column(UUID(as_uuid=True), primary_key=True, server_default=sa_text("uuid_generate_v4()"))
    address_1 = db.Column(db.String(256))
    city = db.Column(db.String(128))
    state = db.Column(db.String(8))
    zip = db.Column(db.String(16))
    latitude = db.Column(db.Float())
    longitude = db.Column(db.Float())
    purchase_price = db.Column(db.Float())
    after_repair_price = db.Column(db.Float())
    purchase_closing_cost = db.Column(db.Float())
    repair_cost = db.Column(db.Float())
    down_payment_percentage = db.Column(db.Float())
    loan_interest_rate = db.Column(db.Float())
    rent_per_unit = db.Column(db.Float())
    rent_num_units = db.Column(db.Integer())
    property_taxes = db.Column(db.Float())
    insurance = db.Column(db.Float())
    created_at = db.Column(db.DateTime(),server_default=sa_text("now()"))
    updated_at = db.Column(db.DateTime(),server_default=sa_text("now()"))
예제 #14
0
class Project(ModelMixin):
    __tablename__ = 'project'
    id = db.Column(UUID(as_uuid=True), primary_key=True, server_default=sa_text("uuid_generate_v4()"))
    name = db.Column(db.String(256), nullable=True)
    alias = db.Column(db.String(256), nullable=False, unique=True)
    owner_id = db.Column(UUID(as_uuid=True), db.ForeignKey('user.id'), nullable=False)
    cluster_id = db.Column(UUID(as_uuid=True), db.ForeignKey('clusters.id'), nullable=False)
    apps = db.relationship('App', backref='project', lazy=True)
    description = db.Column(db.String, nullable=True)
    date_created = db.Column(db.DateTime, default=db.func.current_timestamp())
    project_databases = db.relationship(
        'ProjectDatabase', backref='project', lazy=True)
예제 #15
0
class Cluster(ModelMixin):

    __tablename__ = 'clusters'

    id = db.Column(UUID(as_uuid=True),
                   primary_key=True,
                   server_default=sa_text("uuid_generate_v4()"))
    name = db.Column(db.String, nullable=False, unique=True)
    host = db.Column(db.String, nullable=False, unique=True)
    token = db.Column(EncryptedType(db.String, secret), nullable=False)
    description = db.Column(db.String, nullable=False)
    projects = db.relationship('Project', backref='cluster', lazy=True)
    date_created = db.Column(db.DateTime, default=db.func.current_timestamp())
예제 #16
0
파일: app.py 프로젝트: crane-cloud/backend
class App(ModelMixin):
    __tablename__ = 'app'
    id = db.Column(UUID(as_uuid=True),
                   primary_key=True,
                   server_default=sa_text("uuid_generate_v4()"))
    name = db.Column(db.String(256), nullable=True)
    image = db.Column(db.String(256), nullable=False)
    project_id = db.Column(UUID(as_uuid=True),
                           db.ForeignKey('project.id'),
                           nullable=False)
    url = db.Column(db.String(256), nullable=True)
    alias = db.Column(db.String(256), nullable=True, unique=True)
    port = db.Column(db.Integer, nullable=False)
    date_created = db.Column(db.DateTime, default=db.func.current_timestamp())
예제 #17
0
파일: role.py 프로젝트: crane-cloud/backend
class Role(ModelMixin):
    """  Roles Table Definition """
    _tablename_ = 'role'

    # fields of the Roles table
    id = db.Column(UUID(as_uuid=True),
                   primary_key=True,
                   server_default=sa_text("uuid_generate_v4()"))
    name = db.Column(db.String(256), nullable=False)

    users = relationship('User', secondary='user_role', backref='roles')

    def __init__(self, name):
        """ initialize with name """
        self.name = name
예제 #18
0
class Organisation(ModelMixin):
    """ Organisation table definition """

    _tablename_ = 'organisations'
    __table_args__ = (db.UniqueConstraint('name', name='organisation_unique_name'),)

    # fields of the Organisation table
    id = db.Column(UUID(as_uuid=True), primary_key=True, server_default=sa_text("uuid_generate_v4()"))
    name = db.Column(db.String(256), nullable=False)
    date_created = db.Column(db.DateTime, default=db.func.current_timestamp())
    admins = relationship('User', secondary='organisation_admins', backref='organisation_admin')
    members = relationship('User', secondary='organisation_members', backref='organisations')

    def __init__(self, name):
        """ initialize with name, member and namespace """
        self.name = name
예제 #19
0
class Activity(db.Model):
    __tablename__ = "activity"
    id = db.Column(db.Integer, primary_key=True)

    actor = db.Column(db.Integer, db.ForeignKey("actor.id"))

    uuid = db.Column(UUID(as_uuid=True), server_default=sa_text("uuid_generate_v4()"), unique=True)
    url = db.Column(URLType(), unique=True, nullable=True)
    type = db.Column(db.String(100), index=True)
    box = db.Column(db.String(100))
    payload = db.Column(JSONB())
    creation_date = db.Column(db.DateTime(timezone=False), default=datetime.datetime.utcnow)
    delivered = db.Column(db.Boolean, default=None, nullable=True)
    delivered_date = db.Column(db.DateTime(timezone=False), nullable=True)
    local = db.Column(db.Boolean, default=True)
    meta_deleted = db.Column(db.Boolean, default=False)
    meta_undo = db.Column(db.Boolean, default=False)
    meta_pinned = db.Column(db.Boolean, default=False)
예제 #20
0
class ProjectDatabase(ModelMixin):
    __tablename__ = 'project_database'
    id = db.Column(UUID(as_uuid=True),
                   primary_key=True,
                   server_default=sa_text("uuid_generate_v4()"))
    host = db.Column(db.String(256), nullable=True)
    name = db.Column(db.String(256), nullable=False)
    user = db.Column(db.String(256), nullable=False)
    password = db.Column(db.String(256), nullable=False)
    project_id = db.Column(UUID(as_uuid=True), db.ForeignKey('project.id'))
    date_created = db.Column(db.DateTime, default=db.func.current_timestamp())
    port = db.Column(db.Integer, nullable=True)
    database_flavour_name = db.Column(db.String(256))

    # todo: make database_flavour_name nullable=false

    def password_is_valid(self, password):
        """ checks the password against it's hash to validate the user's password """
        return Bcrypt().check_password_hash(self.password, password)
예제 #21
0
class Raw_data(db.Model):
    id = db.Column(UUID(as_uuid=True),
                   primary_key=True,
                   server_default=sa_text("uuid_generate_v4()"))
    eventtime = db.Column(db.DateTime(timezone=True))
    devicename = db.Column(db.String(200))
    item = db.Column(db.String(200))
    detailstatus = db.Column(db.String(200))
    filename = db.Column(db.String(200))
    linenumber = db.Column(db.Integer)

    def __init__(self, id, eventtime, devicename, item, detailstatus, filename,
                 linenumber):
        self.id = id
        self.eventtime = eventtime
        self.devicename = devicename
        self.item = item
        self.detailstatus = detailstatus
        self.filename = filename
        self.linenumber = linenumber
예제 #22
0
class Namespace(ModelMixin):
    """ Namespace table definition """

    _tablename_ = 'namespace'
    __table_args__ = (db.UniqueConstraint('name',
                                          name='namespace_unique_name'), )

    # fields of the Namespace table
    id = db.Column(UUID(as_uuid=True),
                   primary_key=True,
                   server_default=sa_text("uuid_generate_v4()"))
    name = db.Column(db.String(256), nullable=False)
    organisation_id = db.Column(
        "organisation_id", UUID(as_uuid=True),
        db.ForeignKey(Organisation.id, ondelete='CASCADE'))
    date_created = db.Column(db.DateTime, default=db.func.current_timestamp())

    def __init__(self, name, organisation_id):
        """ initialize with name and org_id """
        self.name = name
        self.organisation_id = organisation_id
예제 #23
0
class User(ModelMixin):
    """ user table definition """

    _tablename_ = "users"

    # fields of the user table
    id = db.Column(UUID(as_uuid=True),
                   primary_key=True,
                   server_default=sa_text("uuid_generate_v4()"))
    email = db.Column(db.String(256), unique=True, nullable=False, default="")
    name = db.Column(db.String(256), nullable=False, default="")
    username = db.Column(db.String(256), nullable=False, default="")
    password = db.Column(db.String(256), nullable=False, default="")
    verified = db.Column(db.Boolean, nullable=False, default=False)
    date_created = db.Column(db.DateTime, default=db.func.current_timestamp())
    projects = db.relationship('Project', backref='owner', lazy=True)

    def __init__(self, email, name, password):
        """ initialize with email, username and password """
        self.email = email
        self.name = name
        self.username = name
        self.password = Bcrypt().generate_password_hash(password).decode()

    def password_is_valid(self, password):
        """ checks the password against it's hash to validate the user's password """
        return Bcrypt().check_password_hash(self.password, password)

    def generate_token(self, user):
        """ generates the access token """

        # set token expiry period
        expiry = timedelta(days=10)

        return create_access_token(user, expires_delta=expiry)

    def __repr__(self):
        return "<User: {}>".format(self.email)
예제 #24
0
class BaseMixin:
    uuid: UUID = Column(UUID2STR,
                        primary_key=True,
                        unique=True,
                        nullable=False,
                        server_default=sa_text("uuid_generate_v4()"))
    created: datetime = Column(UTCDatetime, default=datetime.now)

    @db_handler
    def add(self, session: Session):
        session.add(self)
        session.commit()

    # pylint: disable=R0201
    @db_handler
    def update(self, session: Session):
        session.commit()

    # pylint: enable=R0201

    @db_handler
    def delete(self, session: Session):
        session.delete(self)
        session.commit()
예제 #25
0
class Prediction(database.Model):
    __tablename__ = 'prediction'

    id = database.Column(database.BigInteger,
                         primary_key=True,
                         autoincrement=True)
    file_name = database.Column(database.String(75),
                                unique=False,
                                nullable=False)
    model_name = database.Column(database.String(75),
                                 unique=False,
                                 nullable=False)
    model_path = database.Column(database.String(75),
                                 unique=False,
                                 nullable=False)
    results = database.Column(database.Text(), unique=False, nullable=False)
    creation_date = database.Column(
        database.DateTime,
        unique=False,
        nullable=False,
        server_default=sa_text("date '2019-08-01'"))

    @property
    def serialize(self):
        return {
            'id': self.id,
            'file_name': self.file_name,
            'model_name': self.model_name,
            'model_path': self.model_path,
            'results': self.results,
            'creation_date': self.creation_date
        }

    @property
    def serialize_without_sensitive_data(self):
        return {
            'id': self.id,
            'file_name': self.file_name,
            'creation_date': self.creation_date
        }

    @staticmethod
    def check_data(object_json):
        if 'file_name' not in object_json:
            return False
        if 'model_name' not in object_json:
            return False
        if 'model_path' not in object_json:
            return False
        if 'results' not in object_json:
            return False
        return True

    def __init__(self, json):
        if self.check_data(json):
            self.file_name = json['file_name']
            self.model_name = json['model_name']
            self.model_path = json['model_path']
            self.results = json['results']
            self.creation_date = datetime.now()
        else:
            raise PredictException('MALFORMED')

    def __repr__(self):
        return 'ID %s and file_name %s.' % (self.id, self.file_name)
예제 #26
0
 def _query(self, query, **kwargs):
     """Converts a text SQL expression to a sqlalchemy object, adding named 
     parameters and returning the result as a list of dicts."""
     query = sa_text(query)
     result = self.connection.execute(query, kwargs)
     return self._resultproxy_to_list_of_dicts(result)
예제 #27
0
 def truncate_table(self):
     self.engine.execute(sa_text('''TRUNCATE TABLE webpage''').execution_options(autocommit=True))
예제 #28
0
파일: models.py 프로젝트: talkhasib/magma
def now():
    """
    Return a function for setting actual time of datetime columns
    """
    return sa_text('statement_timestamp()')
예제 #29
0
def truncate_api_db():
    with get_contexted_session() as session:
        meta = ApiBase.metadata
        for table in reversed(meta.sorted_tables):
            session.execute(sa_text(f'DELETE FROM {table.name};'))
            session.commit()
예제 #30
0
def truncate_structured_db(db):
    session = db.get_session()
    meta = StructuredBase.metadata
    for table in reversed(meta.sorted_tables):
        session.execute(sa_text(f'DELETE FROM {table.name};'))
예제 #31
0
파일: db.py 프로젝트: srittau/dbupgrade
def _execute_sql_ignore_errors(engine: Engine, query: str) -> None:
    try:
        engine.execute(sa_text(query))
    except SQLAlchemyError:
        pass