[sqlalchemy] Re: new LIMIT/OFFSET Support for oracle - huge speed penalty
I checked the results (for my case) for the two variants of ordering our table (actually a view) and I could not find any difference. No duplicates, everything in same order. Next monday I can provide you with some benchmarks. Some more details: - the select was run on a view with 20 columns, coming from 7 tables - no index on the ordered column (users are able to sort arbitrarily over any column through a web interface) The difference was huge, the ROW_NUMBER() OVER approach was finished after 1.7s, the newer nested approach took about 25s. More details next week. Ciao ciao Ralph On Jun 23, 5:29 pm, Ian Kelly ian.g.ke...@gmail.com wrote: On Wed, Jun 23, 2010 at 1:50 AM, Ralph Heinkel ralph.hein...@web.de wrote: Hi, we are about upgrading our sqlalchemy library from 0.4.8 to something newer and during this process we have detected that the LIMIT/OFFSET support for oracle has been changed, from using “ROW NUMBER OVER...” to a wrapped subquery approach in conjunction with ROWNUM as described in http://www.sqlalchemy.org/docs/reference/dialects/oracle.html#limit-o... Unfortunately this approch is about 10 times slower for large tables which is mainly related to the fact that the innermost subquery has to sort the entire table with a plain 'order by'. Interestingly the ROW_NUMBER() OVER (ORDER BY some db fields) is so much more efficient than the normal order by approach. Do you have benchmarks to back that up? In Django, we switched from using row_number to rownum after a contributor convinced me that rownum was faster. See: http://code.djangoproject.com/ticket/9136 Thanks, Ian -- 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.
[sqlalchemy] Re: new LIMIT/OFFSET Support for oracle - huge speed penalty
Hi Michael, We have also tried the /*+ FIRST_ROWS(N) */ optimization hint, it only gave a 25% speed improvement, but the result was still 5 or 7 times slower than the ROW_NUMBER() OVER approach. I'll provide benchmark details on Monday, also details about table (actually a view) layout, indices, etc. On Jun 23, 6:04 pm, Michael Bayer mike...@zzzcomputing.com wrote: A full history of this feature is here: http://www.sqlalchemy.org/trac/ticket/536 The rationale is based on the bug described in that ticket, as well as that we preferred to go with an approach that was recommended by a lead engineer at Oracle. The dialect includes an option to add the /*+ FIRST_ROWS(N) */ directive, by specifying the optimize_limits keyword to create engine - we originally had that in the query in all cases, until some folks chimed in that we shouldn't make that decision by default. I don't know if that helps your use case. The previous system can be restored using a @compiles directive. I have documented that recipe athttp://www.sqlalchemy.org/trac/wiki/UsageRecipes/OracleRowNumberOver. I don't understand yet how the @compiles directive works, but I'm also not that familiar with SA internals. Could you point me to some URL where this is described? Thanks, Ralph -- 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.
[sqlalchemy] SQLite: Rolling back DDL requests
Hi *, This is as much a question as it is a write up of the issue I am fighting with. To summarize: my problem was caused by the sqlite3 python module inserting commits automatically. I think it should not not that!! today I ran into a problem with rolling back DDL requests to SQLite. Interestingly, this works just fine from the sqlite3 command line utility. Here is a minimal example to illustrate: - from sqlalchemy import engine engine = create_engine(sqlite:///test.sqlite, echo=True) conn = engine.connect() txn = conn.begin(): conn.execute(create table demo (foo varchar, bar varchar)) txn.rollback() - I am actually using DDL instances for those requests and they are creating triggers, but either way, nothing is rolled back. The output is: INFO sqlalchemy.engine.base.Engine.0x...f090 BEGIN INFO sqlalchemy.engine.base.Engine.0x...f090 create table demo (foo varchar, bar varchar) INFO sqlalchemy.engine.base.Engine.0x...f090 () INFO sqlalchemy.engine.base.Engine.0x...f090 ROLLBACK Typing the exactly same commands into the sqlite3 command line interface shows that it is in fact rolling back fine. I reproduced the same thing using the sqlite3 python bindings directly: - from sqlite3 import connect c = connect(test.sqlite) c.execute(begin) c.execute(create table demo (foo varchar, bar varchar)) c.rollback() - Same problem. Now, I can't really infer from the sqlite3 documentation how transactions are managed. After a bit of experiment, I found out that passing isolation_level=None to the sqlite3.connect function gives me the expected behaviour: My changes are rolled back. However, when passing this same option to create_engine, it has no effect. Which does not surprise me given that the documentation at http://www.sqlalchemy.org/docs/reference/dialects/sqlite.html only talks about sending a pragma down to each sqlite connection, while the misbehaviour seems to be caused by the sqlite3 module adding commit instructions into the command stream. More precisely, this code here automatically inserts a commit in front of all commands send to sqlite which are not select, update, delete, insert, replace: http://code.python.org/hg/branches/release2.6-maint/file/7fa70e059572/Modules/_sqlite/cursor.c#l571 So, as a stop gap measure, how do I pass isolation_level=None to sqlite3.connect via SA? Thanks! Torsten -- DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH Torsten Landschoff Office Dresden Tel: +49-(0)351-4519587 Fax: +49-(0)351-4519561 mailto:torsten.landsch...@dynamore.de http://www.dynamore.de Registration court: Mannheim, HRB: 109659, based in Karlsruhe, Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz -- 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.
[sqlalchemy] Column X on class Y conflicts with existing column Z
Is this legal? The field title is exposed in one polymorphic subclass
as the attribute file_name and in another as name.
code
class _Doc(Base):
An OpenGroupare Document object
__tablename__ = 'doc'
object_id = Column(document_id,
Integer,
Sequence('key_generator'),
primary_key=True)
_is_folder = Column(is_folder, Integer)
__mapper_args__ = {'polymorphic_on': _is_folder}
class Document(_Doc):
__entityName__ = 'File'
__mapper_args__ = {'polymorphic_identity': 0}
file_name = Column(title, String(255))
class Folder(_Doc):
__entityName__ = 'Folder'
__mapper_args__ = {'polymorphic_identity': 1}
name = Column(title, String(255))
/code
When I run this as python app.py it works. However if I install the
Egg and run app.py it fails with the error -
error
Traceback (most recent call last):
File /usr/bin/coils-master-service, line 5, in module
pkg_resources.run_script('OpenGroupware==0.1.16',
'coils-master-service')
File /usr/lib/python2.6/site-packages/pkg_resources.py, line 448, in
run_script
self.require(requires)[0].run_script(script_name, ns)
File /usr/lib/python2.6/site-packages/pkg_resources.py, line 1173,
in run_script
exec script_code in namespace, namespace
File
/usr/lib/python2.6/site-packages/OpenGroupware-0.1.16-py2.6.egg/EGG-INFO/scripts/coils-master-service,
line 23, in module
File build/bdist.linux-x86_64/egg/coils/foundation/__init__.py, line
24, in module
File
build/bdist.linux-x86_64/egg/coils/foundation/alchemy/__init__.py,
line 28, in module
File build/bdist.linux-x86_64/egg/coils/foundation/alchemy/doc.py,
line 206, in module
File
/usr/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6.egg/sqlalchemy/ext/declarative.py,
line 830, in __init__
_as_declarative(cls, classname, cls.__dict__)
File
/usr/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6.egg/sqlalchemy/ext/declarative.py,
line 806, in _as_declarative
(c, cls, inherited_table.c[c.name])
sqlalchemy.exc.ArgumentError: Column 'title' on class class
'coils.foundation.alchemy.doc.Folder' conflicts with existing column
'doc.title'
/error
--
Adam Tauno Williams awill...@whitemice.org LPIC-1, Novell CLA
http://www.whitemiceconsulting.com
OpenGroupware, Cyrus IMAPd, Postfix, OpenLDAP, Samba
--
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] SQLite: Rolling back DDL requests
I am still astonished about sqlite3 messing up transaction boundaries. And it is even worse than I thought because it breaks savepoints completely. Have a look at this thread: http://mail.python.org/pipermail/python-list/2010-March/1239395.html Quote: Setting isolation_level=None is a must for anyone who want to do any serious work with sqlite. I tend to agree. Hope this stops somebody from running into the same problem. Greetings, Torsten -- DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH Torsten Landschoff Office Dresden Tel: +49-(0)351-4519587 Fax: +49-(0)351-4519561 mailto:torsten.landsch...@dynamore.de http://www.dynamore.de Registration court: Mannheim, HRB: 109659, based in Karlsruhe, Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz -- 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] Re: new LIMIT/OFFSET Support for oracle - huge speed penalty
On Jun 24, 2010, at 6:31 AM, Ralph Heinkel wrote: Hi Michael, We have also tried the /*+ FIRST_ROWS(N) */ optimization hint, it only gave a 25% speed improvement, but the result was still 5 or 7 times slower than the ROW_NUMBER() OVER approach. I'll provide benchmark details on Monday, also details about table (actually a view) layout, indices, etc. On Jun 23, 6:04 pm, Michael Bayer mike...@zzzcomputing.com wrote: A full history of this feature is here: http://www.sqlalchemy.org/trac/ticket/536 The rationale is based on the bug described in that ticket, as well as that we preferred to go with an approach that was recommended by a lead engineer at Oracle. The dialect includes an option to add the /*+ FIRST_ROWS(N) */ directive, by specifying the optimize_limits keyword to create engine - we originally had that in the query in all cases, until some folks chimed in that we shouldn't make that decision by default.I don't know if that helps your use case. The previous system can be restored using a @compiles directive. I have documented that recipe athttp://www.sqlalchemy.org/trac/wiki/UsageRecipes/OracleRowNumberOver. I don't understand yet how the @compiles directive works, but I'm also not that familiar with SA internals. Could you point me to some URL where this is described? http://www.sqlalchemy.org/docs/reference/ext/compiler.html -- 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] Column X on class Y conflicts with existing column Z
On Jun 24, 2010, at 9:44 AM, Adam Tauno Williams wrote:
Is this legal? The field title is exposed in one polymorphic subclass
as the attribute file_name and in another as name.
code
class _Doc(Base):
An OpenGroupare Document object
__tablename__ = 'doc'
object_id = Column(document_id,
Integer,
Sequence('key_generator'),
primary_key=True)
_is_folder = Column(is_folder, Integer)
__mapper_args__ = {'polymorphic_on': _is_folder}
class Document(_Doc):
__entityName__ = 'File'
__mapper_args__ = {'polymorphic_identity': 0}
file_name = Column(title, String(255))
class Folder(_Doc):
__entityName__ = 'Folder'
__mapper_args__ = {'polymorphic_identity': 1}
name = Column(title, String(255))
/code
When I run this as python app.py it works. However if I install the
Egg and run app.py it fails with the error -
theres probably some dictionary ordering issue at play, it should be raising
every time. put title on the base class and then map it to different names
on each subclass with synonym:
class _Doc(...):
_title = Column(title, String(255))
class Folder(...):
name = orm.synonym(_Doc._title)
error
Traceback (most recent call last):
File /usr/bin/coils-master-service, line 5, in module
pkg_resources.run_script('OpenGroupware==0.1.16',
'coils-master-service')
File /usr/lib/python2.6/site-packages/pkg_resources.py, line 448, in
run_script
self.require(requires)[0].run_script(script_name, ns)
File /usr/lib/python2.6/site-packages/pkg_resources.py, line 1173,
in run_script
exec script_code in namespace, namespace
File
/usr/lib/python2.6/site-packages/OpenGroupware-0.1.16-py2.6.egg/EGG-INFO/scripts/coils-master-service,
line 23, in module
File build/bdist.linux-x86_64/egg/coils/foundation/__init__.py, line
24, in module
File
build/bdist.linux-x86_64/egg/coils/foundation/alchemy/__init__.py,
line 28, in module
File build/bdist.linux-x86_64/egg/coils/foundation/alchemy/doc.py,
line 206, in module
File
/usr/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6.egg/sqlalchemy/ext/declarative.py,
line 830, in __init__
_as_declarative(cls, classname, cls.__dict__)
File
/usr/lib/python2.6/site-packages/SQLAlchemy-0.6.1-py2.6.egg/sqlalchemy/ext/declarative.py,
line 806, in _as_declarative
(c, cls, inherited_table.c[c.name])
sqlalchemy.exc.ArgumentError: Column 'title' on class class
'coils.foundation.alchemy.doc.Folder' conflicts with existing column
'doc.title'
/error
--
Adam Tauno Williams awill...@whitemice.org LPIC-1, Novell CLA
http://www.whitemiceconsulting.com
OpenGroupware, Cyrus IMAPd, Postfix, OpenLDAP, Samba
--
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.
--
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] SQLite: Rolling back DDL requests
On Jun 24, 2010, at 9:38 AM, Torsten Landschoff wrote:
However, when passing this same option to create_engine, it has no
effect. Which does not surprise me given that the documentation at
http://www.sqlalchemy.org/docs/reference/dialects/sqlite.html only talks
about sending a pragma down to each sqlite connection, while the
misbehaviour seems to be caused by the sqlite3 module adding commit
instructions into the command stream.
More precisely, this code here automatically inserts a commit in front
of all commands send to sqlite which are not select, update, delete,
insert, replace:
http://code.python.org/hg/branches/release2.6-maint/file/7fa70e059572/Modules/_sqlite/cursor.c#l571
So, as a stop gap measure, how do I pass isolation_level=None to
sqlite3.connect via SA?
just use connect_args, should be in the docs:
from sqlalchemy import *
e = create_engine('sqlite://', connect_args={'isolation_level':None})
c = e.raw_connection()
assert c.isolation_level is None
--
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] SQLite: Rolling back DDL requests
On Jun 24, 2010, at 12:35 PM, Torsten Landschoff wrote:
Hi Michael,
Am Donnerstag, den 24.06.2010, 11:07 -0400 schrieb Michael Bayer:
So, as a stop gap measure, how do I pass isolation_level=None to
sqlite3.connect via SA?
just use connect_args, should be in the docs:
from sqlalchemy import *
e = create_engine('sqlite://', connect_args={'isolation_level':None})
That's what I thought but it does not cure my problem.
e.raw_connect().isolation_level is in fact None, but the rollback is not
done anyway. :-(
its passing it through.dont know what else we can do there
Thanks, Torsten
--
DYNAmore Gesellschaft fuer Ingenieurdienstleistungen mbH
Torsten Landschoff
Office Dresden
Tel: +49-(0)351-4519587
Fax: +49-(0)351-4519561
mailto:torsten.landsch...@dynamore.de
http://www.dynamore.de
Registration court: Mannheim, HRB: 109659, based in Karlsruhe,
Managing director: Prof. Dr. K. Schweizerhof, Dipl.-Math. U. Franz
--
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.
--
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.
[sqlalchemy] Problem with eager load disrupting joins
I seem to have found a condition in which eager loading decouples an
applied filter, distorting results an an unexpected way.
I have the following simple 1:N:1 table structure
t_swath_metadata: swath_id, ...
t_productfile: swath_id, product_id, filename, ...
t_product: product_id, product_name
which is just a logical entity, related files and their file types.
This is mapped to the following using SQL Alchemy 0.5.x
class SwathMetadata(object):
pass
class ProductFile(object)
pass
class Product(object)
pass
t_swath_metadata = Table('t_swath_metadata', metadata,
Column('swath_id', Integer, primary_key=True), ...)
t_productfile = Table('t_productfile', metadata,
Column('filename', String, primary_key=True),
Column('product_id', Integer,
ForeignKey('wastac.t_product.product_id')),
Column('swath_id', Integer,
ForeignKey('wastac.t_swath_metadata.swath_id')), ...)
t_product = Table('t_product', metadata,
Column('product_id', Integer, primary_key=True),
Column('product_name', String) ...)
swathMapper = mapper(SwathMetadata, t_swath_metadata,
properties={'productfile': relation(ProductFile)})
productFileMapper = mapper(ProductFile, t_productfile,
properties={'product': relation(Product)})
productMapper = mapper(Product, t_product)
Using this I want to query t_swath_metadata based on that joined
t_product using the following
q = sess.query(SwathMetadata)
q = q.join((ProductFile, ProductFile.swath_id ==SwathMetadata.swath_id))
q = q.join((Product, Product.product_id ==ProductFile.product_id))
q = q.filter(Product.product_name=='qlgt')
results = q.all()
No problem. But I also want access to t_productfile in the results.
So expectedly using the instrumented attribute like this
results[i].productfile.filename
results in additional undesired loads for each result[i]. Enabling
eager loading seemed logical, so using
q = q.options(eagerload(SwathMetadata.productfile))
is where the problem comes in. Each SwathMetadata result is now no
longer attached to single SwathMetadata.productfile (as implied by the
Product.product_name=='qlgt' filter), but attached to many ProductFile
instances, thus completely ignoring the filter.
Behind the scenes I think the eager load is disrupting things causing
an enforced outer join and subquery:
SELECT foo.*, t_productfile.* from (SELECT t_swath_metadata.* FROM
t_swath_metadata JOIN t_productfile JOIN t_product WHERE
t_product.product_name = 'qlgt') as foo LEFT OUTER JOIN t_productfile;
And this is wrong given my applied filter because although the right
SwathMetadata results are returned, traversing
SwathMetadata.productfile now returns all instances of ProductFile,
not just the ones related to Product.product_name = 'qlgt'.
How do I get around this?
--
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] Problem with eager load disrupting joins
On Jun 24, 2010, at 10:04 PM, Nicholas Bower wrote:
I seem to have found a condition in which eager loading decouples an
applied filter, distorting results an an unexpected way.
I have the following simple 1:N:1 table structure
t_swath_metadata: swath_id, ...
t_productfile: swath_id, product_id, filename, ...
t_product: product_id, product_name
which is just a logical entity, related files and their file types.
This is mapped to the following using SQL Alchemy 0.5.x
class SwathMetadata(object):
pass
class ProductFile(object)
pass
class Product(object)
pass
t_swath_metadata = Table('t_swath_metadata', metadata,
Column('swath_id', Integer, primary_key=True), ...)
t_productfile = Table('t_productfile', metadata,
Column('filename', String, primary_key=True),
Column('product_id', Integer,
ForeignKey('wastac.t_product.product_id')),
Column('swath_id', Integer,
ForeignKey('wastac.t_swath_metadata.swath_id')), ...)
t_product = Table('t_product', metadata,
Column('product_id', Integer, primary_key=True),
Column('product_name', String) ...)
swathMapper = mapper(SwathMetadata, t_swath_metadata,
properties={'productfile': relation(ProductFile)})
productFileMapper = mapper(ProductFile, t_productfile,
properties={'product': relation(Product)})
productMapper = mapper(Product, t_product)
Using this I want to query t_swath_metadata based on that joined
t_product using the following
q = sess.query(SwathMetadata)
q = q.join((ProductFile, ProductFile.swath_id ==SwathMetadata.swath_id))
q = q.join((Product, Product.product_id ==ProductFile.product_id))
q = q.filter(Product.product_name=='qlgt')
results = q.all()
No problem. But I also want access to t_productfile in the results.
So expectedly using the instrumented attribute like this
results[i].productfile.filename
results in additional undesired loads for each result[i]. Enabling
eager loading seemed logical, so using
q = q.options(eagerload(SwathMetadata.productfile))
is where the problem comes in. Each SwathMetadata result is now no
longer attached to single SwathMetadata.productfile (as implied by the
Product.product_name=='qlgt' filter), but attached to many ProductFile
instances, thus completely ignoring the filter.
Behind the scenes I think the eager load is disrupting things causing
an enforced outer join and subquery:
SELECT foo.*, t_productfile.* from (SELECT t_swath_metadata.* FROM
t_swath_metadata JOIN t_productfile JOIN t_product WHERE
t_product.product_name = 'qlgt') as foo LEFT OUTER JOIN t_productfile;
And this is wrong given my applied filter because although the right
SwathMetadata results are returned, traversing
SwathMetadata.productfile now returns all instances of ProductFile,
not just the ones related to Product.product_name = 'qlgt'.
How do I get around this?
eagerload() doesn't do anything with the existing joins or filter criterion you
have, it specifically generates its own joins that will load everything that is
logically part of the SwathMetadata.productfile relationship, which here is a
one-to-many. The purpose here is to separate the concern of populating a
collection from that of the filtering/joining intended to locate the primary
object rows. In the typical Parent-Child one-to-many scenario, you might
want to load Parent id 2, because its the one that's linked to Child id 12, but
once you have that Parent, you'd like its children collection to represent
the full list of Child objects referenced by the Parent, not just Child 12.
Here, you'd like the joins and such that you've spelled out manually to also
result in the population of SwathMetadata.productfile, limiting the collection
to only those items selected by your joins. For this purpose, use the
contains_eager() option, introduced in the ORM tutorial at:
http://www.sqlalchemy.org/docs/ormtutorial.html?#using-join-to-eagerly-load-collections-attributes
(note joinedload() is the same as eagerload() in 0.5)
and described in more detail at:
http://www.sqlalchemy.org/docs/mappers.html#routing-explicit-joins-statements-into-eagerly-loaded-collections
Also, if the SwathMetadata.productfile relationship is really intended to point
to one specific ProductFile, you also might consider specifying the
primaryjoin of the relationship so that when queried it loads what is
intended to be a member of the collection.
--
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] Problem with eager load disrupting joins
eagerload() doesn't do anything with the existing joins or filter criterion you have, it specifically generates its own joins that will load everything that is logically part of the SwathMetadata.productfile relationship, which here is a one-to-many. The purpose here is to separate the concern of populating a collection from that of the filtering/joining intended to locate the primary object rows. Ok well that explains it. The decoupling is intended then. Here, you'd like the joins and such that you've spelled out manually to also result in the population of SwathMetadata.productfile, limiting the collection to only those items selected by your joins. For this purpose, use the contains_eager() option, introduced in the ORM tutorial at: http://www.sqlalchemy.org/docs/ormtutorial.html?#using-join-to-eagerly-load-collections-attributes (note joinedload() is the same as eagerload() in 0.5) Aha! Am I right in thinking this is 0.6.x only? Thanks, Nick -- 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] Problem with eager load disrupting joins
On Jun 24, 2010, at 10:23 PM, Nicholas Bower wrote: eagerload() doesn't do anything with the existing joins or filter criterion you have, it specifically generates its own joins that will load everything that is logically part of the SwathMetadata.productfile relationship, which here is a one-to-many. The purpose here is to separate the concern of populating a collection from that of the filtering/joining intended to locate the primary object rows. Ok well that explains it. The decoupling is intended then. Here, you'd like the joins and such that you've spelled out manually to also result in the population of SwathMetadata.productfile, limiting the collection to only those items selected by your joins. For this purpose, use the contains_eager() option, introduced in the ORM tutorial at: http://www.sqlalchemy.org/docs/ormtutorial.html?#using-join-to-eagerly-load-collections-attributes (note joinedload() is the same as eagerload() in 0.5) Aha! Am I right in thinking this is 0.6.x only? contains_eager() has been around for awhile since 0.5 at least. 0.6 has some name changes regarding eager and a new feature subqueryload, but otherwise 0.5 has all the same features. Thanks, Nick -- 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. -- 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] Problem with eager load disrupting joins
Here, you'd like the joins and such that you've spelled out manually to also result in the population of SwathMetadata.productfile, limiting the collection to only those items selected by your joins. For this purpose, use the contains_eager() option, introduced in the ORM tutorial at: http://www.sqlalchemy.org/docs/ormtutorial.html?#using-join-to-eagerly-load-collections-attributes (note joinedload() is the same as eagerload() in 0.5) Aha! Am I right in thinking this is 0.6.x only? contains_eager() has been around for awhile since 0.5 at least. 0.6 has some name changes regarding eager and a new feature subqueryload, but otherwise 0.5 has all the same features. Got it thanks - I see now the query option was instead in the API and Mapper docs. -- 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.
