How do I create a child class from a parent class in SQLAlchemy's Joined Table Inheritance?

[Alex Chamberlain]

I posted this on StackOverflow (http://stackoverflow.com/q/13109085/961353), but it received no answers so I'm hoping I'll have more luck here.

I'm developing a small database where there are far more People than Users, so currently have the following Model.(I'm using Flask-SQLAlchemy and db is an instance of flask.ext.sqlalchemy.SQLAlchemy.)

    class Person(db.Model):

      __tablename__ = 'people'

      id       = db.Column(db.Integer, primary_key = True)

      forename = db.Column(db.String(64))

      surname  = db.Column(db.String(64))

    

      memberships = db.relationship('Membership', backref='person')

    

      @property

      def name(self):

        return self.forename + ' ' + self.surname

    

      def __repr__(self):

        return '<Person %r %r>' % (self.forename, self.surname)

    

    class User(Person):

      __tablename__ = 'users'

      id       = db.Column(db.Integer, db.ForeignKey('people.id'), primary_key = True)

      email    = db.Column(db.String(120), index = True, unique = True)

      role     = db.Column(db.SmallInteger, default = ROLE_USER)

    

      salt     = db.Column(db.BINARY(8))

      password = db.Column(db.BINARY(20))

      def __repr__(self):

        return '<User %r>' % (self.email)

It's working quite well, in that if I create a User then a Person also get's saved. The problem is creating a User when a Person already exists in the database.

I have tried the following:

    >>> p = models.Person.query.get(3)

    >>> u = models.User(id=p.id, email="exa...@example.com")

    >>> u.set_password('password')

    >>> db.session.add(u)

    >>> db.session.commit()

    Traceback

    ...

    sqlalchemy.exc.IntegrityError: (IntegrityError) PRIMARY KEY must be unique u'INSERT INTO people (id, forename, surname) VALUES (?, ?, ?)' (3, None, None)

What am I doing wrong? Is there a way to create a User from a Person?

Thanks in advance,

Alex

[Michael Bayer]

You're using joined table inheritance here, so the creation of a new User object means that a row for both "users" and "people" will be generated.  There's no such thing as a "User that points to a Person" in this model, there's only Persons and Users (who are also Persons).

Your two options are to use composition instead of inheritance here (i.e. a one-to-one relationship) or to work around the model by manually inserting rows into "user", that is, session.execute(User.__table__.insert(), {params}).

[Alex Chamberlain]

So, there's no way to upgrade a Person to a User within the normal ORM?

Thanks,

Alex

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Michael Bayer]

there's an old ticket proposing to add the feature of "changing the class" of an item but it has many tricky turns and corners and hasn't been worth the large amount of effort it would take to make it work perfectly in all cases.   It is of course much easier to do with single-table inheritance, though.

Ideally your application would be creating User or Person objects as appropriate from the start.  If that's not how your system works, and it's typical that Person objects become User objects later on, I'd use composition for that.

[Alex Chamberlain]

Thanks for your help. I've taken your advice and User inherits from db.Model only and I've defined a relationship back to Person.

It would be cool if in the future you could "walk" up and down the object tree, but this is my first project with SQLAlchemy.

Thanks again, it's very much appreciated.

Alex