[sqlalchemy] Re: Using mapper with custom select creates unneeded subquery
This caught me out a couple of weeks ago, and I've seen a couple of
other similar questions as well. You need to add 'correlate=False' to
the nested select.
I wonder if this should be added to the FAQ?
Hope that helps,
Simon
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED]
On Behalf Of Koen Bok
Sent: 22 March 2007 10:47
To: sqlalchemy
Subject: [sqlalchemy] Re: Using mapper with custom select creates
unneeded subquery
Let me post some sample code with that:
mapper(Request, request_table, properties={
'children' : relation(
Request,
primaryjoin=request_table.c.id_parent==request_table.c.id,
backref=backref(parent,
remote_side=[request_table.c.id])),
'i': relation(Item,
primaryjoin=item_table.c.id==request_table.c.id_item,
backref='requests', lazy=True),
[SOME MORE STUFF]
'stock': relation(Stock, primaryjoin=and_(
request_table.c.id_item==stock_table.c.id_product,
request_table.c.id_location==stock_table.c.id_location,
request_table.c.id_stocktype==stock_table.c.id_stocktype),
foreign_keys=[stock_table.c.id_product,
stock_table.c.id_location,
stock_table.c.id_stocktype])})
stock_request = select(
[c for c in stock_table.c] + \
[stock_table.c.quantity.op('-')
(func.sum(request_table.c.quantity)).label('unordered')] + \
[stock_table.c.quantity.op('-')
(func.sum(request_table.c.allocation)).label('unallocated')],
and_(
request_table.c.id_item==stock_table.c.id_product,
request_table.c.id_location==stock_table.c.id_location,
request_table.c.id_stocktype==stock_table.c.id_stocktype),
group_by=[c for c in stock_table.c]).alias('stock_request')
mapper(Stock, stock_request, properties={
'product': relation(Item,
primaryjoin=item_table.c.id==stock_table.c.id_product,
backref='_stock'),
'location': relation(Item,
primaryjoin=item_table.c.id==stock_table.c.id_location),
'stocktype': relation(StockType)})
If you need more, just let me know!
Koen
On Mar 22, 11:42 am, Koen Bok [EMAIL PROTECTED] wrote:
Thanks for the reply! If the performance is about equal, that's fine!
But I think I might have found a bug.
When I make a selection it generates the following (faulty) SQL query:
SELECT
stock_request.id_stocktype AS stock_request_id_stocktype,
stock_request.unordered AS stock_request_unordered,
stock_request.id_location AS stock_request_id_location,
stock_request.id_product AS stock_request_id_product,
stock_request.unallocated AS stock_request_unallocated,
stock_request.quantity AS stock_request_quantity,
stock_request.id AS stock_request_id FROM
(
SELECT
stock.id AS id,
stock.id_stocktype AS id_stocktype,
stock.id_product AS id_product,
stock.id_location AS id_location,
stock.quantity AS quantity,
(stock.quantity - sum(request.quantity)) AS unordered,
(stock.quantity - sum(request.allocation)) AS
unallocated
FROM request
WHERE
request.id_item = stock.id_product
AND
request.id_location = stock.id_location
AND
request.id_stocktype = stock.id_stocktype
GROUP BY
stock.id,
stock.id_stocktype,
stock.id_product,
stock.id_location,
stock.quantity,
stock.quantity
) AS stock_request, stock
WHERE
stock.id_product = 5
AND
stock.id_location = 7
AND
stock.id_stocktype = 1
ORDER BY
stock_request.id
LIMIT 1
The FROM in the subquery should be: FROM request, stock
The strange thing is that whenever I print the subquery's sql, it has
stock in the FROM and tehrefore is correct.
Or am I not understanding it right?
Koen
On Mar 22, 2:58 am, Michael Bayer [EMAIL PROTECTED] wrote:
when you pass a selectable to the mapper, the mapper considers that
selectable to be encapsulated, in the same way as a table is. the
Query cannot add any extra criterion to that selectable directly
since it would modify the results and corrupt the meaning, if not
the actual syntax, of the selectable itself. therefore the mapper
is always going to select * from (your selectable) - its the only
way to guarantee the correct results.
the queries it generates, i.e. select * from (select * from ...))
will be optimized by the database's optimizer in most cases and
should not add any overhead to your application.
On Mar 21, 2007, at 8:08 PM, Koen Bok wrote:
My mapper looks like this:
stock_unreserved = select(
[stock_table] + \
[sqlalchemy] Re: Using mapper with custom select creates unneeded subquery
put correlate=False on your subquery.
On Mar 22, 2007, at 6:42 AM, Koen Bok wrote:
Thanks for the reply! If the performance is about equal, that's fine!
But I think I might have found a bug.
When I make a selection it generates the following (faulty) SQL query:
SELECT
stock_request.id_stocktype AS stock_request_id_stocktype,
stock_request.unordered AS stock_request_unordered,
stock_request.id_location AS stock_request_id_location,
stock_request.id_product AS stock_request_id_product,
stock_request.unallocated AS stock_request_unallocated,
stock_request.quantity AS stock_request_quantity,
stock_request.id AS stock_request_id
FROM
(
SELECT
stock.id AS id,
stock.id_stocktype AS id_stocktype,
stock.id_product AS id_product,
stock.id_location AS id_location,
stock.quantity AS quantity,
(stock.quantity - sum(request.quantity)) AS unordered,
(stock.quantity - sum(request.allocation)) AS unallocated
FROM request
WHERE
request.id_item = stock.id_product
AND
request.id_location = stock.id_location
AND
request.id_stocktype = stock.id_stocktype
GROUP BY
stock.id,
stock.id_stocktype,
stock.id_product,
stock.id_location,
stock.quantity,
stock.quantity
) AS stock_request, stock
WHERE
stock.id_product = 5
AND
stock.id_location = 7
AND
stock.id_stocktype = 1
ORDER BY
stock_request.id
LIMIT 1
The FROM in the subquery should be: FROM request, stock
The strange thing is that whenever I print the subquery's sql, it has
stock in the FROM and tehrefore is correct.
Or am I not understanding it right?
Koen
On Mar 22, 2:58 am, Michael Bayer [EMAIL PROTECTED] wrote:
when you pass a selectable to the mapper, the mapper considers that
selectable to be encapsulated, in the same way as a table is. the
Query cannot add any extra criterion to that selectable directly
since it would modify the results and corrupt the meaning, if not the
actual syntax, of the selectable itself. therefore the mapper is
always going to select * from (your selectable) - its the only way to
guarantee the correct results.
the queries it generates, i.e. select * from (select * from ...))
will be optimized by the database's optimizer in most cases and
should not add any overhead to your application.
On Mar 21, 2007, at 8:08 PM, Koen Bok wrote:
My mapper looks like this:
stock_unreserved = select(
[stock_table] + \
[stock_table.c.quantity.op('-')
(func.sum(request_table.c.quantity)).label('unordered')] + \
[stock_table.c.quantity.op('-')
(func.sum(request_table.c.allocation)).label('unallocated')],
and_(
request_table.c.id_item==stock_table.c.id_product,
request_table.c.id_location==stock_table.c.id_location,
request_table.c.id_stocktype==stock_table.c.id_stocktype),
group_by=[c for c in stock_table.c]).alias('stock_unreserved')
mapper(Stock, stock_unreserved, properties={
'product': relation(Item,
primaryjoin=item_table.c.id==stock_table.c.id_product,
backref='_stock'),
'location': relation(Item,
primaryjoin=item_table.c.id==stock_table.c.id_location),
'stocktype': relation(StockType)})
Whenever I try to select an object through the mapper I would
think it
would use the SQL from stock_unreserved which is:
SELECT
stock.id,
stock.id_stocktype,
stock.id_product,
stock.id_location,
stock.quantity, (stock.quantity - sum(request.quantity)) AS
unordered,
(stock.quantity - sum(request.allocation)) AS unallocated
FROM stock, request
WHERE request.id_item = stock.id_product
AND request.id_location = stock.id_location
AND request.id_stocktype = stock.id_stocktype
GROUP BY stock.id, stock.id_stocktype, stock.id_product,
stock.id_location, stock.quantity
Selecting all objects by a plain select() on the mapper works great!
But when I make a selection it does a subquery on all the results
eg:
SELECT * FROM (SELECT * FROM stock_unreserved) WHERE selection
criteria
But I want it to append it to the other selection criteria without
doing a subselect eg:
SELECT * FROM stock_unreserved WERE ... AND ... + extra selection
criteria
Is this possible at all?
Many thanks!
Koen
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Using mapper with custom select creates unneeded subquery
clearly correlate=False has to be more prominently mentioned, not
just in the FAQ but in the main docs, there should be a section
specifically on subqueries and their mechanics.
On Mar 22, 2007, at 6:53 AM, King Simon-NFHD78 wrote:
This caught me out a couple of weeks ago, and I've seen a couple of
other similar questions as well. You need to add 'correlate=False' to
the nested select.
I wonder if this should be added to the FAQ?
Hope that helps,
Simon
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED]
On Behalf Of Koen Bok
Sent: 22 March 2007 10:47
To: sqlalchemy
Subject: [sqlalchemy] Re: Using mapper with custom select creates
unneeded subquery
Let me post some sample code with that:
mapper(Request, request_table, properties={
'children' : relation(
Request,
primaryjoin=request_table.c.id_parent==request_table.c.id,
backref=backref(parent,
remote_side=[request_table.c.id])),
'i': relation(Item,
primaryjoin=item_table.c.id==request_table.c.id_item,
backref='requests', lazy=True),
[SOME MORE STUFF]
'stock': relation(Stock, primaryjoin=and_(
request_table.c.id_item==stock_table.c.id_product,
request_table.c.id_location==stock_table.c.id_location,
request_table.c.id_stocktype==stock_table.c.id_stocktype),
foreign_keys=[stock_table.c.id_product,
stock_table.c.id_location,
stock_table.c.id_stocktype])})
stock_request = select(
[c for c in stock_table.c] + \
[stock_table.c.quantity.op('-')
(func.sum(request_table.c.quantity)).label('unordered')] + \
[stock_table.c.quantity.op('-')
(func.sum(request_table.c.allocation)).label('unallocated')],
and_(
request_table.c.id_item==stock_table.c.id_product,
request_table.c.id_location==stock_table.c.id_location,
request_table.c.id_stocktype==stock_table.c.id_stocktype),
group_by=[c for c in stock_table.c]).alias('stock_request')
mapper(Stock, stock_request, properties={
'product': relation(Item,
primaryjoin=item_table.c.id==stock_table.c.id_product,
backref='_stock'),
'location': relation(Item,
primaryjoin=item_table.c.id==stock_table.c.id_location),
'stocktype': relation(StockType)})
If you need more, just let me know!
Koen
On Mar 22, 11:42 am, Koen Bok [EMAIL PROTECTED] wrote:
Thanks for the reply! If the performance is about equal, that's fine!
But I think I might have found a bug.
When I make a selection it generates the following (faulty) SQL
query:
SELECT
stock_request.id_stocktype AS stock_request_id_stocktype,
stock_request.unordered AS stock_request_unordered,
stock_request.id_location AS stock_request_id_location,
stock_request.id_product AS stock_request_id_product,
stock_request.unallocated AS stock_request_unallocated,
stock_request.quantity AS stock_request_quantity,
stock_request.id AS stock_request_id FROM
(
SELECT
stock.id AS id,
stock.id_stocktype AS id_stocktype,
stock.id_product AS id_product,
stock.id_location AS id_location,
stock.quantity AS quantity,
(stock.quantity - sum(request.quantity)) AS
unordered,
(stock.quantity - sum(request.allocation)) AS
unallocated
FROM request
WHERE
request.id_item = stock.id_product
AND
request.id_location = stock.id_location
AND
request.id_stocktype = stock.id_stocktype
GROUP BY
stock.id,
stock.id_stocktype,
stock.id_product,
stock.id_location,
stock.quantity,
stock.quantity
) AS stock_request, stock
WHERE
stock.id_product = 5
AND
stock.id_location = 7
AND
stock.id_stocktype = 1
ORDER BY
stock_request.id
LIMIT 1
The FROM in the subquery should be: FROM request, stock
The strange thing is that whenever I print the subquery's sql, it has
stock in the FROM and tehrefore is correct.
Or am I not understanding it right?
Koen
On Mar 22, 2:58 am, Michael Bayer [EMAIL PROTECTED] wrote:
when you pass a selectable to the mapper, the mapper considers that
selectable to be encapsulated, in the same way as a table is. the
Query cannot add any extra criterion to that selectable directly
since it would modify the results and corrupt the meaning, if not
the actual syntax, of the selectable itself. therefore the mapper
is always going to select * from (your selectable) - its the only
way to guarantee the correct results.
the queries it generates, i.e. select * from (select * from ...))
will be optimized by the database's
[sqlalchemy] Re: Using mapper with custom select creates unneeded subquery
It worked! Thanks a bunch!
On Mar 22, 5:06 pm, Michael Bayer [EMAIL PROTECTED] wrote:
clearly correlate=False has to be more prominently mentioned, not
just in the FAQ but in the main docs, there should be a section
specifically on subqueries and their mechanics.
On Mar 22, 2007, at 6:53 AM, King Simon-NFHD78 wrote:
This caught me out a couple of weeks ago, and I've seen a couple of
other similar questions as well. You need to add 'correlate=False' to
the nested select.
I wonder if this should be added to the FAQ?
Hope that helps,
Simon
-Original Message-
From: sqlalchemy@googlegroups.com [mailto:[EMAIL PROTECTED]
On Behalf Of Koen Bok
Sent: 22 March 2007 10:47
To: sqlalchemy
Subject: [sqlalchemy] Re: Using mapper with custom select creates
unneeded subquery
Let me post some sample code with that:
mapper(Request, request_table, properties={
'children' : relation(
Request,
primaryjoin=request_table.c.id_parent==request_table.c.id,
backref=backref(parent,
remote_side=[request_table.c.id])),
'i': relation(Item,
primaryjoin=item_table.c.id==request_table.c.id_item,
backref='requests', lazy=True),
[SOME MORE STUFF]
'stock': relation(Stock, primaryjoin=and_(
request_table.c.id_item==stock_table.c.id_product,
request_table.c.id_location==stock_table.c.id_location,
request_table.c.id_stocktype==stock_table.c.id_stocktype),
foreign_keys=[stock_table.c.id_product,
stock_table.c.id_location,
stock_table.c.id_stocktype])})
stock_request = select(
[c for c in stock_table.c] + \
[stock_table.c.quantity.op('-')
(func.sum(request_table.c.quantity)).label('unordered')] + \
[stock_table.c.quantity.op('-')
(func.sum(request_table.c.allocation)).label('unallocated')],
and_(
request_table.c.id_item==stock_table.c.id_product,
request_table.c.id_location==stock_table.c.id_location,
request_table.c.id_stocktype==stock_table.c.id_stocktype),
group_by=[c for c in stock_table.c]).alias('stock_request')
mapper(Stock, stock_request, properties={
'product': relation(Item,
primaryjoin=item_table.c.id==stock_table.c.id_product,
backref='_stock'),
'location': relation(Item,
primaryjoin=item_table.c.id==stock_table.c.id_location),
'stocktype': relation(StockType)})
If you need more, just let me know!
Koen
On Mar 22, 11:42 am, Koen Bok [EMAIL PROTECTED] wrote:
Thanks for the reply! If the performance is about equal, that's fine!
But I think I might have found a bug.
When I make a selection it generates the following (faulty) SQL
query:
SELECT
stock_request.id_stocktype AS stock_request_id_stocktype,
stock_request.unordered AS stock_request_unordered,
stock_request.id_location AS stock_request_id_location,
stock_request.id_product AS stock_request_id_product,
stock_request.unallocated AS stock_request_unallocated,
stock_request.quantity AS stock_request_quantity,
stock_request.id AS stock_request_id FROM
(
SELECT
stock.id AS id,
stock.id_stocktype AS id_stocktype,
stock.id_product AS id_product,
stock.id_location AS id_location,
stock.quantity AS quantity,
(stock.quantity - sum(request.quantity)) AS
unordered,
(stock.quantity - sum(request.allocation)) AS
unallocated
FROM request
WHERE
request.id_item = stock.id_product
AND
request.id_location = stock.id_location
AND
request.id_stocktype = stock.id_stocktype
GROUP BY
stock.id,
stock.id_stocktype,
stock.id_product,
stock.id_location,
stock.quantity,
stock.quantity
) AS stock_request, stock
WHERE
stock.id_product = 5
AND
stock.id_location = 7
AND
stock.id_stocktype = 1
ORDER BY
stock_request.id
LIMIT 1
The FROM in the subquery should be: FROM request, stock
The strange thing is that whenever I print the subquery's sql, it has
stock in the FROM and tehrefore is correct.
Or am I not understanding it right?
Koen
On Mar 22, 2:58 am, Michael Bayer [EMAIL PROTECTED] wrote:
when you pass a selectable to the mapper, the mapper considers that
selectable to be encapsulated, in the same way as a table is. the
Query cannot add any extra criterion to that selectable directly
since it would modify the results and corrupt the meaning, if not
the actual syntax, of the selectable itself. therefore the mapper
is always going to select * from (your selectable) - its the only
way to guarantee the correct
[sqlalchemy] Re: Fix on ADODBAPI
Hi,
That doesn't work for me - it makes create_engine() hang for ages then
fail. I wonder if this is because without a port, the driver first tries
a named pipe connection. If you are keen to include this functionality,
you'll have to do a bit more ground work.
The other issue you mention, with CoInitialize(), I have hit as well. I
see this as an adodbapi bug, and unfortunately adodbapi is not
maintained any more. This issue and others pushed me to pyodbc, which is
working great.
Paul
El Gringo wrote:
I found that the conenctionString is not complete, the port is
missing. Not like anywhere else, host and port must be separated by a
comma !
def make_connect_string(keys):
connectors = [Provider=SQLOLEDB]
connectors.append (Data Source=%s,%s % (keys.get(host),
keys.get(port, 1433)))
connectors.append (Initial Catalog=%s % keys.get(database))
user = keys.get(user)
if user:
connectors.append(User Id=%s % user)
connectors.append(Password=%s % keys.get(password, ))
else:
connectors.append(Integrated Security=SSPI)
return [[;.join (connectors)], {}]
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Using mapper with custom select creates unneeded subquery
Hi, clearly correlate=False has to be more prominently mentioned, not just in the FAQ but in the main docs, there should be a section specifically on subqueries and their mechanics. Could SQLAlchemy deal with this case automatically? I think it could. Subqueries that are used in a from clause should not be correlated, while other subqueries should be. This touches on the work we're doing on ticket #513. Paul --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: how to display all the tables of my DB
I launched it, but I receive this error message: Traceback (most recent call last): File autocode.py, line 20, in module tbl = Table(tname, metadata, schema=schema, autoload=True); File build/bdist.macosx-10.3-fat/egg/sqlalchemy/schema.py, line 143, in __call__ File build/bdist.macosx-10.3-fat/egg/sqlalchemy/engine/base.py, line 505, in reflecttable File build/bdist.macosx-10.3-fat/egg/sqlalchemy/databases/ postgres.py, line 385, in reflecttable KeyError: 'information_schema.cardinal_number' Somes ideas? thanks again! --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Help with saving mapped objects to pylons session
Hi,
I am having problems with saving/restoring mapped objects from a pylons
session.
I am getting the no attribute on a list attribute (which is a one-many
relationship) error when pylons tries to unpickle the object.
I've read a previous post where Michael explains why this happens.
I have turned off all lazy loaders. I have also tried to implement
__setstate__,
but not sure what I should be doing in there.
This is my example:
class Invoice: pass
class InvoiceLine: pass
db.mapper(InvoiceLine, db.invoice_line_table)
db.mapper(Invoice, db.invoice_table,
properties = {
'client': relation(Client, lazy=True),
'lines': relation(InvoiceLine, lazy=True),
}
)
This works:
import pickle
i = Invoice()
pickle.dump(i, file('test', 'w'))
i = pickle.load(file('test'))
if I then do this
il = InvoiceLine()
i.lines.append(il)
pickle.dump(i, file('test', 'w'))
I get this:
File /home/huy/apps/sqlalchemy/lib/sqlalchemy/orm/attributes.py, line
452, in __setstate__
AttributeError: type object 'Invoice' has no attribute 'lines'
How can I get around this problem ?
Thanks
Huy
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
