Hi Simon
Thanks for that - I knew it was something wrong with the approach but simply
could not pick it! Back to the test bench for another go :-)
Cheers
Warwick
P.S. OK - I have to ask - when and how (why?) do I do the .join on the
query? ;-)
On 5 October 2010 19:41, King Simon-NFHD78 <simon.k...@motorola.com> wrote:
> Warwick Prince wrote:
> >
> > Hi All
> >
> > I have what I hope is a very simple question;
> >
> > Just started experimenting with joins, so I tried a very basic test
> > and got a fail that I don't understand. It appears that SA is
> > creating bad SQL, but I'm sure it's something I'm missing.. Here's
> > what I did;
> >
> > I have two tables. products and product_prices. There is a one to
> > many relationship based on Foreign Keys of Group and Code Both
> > tables have columns Group and Code and they are also the primary of
> > each.
> >
> > I do this;
> >
> > e = an Engine (MySQL connector)
> > m = MetaData(e)
> >
> > prod = Table('products', m, autoload=True)
> > price = Table('product_prices, m, autoload=True
> > # These tables are both fine and load correctly
> >
> > # I want to build up my query generatively, so..
> >
> > # Note that I'm selecting specific columns, and both sets of Foreign
> > Keys are in the selected columns (not that I believe I should need to
> > do that)
> > q = prod.select().with_only_columns(['products.Group',
> > 'products.Code', 'product_prices.Group', 'product_prices.Code',
> > 'product_prices.ListPriceEx', 'product_prices.ListPriceInc'])
> >
> > q = q.join(price)
> >
> > # I get this error;
> > ArgumentError: Can't find any foreign key relationships between
> > 'Select object' and 'product_prices'. (They do exists BTW)
> >
> > So, I remove my .with_only_columns and try again
> >
> > q = prod.select()
>
>
> Here you are creating a Select object (ie "SELECT <all columns> FROM
> products")
>
>
> >
> > q = q.join(price)
> >
>
> Now you are joining that Select object with another table
>
> ie. (SELECT <all columns> FROM products) JOIN price ON <join condition>
>
> The extra parentheses are there because you are joining a SELECT with a
> table.
>
> Instead, you want to join the tables together:
>
> prod.join(price)
>
> To select from that, you can use the standalone select function:
>
> http://www.sqlalchemy.org/docs/core/expression_api.html#sqlalchemy.sql.e
> xpression.select
>
> eg.
>
> select([products.c.Group, products.c.Code, price.c.ListPriceEx],
> from_obj=[prod.join(price)])
>
> Hope that helps,
>
> Simon
>
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com<sqlalchemy%2bunsubscr...@googlegroups.com>
> .
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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.
