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=F

Solution 1:

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()

classUser(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()