SQLAlchemy 相当于 Django ORM 的关系跨越过滤器
此示例来自 Django 文档。
给定 (Django) 数据库模型：
class Blog(models.Model):
name = models.CharField(max_length=100)
class Entry(models.Model):
blog = models.ForeignKey(Blog)
headline = models.CharField(max_length=255)
body_text = models.TextField()
在 Django 中，我可以使用：
Entry.objects.filter(blog__name__exact='Beatles Blog')
获取具有指定名称的博客的所有 Entry 对象。
问题：根据下面的模型定义，等效的 SQLAlchemy 语句是什么？
class Blog(Base):
__tablename__ = "blog"
id = Column(Integer, primary_key=True)
name = Column(Unicode(100))
class Entry(Base):
__tablename__ = "entry"
id = Column(Integer, primary_key=True)
blogid = Column(Integer, ForeignKey(Blog.id))
headline = Column(Unicode(255))
body_text = Column(UnicodeText)
blog = relationship(Blog, backref="entries")
编辑
我相信有两种方法可以实现此目的：
>>> q = session.query
>>> print q(Entry).join(Blog).filter(Blog.name == u"One blog")
SELECT entry.id AS entry_id, entry.blogid AS entry_blogid, entry.headline AS entry_headline, entry.body_text AS entry_body_text
FROM entry JOIN blog ON blog.id = entry.blogid
WHERE blog.name = ?
>>> print q(Entry).filter(Entry.blog.has(Blog.name == u"One blog"))
SELECT entry.id AS entry_id, entry.blogid AS entry_blogid, entry.headline AS entry_headline, entry.body_text AS entry_body_text
FROM entry
WHERE EXISTS (SELECT 1
FROM blog
WHERE blog.id = entry.blogid AND blog.name = ?)
# ... and of course
>>> blog = q(Blog).filter(Blog.name == u"One blog")
>>> q(Entry).filter(Entry.blog == blog)
还有几个问题：
- 除了上述方法之外，还有其他方法可以使用 SQLAlchemy 来实现此目的吗？
- 如果您可以在多对一关系中执行
session.query(Entry).filter(Entry.blog.name == u"One blog")，这是否没有意义？ - 在这种情况下，Django 的 ORM 会生成什么 SQL？
This example is from the Django documentation.
Given the (Django) database model:
class Blog(models.Model):
name = models.CharField(max_length=100)
class Entry(models.Model):
blog = models.ForeignKey(Blog)
headline = models.CharField(max_length=255)
body_text = models.TextField()
In Django I can use:
Entry.objects.filter(blog__name__exact='Beatles Blog')
to get all Entry objects for blogs with the specified name.
Question: What is the equivalent SQLAlchemy statement, given the model definition below?
class Blog(Base):
__tablename__ = "blog"
id = Column(Integer, primary_key=True)
name = Column(Unicode(100))
class Entry(Base):
__tablename__ = "entry"
id = Column(Integer, primary_key=True)
blogid = Column(Integer, ForeignKey(Blog.id))
headline = Column(Unicode(255))
body_text = Column(UnicodeText)
blog = relationship(Blog, backref="entries")
EDIT
I believe there are two ways to accomplish this:
>>> q = session.query
>>> print q(Entry).join(Blog).filter(Blog.name == u"One blog")
SELECT entry.id AS entry_id, entry.blogid AS entry_blogid, entry.headline AS entry_headline, entry.body_text AS entry_body_text
FROM entry JOIN blog ON blog.id = entry.blogid
WHERE blog.name = ?
>>> print q(Entry).filter(Entry.blog.has(Blog.name == u"One blog"))
SELECT entry.id AS entry_id, entry.blogid AS entry_blogid, entry.headline AS entry_headline, entry.body_text AS entry_body_text
FROM entry
WHERE EXISTS (SELECT 1
FROM blog
WHERE blog.id = entry.blogid AND blog.name = ?)
# ... and of course
>>> blog = q(Blog).filter(Blog.name == u"One blog")
>>> q(Entry).filter(Entry.blog == blog)
A few more questions:
- Are there other ways to accomplish this using SQLAlchemy than the ones above?
- Would it not make sense if you could do
session.query(Entry).filter(Entry.blog.name == u"One blog")in many-to-one relationships? - What SQL does Django's ORM produce in this case?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
我也一直梦想着拥有像姜戈一样的“魔法连接”。我熟悉
sqlalchemy-django-query它，我发现它对于我的任务来说不够强大。这就是我创建的原因
https://github.com/absent1706/sqlalchemy-mixins#django -类似查询。
它的工作原理与 sqlalchemy-django-query 类似，但具有更多附加功能（这是一个比较）。它还经过充分测试和记录。
I also always dreamed of having Django-like "magic joins". I'm familiar with
sqlalchemy-django-queryit, I found that it's not powerful enough for my tasks.That's why I created
https://github.com/absent1706/sqlalchemy-mixins#django-like-queries.
It works similar to
sqlalchemy-django-querybut has more additional features (here's a comparison). Also it's well tested and documented.怎么样：
How about:
聚会已经迟到了，但我偶然发现了这个：
https://github.com/mitsuhiko/sqlalchemy-django- query/blob/master/sqlalchemy_django_query.py
这尝试使用 django 表示法构建查询。
来自文档：
Post.query.filter_by(pub_date__year=2008)Already late to the party, but i stumbled across this:
https://github.com/mitsuhiko/sqlalchemy-django-query/blob/master/sqlalchemy_django_query.py
This tries to build queries using the django notation.
From the docs:
Post.query.filter_by(pub_date__year=2008)