[sqlalchemy] Simple Join failing
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()
q = q.join(price)
# OK - no errors so far.. BUT...
print q1 gives me this;
*(*SELECT products.`Group` AS `Group`, products.`Code` AS `Code`,
products.`Description` AS `Description`,
...lots of other columns removed for your viewing pleasure...
FROM products*)* INNER JOIN product_prices ON `Group` =
product_prices.`Group` AND `Code` = product_prices.`Code`
Note the ( ) around the (SELECT . products)
When I execute that query it fails and gives me this;
Traceback (most recent call last):
File interactive input, line 1, in module
File string, line 1, in lambda
File C:\Python26\lib\site-packages\sqlalchemy\util.py, line 1780, in
warned
return fn(*args, **kwargs)
File C:\Python26\lib\site-packages\sqlalchemy\sql\expression.py, line
1290, in execute
return e._execute_clauseelement(self, multiparams, params)
File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 1798,
in _execute_clauseelement
return connection._execute_clauseelement(elem, multiparams, params)
File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 1272,
in _execute_clauseelement
parameters=params
File C:\Python26\lib\site-packages\sqlalchemy\engine\base.py, line 1380,
in __create_execution_context
connection=self, **kwargs)
File C:\Python26\lib\site-packages\sqlalchemy\engine\default.py, line
342, in __init__
raise exc.*ArgumentError(Not an executable clause: %s % compiled)*
So, I have two basic questions;
1) Why did the first error occur with the FK's simply because I limited the
columns returned?
2) What's wrong with the join I'm doing and why is SA creating SQL that it
can't use?
I'm guessing I'm doing SOMETHING wrong, so the goal here is simple -This is
what I WANT to do;
Create a query that allows me to dictate the columns returned from a join of
two or more tables giving me a result set with access to all the columns
I've nominated.
I've looked hard in the docs and Google, and I guess everyone thinks this is
too basic to actually SHOW you how to do it! ;-)I took the ( ) out with
a cut and paste and dropped the SQL into an e.execute('SELECT.) and it
worked fine, so it's really close it just appears to be adding erroneous
brackets.
I'm running 0.6.4 BTW.
Thanking you in advance.
Cheers
Warwick
--
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.
RE: [sqlalchemy] Simple Join failing
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.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Simple Join failing
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.comsqlalchemy%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.
RE: [sqlalchemy] Simple Join failing
Warwick Prince wrote: 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? ;-) In SQL, you can treat a query just like a table, so you can join 2 queries together, or join a query to another table. For example: SELECT * FROM (SELECT a, b FROM table_1) as q1 INNER JOIN (SELECT c, d FROM table_2) as q2 ON q1.b = q2.c That example is not very helpful - it could easily be rewritten as a single SELECT, but I hope you see that the subqueries can be as complicated as you like. The object that you were originally producing with your 'q.join(price)' wasn't a Select object, but a Join - something that you can select from. You could write something like this: # JOIN the price table with a query on the products table: j = prod.select().join(price) # SELECT from that JOIN: q = select(some_columns, from_obj=[j]) This almost certainly isn't what you wanted in your situation, but there are plenty of cases where subqueries are very useful. 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. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Simple Join failing
Hi Simon Thanks for your help. It's amazing what a tiny hint in the right direction can do.. Between these emails, I've made a proof on concept, and am now implementing the code in the real app. So easy when I'm not blocked by a warped vision of what I'm doing. Funny, looking back at the docs I can now clearly see TABLE.join T A B L E.join, not query.join...I'm SURE that wasn't there before ;-D Thanks again. Cheers Warwick On 5 October 2010 20:39, King Simon-NFHD78 simon.k...@motorola.com wrote: Warwick Prince wrote: 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? ;-) In SQL, you can treat a query just like a table, so you can join 2 queries together, or join a query to another table. For example: SELECT * FROM (SELECT a, b FROM table_1) as q1 INNER JOIN (SELECT c, d FROM table_2) as q2 ON q1.b = q2.c That example is not very helpful - it could easily be rewritten as a single SELECT, but I hope you see that the subqueries can be as complicated as you like. The object that you were originally producing with your 'q.join(price)' wasn't a Select object, but a Join - something that you can select from. You could write something like this: # JOIN the price table with a query on the products table: j = prod.select().join(price) # SELECT from that JOIN: q = select(some_columns, from_obj=[j]) This almost certainly isn't what you wanted in your situation, but there are plenty of cases where subqueries are very useful. 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.comsqlalchemy%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.
