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="exam...@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)
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 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.