Reputation: 61
Let's say I have a blog front page with several posts that each have a number of tags (like the example at http://pythonhosted.org/Flask-SQLAlchemy/models.html#many-to-many-relationships but with posts instead of pages). How do I retrieve all tags for all shown posts in a single query with SQLAlchemy?
The way I would do it is this (I'm just curious if there's a better way):
Is that the way to do it?
Upvotes: 0
Views: 1710
Reputation: 3620
This is of course not the way to do it. The purpose of an ORM like sqlalchemy is to represent the records and all relations/related records as objects which you can just work on without thinking about the underlying sql-queries.
You don't need to retrieve anything. You already have it. The tags
-property of your Post()
-objects is (something like) a list
of Tag()
-objects.
I don't know Flask-SQLAlchemy but since you asked for SQLAlchemy
I feel free to post a pure SQLAlchemy
example that uses the models from the Flask example (and is self contained):
#!/usr/bin/env python3
# coding: utf-8
import sqlalchemy as sqAl
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship, backref
engine = sqAl.create_engine('sqlite:///m2m.sqlite') #, echo=True)
metadata = sqAl.schema.MetaData(bind=engine)
Base = declarative_base(metadata)
tags = sqAl.Table('tags', Base.metadata,
sqAl.Column('tag_id', sqAl.Integer, sqAl.ForeignKey('tag.id')),
sqAl.Column('page_id', sqAl.Integer, sqAl.ForeignKey('page.id'))
)
class Page(Base):
__tablename__ = 'page'
id = sqAl.Column(sqAl.Integer, primary_key=True)
content = sqAl.Column(sqAl.String)
tags = relationship('Tag', secondary=tags,
backref=backref('pages', lazy='dynamic'))
class Tag(Base):
__tablename__ = 'tag'
id = sqAl.Column(sqAl.Integer, primary_key=True)
label = sqAl.Column(sqAl.String)
def create_sample_data(sess):
tag_strings = ('tag1', 'tag2', 'tag3', 'tag4')
page_strings = ('This is page 1', 'This is page 2', 'This is page 3', 'This is page 4')
tag_obs, page_obs = [], []
for ts in tag_strings:
t = Tag(label=ts)
tag_obs.append(t)
sess.add(t)
for ps in page_strings:
p = Page(content=ps)
page_obs.append(p)
sess.add(p)
page_obs[0].tags.append(tag_obs[0])
page_obs[0].tags.append(tag_obs[1])
page_obs[1].tags.append(tag_obs[2])
page_obs[1].tags.append(tag_obs[3])
page_obs[2].tags.append(tag_obs[0])
page_obs[2].tags.append(tag_obs[1])
page_obs[2].tags.append(tag_obs[2])
page_obs[2].tags.append(tag_obs[3])
sess.commit()
Base.metadata.create_all(engine, checkfirst=True)
session = sessionmaker(bind=engine)()
# uncomment the next line and run it once to create some sample data
# create_sample_data(session)
pages = session.query(Page).all()
for p in pages:
print("page '{0}', content:'{1}', tags: '{2}'".format(
p.id, p.content, ", ".join([t.label for t in p.tags])))
Yes, life can be so easy...
Upvotes: 1