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.
