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 On Monday, October 29, 2012 3:47:51 PM UTC, Michael Bayer wrote: > > 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. > > > > > On Oct 29, 2012, at 11:43 AM, Alex Chamberlain wrote: > > So, there's no way to upgrade a Person to a User within the normal ORM? > > Thanks, > > Alex > On Oct 29, 2012 3:00 PM, "Michael Bayer" > <mik...@zzzcomputing.com<javascript:>> > wrote: > >> >> On Oct 29, 2012, at 5:06 AM, Alex Chamberlain wrote: >> >> 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 <javascript:> >> ") >> >>> 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) >> >> >> 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}). >> >> >> >> -- >> 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<javascript:> >> . >> To unsubscribe from this group, send email to >> sqlalchemy+...@googlegroups.com <javascript:>. >> For more options, visit this group at >> http://groups.google.com/group/sqlalchemy?hl=en. >> > > -- > 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<javascript:> > . > To unsubscribe from this group, send email to > sqlalchemy+...@googlegroups.com <javascript:>. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > > > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/WIVjXOQhWEQJ. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
