Autoload in SQLAlchemy

A quick tip,

If you want your data models to autoload their contents as you create them, you can use the following idiom:

I generally use three python modules in my database related (thus using SQLAlchemy) applications, one for the declarative Base (declarative.py), one for the database url, session, query and engine (db.py) and a last one for the models (models.py).

In the declarative.py:

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

In the db.py:

from declarative import Base
engine = None
session = None
query = None
metadata = Base.metadata
database_url = None

def setup(url="sqlite:///:memory:"):
    global engine
    global session
    global query
    global metadata
    global database_url
    
    # to let my models to register them selfs 
    # and fill the Base.metadata
    import models
    
    database_url = url
    
    engine = sqlalchemy.create_engine(database_url, echo=False)
    
    # create the tables
    metadata.create_all(engine)
    
    # create the Session class
    Session = sqlalchemy.orm.sessionmaker(bind=engine)
    
    # create and save session object to session
    session = Session()
    query = session.query
    
    return session

Setting up the database becomes db.setup(), easy enough.

In the model.py:

import db
from declarative import Base

class MyClass(Base):
    __tablename__ = "MyClasses"
    id = Column(Integer, primary_key=True)
    name = Column(String(256), unique=True, nullable=False)
    description = Column(String)
    
    def __new__(cls, name):
        if name:
            # get the instance from the db
            if db.session is None:
                # create the session first
                db.setup()
            
            obj_db = db.query(MyClass).\
                         filter_by(name=name).first()
        
            if obj_db is not None:
                # return the database instance
            
                # skip the __init__
                obj_db.__skip_init__ = None
                
                return obj_db
        
        # if we are here,
        # it means that there is no instance
        # in the database with the given name
        # so just create one normally
        # And SQLAlchemy queries will use this part
        return super(MyClass, cls).__new__(cls, name)
    
    def __init__(self, name):
        # do not initialize if it is created from the DB
        if hasattr(self, "__skip_init__"):
            return
        
        self.name = name

In the above example the __init__ method is unnecessarily included to show how to skip the __init__ when there is an instance retrieved from the database.

The key in the above example is the usage of __new__ and using the super to hand the class creation to Type as shown above, this will trigger __init__. We can not just return because the class will not be initialized and we should skip the __init__ for instances returned from the database.

So by using this idiom, you should be able to restore an instance without querying it:

from model import MyClass
import db

db.setup()

myC1 = MyClass(name="Test")
myC1.description = "To show the instance retrieved correctly"
db.session.add(myC1)
db.session.commit()

myC2 = MyClass(name="Test")
print myC2.description
# this should print:
#     To show the instance retrieved correctly

Thats all, easy and smooth...

Edit: Now I need to note that SQLAlchemy also uses __new__ to create new instances, but we prevent it doing another query again inside __new__ by checking the name argument, which is not used by SQLAlchemy when restoring from database.