[sqlalchemy] Re: query and subquery problem with anonymous field
On Jul 16, 7:33 pm, Conor conor.edward.da...@gmail.com wrote:
On 07/16/2010 09:53 AM, dr wrote:
Michael,
I have implemented what you suggested and below is a comparison of the
results:
***
2010-07-16 15:44:31,520 INFO sqlalchemy.engine.base.Engine.0x...7910
BEGIN
2010-07-16 15:44:31,520 INFO sqlalchemy.engine.base.Engine.0x...7910
SELECT tbl1.id, tbl1.text, tbl2.id AS parent
FROM nsm AS tbl1
LEFT OUTER JOIN nsm AS tbl2
ON tbl2.lft = (SELECT MAX(tbl3.lft)
FROM nsm AS tbl3
WHERE tbl1.lft tbl3.lft
AND tbl1.lft tbl3.rgt)
2010-07-16 15:44:31,520 INFO sqlalchemy.engine.base.Engine.0x...7910
()
(1, 'Albert', None)
(2, 'Bert', 1)
(3, 'Chuck', 1)
(4, 'Donna', 3)
(5, 'Eddie', 3)
(6, 'Fred', 3)
***
2010-07-16 15:44:31,520 INFO sqlalchemy.engine.base.Engine.0x...7910
SELECT nsm_1.id AS nsm_1_id, nsm_1.text AS nsm_1_text, nsm_2.id AS
parent
FROM nsm AS nsm_1 LEFT OUTER JOIN nsm AS nsm_2 ON nsm_2.lft = (SELECT
max(nsm_3.lft) AS max_left
FROM nsm AS nsm_3, nsm AS nsm_1
WHERE nsm_1.lft nsm_3.lft AND nsm_1.lft nsm_3.rgt)
2010-07-16 15:44:31,536 INFO sqlalchemy.engine.base.Engine.0x...7910
()
(1, 'Albert', 6)
(2, 'Bert', 6)
(3, 'Chuck', 6)
(4, 'Donna', 6)
(5, 'Eddie', 6)
(6, 'Fred', 6)
***
Comparing the raw sql (correct) and query/subquery results you will
notice that the query/subquery does not return the correct parent id
Any further ideas about why this is producing this result?
You need to force SQLAlchemy to correlate the subquery to the outer
query. This will ensure that the subquery uses the outer query's tbl1
instead of trying to generate a new tbl1 FROM clause.
subq = session.query(func.max(tbl3.left).label('max_left'))
subq = subq.filter(tbl1.left tbl3.left)
subq = subq.filter(tbl1.left tbl3.right)
# NEW: Add correlation
subq = subq.correlate(tbl1)
subq = subq.subquery().as_scalar()
q = session.query(tbl1.id, tbl1.text, tbl2.id.label('parent'))
q = q.outerjoin((tbl2,tbl2.left==subq))
-Conor
Conor,
Thank you that was the solution. I guess it is back to the
documentation for me.
--
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] Python logging with sqlalchemy
Does anyone have a custom handler for the logging module that would use a sqlalchemy connection to save the log into the db. Tnx :) -- 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] Close the engine and go home
This is a really dumb noob question... I know how to create an engine, a Session, and a session. I see how to do session.close(). How do I close the engine? (i.e. How do I close all connections to the database cleanly so my application can exit and not leave the database server hanging?) Thanks, Michael -- 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] Struqtural for SQL Alchemy
Wanted to drop by and mention that I just released the first public version of Struqtural. In a nutshell, Struqtural tries to make it as easy as possible to get data into a database, out of a database (as python objects) and to build some of the more common complex table arrangements. Struqtural also provides facades or additional functionality for some things in SQL Alchemy I didn't enjoy working with directly. I've tried to make it as configurable as possible, the behavior of most things can be changed pretty radically without any monkey patching. More information is available at: Home page: http://www.turnkey-analytics.com/struqtural/ PyPI: http://pypi.python.org/pypi/Struqtural Source: https://code.launchpad.net/pydatastep Bugs: https://bugs.launchpad.net/pydatastep This library scratches a big itch of mine. I'm very interested in feedback on how I can develop it further to make it as useful as possible for other people. I'm also very interested in feedback from people here on how I can leverage SQL Alchemy more effectively. -- 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] lockmode
Hello, I am running Python 2.6 SQL Alchemy 0.5.8 against MS SQL Server Express 2008 with pyODBC. When I issue something like: sess.query(job).with_lockmode(update) It does not seem to be locking according to the query I am getting back from my profiler. Is this the correct usage? Thanks Mike -- 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.
