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="[email protected]")
> >>> 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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
