Friday, December 2, 2011

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.

1 comment:

GuildWars2Items said...

The consequences of today are determined by the actions of the past scarlet blade gold. To change your future, alter your decisions today scarlet blade gold, Experience is a hard teacher because she gives the test first, the lesson afterwards scarlet blade gold, but it takes character to keep you there.

Empty what's full. Scratch where it itches. Don't go around saying the world owes you a living rs gold, The world owes you nothing. It was here first. Being happy doesn't mean that everything is perfect Runescape Gold, It means that you've decided to look beyond the imperfections. Do not pray for tasks equal to your powersrs gold.