Python sqlalchemy.orm() Examples

def get_rse(rse_id, session=None):
    """
    Get a RSE or raise if it does not exist.

    :param rse_id:  The rse id.
    :param session: The database session in use.

    :raises RSENotFound: If referred RSE was not found in the database.
    """

    false_value = False  # To make pep8 checker happy ...
    try:
        tmp = session.query(models.RSE).\
            filter(sqlalchemy.and_(models.RSE.deleted == false_value,
                                   models.RSE.id == rse_id))\
            .one()
        tmp['type'] = tmp.rse_type
        return tmp
    except sqlalchemy.orm.exc.NoResultFound:
        raise exception.RSENotFound('RSE with id \'%s\' cannot be found' % rse_id) 

def query_singleton_edges_from_network(self, network: Network) -> sqlalchemy.orm.query.Query:
        """Return a query selecting all edge ids that only belong to the given network."""
        ne1 = aliased(network_edge, name='ne1')
        ne2 = aliased(network_edge, name='ne2')
        singleton_edge_ids_for_network = (
            self.session
                .query(ne1.c.edge_id)
                .outerjoin(
                    ne2, and_(
                        ne1.c.edge_id == ne2.c.edge_id,
                        ne1.c.network_id != ne2.c.network_id,
                    ),
                )
                .filter(  # noqa: E131
                    and_(
                        ne1.c.network_id == network.id,
                        ne2.c.edge_id == None,  # noqa: E711
                    ),
                )
        )
        return singleton_edge_ids_for_network 

def link_pr_issue(session, issue, request, origin="commit"):
    """ Associate the specified issue with the specified pull-requets.

    :arg session: The SQLAlchemy session to use
    :type session: sqlalchemy.orm.session.Session
    :arg issue: The issue mentioned in the commits of the pull-requests to
        be associated with
    :type issue: pagure.lib.model.Issue
    :arg request: A pull-request to associate the specified issue with
    :type request: pagure.lib.model.PullRequest

    """

    associated_issues = [iss.uid for iss in request.related_issues]
    if issue.uid not in associated_issues:
        obj = model.PrToIssue(
            pull_request_uid=request.uid, issue_uid=issue.uid, origin=origin
        )
        session.add(obj)
        session.flush() 

def get_session():
    """Get a new session.

    Lazy load the database connection and create the tables.

    Returns:
        sqlalchemy.orm.session.Session -- SQLAlchemy Session object
    """
    global __session__
    # Create database connection, tables and Sessionmaker if neccessary.
    if not __session__:
        Engine = create_engine(
            database, echo=logger.getEffectiveLevel() == logging.DEBUG)
        __session__ = sessionmaker(bind=Engine)
        Base.metadata.create_all(Engine)

    # Return new session object
    return __session__() 

def find_ssh_key(session, search_key, username):
    """ Finds and returns SSHKey matching the requested search_key.

    Args:
        session: database session
        search_key (string): The SSH fingerprint we are requested to look up
        username (string or None): If this is provided, the key is looked up
            to belong to the requested user.
    """
    query = session.query(model.SSHKey).filter(
        model.SSHKey.ssh_search_key == search_key
    )

    if username:
        userowner = (
            session.query(model.User.id)
            .filter(model.User.user == username)
            .subquery()
        )
        query = query.filter(model.SSHKey.user_id == userowner)

    try:
        return query.one()
    except sqlalchemy.orm.exc.NoResultFound:
        return None 

def invalidate(cache, session, earliest, latest):
        """Invalidates/deletes all cached responses in the given interval to
        ensure these data is generated anew. This is meant to be run when the
        underlying data for this interval changes, for instance since new data
        has been imported.

        :param session: SQLAlchemy Session
        :type session: sqlalchemy.orm.session.Session
        :param earliest: Earliest time of the interval to invalidate
        :type earliest: datetime.datetime
        :param latest: Latest time of the interval to invalidate
        :type latest: datetime.datetime

        """
        logger.debug('Invalidating cache in interval %s..%s',
                     earliest.isoformat(), latest.isoformat())
        session.query(cache)\
               .filter(and_(or_(cache.begin.is_(None),
                                cache.begin <= latest),
                            or_(cache.end.is_(None),
                                cache.end > earliest)))\
               .delete()
        session.commit() 

def related_limit(self, relationship):
        """Paging limit for related resources.

        **Query Parameters**

            **page[limit:relationships:<relname>]:** number of results to
            return per page for related resource <relname>.

        Parameters:
            relationship(sqlalchemy.orm.relationships.RelationshipProperty):
                the relationship to get the limit for.

        Returns:
            int: paging limit for related resources.
        """
        limit_comps = ['limit', 'relationships', relationship.obj.key]
        limit = self.default_limit
        qinfo = self.collection_query_info(self.request)
        while limit_comps:
            if '.'.join(limit_comps) in qinfo['_page']:
                limit = int(qinfo['_page']['.'.join(limit_comps)])
                break
            limit_comps.pop()
        return min(limit, self.max_limit) 

def _setup_retry_tracker_table(self):
        metadata = sqlalchemy.MetaData()
        self.retry_table = sqlalchemy.Table(
            'retry_tracker', metadata,
            sqlalchemy.Column(
                'id', sqlalchemy.Integer,
                autoincrement=True,
                primary_key=True,
            ),
        )
        metadata.create_all(self.engine)
        self.addCleanup(metadata.drop_all, self.engine)

        class RetryTracker(object):
            pass

        sqlalchemy.orm.mapper(RetryTracker, self.retry_table)
        self.retry_tracker = RetryTracker 

def del_rse(rse_id, session=None):
    """
    Disable a rse with the given rse id.

    :param rse_id: the rse id.
    :param session: The database session in use.
    """

    old_rse = None
    try:
        old_rse = session.query(models.RSE).filter_by(id=rse_id, deleted=False).one()
        if not rse_is_empty(rse_id=rse_id, session=session):
            raise exception.RSEOperationNotSupported('RSE \'%s\' is not empty' % get_rse_name(rse_id=rse_id, session=session))
    except sqlalchemy.orm.exc.NoResultFound:
        raise exception.RSENotFound('RSE with id \'%s\' cannot be found' % rse_id)
    rse = old_rse.rse
    old_rse.delete(session=session)
    try:
        del_rse_attribute(rse_id=rse_id, key=rse, session=session)
    except exception.RSEAttributeNotFound:
        pass 

def restore_rse(rse_id, session=None):
    """
    Restore a rse with the given rse id.

    :param rse_id: the rse id.
    :param session: The database session in use.
    """

    old_rse = None
    try:
        old_rse = session.query(models.RSE).filter_by(id=rse_id, deleted=True).one()
    except sqlalchemy.orm.exc.NoResultFound:
        raise exception.RSENotFound('RSE with id \'%s\' cannot be found' % rse_id)
    old_rse.deleted = False
    old_rse.deleted_at = None
    old_rse.save(session=session)
    rse = old_rse.rse
    add_rse_attribute(rse_id=rse_id, key=rse, value=True, session=session) 

def insert_dataset(session, data, tbl):
    '''Batch insert data into the database using PostGIS specific functions.

    :param session: SQLAlchemy Session
    :type session: sqlalchemy.orm.session.Session
    :param data: DataFrame containing value, timestamp, longitude and latitude
    :type data: pandas.core.frame.DataFrame
    :param tbl: Base class representing the database table for the data
    :type tbl: sqlalchemy.ext.declarative.api.DeclarativeMeta
    '''
    values = sqlalchemy.select([sqlalchemy.func.unnest(data.value),
                                sqlalchemy.func.unnest(data.timestamp),
                                sqlalchemy.func.ST_MakePoint(
                                    sqlalchemy.func.unnest(data.longitude),
                                    sqlalchemy.func.unnest(data.latitude))])
    query = sqlalchemy.insert(tbl).from_select(tbl.columns, values)
    session.execute(query) 

def single_item_query(self, loadonly=None):
        """A query representing the single item referenced by the request.

        **URL (matchdict) Parameters**

            **id** (*str*): resource id

        Returns:
            sqlalchemy.orm.query.Query: query which will fetch item with id
            'id'.
        """
        if not loadonly:
            loadonly = self.allowed_requested_query_columns.keys()
        return self.dbsession.query(
            self.model
        ).options(
            load_only(*loadonly)
        ).filter(
            self.id_col(self.model) == self.obj_id
        ) 

def drop_database(url):
    url = copy(sqlalchemy.engine.url.make_url(url))
    database = url.database
    if url.drivername.startswith('postgresql'):
        url.database = 'postgres'
    elif not url.drivername.startswith('sqlite'):
        url.database = None

    engine = sqlalchemy.create_engine(url)
    if engine.dialect.name == 'sqlite' and url.database != ':memory:':
        os.remove(url.database)
    else:
        text = 'DROP DATABASE {0}'.format(orm.quote(engine, database))
        cnx = engine.connect()
        cnx.execute("ROLLBACK")
        cnx.execute(text)
        cnx.execute("commit")
        cnx.close() 

def global_init(db_file: str):
        if DbSession.factory:
            return

        if not db_file or not db_file.strip():
            raise Exception("You must specify a data file.")

        conn_str = 'sqlite:///' + db_file
        print("Connecting to DB at: {}".format(conn_str))

        engine = sqlalchemy.create_engine(conn_str, echo=False)
        DbSession.engine = engine
        DbSession.factory = sqlalchemy.orm.sessionmaker(bind=engine)

        SqlAlchemyBase.metadata.create_all(engine) 

def test_onetomany_noinflect(metadata):
    Table(
        "oglkrogk",
        metadata,
        Column("id", INTEGER, primary_key=True),
        Column("fehwiuhfiwID", INTEGER),
        ForeignKeyConstraint(["fehwiuhfiwID"], ["fehwiuhfiw.id"]),
    )
    Table("fehwiuhfiw", metadata, Column("id", INTEGER, primary_key=True))

    assert (
        generate_code(metadata)
        == """\
# coding: utf-8
from sqlalchemy import Column, ForeignKey, Integer
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class Fehwiuhfiw(Base):
    __tablename__ = 'fehwiuhfiw'

    id = Column(Integer, primary_key=True)


class Oglkrogk(Base):
    __tablename__ = 'oglkrogk'

    id = Column(Integer, primary_key=True)
    fehwiuhfiwID = Column(ForeignKey('fehwiuhfiw.id'))

    fehwiuhfiw = relationship('Fehwiuhfiw')
"""
    ) 

def association_proxy_query(self, obj_id, rel, full_object=True):
        """Construct query for related objects across an association proxy.

        Parameters:
            obj_id (str): id of an item in this view's collection.

            proxy (sqlalchemy.ext.associationproxy.ObjectAssociationProxyInstance):
                the relationships to get related objects from.

            full_object (bool): if full_object is ``True``, query for all
                requested columns (probably to build resource objects). If
                full_object is False, only query for the key column (probably
                to build resource identifiers).

        Returns:
            sqlalchemy.orm.query.Query: query which will fetch related
            object(s).
        """
        rel_view = self.view_instance(rel.tgt_class)
        proxy = rel.obj.for_class(rel.src_class)
        query = self. dbsession.query(
            rel.tgt_class
        ).join(proxy.remote_attr).filter(
            # I thought the simpler
            # proxy.local_attr.contains() should work but it doesn't
            proxy.local_attr.property.local_remote_pairs[0][1] == obj_id
        )
        if full_object:
            query = query.options(
                load_only(*rel_view.allowed_requested_query_columns.keys())
            )
        else:
            query = query.options(load_only(rel_view.key_column.name))
        return query 

def test_foreign_key_schema(metadata):
    Table(
        "simple_items",
        metadata,
        Column("id", INTEGER, primary_key=True),
        Column("other_item_id", INTEGER),
        ForeignKeyConstraint(["other_item_id"], ["otherschema.other_items.id"]),
    )
    Table("other_items", metadata, Column("id", INTEGER, primary_key=True), schema="otherschema")

    assert (
        generate_code(metadata)
        == """\
# coding: utf-8
from sqlalchemy import Column, ForeignKey, Integer
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
metadata = Base.metadata


class OtherItem(Base):
    __tablename__ = 'other_items'
    __table_args__ = {'schema': 'otherschema'}

    id = Column(Integer, primary_key=True)


class SimpleItem(Base):
    __tablename__ = 'simple_items'

    id = Column(Integer, primary_key=True)
    other_item_id = Column(ForeignKey('otherschema.other_items.id'))

    other_item = relationship('OtherItem')
"""
    ) 

def configure_db(self):
        super(SQLAuthDBConnector, self).configure_db()
        self.db_session_factory = sqlalchemy.orm.sessionmaker(bind=self.engine,
                                                              autocommit=False,
                                                              autoflush=False)
        self._audit_log = AuditLog(
            session_factory=self.db_session_factory,
            external_meta_items_getter=self._get_audit_log_external_meta_items) 

def global_init(db_file: str):
        if DbSession.factory:
            return

        if not db_file or not db_file.strip():
            raise Exception("You must specify a data file.")

        conn_str = 'sqlite:///' + db_file
        print("Connecting to DB at: {}".format(conn_str))

        engine = sqlalchemy.create_engine(conn_str, echo=False)
        DbSession.engine = engine
        DbSession.factory = sqlalchemy.orm.sessionmaker(bind=engine)

        SqlAlchemyBase.metadata.create_all(engine) 

def global_init(db_file: str):
        if DbSession.factory:
            return

        if not db_file or not db_file.strip():
            raise Exception("You must specify a data file.")

        conn_str = 'sqlite:///' + db_file
        print("Connecting to DB at: {}".format(conn_str))

        engine = sqlalchemy.create_engine(conn_str, echo=False)
        DbSession.engine = engine
        DbSession.factory = sqlalchemy.orm.sessionmaker(bind=engine)

        SqlAlchemyBase.metadata.create_all(engine) 

def global_init(db_file: str):
        if DbSession.factory:
            return

        if not db_file or not db_file.strip():
            raise Exception("You must specify a data file.")

        conn_str = 'sqlite:///' + db_file
        print("Connecting to DB at: {}".format(conn_str))

        engine = sqlalchemy.create_engine(conn_str, echo=False)
        DbSession.engine = engine
        DbSession.factory = sqlalchemy.orm.sessionmaker(bind=engine)

        SqlAlchemyBase.metadata.create_all(engine) 

def global_init(db_file: str):
        if DbSession.factory:
            return

        if not db_file or not db_file.strip():
            raise Exception("You must specify a data file.")

        conn_str = 'sqlite:///' + db_file
        print("Connecting to DB at: {}".format(conn_str))

        engine = sqlalchemy.create_engine(conn_str, echo=False)
        DbSession.engine = engine
        DbSession.factory = sqlalchemy.orm.sessionmaker(bind=engine)

        SqlAlchemyBase.metadata.create_all(engine) 

def global_init(db_file: str):
        if DbSession.factory:
            return

        if not db_file or not db_file.strip():
            raise Exception("You must specify a data file.")

        conn_str = 'sqlite:///' + db_file
        print("Connecting to DB at: {}".format(conn_str))

        engine = sqlalchemy.create_engine(conn_str, echo=False)
        DbSession.engine = engine
        DbSession.factory = sqlalchemy.orm.sessionmaker(bind=engine)

        SqlAlchemyBase.metadata.create_all(engine) 

def global_init(db_file: str):
        if DbSession.factory:
            return

        if not db_file or not db_file.strip():
            raise Exception("You must specify a data file.")

        conn_str = 'sqlite:///' + db_file
        print("Connecting to DB at: {}".format(conn_str))

        engine = sqlalchemy.create_engine(conn_str, echo=False)
        DbSession.engine = engine
        DbSession.factory = sqlalchemy.orm.sessionmaker(bind=engine)

        SqlAlchemyBase.metadata.create_all(engine) 

def global_init(db_file: str):
        if DbSession.factory:
            return

        if not db_file or not db_file.strip():
            raise Exception("You must specify a data file.")

        conn_str = 'sqlite:///' + db_file
        print("Connecting to DB at: {}".format(conn_str))

        engine = sqlalchemy.create_engine(conn_str, echo=False)
        DbSession.engine = engine
        DbSession.factory = sqlalchemy.orm.sessionmaker(bind=engine)

        SqlAlchemyBase.metadata.create_all(engine) 

def global_init(db_file: str):
        if DbSession.factory:
            return

        if not db_file or not db_file.strip():
            raise Exception("You must specify a data file.")

        conn_str = 'sqlite:///' + db_file
        print("Connecting to DB at: {}".format(conn_str))

        engine = sqlalchemy.create_engine(conn_str, echo=False)
        DbSession.engine = engine
        DbSession.factory = sqlalchemy.orm.sessionmaker(bind=engine)

        SqlAlchemyBase.metadata.create_all(engine) 

def global_init(db_file: str):
        if DbSession.factory:
            return

        if not db_file or not db_file.strip():
            raise Exception("You must specify a data file.")

        conn_str = 'sqlite:///' + db_file
        print("Connecting to DB at: {}".format(conn_str))

        engine = sqlalchemy.create_engine(conn_str, echo=False)
        DbSession.engine = engine
        DbSession.factory = sqlalchemy.orm.sessionmaker(bind=engine)

        SqlAlchemyBase.metadata.create_all(engine) 

def global_init(db_file: str):
        if DbSession.factory:
            return

        if not db_file or not db_file.strip():
            raise Exception("You must specify a data file.")

        conn_str = 'sqlite:///' + db_file
        print("Connecting to DB at: {}".format(conn_str))

        engine = sqlalchemy.create_engine(conn_str, echo=False)
        DbSession.engine = engine
        DbSession.factory = sqlalchemy.orm.sessionmaker(bind=engine)

        SqlAlchemyBase.metadata.create_all(engine) 

def global_init(db_file: str):
        if DbSession.factory:
            return

        if not db_file or not db_file.strip():
            raise Exception("You must specify a data file.")

        conn_str = 'sqlite:///' + db_file
        print("Connecting to DB at: {}".format(conn_str))

        engine = sqlalchemy.create_engine(conn_str, echo=False)
        DbSession.engine = engine
        DbSession.factory = sqlalchemy.orm.sessionmaker(bind=engine)

        SqlAlchemyBase.metadata.create_all(engine) 

def setup_class(cls):
        if not tests.is_datastore_supported():
            raise nose.SkipTest("Datastore not supported")
        p.load('datastore')
        ctd.CreateTestData.create()
        cls.sysadmin_user = model.User.get('testsysadmin')
        cls.normal_user = model.User.get('annafan')
        resource = model.Package.get('annakarenina').resources[0]
        cls.data = {
            'resource_id': resource.id,
            'aliases': u'b\xfck2',
            'fields': [{'id': 'book', 'type': 'text'},
                       {'id': 'author', 'type': 'text'},
                       {'id': 'rating with %', 'type': 'text'}],
            'records': [{'book': 'annakarenina', 'author': 'tolstoy',
                         'rating with %': '90%'},
                        {'book': 'warandpeace', 'author': 'tolstoy',
                         'rating with %': '42%'}]
        }

        engine = db._get_engine(
            {'connection_url': config['ckan.datastore.write_url']})
        cls.Session = orm.scoped_session(orm.sessionmaker(bind=engine))
        set_url_type(
            model.Package.get('annakarenina').resources, cls.sysadmin_user)