With declarative_base() can I assign different mappings to different engines?

217 views
Skip to first unread message

jens.t...@gmail.com

unread,
Jul 28, 2022, 8:09:26 AM7/28/22
to sqlalchemy
Hello,

I’m using a Base = declarative_base() in a single, shared package that other packages can import and use. Now suppose I have two more packages, each of which declares a bunch of mappings using that one common Base.

Is it possible to separate the mappings? I noticed that after importing both packages, Base.metadata.tables.keys() lists all table mappings from both packages.

However, I’d like to treat the tables from these two packages separately and bind them to different engines (and databases).

Is that possible?

Much thanks!
Jens

jens.t...@gmail.com

unread,
Jul 29, 2022, 12:16:45 AM7/29/22
to sqlalchemy
After digging through docs and some more code, here’s what seems to work.

The single, shared package should not generate that Base class which is then used by other packages. Instead, it now provides a function that generates and returns a Base class; better yet, that function takes a schema parameter and passes that on to the MetaData initializer.

With that function available, all other packages can create their own Base class and use that for their respective table mappings. Passing the schema parameter also allows a small step towards customizing that generated Base, e.g. with a schema name for PostgreSQL dbs.

I think I can now create an Engine and Connection, and use the different Base classes and their mappings in isolation…

One interesting detail: SQLA defines the type returned by declarative_base() as Any (code, see also this SO conversation) such that I have to use:

    CustomBase: typing.Any = create_base(schema="foo")

Is there a better way to type-hint CustomBase here?

Mike Bayer

unread,
Jul 29, 2022, 4:47:24 PM7/29/22
to noreply-spamdigest via sqlalchemy


On Fri, Jul 29, 2022, at 12:16 AM, jens.t...@gmail.com wrote:
After digging through docs and some more code, here’s what seems to work.

The single, shared package should not generate that Base class which is then used by other packages. Instead, it now provides a function that generates and returns a Base class; better yet, that function takes a schema parameter and passes that on to the MetaData initializer.

With that function available, all other packages can create their own Base class and use that for their respective table mappings. Passing the schema parameter also allows a small step towards customizing that generated Base, e.g. with a schema name for PostgreSQL dbs.

I think I can now create an Engine and Connection, and use the different Base classes and their mappings in isolation…

One interesting detail: SQLA defines the type returned by declarative_base() as Any (code, see also this SO conversation) such that I have to use:

    CustomBase: typing.Any = create_base(schema="foo")

Is there a better way to type-hint CustomBase here?

yes use the recipe given at https://docs.sqlalchemy.org/en/14/orm/declarative_styles.html#creating-an-explicit-base-non-dynamically-for-use-with-mypy-similar .   That is SQLAlchemy 1.4 documentation.  SQLAlchemy 2.0, when released, moves "declarative_base()" to legacy status for this reason and provides a new base class to use.



On Thursday, July 28, 2022 at 10:09:26 PM UTC+10 jens.t...@gmail.com wrote:
Hello,

I’m using a Base = declarative_base() in a single, shared package that other packages can import and use. Now suppose I have two more packages, each of which declares a bunch of mappings using that one common Base.

Is it possible to separate the mappings? I noticed that after importing both packages, Base.metadata.tables.keys() lists all table mappings from both packages.

However, I’d like to treat the tables from these two packages separately and bind them to different engines (and databases).

Is that possible?

Much thanks!
Jens


--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
 
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

jens.t...@gmail.com

unread,
Jul 29, 2022, 6:57:18 PM7/29/22
to sqlalchemy
Thank you, Mike!

I’m still a little unclear: how would I use different MetaData for different sets of tables? For example, currently I use this function:

def create_base(schema: typing.Optional[str] = None) -> typing.Any:
    return declarative_base(
        cls=_Base,
        metadata=MetaData(schema=schema, naming_convention=_NAMING_CONVENTION),
        metaclass=_BaseMeta
    )

where I have a custom _Base (to supply e.g. useful __str__ etc. helpers) and a custom _BaseMeta which derives from DeclarativeBase. The MetaData’s schema is a parameter to that function so that I can create different Base classes with their own MetaData. Using these different Base classes I then declare different table mappings, each with their own MetaData.

Or… would I now statically declare a class CommonBase(metaclass=DeclarativeMeta) (contains the helpers, etc.) and then derive specific classes with their respective MetaData, e.g.

class Base(CommonBase):
    metadata = MetaData(schema=schema, naming_convention=_NAMING_CONVENTION)

Or… maybe I’m thinking about this the wrong way?

Jens

Mike Bayer

unread,
Jul 29, 2022, 10:13:13 PM7/29/22
to noreply-spamdigest via sqlalchemy


On Fri, Jul 29, 2022, at 6:57 PM, jens.t...@gmail.com wrote:
Thank you, Mike!

I’m still a little unclear: how would I use different MetaData for different sets of tables? For example, currently I use this function:

def create_base(schema: typing.Optional[str] = None) -> typing.Any:
    return declarative_base(
        cls=_Base,
        metadata=MetaData(schema=schema, naming_convention=_NAMING_CONVENTION),
        metaclass=_BaseMeta
    )
where I have a custom _Base (to supply e.g. useful __str__ etc. helpers) and a custom _BaseMeta which derives from DeclarativeBase. The MetaData’s schema is a parameter to that function so that I can create different Base classes with their own MetaData. Using these different Base classes I then declare different table mappings, each with their own MetaData.

pep-484 typing is entirely not compatible with functions that return classes unfortunately, so if you wanted Base to be recognized as a class, you have to declare it as needed.  The quickest way to do this is likely with the as_declarative_base function:

mapper_registry = registry()

@mapper_registry.as_declarative_base()
class Base:
    pass

Above, Base is now  a new declarative base with its own MetaData collection.






Or… would I now statically declare a class CommonBase(metaclass=DeclarativeMeta) (contains the helpers, etc.) and then derive specific classes with their respective MetaData, e.g.

class Base(CommonBase):
    metadata = MetaData(schema=schema, naming_convention=_NAMING_CONVENTION)

Or… maybe I’m thinking about this the wrong way?

right we have this in SQLAlchemy 2.0 called DeclarativeBase and it works exactly that way.   To get that in 1.4, here's one that I had to look in the current source for DeclarativeMeta to figure out

class CommonMeta(DeclarativeMeta):
    def __init__(cls, classname, bases, dict_, **kw):
        if not bases or CommonBase in bases:
            cls.registry = cls._sa_registry = reg = registry()
            cls.metadata = reg.metadata
            cls.__init__ = reg.constructor
            type.__init__(cls, classname, bases, dict_)
        else:
            super().__init__(classname, bases, dict_, **kw)

class CommonBase(metaclass=CommonMeta):
    pass



class Base(CommonBase):
    pass



Jens


On Saturday, July 30, 2022 at 6:47:24 AM UTC+10 Mike Bayer wrote:

yes use the recipe given at https://docs.sqlalchemy.org/en/14/orm/declarative_styles.html#creating-an-explicit-base-non-dynamically-for-use-with-mypy-similar .   That is SQLAlchemy 1.4 documentation.  SQLAlchemy 2.0, when released, moves "declarative_base()" to legacy status for this reason and provides a new base class to use.


--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
 
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
Reply all
Reply to author
Forward
0 new messages