well, these all do *something* but I am a little lost on how to frame
these
Session.query(Cookbook).select_from( join
(Cookbook,Keyword,Cookbook.keywords)).all()
## selects id and description from cookbook, but empty set comes back?
Session.query(Cookbook.keywords).select_from( join
(Cookbook,Keyword,Cookbook.keywords)).all()
## ibid
Session.query(Cookbook,Keyword).select_from( join
(Cookbook,Keyword,Cookbook.keywords)).all()
## selects cookbook id, description, and keyword id, name
On Aug 30, 5:02 pm, darkblueB <brian.darkbl...@gmail.com> wrote:
> 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
-~----------~----~----~----~------~----~------~--~---
