[sqlalchemy] Re: M:N select
I found out the solution by explicitly adding join condition: eq1 = ad_equipment_table.alias() eq2 = ad_equipment_table.alias() e1 = equipment_table.alias() e2 = equipment_table.alias() ad_table.join(eq1, join_condition(ad_table, eq1)).join(e1, join_condition(eq1, e1)).join(eq2, join_condition(ad_table, eq2)).join(e2, join_condition(eq2, e2)) On Wed, Apr 29, 2009 at 7:12 PM, tomas to...@drencak.com wrote: Hi, I've got table Ad which has many to many relation with table Equipment through table ad_equipment. I want to select all ads which have all equipments in the list. Corresponding SQL query will look like this: select * from ad where ad.id in ( select id from ad join ad_equipment eq1 on ad.id = eq1.ad join equipment e1 on eq1.equipment = e1.id join ad_equipment eq2 on ad.id = eq2.ad join equipment e2 on eq2.equipment = e2.id where e1.name = 'name1' and e2.name = 'name2' ) How can I do this kind of select? I've tried something like eq1 = ad_equipment_table.alias() eq2 = ad_equipment_table.alias() e1 = equipment_table.alias() e2 = equipment_table.alias() ad_table.join(eq1).join(e1).join(eq2).join(e2) but this is not I wanted as resulting SQL joins for e1 and e2 are wired together... Is it possible to create this select by SA expression? Or is there a better way how to do that? thanks, tomas --~--~-~--~~~---~--~~ 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] Re: puzzling outerjoin in the mapper
Thanks. I do appreciate that this will become the default as I think that if you ask for an outer join that's what you expect. thanks again for you time sandro --~--~-~--~~~---~--~~ 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] Re: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
Hi Lucas, I don't think #1350 applies here, but just in case, I pass-ed out the mssql dialect do_begin per the suggestion in the discussion thread referenced by that ticket: no impact on the invalid cursor state exception. -Tom Can you read over this ticket and see if maybe you are affected.http://www.sqlalchemy.org/trac/ticket/1350 If not then somebody more familiar with sa would need to look into why these tests are failing. Thanks, Lucas --~--~-~--~~~---~--~~ 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] Re: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
On Thu, Apr 30, 2009 at 8:36 AM, Tom Wood thomas.a.w...@gmail.com wrote: Hi Lucas, I don't think #1350 applies here, but just in case, I pass-ed out the mssql dialect do_begin per the suggestion in the discussion thread referenced by that ticket: no impact on the invalid cursor state exception. I guess at this point you would need to: 1. try doing the insert using plain pyodbc. See if pyodbc inserts the record with no problems. If pyodbc works then create a bug in sqlalchemy. If pyodbc does not work we need to find out why. Thanks, Lucas -Tom Can you read over this ticket and see if maybe you are affected.http://www.sqlalchemy.org/trac/ticket/1350 If not then somebody more familiar with sa would need to look into why these tests are failing. Thanks, Lucas -- How to create python package? http://lucasmanual.com/mywiki/PythonPaste DataHub - create a package that gets, parses, loads, visualizes data http://lucasmanual.com/mywiki/DataHub --~--~-~--~~~---~--~~ 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] best way to force upper case on a column
What's the best way to force upper case on a column? I see the
following in the tutorial/documentation...
class MyClass(Base):
__tablename__ = 'sometable'
name = Column('name', String)
@comparable_using(MyUpperCaseComparator)
@property
def uc_name(self):
return self.name.upper()
Is that the best way? Using decorators? It'd be nice to be able to
specify keywords in the Column() call.
--~--~-~--~~~---~--~~
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] Re: InvalidRequestError: The transaction is inactive due to a rollback... using sqlite with multiple commits.
Hello Michael, Thank you for your replies. I've just tried to create a testcase but proven to myself that it does raise an exception. I'll go back and double check where I might be catching and passing on the exception. Daniel On Apr 29, 3:19 pm, Michael Bayer mike...@zzzcomputing.com wrote: how is it that you know this is due to the SQLite timeout ? did you create a test case ? creating a fully reproducible test case would be the next step. On Apr 29, 2009, at 5:15 PM, Daniel wrote: I'm not catching it or re-raising it. Where else could I look to solve this. On Apr 29, 11:57 am, Michael Bayer mike...@zzzcomputing.com wrote: Daniel wrote: I've learned a bit more. Apparently the sqlite database occasionally gets locked by another process and that lock lasts longer than the five second default timeout. SQL Alchemy quietly issues a ROLLBACK, but doesn't say anything more about it. The result is that the session is no longer active and eventually produces the error I mentioned above. Here are the log messages showing the insert failing after the five second default timeout. 2009-04-28 15:10:48,118 INFO sqlalchemy.engine.base.Engine.0x...ae70 INSERT INTO table (id, value) VALUES (?, ?) 2009-04-28 15:10:48,118 INFO sqlalchemy.engine.base.Engine.0x...ae70 [1, 'my value'] 2009-04-28 15:10:53,148 INFO sqlalchemy.engine.base.Engine.0x...ae70 ROLLBACK Is there some way to configure SQL Alchemy to raise an exception when it issues the rollback, or when it finds the database locked? SQLAlchemy definitely raises an exception if the DBAPI sends one. a ROLLBACK does not occur if there was no exception thrown. check if you're catching it and not re-raising. --~--~-~--~~~---~--~~ 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] Re: best way to force upper case on a column
the @validates decorator will modify values on the way in:
http://www.sqlalchemy.org/docs/05/mappers.html#simple-validators
if you're looking for upper case at the SQL expression level then you
need to build a comparator. but you can send that to column_property:
name = column_property(Column('name', String),
comparator_factory=MyComparator)
On Apr 30, 2009, at 3:38 PM, Sneaky Wombat wrote:
What's the best way to force upper case on a column? I see the
following in the tutorial/documentation...
class MyClass(Base):
__tablename__ = 'sometable'
name = Column('name', String)
@comparable_using(MyUpperCaseComparator)
@property
def uc_name(self):
return self.name.upper()
Is that the best way? Using decorators? It'd be nice to be able to
specify keywords in the Column() call.
--~--~-~--~~~---~--~~
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] Re: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
On Apr 29, 10:08 am, Tom Wood thomas.a.w...@gmail.com wrote: Some additional info, and a possible fix: === --- lib/sqlalchemy/databases/mssql.py (revision 5930) +++ lib/sqlalchemy/databases/mssql.py (working copy) @@ -991,7 +991,7 @@ # We may have to skip over a number of result sets with no data (due to triggers, etc.) while True: try: - row = self.cursor.fetchone() + row = self.cursor.fetchall()[0] break except pyodbc.Error, e: self.cursor.nextset() I.e., calling fetchall() instead of fetchone() seems to clean up the cursor state. This change does not affect any of the tests on Windows. So that's good. I'd like to confirm a couple of other things before we commit. Thanks a lot. Michael --~--~-~--~~~---~--~~ 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] Re: 0.5.3 ORM, MSSQL and FreeTDS: Invalid Cursor State exception?
On Apr 30, 11:04 pm, mtrier mtr...@gmail.com wrote: Some additional info, and a possible fix: === --- lib/sqlalchemy/databases/mssql.py (revision 5930) +++ lib/sqlalchemy/databases/mssql.py (working copy) @@ -991,7 +991,7 @@ # We may have to skip over a number of result sets with no data (due to triggers, etc.) while True: try: - row = self.cursor.fetchone() + row = self.cursor.fetchall()[0] break except pyodbc.Error, e: self.cursor.nextset() I.e., calling fetchall() instead of fetchone() seems to clean up the cursor state. Also, FWIW, the original test passes just fine on Windows and pyodbc. So it's definitely a FreeTDS issue. Michael --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
