Adding data to the appropriate table using SQLAlchemy

Question

I have this for SQLAlchemy (using Flask SqlAlchemy objects):

class User(db.Model): id = db.Column(db.Integer, primary_key=True) username = db.Column(db.String(20), unique=True) password = db.Column(db.String(30)) email = db.Column(db.String(45), unique=True) friends = db.relationship('Friend', backref='user', lazy='dynamic') def __init__(self, username, password, email): self.username = username self.password = password self.email = email def __repr__(self): return "<User('%s','%s','%s')>" % (self.username, self.email, self.id) class Friend(db.Model): id = db.Column(db.Integer, primary_key=True) userId = db.Column(db.Integer, db.ForeignKey('user.id')) friendId = db.Column(db.Integer) created = db.Column(db.DateTime) def __init__(self, userId, friendId): self.userId = userId self.friendId = friendId self.created = datetime.datetime.now() def __repr__(self): return "<Friend(%i,%i)>" % (self.userId, self.friendId) 

As I understand it, in order to add a friend, I would have to do something like this:

First user:

 MyUser = bpdata.User.query.filter_by(id=1).first() 

Getting a friend:

 MyFriend = bpdata.User.query.filter_by(id=2).first() 

Now I would like to do:

 MyUser.Friends.Append(MyFriend) 

Is this possible or do I just need to add friend IDs directly to the Friend table?

Answer 1

I thought about it myself.

What I needed to do:

 MyUser.friends.append(Friend(MyUser.id, MyFriend.id)) 

and then commit the update.

Update:

Well, I found the right way to do what I wanted. At first I don't need a Friend table / class. Full code:

 association_table = db.Table('association', db.Column('user_id', db.Integer, db.ForeignKey('user.id')), db.Column('friend_id', db.Integer, db.ForeignKey('user.id')) ) class User(db.Model): id = db.Column(db.Integer, primary_key=True) username = db.Column(db.String(20), unique=True) password = db.Column(db.String(30)) email = db.Column(db.String(45), unique=True) friends = db.relationship("User", secondary=association_table, backref='added_by', primaryjoin=id == association_table.c.user_id, secondaryjoin=id == association_table.c.friend_id) 

With this, I can now do the following:

 >>> user1 = User.query.filter_by(id=1).first() >>> user1.friends [] >>> user2 = User.query.filter_by(id=2).first() >>> user1.friends.append(user2) >>> user1.friends [<User('user1',' [e-mail] ','2')>] >>> user1.friends[0].added_by [<User('admin',' [e-mail] ','1')>]