CODEWITHSUNDEEP
pythonsqlalchemy
user3362454
user3362454

Reputation: 61

SQLAlchemy many-to-many query

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

Answers (1)

TNT
TNT

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

Related Questions