flask-sqlalchemy query association table having additional columns

Question

I have created many-to-many relationship as per below code, but i have added additional columns to the association table. I want to retrieve those columns by specific user id. Could someone advise how can query it? I have read many posts but none of them has additional columns.

If I do:

u = Users.query.filter_by(id='8').first()
u.subscriptions[0].id

I can see data from Subscription table, but if i do:

u.subscriptions[0].subscription_id
OR
u.subscriptions[0].User_Subscription

I am getting 'Subscription' object has no attribute, as I am trying to get data of columns in User_Subscription association table.

User_Subscription = db.Table('user_subscription', 
    db.Column('user_id', db.Integer, db.ForeignKey('users.id')),
    db.Column('subscription_id', db.Integer, db.ForeignKey('subscription.id')),
    db.Column('stripe_subscription_id', db.String(100), nullable=True),
    db.Column('paypal_subscription_id', db.String(100), nullable=True)
)

class Subscription(db.Model):
    __tablename__ = "subscription"
    id = db.Column(db.Integer, primary_key=True)
    subscription_name = db.Column(db.String(100), unique=True, nullable=False)
    subscription_desc = db.Column(db.String(100), unique=False, nullable=False)
    date_created = db.Column(db.DateTime, default=datetime.datetime.utcnow)
    date_updated = db.Column(db.DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow)
    subscriptions = db.relationship('Users', secondary=User_Subscription, backref= db.backref('subscriptions', lazy='dynamic'))

class Users(db.Model, UserMixin):
    __tablename__ = "users"
    id = db.Column(db.Integer, primary_key=True)
    firstname = db.Column(db.String(50), unique=False, nullable=False)
    lastname = db.Column(db.String(50), unique=False, nullable=False)
    email = db.Column(db.String(120), unique=True, nullable=False)
    password = db.Column(db.String(60), nullable=False)
    date_created = db.Column(db.DateTime, default=datetime.datetime.utcnow)
    date_updated = db.Column(db.DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow)
    user_status_id = db.Column(db.String(2), db.ForeignKey('user_status.id'))

Matija Lukic · Accepted Answer · 2019-04-18 14:32:23Z

You've got the error ('Subscription' object has no attribute) because it is an relationship object, not table.

In this line of code:

u.subscriptions[0].User_Subscription

you are trying to access attribute of the object User_Subcription which is the relationship object.

If you want to create new association table, create same table in your database and define one more class UserSubscription:

class UserSubscription(db.Model)
    __tablename__ = 'user_subscription'
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    subscription_id = db.Column(db.Integer, db.ForeignKey('subscription.id'))
    stripe_subscription_id = db.Column(db.String(100), nullable=True)
    paypal_subscription_id = db.Column(db.String(100), nullable=True)

    user = db.relationship('Users', backref='user_subscription')

And then you can access attribute of UserSubscription class:

u.user_subscription.subscription_id

Collectives™ on Stack Overflow

flask-sqlalchemy query association table having additional columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related