so I am working through the cookbook many-to-many ORM example on
zzzeek's blog.
after executing, Base.metadata.tables looks like this:
{
'cookbook': Table('cookbook', MetaData(None),
Column('id', Integer(), table=<cookbook>, primary_key=True,
nullable=False),
Column('description', Text(length=None, convert_unicode=False,
assert_unicode=None), table=<cookbook>),
schema=None),
'cookbook_keyword': Table('cookbook_keyword', MetaData(None),
Column('cookbook_id', Integer(), ForeignKey('cookbook.id'),
table=<cookbook_keyword>),
Column('keyword_id', Integer(), ForeignKey('keyword.id'),
table=<cookbook_keyword>),
schema=None),
'keyword': Table('keyword', MetaData(None),
Column('id', Integer(), table=<keyword>, primary_key=True,
nullable=False),
Column('name', String(length=255, convert_unicode=False,
assert_unicode=None), table=<keyword>),
schema=None),
'recipe': Table('recipe', MetaData(None),
Column('id', Integer(), table=<recipe>, primary_key=True,
nullable=False),
Column('description', Text(length=None, convert_unicode=False,
assert_unicode=None), table=<recipe>),
schema=None),
'recipe_keyword': Table('recipe_keyword', MetaData(None),
Column('recipe_id', Integer(), ForeignKey('recipe.id'),
table=<recipe_keyword>),
Column('keyword_id', Integer(), ForeignKey('keyword.id'),
table=<recipe_keyword>),
schema=None)
}
the blog post shows some queries set up, all using just the Class
declarations..
I added some data, this way
myKwds = []
for nn in keywords:
tk = Keyword()
tk.description = nn
Session.add(tk)
Session.commit()
myCookbook = Cookbook()
myCookbook.description = "timely advice on legumes"
Session.add(myCookbook)
Session.commit()
so now I should be able to use the many-to-many definition in Cookbook
thus ?
Session.query(Cookbook.keywords).all()
but that generates only
Session.query(Cookbook.keywords).all()
2009-08-30 16:52:26,398 INFO sqlalchemy.engine.base.Engine.0x...cecc
SELECT cookbook.id AS cookbook_id, cookbook.description AS
cookbook_description
FROM cookbook
2009-08-30 16:52:26,399 INFO sqlalchemy.engine.base.Engine.0x...cecc
[]
Out[62]: [(1, u'timely advice on legumes')]
that doesnt look right to me.. nothing about the many-to-many join
???
how else ought I be able to use this setup?
==
#!/usr/bin/python
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('sqlite://', echo=True)
Session = scoped_session(sessionmaker(bind=engine))
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Keyword(Base):
__tablename__ = "keyword"
id = Column(Integer, primary_key=True)
name = Column(String(255))
Base.metadata.create_all(engine)
keywords = ["beans", "lentils", "legumes"]
# print Session.query(func.count(Keyword.id)).filter(Keyword.name.in_
(keywords))
# print Session.query(exists().where(Keyword.name.in_(keywords)))
recipe_keywords = Table("recipe_keyword", Base.metadata,
Column("recipe_id", Integer, ForeignKey
("recipe.id")),
Column("keyword_id", Integer, ForeignKey
("keyword.id"))
)
cookbook_keywords = Table("cookbook_keyword", Base.metadata,
Column("cookbook_id", Integer, ForeignKey
("cookbook.id")),
Column("keyword_id", Integer, ForeignKey
("keyword.id"))
)
class Recipe(Base):
__tablename__ = "recipe"
id = Column(Integer, primary_key=True)
description = Column(Text)
keywords = relation(Keyword, secondary=recipe_keywords)
class Cookbook(Base):
__tablename__ = "cookbook"
id = Column(Integer, primary_key=True)
description = Column(Text)
keywords = relation(Keyword, secondary=cookbook_keywords)
Base.metadata.create_all(engine)
##-------------------------------------------------------
## test
## spell out the join explicitly
##(ret, ), = Session.query(
## exists().where(Recipe.id==recipe_keywords.c.recipe_id).
## where(recipe_keywords.c.keyword_id==Keyword.id).
## where(Keyword.name.in_(keywords))
##)
## use ORM join()
(ret, ), = Session.query(
exists().select_from(join(Recipe, Keyword, Recipe.keywords)).
where(Keyword.name.in_(keywords))
)
## use ORM join(), multipe search criteria
(ret, ), = Session.query(or_(
exists().select_from(join(Recipe, Keyword, Recipe.keywords)).
where(Keyword.name.in_(keywords)),
exists().select_from(join(Cookbook, Keyword,
Cookbook.keywords)).
where(Keyword.name.in_(keywords))
))
## use fancy any().. may have gotchas...
## rows = Session.query(Recipe).filter(Recipe.keywords.any
(Keyword.name.in_(keywords))).all()
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
