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
[sqlalchemy] outerjoin condition matching tuples
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.
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.
[sqlalchemy] outerjoin where first selected column is a literal dies with confusing error
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. #!/usr/bin/env python from sqlalchemy import create_engine, MetaData, Table, Column, Integer from sqlalchemy.orm import sessionmaker from sqlalchemy.sql.expression import literal engine = create_engine(sqlite:///:memory:, echo=True) session = sessionmaker(bind=engine)() metadata = MetaData() some_table = Table(some_table, metadata, Column(foo, Integer), ) other_table = Table(other_table, metadata, Column(foo, Integer), ) results = ( session.query( literal(1), some_table.c.foo, other_table.c.foo) .outerjoin(other_table, some_table.c.foo == some_table.c.foo) ).all()
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.
[sqlalchemy] outerjoin
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 The above sql doesnt return any values if I run the sql in python. It also generates error when I run it in db artisan generating a missing keyword error. However, if I remove the keyword AS in clause AS anon_1, it succeeds in DbArtisan. Any help would be appreciated -- 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.
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.
[sqlalchemy] outerjoin in mapper
Hi,
I have 3 tables - tbl_item, tbl_item_sku, and tbl_item_lot
Table tbl_item has 2 FKs
my mapper is pretty simple:
mdbItem = mapper(dbItem, tbl_item,
properties={
'sku': relation(dbItemSKU,
uselist=False,
primaryjoin=tbl_item_sku.c.id==tbl_item.c.ITEM_SKU_id,
foreign_keys=[tbl_item.c.ITEM_SKU_id]
),
'lot': relation(dbItemLot,
uselist=False,
primaryjoin=outerjoin(tbl_item_lot,tbl_item,tbl_item.c.ITEM_LOT_id==tbl_item_lot.c.id),
foreign_keys=[tbl_item.c.ITEM_LOT_id])})
as soon as I try access anything from tbl_item_lot
e.g. dbItem.lot.CODE
I am getting:
Unexpected error: class 'sqlalchemy.exc.ProgrammingError'
(ProgrammingError) (1064, You have an error in your SQL syntax; check
the manual that corresponds to your MySQL server version for the right
syntax to use near 'LEFT OUTER JOIN `ITEM` ON 1 = `ITEM_LOT`.id' at
line 3) u'SELECT `ITEM_LOT`.id AS `ITEM_LOT_id`, `ITEM_LOT`.`CODE` AS
`ITEM_LOT_CODE`, `ITEM_LOT`.`SERIAL_NO` AS `ITEM_LOT_SERIAL_NO`,
`ITEM_LOT`.`COMMENT` AS `ITEM_LOT_COMMENT` \nFROM `ITEM_LOT` LEFT
OUTER JOIN `ITEM` ON %s = `ITEM_LOT`.id \nWHERE `ITEM_LOT` LEFT OUTER
JOIN `ITEM` ON %s = `ITEM_LOT`.id' [1L, 1L]
if I just access data from first join then everything is fine.
Another thing is that I have to specify foreign_keys without that I am
getting:
Unexpected error: class 'sqlalchemy.exc.ArgumentError' Could not
determine relation direction for primaryjoin condition '`ITEM_SKU`.id
= `ITEM`.`ITEM_SKU_id`', on relation dbItem.sku. Specify the
'foreign_keys' argument to indicate which columns on the relation are
foreign.
even the relations are setup in the database
CREATE TABLE `ITEM` (
`id` int(10) unsigned NOT NULL auto_increment,
`ITEM_SKU_id` int(10) unsigned NOT NULL,
`ITEM_LOT_id` int(10) unsigned default NULL,
PRIMARY KEY (`id`),
KEY `FK_ITEM_LOT` (`ITEM_LOT_id`),
KEY `FK_ITEM_SKU` (`ITEM_SKU_id`),
CONSTRAINT `FK_ITEM_LOT` FOREIGN KEY (`ITEM_LOT_id`) REFERENCES
`ITEM_LOT` (`id`),
CONSTRAINT `FK_ITEM_SKU` FOREIGN KEY (`ITEM_SKU_id`) REFERENCES
`ITEM_SKU` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8
I guess I need help :-)
regards
-- jacek
--~--~-~--~~~---~--~~
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] Outerjoin on Subselect?
Hello all; I am trying to do a subselect on an Outer join. I have a table of votes that can apply to multiple objects in my system so their is no direct foreign key. A Voteable type implementation. When i load the list of Projects, i want to return the current users votes eagerly if possible, but if they haven't voted still return the project. It is similar to the Association examples, or this example: http://techspot.zzzeek.org/?cat=3 With the difference being that its possible that the associated table won't have any, in which case it still needs to return the parent table, I can't get this accomplished. Currently i have written the SQL by hand, and am loading it into a secondary mapper, but it breaks with Could not find any Tables in the mapped object. I have tried a variety of things before that with less success. Any suggestions would be appreciated. Thank You. Aaron project_table = Table(project, metadata, Column(id, Integer, primary_key=True), Column(title, String(255), nullable=False), Column(createdby_id, Integer), Column(rating_ct, Integer, default=0), ) # ratings rating_table = Table(vote, metadata, Column(id, Integer, primary_key=True), Column(person_id, Integer), Column(entry, Integer), Column(obj_id, Integer), ) # This is the SQL that works by hand SELECT project.id AS project_id, project.title AS project_title, project.createdby_id AS project_createdby_id, project.vote_ct AS project_vote_ct, vote_der.vote_id, vote_der.vote_person_id, vote_der.vote_entry, vote_der.vote_obj_id FROM project LEFT OUTER JOIN ( SELECT vote.id AS vote_id, vote.person_id AS vote_person_id, vote.entry AS vote_entry, vote.obj_id AS vote_obj_id FROM vote where person_id = 24 ) AS vote_der ON project.id = vote_der.vote_obj_id --~--~-~--~~~---~--~~ 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] outerjoin constructor throws exception from 0.3.9. Bug or user error?
Hi,
I have the following two tables (in MySql):
CREATE TABLE `A` (
`xkey` varchar(200) NOT NULL,
`yval` int(11) default NULL,
PRIMARY KEY (`xkey`)
) ENGINE=InnoDB;
and
CREATE TABLE `B` (
`xkey` varchar(200) NOT NULL default '',
`s` enum('yes','no') NOT NULL default 'yes',
PRIMARY KEY (`xkey`,`s`)
) ENGINE=InnoDB;
The following statement worked fine till 0.3.8 , and from 0.3.9 throws
the exception:
oj=outerjoin(A, B, and_(A.c.xkey==B.c.xkey, B.c.s=='yes'))
Traceback (most recent call last):
File join.py, line 13, in ?
oj=outerjoin(A, B, and_(A.c.xkey==B.c.xkey, B.c.s=='yes'))
File /usr/local/lib/python2.4/site-packages/SQLAlchemy-0.3.9-
py2.4.egg/sqlalchemy/sql.py, line 117, in outerjoin
return Join(left, right, onclause, isouter = True, **kwargs)
File /usr/local/lib/python2.4/site-packages/SQLAlchemy-0.3.9-
py2.4.egg/sqlalchemy/sql.py, line 2256, in __init__
self._init_primary_key()
File /usr/local/lib/python2.4/site-packages/SQLAlchemy-0.3.9-
py2.4.egg/sqlalchemy/sql.py, line 2287, in _init_primary_key
if p.references(c) or (c.primary_key and not p.primary_key):
AttributeError: '_BindParamClause' object has no attribute
'primary_key'
Note, if B.s were not an enum but a varchar, it would have worked.
1) Is this a usage error or a bug?
2) If the latter. How can i work around it?
TIA
--~--~-~--~~~---~--~~
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] Outerjoin with a subset of columns
Hi, Note the following code: oj=outerjoin(s,f) r1=select([oj]).execute().fetchall() r2=select([oj.left.c.id, oj.right.c.status]).execute().fetchall() The first select executes a LEFT OUTER JOIN and returns all columns. I wanted only 2 specific columns, so I tried the 2nd select. That does not execute a LEFT OUTER JOIN, though. How do I achieve my goal? TIA --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
