CODEWITHSUNDEEP
pythonsqlalchemy
wheaties
wheaties

Reputation: 35970

SqlAlchemy: Self referencing default value as query

Let's say I have the following structure (using Flask-SqlAlchemy):

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String, nullable=False, index=True)
    # The following line throws an error at runtime.
    variant = db.Column(db.Integer, nullable=False, index=True,
                        default=select(func.count(User.id)).where(User.name == self.name))

    def __init__(self, name):
        super(User, self).__init__()
        self.name = name

    @property
    def clause(self):
        return '/'.join([str(self.variant), self.name])

Problem is, "User is not defined." I would like to model a system with Users who may choose the same name but add a field to differentiate between users in a systemic way without using (thereby exposing) the "id" field.

Anyone know how to make a self-referential query to use to populate a default value?

Upvotes: 4

Views: 1838

Answers (1)

zzzeek
zzzeek

Reputation: 75127

The issue of the default not referring to User here is solved by just assigning "default" to the Column once User is available. However, that's not going to solve the problem here because "self" means nothing either, there is no User method being called so you can't just refer to "self". The challenge with this statement is that you want it to be rendered as an inline sub-SELECT but it still needs to know the in-memory value of ".name". So you have to assign that sub-SELECT per-object in some way.

The usual way people approach ORM-level INSERT defaults like this is usually by using a before_insert handler.

Another way that's worth pointing out is by creating a SQL level INSERT trigger. This is overall the most "traditional" approach, as here you need to have access to the row being inserted; triggers define a means of getting at the row values that are being inserted.

As far as using a default at the column level, you'd need to use a callable function as the default which can look at the current value of the row being inserted, but at the moment that means that your SELECT statement will not be rendered inline with the INSERT statement, you'd need to pre-execute the SELECT which is not really what we want here.

Anyway, the basic task of rendering a SQL expression into the INSERT while also having that SQL expression refer to some local per-object state is achieved by assigning that expression to the attribute, the ORM picks up on this at flush time. Below we do this in the constructor, but this can also occur inside of before_insert() as well:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)
    name = Column(String, nullable=False, index=True)
    variant = Column(Integer, nullable=False, index=True)

    def __init__(self, name):
        self.name = name
        self.variant = select([func.count(User.id)]).where(User.name == self.name)

e = create_engine("sqlite://", echo=True)

Base.metadata.create_all(e)

s = Session(e)

s.add(User(name='n1'))
s.commit()

s.add(User(name='n1'))
s.commit()

print s.query(User.variant).all()

Upvotes: 5

Related Questions