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.