Forums

one-one relation sqlalchemy flask

Hi,

I am trying to implement 1-1 relation for User and Profile Tabels like below.

class User(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(255), unique=True)
    password = db.Column(db.String(255))
    active = db.Column(db.Boolean())
    confirmed_at = db.Column(db.DateTime(), default=datetime.now())

    roles = db.relationship('Role', secondary=roles_users,
                            backref=db.backref('users', lazy='dynamic'))

    def __repr__(self):
        return '<User {self.email}>'.format(self=self)


class CustomerProfile(db.Model):
    __tablename__ = 'customer_profiles'
    id = db.Column(db.Integer(), primary_key=True)
    full_name = db.Column(db.String(200), nullable=True)

    user_id = db.Column(db.Integer(), db.ForeignKey('user.id'), unique=True)
    user = db.relationship('User', backref=db.backref("CustomerProfile", uselist=False))

    def __repr__(self):
        return '<CustomerProfile {self.full_name}>'.format(self=self)

My question is:

  • Is this the correct representation of 1-1 mapping? Cause when I try to reverse engineer the database with MySQL Workbench, it does show me one to many mapping
  • If this is the correct representation, then inserting a duplicate row is being allowed unless I give unique=True in user_id. I would have expected that since I told MySql about the mapping, it should not allow duplicate row...Is that not true?
deleted-user-2133955 | 7 posts | June 5, 2017, 8:51 a.m. | permalink

That looks like it would work (with the unique constraint), but it appears that SQLAlchemy has a different preferred way of representing one-to-one mappings. See this part of their documentation.

Staff giles | 12671 posts | PythonAnywhere staff | June 5, 2017, 1:10 p.m. | permalink

I've changed the code to below from the article :

class User(db.Model, UserMixin):
    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(255), unique=True)
    password = db.Column(db.String(255))
    active = db.Column(db.Boolean())
    confirmed_at = db.Column(db.DateTime(), default=datetime.now())

    roles = db.relationship('Role', secondary=roles_users,
                            backref=db.backref('users', lazy='dynamic'))

    customer_profiles = db.relationship('CustomerProfile', uselist=False, back_populates="user")

    def __repr__(self):
        return '<User {self.email}>'.format(self=self)


class CustomerProfile(db.Model):
    __tablename__ = 'customer_profiles'
    id = db.Column(db.Integer(), primary_key=True)
    full_name = db.Column(db.String(200), nullable=True)

    user_id = db.Column(db.Integer(), db.ForeignKey('user.id'))
    user = db.relationship('User', back_populates='customer_profiles')

    def __repr__(self):
        return '<CustomerProfile {self.full_name}>'.format(self=self)

but still, I am able to insert a duplicate row in to profiles....

deleted-user-2133955 | 7 posts | June 5, 2017, 6:41 p.m. | permalink

Have you reloaded your webapp and do you need to do any migration of your db schema (eg: if you had created the db table without a uniqueness constraint, you'd need to wipe the existing table/somehow let the db know you changed the constraints?)

Staff conrad | 4232 posts | PythonAnywhere staff | June 6, 2017, 11:36 a.m. | permalink

yes, all that's done. I am running it local.

Since I am mapping it at the DB level, I am assuming that DB will stop me from inserting a duploicate row even though I do not give unique=True.. Is my understanding correct?

deleted-user-2133955 | 7 posts | June 6, 2017, 6:14 p.m. | permalink

whaat do you mean mapping it at the DB level?

Staff conrad | 4232 posts | PythonAnywhere staff | June 8, 2017, 12:06 p.m. | permalink