Python sqlalchemy.ext.automap.automap_base() Examples

def _init_db(self):
        """Initialize the database and automapper."""
        self.models = {}

        # initialize the DB engine
        self.engine = create_engine(self.options["database_url"])

        # initialize DB metadata
        self.metadata = MetaData()
        self.metadata.bind = self.engine

        # Create the tables
        self._create_tables()

        # initialize the automap mapping
        self.base = automap_base(bind=self.engine, metadata=self.metadata)
        self.base.prepare(self.engine, reflect=True)

        # initialize session
        self.session = create_session(bind=self.engine, autocommit=False) 

def _is_many_to_many(automap_base, table):
    fk_constraints = [
        const
        for const in table.constraints
        if isinstance(const, ForeignKeyConstraint)
    ]
    if len(fk_constraints) != 2:
        return None, None, None

    cols = sum(
        [
            [fk.parent for fk in fk_constraint.elements]
            for fk_constraint in fk_constraints
        ],
        [],
    )

    if set(cols) != set(table.c):
        return None, None, None

    return (
        fk_constraints[0].elements[0].column.table,
        fk_constraints[1].elements[0].column.table,
        fk_constraints,
    ) 

def _is_many_to_many(automap_base, table):
    fk_constraints = [const for const in table.constraints
                      if isinstance(const, ForeignKeyConstraint)]
    if len(fk_constraints) != 2:
        return None, None, None

    cols = sum(
        [[fk.parent for fk in fk_constraint.elements]
         for fk_constraint in fk_constraints], [])

    if set(cols) != set(table.c):
        return None, None, None

    return (
        fk_constraints[0].elements[0].column.table,
        fk_constraints[1].elements[0].column.table,
        fk_constraints
    ) 

def _is_many_to_many(automap_base, table):
    fk_constraints = [const for const in table.constraints
                    if isinstance(const, ForeignKeyConstraint)]
    if len(fk_constraints) != 2:
        return None, None, None

    cols = sum(
                [[fk.parent for fk in fk_constraint.elements]
                for fk_constraint in fk_constraints], [])

    if set(cols) != set(table.c):
        return None, None, None

    return (
        fk_constraints[0].elements[0].column.table,
        fk_constraints[1].elements[0].column.table,
        fk_constraints
    ) 

def _is_many_to_many(automap_base, table):
    fk_constraints = [const for const in table.constraints
                      if isinstance(const, ForeignKeyConstraint)]
    if len(fk_constraints) != 2:
        return None, None, None

    cols = sum(
        [[fk.parent for fk in fk_constraint.elements]
         for fk_constraint in fk_constraints], [])

    if set(cols) != set(table.c):
        return None, None, None

    return (
        fk_constraints[0].elements[0].column.table,
        fk_constraints[1].elements[0].column.table,
        fk_constraints
    ) 

def test_joined_inheritance_reflect(self):
        Base = automap_base()

        class Joined(Base):
            __tablename__ = "joined_base"

            type = Column(String)

            __mapper_args__ = {
                "polymorphic_identity": "u0",
                "polymorphic_on": type,
            }

        class SubJoined(Joined):
            __tablename__ = "joined_inh"
            __mapper_args__ = {"polymorphic_identity": "u1"}

        Base.prepare(engine=testing.db, reflect=True)

        assert SubJoined.__mapper__.inherits is Joined.__mapper__

        assert not Joined.__mapper__.relationships
        assert not SubJoined.__mapper__.relationships 

def test_single_inheritance_reflect(self):
        Base = automap_base()

        class Single(Base):
            __tablename__ = "single"

            type = Column(String)

            __mapper_args__ = {
                "polymorphic_identity": "u0",
                "polymorphic_on": type,
            }

        class SubUser1(Single):
            __mapper_args__ = {"polymorphic_identity": "u1"}

        class SubUser2(Single):
            __mapper_args__ = {"polymorphic_identity": "u2"}

        Base.prepare(engine=testing.db, reflect=True)

        assert SubUser2.__mapper__.inherits is Single.__mapper__ 

def test_prepare_defaults_to_no_schema(self):
        """
        The underlying reflect call accepts an optional schema argument.
        This is for determining which database schema to load.
        This test verifies that prepare passes a default None if no schema is
        provided.
        """
        Base = automap_base(metadata=self.metadata)
        engine_mock = Mock()
        with patch.object(Base.metadata, "reflect") as reflect_mock:
            Base.prepare(engine_mock, reflect=True)
            reflect_mock.assert_called_once_with(
                engine_mock,
                schema=None,
                extend_existing=True,
                autoload_replace=False,
            ) 

def test_prepare_accepts_optional_schema_arg(self):
        """
        The underlying reflect call accepts an optional schema argument.
        This is for determining which database schema to load.
        This test verifies that prepare can accept an optional schema
        argument and pass it to reflect.
        """
        Base = automap_base(metadata=self.metadata)
        engine_mock = Mock()
        with patch.object(Base.metadata, "reflect") as reflect_mock:
            Base.prepare(engine_mock, reflect=True, schema="some_schema")
            reflect_mock.assert_called_once_with(
                engine_mock,
                schema="some_schema",
                extend_existing=True,
                autoload_replace=False,
            ) 

def test_exception_prepare_not_called(self):
        Base = automap_base(metadata=self.metadata)

        class User(Base):
            __tablename__ = "users"

        s = Session()

        assert_raises_message(
            orm_exc.UnmappedClassError,
            "Class test.ext.test_automap.User is a subclass of AutomapBase.  "
            r"Mappings are not produced until the .prepare\(\) method is "
            "called on the class hierarchy.",
            s.query,
            User,
        ) 

def test_relationship_explicit_override_o2m(self):
        Base = automap_base(metadata=self.metadata)
        prop = relationship("addresses", collection_class=set)

        class User(Base):
            __tablename__ = "users"

            addresses_collection = prop

        Base.prepare()
        assert User.addresses_collection.property is prop
        Address = Base.classes.addresses

        a1 = Address(email_address="e1")
        u1 = User(name="u1", addresses_collection=set([a1]))
        assert a1.user is u1 

def _is_many_to_many(automap_base, table):
    fk_constraints = [const for const in table.constraints
                      if isinstance(const, ForeignKeyConstraint)]
    if len(fk_constraints) != 2:
        return None, None, None

    cols = sum(
        [[fk.parent for fk in fk_constraint.elements]
         for fk_constraint in fk_constraints], [])

    #STDM association entities have 3 columns
    if len(table.c) > 3:
        return None, None, None

    return (
        fk_constraints[0].elements[0].column.table,
        fk_constraints[1].elements[0].column.table,
        fk_constraints
    ) 

def _is_many_to_many(automap_base, table):
    fk_constraints = [const for const in table.constraints
                      if isinstance(const, ForeignKeyConstraint)]
    if len(fk_constraints) != 2:
        return None, None, None

    cols = sum(
        [[fk.parent for fk in fk_constraint.elements]
         for fk_constraint in fk_constraints], [])

    if set(cols) != set(table.c):
        return None, None, None

    return (
        fk_constraints[0].elements[0].column.table,
        fk_constraints[1].elements[0].column.table,
        fk_constraints
    ) 

def _is_many_to_many(automap_base, table):
    fk_constraints = [const for const in table.constraints
                      if isinstance(const, ForeignKeyConstraint)]
    if len(fk_constraints) != 2:
        return None, None, None

    cols = sum(
        [[fk.parent for fk in fk_constraint.elements]
         for fk_constraint in fk_constraints], [])

    if set(cols) != set(table.c):
        return None, None, None

    return (
        fk_constraints[0].elements[0].column.table,
        fk_constraints[1].elements[0].column.table,
        fk_constraints
    ) 

def _is_many_to_many(automap_base, table):
    fk_constraints = [const for const in table.constraints
                      if isinstance(const, ForeignKeyConstraint)]
    if len(fk_constraints) != 2:
        return None, None, None

    cols = sum(
        [[fk.parent for fk in fk_constraint.elements]
         for fk_constraint in fk_constraints], [])

    if set(cols) != set(table.c):
        return None, None, None

    return (
        fk_constraints[0].elements[0].column.table,
        fk_constraints[1].elements[0].column.table,
        fk_constraints
    ) 

def _is_many_to_many(automap_base, table):
    fk_constraints = [const for const in table.constraints
                      if isinstance(const, ForeignKeyConstraint)]
    if len(fk_constraints) != 2:
        return None, None, None

    cols = sum(
        [[fk.parent for fk in fk_constraint.elements]
         for fk_constraint in fk_constraints], [])

    if set(cols) != set(table.c):
        return None, None, None

    return (
        fk_constraints[0].elements[0].column.table,
        fk_constraints[1].elements[0].column.table,
        fk_constraints
    ) 

def __init__(self):
        Base = automap_base()
        engine = create_engine("sqlite:///sqlcell.db")
        Base.prepare(engine, reflect=True)
        self.classes = Base.classes
        self.tables = Base.metadata.tables.keys()
        self.Sqlcell = Base.classes.sqlcell
        self.Engines = Base.classes.engines
        self.Hooks = Base.classes.hooks
        Session = sessionmaker(autoflush=False)
        Session.configure(bind=engine)
        self.session = Session()

        dbs = self.session.query(self.Engines).all()
        self.db_info = {}
        for row in dbs:
            engine = row.engine
            if row.db:
                self.db_info[row.db] = engine
            if row.alias:
                self.db_info[row.alias] = engine
            self.db_info[engine] = engine
            self.db_info[row.host] = engine 

def test_relationship_m2m(self):
        Base = automap_base(metadata=self.metadata)

        Base.prepare()

        Order, Item = Base.classes.orders, Base.classes["items"]

        o1 = Order()
        i1 = Item()
        o1.items_collection.append(i1)
        assert o1 in i1.orders_collection 

def automap_base(declarative_base=None, **kw):
    r"""Produce a declarative automap base.

    This function produces a new base class that is a product of the
    :class:`.AutomapBase` class as well a declarative base produced by
    :func:`.declarative.declarative_base`.

    All parameters other than ``declarative_base`` are keyword arguments
    that are passed directly to the :func:`.declarative.declarative_base`
    function.

    :param declarative_base: an existing class produced by
     :func:`.declarative.declarative_base`.  When this is passed, the function
     no longer invokes :func:`.declarative.declarative_base` itself, and all
     other keyword arguments are ignored.

    :param \**kw: keyword arguments are passed along to
     :func:`.declarative.declarative_base`.

    """
    if declarative_base is None:
        Base = _declarative_base(**kw)
    else:
        Base = declarative_base

    return type(
        Base.__name__,
        (AutomapBase, Base,),
        {"__abstract__": True, "classes": util.Properties({})}
    ) 

def automap_base(declarative_base=None, **kw):
    r"""Produce a declarative automap base.

    This function produces a new base class that is a product of the
    :class:`.AutomapBase` class as well a declarative base produced by
    :func:`.declarative.declarative_base`.

    All parameters other than ``declarative_base`` are keyword arguments
    that are passed directly to the :func:`.declarative.declarative_base`
    function.

    :param declarative_base: an existing class produced by
     :func:`.declarative.declarative_base`.  When this is passed, the function
     no longer invokes :func:`.declarative.declarative_base` itself, and all
     other keyword arguments are ignored.

    :param \**kw: keyword arguments are passed along to
     :func:`.declarative.declarative_base`.

    """
    if declarative_base is None:
        Base = _declarative_base(**kw)
    else:
        Base = declarative_base

    return type(
        Base.__name__,
        (AutomapBase, Base,),
        {"__abstract__": True, "classes": util.Properties({})}
    ) 

def __init__(self, jobs, broker, broker_host, broker_port, user, password, host, port, dbname):

        self.broker_host = broker_host
        self.broker_port = broker_port
        self.broker = broker
        DB_STR = 'postgresql://{}:{}@{}:{}/{}'.format(
            user, password, host, port, dbname
        )

        dbschema='augur_data'
        self.db = s.create_engine(DB_STR, poolclass=s.pool.NullPool,
            connect_args={'options': '-csearch_path={}'.format(dbschema)})

        helper_schema = 'augur_operations'
        self.helper_db = s.create_engine(DB_STR, poolclass = s.pool.NullPool,
            connect_args={'options': '-csearch_path={}'.format(helper_schema)})

        helper_metadata = MetaData()
        helper_metadata.reflect(self.helper_db, only=['worker_job'])
        HelperBase = automap_base(metadata=helper_metadata)
        HelperBase.prepare()

        self.job_table = HelperBase.classes.worker_job.__table__

        repoUrlSQL = s.sql.text("""
            SELECT repo_git FROM repo
        """)

        rs = pd.read_sql(repoUrlSQL, self.db, params={})

        all_repos = rs['repo_git'].values.tolist()

        # List of tasks that need periodic updates
        self.__updatable = self.prep_jobs(jobs)

        self.__processes = []
        self.__updater() 

def reflect_hints_db(db_path):
    """
    Reflect the database schema of the hints database, automapping the existing tables

    The NullPool is used to avoid concurrency issues with luigi. Using this activates pooling, but since sqlite doesn't
    really support pooling, what effectively happens is just that it locks the database and the other connections wait.

    :param db_path: path to hints sqlite database
    :return: sqlalchemy.MetaData object, sqlalchemy.orm.Session object
    """
    engine = sqlalchemy.create_engine('sqlite:///{}'.format(db_path), poolclass=NullPool)
    metadata = sqlalchemy.MetaData()
    metadata.reflect(bind=engine)
    Base = automap_base(metadata=metadata)
    Base.prepare()
    speciesnames = Base.classes.speciesnames
    seqnames = Base.classes.seqnames
    hints = Base.classes.hints
    featuretypes = Base.classes.featuretypes
    Session = sessionmaker(bind=engine)
    session = Session()
    return speciesnames, seqnames, hints, featuretypes, session 

def automap_base(declarative_base=None, **kw):
    """Produce a declarative automap base.

    This function produces a new base class that is a product of the
    :class:`.AutomapBase` class as well a declarative base produced by
    :func:`.declarative.declarative_base`.

    All parameters other than ``declarative_base`` are keyword arguments
    that are passed directly to the :func:`.declarative.declarative_base`
    function.

    :param declarative_base: an existing class produced by
     :func:`.declarative.declarative_base`.  When this is passed, the function
     no longer invokes :func:`.declarative.declarative_base` itself, and all other
     keyword arguments are ignored.

    :param \**kw: keyword arguments are passed along to
     :func:`.declarative.declarative_base`.

    """
    if declarative_base is None:
        Base = _declarative_base(**kw)
    else:
        Base = declarative_base

    return type(
                Base.__name__,
                (AutomapBase, Base,),
                {"__abstract__": True, "classes": util.Properties({})}
            ) 

def automap_base(declarative_base=None, **kw):
    r"""Produce a declarative automap base.

    This function produces a new base class that is a product of the
    :class:`.AutomapBase` class as well a declarative base produced by
    :func:`.declarative.declarative_base`.

    All parameters other than ``declarative_base`` are keyword arguments
    that are passed directly to the :func:`.declarative.declarative_base`
    function.

    :param declarative_base: an existing class produced by
     :func:`.declarative.declarative_base`.  When this is passed, the function
     no longer invokes :func:`.declarative.declarative_base` itself, and all
     other keyword arguments are ignored.

    :param \**kw: keyword arguments are passed along to
     :func:`.declarative.declarative_base`.

    """
    if declarative_base is None:
        Base = _declarative_base(**kw)
    else:
        Base = declarative_base

    return type(
        Base.__name__,
        (AutomapBase, Base,),
        {"__abstract__": True, "classes": util.Properties({})}
    ) 

def __init__(self, **kwargs):
        """Read current structure from database"""
        super().__init__(**kwargs)

        # Generate mappings from existing tables
        metadata = MetaData(schema='raw')
        metadata.reflect(self.engine)
        Base = automap_base(metadata=metadata)
        Base.prepare()

        # Our fundamental objects are:
        self.Onion = Base.classes.hs_history
        self.Example = Base.classes.frontpage_examples
        self.Cell = Base.classes.frontpage_traces
        self.Crawl = Base.classes.crawls 

def test_conditional_relationship(self):
        Base = automap_base()

        def _gen_relationship(*arg, **kw):
            return None

        Base.prepare(
            engine=testing.db,
            reflect=True,
            generate_relationship=_gen_relationship,
        ) 

def automap_base(declarative_base=None, **kw):
    """Produce a declarative automap base.

    This function produces a new base class that is a product of the
    :class:`.AutomapBase` class as well a declarative base produced by
    :func:`.declarative.declarative_base`.

    All parameters other than ``declarative_base`` are keyword arguments
    that are passed directly to the :func:`.declarative.declarative_base`
    function.

    :param declarative_base: an existing class produced by
     :func:`.declarative.declarative_base`.  When this is passed, the function
     no longer invokes :func:`.declarative.declarative_base` itself, and all
     other keyword arguments are ignored.

    :param \**kw: keyword arguments are passed along to
     :func:`.declarative.declarative_base`.

    """
    if declarative_base is None:
        Base = _declarative_base(**kw)
    else:
        Base = declarative_base

    return type(
        Base.__name__,
        (AutomapBase, Base,),
        {"__abstract__": True, "classes": util.Properties({})}
    ) 

def test_o2m_relationship_cascade(self):
        Base = automap_base(metadata=self.metadata)
        Base.prepare()

        configure_mappers()

        b_rel = Base.classes.a.b_collection
        assert not b_rel.property.cascade.delete
        assert not b_rel.property.cascade.delete_orphan
        assert not b_rel.property.passive_deletes

        assert b_rel.property.cascade.save_update

        c_rel = Base.classes.a.c_collection
        assert c_rel.property.cascade.delete
        assert c_rel.property.cascade.delete_orphan
        assert not c_rel.property.passive_deletes

        assert c_rel.property.cascade.save_update

        d_rel = Base.classes.a.d_collection
        assert d_rel.property.cascade.delete
        assert d_rel.property.cascade.delete_orphan
        assert d_rel.property.passive_deletes

        assert d_rel.property.cascade.save_update

        e_rel = Base.classes.a.e_collection
        assert not e_rel.property.cascade.delete
        assert not e_rel.property.cascade.delete_orphan
        assert e_rel.property.passive_deletes

        assert e_rel.property.cascade.save_update 

def test_relationship_pass_params(self):
        Base = automap_base(metadata=self.metadata)

        mock = Mock()

        def _gen_relationship(
            base, direction, return_fn, attrname, local_cls, referred_cls, **kw
        ):
            mock(base, direction, attrname)
            return generate_relationship(
                base,
                direction,
                return_fn,
                attrname,
                local_cls,
                referred_cls,
                **kw
            )

        Base.prepare(generate_relationship=_gen_relationship)
        assert set(tuple(c[1]) for c in mock.mock_calls).issuperset(
            [
                (Base, interfaces.MANYTOONE, "nodes"),
                (Base, interfaces.MANYTOMANY, "keywords_collection"),
                (Base, interfaces.MANYTOMANY, "items_collection"),
                (Base, interfaces.MANYTOONE, "users"),
                (Base, interfaces.ONETOMANY, "addresses_collection"),
            ]
        ) 

def db():
    import os.path
    from sqlalchemy.ext.automap import automap_base
    from sqlalchemy import create_engine

    dbname = os.path.join(os.path.abspath(os.path.dirname(__file__)), "reflection.db")
    engine = create_engine("sqlite:///{}".format(dbname))
    Base = automap_base()
    Base.prepare(engine, reflect=True)
    return Base