Re: [sqlalchemy] outerjoin condition matching tuples
Interesting, and I had thought about it, but wouldn't that run the subquery (myselect) 3 times for each join? On Sunday, April 20, 2014 10:01:20 PM UTC-4, Michael Bayer wrote: there’s a way to get the tuple comparison to work here though it’s not as common, I’d have to try it out and there might be some quirks to work out. I would think this would be more straightforward using a straight AND: and_(mo.c.col1 == myselect.c.col1, mo.c.col2 == myselect.c.col2, mo.c.col3 == myselect.c.col3) where “myselect” is like: select([…]).where(…).alias() On Apr 20, 2014, at 9:33 PM, Rick Otten rottenw...@gmail.comjavascript: wrote: So FWIW, for now, I cast all three columns into strings and concatenated them using the column_property() expression in the mapper, and then did the comparison on that. It worked ok to create the join condition I needed. It wasn't a pretty as a tuple comparison but it got the job done and I can hit my deadline for tomorrow morning. On Sunday, April 20, 2014 2:38:21 PM UTC-4, Rick Otten wrote: I would like to apply a condition in an outer join that matches a multi-item list rather than a single value. In other words, I'm trying to get SQLAlchemy 0.8.6 connecting to PostgreSQL 9.3.4 to generate SQL like this: select * from mytable mt left outer join myothertable mo on (m0.col1, m0.col2, mo.col3) = (select m2.col1, m2.col2, m2.col3 from myothertable m2 where mt.col1 = m2.wilson_id order by m2.col3 desc limit 1) When I construct my query object back in Python 2.7: mo = aliased(myothertable) m2 = aliased(myothertable) mySession.query(mytable, myothertable)\ .select_from(mytable)\ .outerjoin(mo, [mo.col1, mo.col2, mo.col3] == select(\ [m2.col1, m2.col2, m2.col3])\ .where(m2.col1 == mytable.col1)\ .order_by(m2.col3.desc())\ .limit(1)) I get: File build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py, line 1699, in outerjoin File string, line 1, in lambda File build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py, line 51, in generate File build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py, line 1816, in _join File build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py, line 1846, in _join_left_to_right File build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py, line 1861, in _prepare_right_side File build/bdist.linux-x86_64/egg/sqlalchemy/inspection.py, line 74, in inspect *sqlalchemy.exc.NoInspectionAvailable: No inspection system is available for object of type type 'bool'* I tried both List type and Tuple type in my comparison from the inner select statement. When I look at the code where this exception is thrown, it suggests that something is wrong with my mapping. I've done this join by comparing a single column with these two tables, and I've done similar things with other single column comparisons. I'm not sure why I'm unable to compare a set of columns instead. I've been pondering casting all three columns to string and then concatenating them (either in the table mapping, or in the query) to see if I can get back to a single value comparision which I expect to work. I'd rather just compare the tuple/lists though. I could use some hints. If you guys have some. Thanks! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com javascript:. To post to this group, send email to sqlal...@googlegroups.comjavascript: . Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] outerjoin condition matching tuples
no, the SQL is SELECT ... FROM x JOIN y ON x.a=y.a AND x.b=y.b AND x.c=y.c On Apr 21, 2014, at 9:02 AM, Rick Otten rottenwindf...@gmail.com wrote: Interesting, and I had thought about it, but wouldn't that run the subquery (myselect) 3 times for each join? On Sunday, April 20, 2014 10:01:20 PM UTC-4, Michael Bayer wrote: there's a way to get the tuple comparison to work here though it's not as common, I'd have to try it out and there might be some quirks to work out. I would think this would be more straightforward using a straight AND: and_(mo.c.col1 == myselect.c.col1, mo.c.col2 == myselect.c.col2, mo.c.col3 == myselect.c.col3) where myselect is like: select([...]).where(...).alias() On Apr 20, 2014, at 9:33 PM, Rick Otten rottenw...@gmail.com wrote: So FWIW, for now, I cast all three columns into strings and concatenated them using the column_property() expression in the mapper, and then did the comparison on that. It worked ok to create the join condition I needed. It wasn't a pretty as a tuple comparison but it got the job done and I can hit my deadline for tomorrow morning. On Sunday, April 20, 2014 2:38:21 PM UTC-4, Rick Otten wrote: I would like to apply a condition in an outer join that matches a multi-item list rather than a single value. In other words, I'm trying to get SQLAlchemy 0.8.6 connecting to PostgreSQL 9.3.4 to generate SQL like this: select * from mytable mt left outer join myothertable mo on (m0.col1, m0.col2, mo.col3) = (select m2.col1, m2.col2, m2.col3 from myothertable m2 where mt.col1 = m2.wilson_id order by m2.col3 desc limit 1) When I construct my query object back in Python 2.7: mo = aliased(myothertable) m2 = aliased(myothertable) mySession.query(mytable, myothertable)\ .select_from(mytable)\ .outerjoin(mo, [mo.col1, mo.col2, mo.col3] == select(\ [m2.col1, m2.col2, m2.col3])\ .where(m2.col1 == mytable.col1)\ .order_by(m2.col3.desc())\ .limit(1)) I get: File build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py, line 1699, in outerjoin File string, line 1, in lambda File build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py, line 51, in generate File build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py, line 1816, in _join File build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py, line 1846, in _join_left_to_right File build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py, line 1861, in _prepare_right_side File build/bdist.linux-x86_64/egg/sqlalchemy/inspection.py, line 74, in inspect sqlalchemy.exc.NoInspectionAvailable: No inspection system is available for object of type type 'bool' I tried both List type and Tuple type in my comparison from the inner select statement. When I look at the code where this exception is thrown, it suggests that something is wrong with my mapping. I've done this join by comparing a single column with these two tables, and I've done similar things with other single column comparisons. I'm not sure why I'm unable to compare a set of columns instead. I've been pondering casting all three columns to string and then concatenating them (either in the table mapping, or in the query) to see if I can get back to a single value comparision which I expect to work. I'd rather just compare the tuple/lists though. I could use some hints. If you guys have some. Thanks! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com. To post to this group, send email to sqlal...@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to
Re: [sqlalchemy] outerjoin condition matching tuples
there's a way to get the tuple comparison to work here though it's not as common, I'd have to try it out and there might be some quirks to work out. I would think this would be more straightforward using a straight AND: and_(mo.c.col1 == myselect.c.col1, mo.c.col2 == myselect.c.col2, mo.c.col3 == myselect.c.col3) where myselect is like: select([...]).where(...).alias() On Apr 20, 2014, at 9:33 PM, Rick Otten rottenwindf...@gmail.com wrote: So FWIW, for now, I cast all three columns into strings and concatenated them using the column_property() expression in the mapper, and then did the comparison on that. It worked ok to create the join condition I needed. It wasn't a pretty as a tuple comparison but it got the job done and I can hit my deadline for tomorrow morning. On Sunday, April 20, 2014 2:38:21 PM UTC-4, Rick Otten wrote: I would like to apply a condition in an outer join that matches a multi-item list rather than a single value. In other words, I'm trying to get SQLAlchemy 0.8.6 connecting to PostgreSQL 9.3.4 to generate SQL like this: select * from mytable mt left outer join myothertable mo on (m0.col1, m0.col2, mo.col3) = (select m2.col1, m2.col2, m2.col3 from myothertable m2 where mt.col1 = m2.wilson_id order by m2.col3 desc limit 1) When I construct my query object back in Python 2.7: mo = aliased(myothertable) m2 = aliased(myothertable) mySession.query(mytable, myothertable)\ .select_from(mytable)\ .outerjoin(mo, [mo.col1, mo.col2, mo.col3] == select(\ [m2.col1, m2.col2, m2.col3])\ .where(m2.col1 == mytable.col1)\ .order_by(m2.col3.desc())\ .limit(1)) I get: File build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py, line 1699, in outerjoin File string, line 1, in lambda File build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py, line 51, in generate File build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py, line 1816, in _join File build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py, line 1846, in _join_left_to_right File build/bdist.linux-x86_64/egg/sqlalchemy/orm/query.py, line 1861, in _prepare_right_side File build/bdist.linux-x86_64/egg/sqlalchemy/inspection.py, line 74, in inspect sqlalchemy.exc.NoInspectionAvailable: No inspection system is available for object of type type 'bool' I tried both List type and Tuple type in my comparison from the inner select statement. When I look at the code where this exception is thrown, it suggests that something is wrong with my mapping. I've done this join by comparing a single column with these two tables, and I've done similar things with other single column comparisons. I'm not sure why I'm unable to compare a set of columns instead. I've been pondering casting all three columns to string and then concatenating them (either in the table mapping, or in the query) to see if I can get back to a single value comparision which I expect to work. I'd rather just compare the tuple/lists though. I could use some hints. If you guys have some. Thanks! -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] outerjoin where first selected column is a literal dies with confusing error
when you do outerjoin() it will try to join from the first item in the list, unless you do select_from: query(literal(), X.foo, Y.bar).select_from(X).join(Y, ) will see what can be done about the error message On Mar 17, 2014, at 2:58 PM, Ryan Kelly rpkell...@gmail.com wrote: Hi: When I run the attached example, I get the following error: sqlalchemy.exc.NoInspectionAvailable: No inspection system is available for object of type type 'NoneType' Which is a result of literal(1) appearing first in the select list. I don't particularly care than I can't order my columns that way, but the error message cost me about 30 minutes just trying to figure out why. Not sure if there is a good way to fix this or what the correct approach is. -Ryan Kelly -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. t.py -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] outerjoin where first selected column is a literal dies with confusing error
the error message has been improved in 9ec01ab35a / 987759aec51e (0.9 / 0.8) On Mar 17, 2014, at 4:16 PM, Michael Bayer mike...@zzzcomputing.com wrote: when you do outerjoin() it will try to join from the first item in the list, unless you do select_from: query(literal(), X.foo, Y.bar).select_from(X).join(Y, ) will see what can be done about the error message On Mar 17, 2014, at 2:58 PM, Ryan Kelly rpkell...@gmail.com wrote: Hi: When I run the attached example, I get the following error: sqlalchemy.exc.NoInspectionAvailable: No inspection system is available for object of type type 'NoneType' Which is a result of literal(1) appearing first in the select list. I don't particularly care than I can't order my columns that way, but the error message cost me about 30 minutes just trying to figure out why. Not sure if there is a good way to fix this or what the correct approach is. -Ryan Kelly -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. t.py -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] outerjoin
On Jan 26, 2011, at 11:46 AM, Pankaj wrote: Hi, I have this subquery, which yields results sp_HeaderDetails = session.query( InvoiceCashFlowPerDocNum.sequence_id, InvoiceHeaderInfo.invoice_ref, InvoiceHeaderInfo.doc_num ) \ .filter( ( InvoiceCashFlowPerDocNum.doc_num == InvoiceHeaderInfo.doc_num ) ) \ .subquery() I then do a outerjoin on another query cashflows = session.query( CashflowEventDetail, sp_HeaderDetails ) \ .outerjoin( ( sp_HeaderDetails, and_(sp_HeaderDetails.c.sequence_id == CashflowEventDetail.sequence_id ) ) ) This above statement generates the following sql: SELECT cash_flow_event_detail.id FROM cash_flow_event_detail LEFT OUTER JOIN (SELECT invoice_cashflows.sequence_id AS sequence_id, invoice_header_information.invoice_ref AS invoice_ref, invoice_header_information.doc_num AS doc_num FROM invoice_cashflows, invoice_header_information WHERE invoice_cashflows.doc_num = invoice_header_information.doc_num) AS anon_1 ON anon_1.sequence_id = cash_flow_event_detail.sequence_id This doesn't make sense, your query has two different selectables passed to query() so should have at least all the columns from sp_HeaderDetails listed out in the columns clause, unless you're paraphrasing. The SQL is otherwise correct, unless you're on a platform such as Oracle which does not generate the AS keyword. SQLAlchemy does not generate AS when the statement is compiled against such a backend. If this is not the case, would need DB in use, version information, test case. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
