I have a project that's a few years old, but needs some maintenance. Since I last worked on the project, two major things have happened. First, Elixir -- a declarative layer on top of SQLAlchemy -- has not been maintained. Secondly, SQLAlchemy now has its own declarative layer.
Rather than continue using Elixir, I decided to migrate my data models to use the new SQLAlchemy declarative layer, which interestingly enough appear to be relatively compatible with Elixir's philosophy.
The first thing I did was do a direct mapping in my model columns and update the import statements.
# from Elixir import Entity, Field, DateTime, Unicode, Integer, Boolean, setup_all, create_all, session, metadata
from sqlalchemy import Column, DateTime, String, Integer, ForeignKey, Boolean
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()Entity => Base (This is what my models inherit from) Field => Column Unicode => String Boolean, DateTime, Integer, Boolean -- are all the same between Elixir and SQLAlchemy
That was basically it.
I also enabled join table inheritance. I have three models, two of which are subclasses of the first. Basically, in the parent class you have to add a new column to track the name of referenced child models, then tell the SQLAlchemy mapper which column to use. Elixir did this automagically, but we have to do it manually. We'll also add a tablename if you hadn't done so already.
In the parent class (Media), I add:
class Media(Base):
# ... other column definitions here ...
media_type = Column(String, default=u'')
__mapper_args__ = {'polymorphic_on': media_type}
__tablename__ = 'media'In the subclasses, we only need two things (in addition to tablename). We are identifying the polymorphic name of this class (so that the parent class can reference it with the column type). We'll also create an id column. Elixir did both of these things automagically as well, so we're going to do them manually. In the two subclasses (Video, Image), we add:
class Image(Media):
# ... other column definitions here ...
__mapper_args__ = {'polymorphic_identity': 'image'}
__tablename__ = 'images'
id = Column(Integer, ForeignKey('media.id'), primary_key=True)'video' looks identical, but with video in place of image. Maybe with a few extra columns (like codec type, duration etc.)
That's pretty much it. Now we just wrap up the connectors. Elixir exported a 'session' which was basically passthrough to an SQL Alchemy session. We need to call sessionmaker()'s factory to get the same effect:
# I put this at the bottom of the file
# Elixir bindings:
#create_all()
#setup_all()
#metadata.bind = "sqlite://mydb.sql"
# updated sqlalchemy:
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
engine = create_engine("sqlite://mydb.sql")
s = sessionmaker()
s.configure(bind=engine)
Base.metadata.create_all(engine)
#export session which is how the rest of the app expects it
session = s()At this point, you should have your models all set up. The only difference now is that the query structure is slightly different.
In Elixir, I could do this:
db.Video.query.all()But in SQLAlchemy, you do:
db.session.query(db.Video).all()So I made a slight helper so that I can keep the same syntax style. The only difference is that we'll call class.query() as a function like: db.Video.query().all()
I'm also going to throw this on the super class, so that we don't have to redefine Video.query and Image.query separately (the only difference being that one calls session.query(Video), the other calls session.query(Image).
class Media(Base):
# ... column definitions
@classmethod
def query(cls):
return session.query(cls)That's it! We use @classmethod which basically means that this function doesn't require a 'self', so it's basically static. However, we do get passed in an instance of the class type, so we can just pass that along to the global session variable.
Now, I can do this:
import db # this is my database file with my classes in it etc.
videos = db.Video.query().all()
images = db.Image.query().filter_by(category='cats').all()To create a new object, we do have to add it to the session first. In Elixir:
db.Image(filename='cat.jpg')
db.session.commit()
Now we do:
img = db.Image(filename='cat.jpg')
db.session.add(img)
db.commit()
For deleting, we have to delete it directly in the session as well (db.session.delete(obj)). I created a nice helper for this too:
class Media(Base):
# columns go here...
def delete(self):
return session.delete(self)Now, we can do this, which is identical to the Elixir API:
obj = db.Image.query().filter_by(id=10).first()
obj.delete()
db.session.commit()There are certainly cleaner ways to do it, for example not using a global session variable. I'll likely refactor that eventually, but for now the important part was minimal impact on the API.
Now, I can use the new version of SQLAlchemy and ditch Elixir. Thanks Elixir -- it was good while it lasted!