SQLAlchemy: return existing object instead of creating a new on when calling constructor

Question

I want to use sqlalchemy in a way like this:

email1 = EmailModel(email="[email protected]", account=AccountModel(name="username"))
email2 = EmailModel(email="[email protected]", account=AccountModel(name="username"))

Usually sqlalchemy will create two entries for the account and link each email address to this. If i set the accountname as unique sqlalchemy is throwing an exception which tells me about an entry with the same value already exists. This makes all sense and works as expected.

Now i figured out an way by my own which allows the mentioned code and just creates an account only once by overwriting the the new Constructor of the AccountModel Class:

def __new__(*cls, **kw):
    if len(kw) and "name" in kw:
        x = session.query(cls.__class__).filter(cls[0].name==kw["name"]).first()
        if x: return x
    return object.__new__(*cls, **kw)

This is working perfectly for me. But the question is:

• Is this the correct way?
• Is there an sqlalchemy way of achieving the same?

I'm using the latest 0.8.x SQLAlchemy Version and Python 2.7.x

Thanks for any help :)

Answer 1

There is exactly this example on the wiki at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/UniqueObject.

Though, more recently I've preferred to use a @classmethod for this instead of redefining the constructor, as explicit is better than implicit, also simpler:

user.email = Email.as_unique('[email protected]')

(I'm actually going to update the wiki now to more fully represent the usage options here.)

Answer 2

I think it's not the best way to achieve this since it creates an dependency of your constructor to the global variable session and also modifies the behaviour of the constructor in an unexpected way (new is expected to return a new object after all). If, for example, someone is using your classes with two sessions in parallel the code will fail and he will have to dig into the code to find the error.

I'm not aware of any "sqlalchemy" way of achieving this, but I would suggest to create a function createOrGetAccountModel like

def createOrGetAccountModel(**kw):
    if len(kw) and "name" in kw:
      x = session.query(AccountModel).filter_by(name=kw["name"]).first()
      if x: return x
    return AccountModel(**kw)