Lazily importing modules on SQLAlchemy

In SQL Alchemy, I have quite a few tables in my database defined. Strangely, every time I want to access an element on that database I have to import the element in the file. It feels wrong (I come from a Perl background and the imports are done lazily in the table file). I tried adding the things meant to be imported into the file with the base table definition, but that results in a cyclic loop apparently and fails to start. Is there something I’m missing? I’ll include a basic example of what the table definitions look like, and how I’m accessing the objects below. (I didn’t create the database, I’m just creating a new system to access it)

Address class:

from sqlalchemy import Column, String, Integer, ForeignKey
from sqlalchemy.orm import relationship, backref

from my_app.db.product import Base

class Address(Base):
    __tablename__ = 'Addresses'

    AddressId = Column(Integer, primary_key=True)
    ClientId = Column(Integer, ForeignKey('Clients.ClientId'))
    CountryId = Column(Integer, ForeignKey('Countries.CountryId'))
    AddressLine1 = Column(String)
    AddressLine2 = Column(String)
    Town = Column(String)
    County = Column(String)
    PostCode = Column(String)
    BusinessName = Column(String)

    Client = relationship("Client", backref=backref("Addresses"))
    Country = relationship("Country", backref=backref("Addresses"))

    def __init__(self, AddressLine1, AddressLine2, Town, County, PostCode, ClientId,
    BusinessName, CountryId):
        self.AddressLine1 = AddressLine1
        self.AddressLine2 = AddressLine2
        self.Town = Town
        self.County = County
        self.PostCode = PostCode
        self.ClientId = ClientId
        self.BusinessName = BusinessName
        self.IsDeleted = IsDeleted
        self.CountryId = CountryId
        self.IsInvoiceAddress = IsInvoiceAddress

Client class:

from sqlalchemy import Column, String, Integer, DateTime, Double, ForeignKey

from my_app.db.product import Base

class Client(Base):
    __tablename__ = 'Clients'

    ClientId = Column(Integer, primary_key=True)
    Name = Column(String)
    AccountNo = Column(String)
    Email = Column(String)
    DateCreated = Column(DateTime)

    def __init__(self, Name, AccountNo, Email, DateCreated):
        self.Name = Name
        self.AccountNo = AccountNo
        self.Email = Email
        self.DateCreated = DateCreated

Accessing like (stripped down for simplicity):

from my_app.db.product.address import Address
from my_app.db.product.client import Client

address_rs= session.query(Address).all()

for address in address_rs:
    client = address.Client

The above code works, but I’m not happy with havign to import the Client object as well as the Address object – it becomes unwieldy when I have a lot of joins in a module.

Is there anything I can try to test out and hopefully resolve this? I’m not sure if it’s wrong, but it feels wrong after coming from a Perl DBIx::Class background.

>Solution :

SQLAlchemy need to import all model classes, which inherit from Base, to be able to form SQL queries. Due to references and back references, these needs to be resolved before you can use any model classes as an one step that SQLAlchemy usually does behind the scenes.

While it may work for simple use cases, it is unlikely to bring the benefits you wish for.

You can do it, however. You can still dynamically import and initialise Base e.g. with the following code using DeferredReflection mechanism:

Base.py:

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.declarative import DeferredReflection

Base = declarative_base()

class DynamicBase(DeferredReflection, Base):
    """Create a model that is dynamically initialised."""


class MyModel(DynamicBase):
    pass

Then you can initialise SQLAlchemy

from sqlalchemy.ext.declarative import DeferredReflection

# At this point you need have imported all Python modules that inherit from DynamicBase
DeferredReflection.prepare(engine)

However any "import speed" benefits of doing this are usually not worth of the headache.