CODEWITHSUNDEEP
pythonsqlalchemy
Mehdi
Mehdi

Reputation: 1360

How to update many to many relationship by using id with sqlalchemy?

I know I can simply update many to many relationship like this:

tags = db.Table('tags',
    db.Column('tag_id', db.Integer, db.ForeignKey('tag.id'), primary_key=True),
    db.Column('page_id', db.Integer, db.ForeignKey('page.id'), primary_key=True)
)

class Page(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    tags = db.relationship('Tag', secondary=tags, lazy='subquery',
        backref=db.backref('pages', lazy=True))

class Tag(db.Model):
    id = db.Column(db.Integer, primary_key=True)

tag1 = Tag()
tag2 = Tag()
page = Page( tags=[tag1])

and later for updating:


page.append(tag2)

but I want to update them only by the tag id, Assume I have to create a general function that only accepts person and ids for addresses and update it. What I want is something like this:

page = Page(tags=[1,2]) # 1 and 2 are primary keys of (tag)s

or in a function

def update_with_foreignkey(page, tags=[1,2]):
 # dosomething to update page without using Tag object
 return  updated page

Upvotes: 0

Views: 1958

Answers (1)

Mehdi
Mehdi

Reputation: 1360

It was a little tricky and by using the evil eval but finally, I find a general way to update many to many relations using foreign keys. My goal was to update my object by getting data from a PUT request and in that request, I only get foreign keys for the relationship with other data.

Step by step solution:

1- Find relationships in the object, I find them using __mapper__.relationships

2- Find the key that represents the relationship.

for rel in Object.__mapper__.relationships:
        key = str(rel).rsplit('.',1)[-1]

in question case it return 'tags' as the result.

3- Find the model for another side of the relation ( in this example Tag). 3-1 Find name of the table. 3-2 Convert table name to camleCase because sqlalchemy use underscore for the table name and camelCase for the model. 3-3 Use eval to get the model.

 if key in data:
            table = eval(convert_to_CamelCase(rel.table.name))
            temp = table.query.filter(table.id.in_(data[key])).all() # this line convert ids to sqlacemy objects

All together

def convert_to_CamelCase(word):
    return ''.join(x.capitalize() or '_' for x in word.split('_'))

def update_relationship_withForeingkey(Object, data):
    for rel in Object.__mapper__.relationships:
        key = str(rel).rsplit('.',1)[-1]

        if key in data:
            table = eval(convert_to_CamelCase(rel.table.name))
            temp = table.query.filter(table.id.in_(data[key])).all() # this line convert ids to sqlacemy objects
            data[key] = temp


    return data

data is what I get from the request, and Object is the sqlalchemy Model that I want to update.

running this few lines update give me the result:

item = Object.query.filter_by(id=data['id'])
data = update_relationship_withForeingkey(Object,data)
for i,j in data.items():
            setattr(item,i,j)
db.session.commit()

I'm not sure about caveats of this approach but it works for me. Any improvement and sugesstion are welcome.

Upvotes: 1

Related Questions