[sqlalchemy] InvalidRequestError
Hi! Is there a way to find out about the state an object is in? I am particular interested to find out if there is the need to call session.update() for an object or if the identidy is already contained in the session. I cannot manage to do this, but get an Invalid Request Errror. So the example to get the InvalidRequestErrro goes as follows: session = Session() obj1 = session.query(Advertisment).get(1) session.expunge(obj1) obj2 = session.query(Advertisment).get(1) session.update(obj1) # raises InvalidRequestError I would like to do something like: if not obj1 in session: session.update(obj1) Any help would be appreciated very much. Thanks, Jan --~--~-~--~~~---~--~~ 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] Postgis+sqlalchemy - KeyError: geometry
Hi, I'm trying to update somes values into a postgis layer through sqlalchemy, but, when I connect with the table I receive this message: Traceback (most recent call last): File /Applications/qgis0.9.0.app/Contents/MacOS/share/qgis/python/ plugins/sortpglayer_plugin/pyarchinit_dbconn.py, line 354, in module query.fields_list('poligono') File /Applications/qgis0.9.0.app/Contents/MacOS/share/qgis/python/ plugins/sortpglayer_plugin/pyarchinit_dbconn.py, line 343, in fields_list table = Table(self.table_name, self.metadata, autoload=True) File build/bdist.macosx-10.3-fat/egg/sqlalchemy/schema.py, line 143, in __call__ metadata.get_engine().reflecttable(table) File build/bdist.macosx-10.3-fat/egg/sqlalchemy/engine/base.py, line 505, in reflecttable self.dialect.reflecttable(conn, table) File build/bdist.macosx-10.3-fat/egg/sqlalchemy/databases/ postgres.py, line 385, in reflecttable coltype = ischema_names[attype] KeyError: 'geometry' I'm working on mac os x 10.4, python 2.5, SQLAlchemy-0.3.3, psycopg2-2.0.6, pgsql821+postgis121-1 Somes suggestions??? Thanks a lot! --~--~-~--~~~---~--~~ 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] Re: InvalidRequestError
On Jan 20, 2008, at 10:03 AM, VitaminJ wrote: Hi! Is there a way to find out about the state an object is in? I am particular interested to find out if there is the need to call session.update() for an object or if the identidy is already contained in the session. I cannot manage to do this, but get an Invalid Request Errror. So the example to get the InvalidRequestErrro goes as follows: session = Session() obj1 = session.query(Advertisment).get(1) session.expunge(obj1) obj2 = session.query(Advertisment).get(1) session.update(obj1) # raises InvalidRequestError I would like to do something like: if not obj1 in session: session.update(obj1) Any help would be appreciated very much. Thanks, Jan well actually you *can* do exactly if obj1 not in session: session.update(obj1), but what that does is tell you if obj1 specifically is in the session, not any object with the same identity as obj1. if you want to check for a certain identity, use the identity_map dictionary directly, using session.identity_key(instance=obj1) in session.idenitity_map. But i think what suits your case above even better is merge(); if you wanted to turn obj1 into obj2, and copy all the changes present on obj1 into the session, you could say: obj1 = session.merge(obj1) the merge() call will return the current persistent object if present, else will load it from the database using obj1's identity. In both cases all of obj1's attributes are merged into the persistent instance. --~--~-~--~~~---~--~~ 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] Re: Postgis+sqlalchemy - KeyError: geometry
On 20 Gen, 18:01, Michael Bayer [EMAIL PROTECTED] wrote: On Jan 20, 2008, at 11:57 AM, Mando wrote: I'm working on mac os x 10.4, python 2.5, SQLAlchemy-0.3.3, psycopg2-2.0.6, pgsql821+postgis121-1 upgrade to at least 0.3.11, the unknown column type of geometry will produce a generically-typed column and a warning message. Ok, and thanks a lot --~--~-~--~~~---~--~~ 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] Re: Postgis+sqlalchemy - KeyError: geometry
On Jan 20, 2008, at 11:57 AM, Mando wrote: I'm working on mac os x 10.4, python 2.5, SQLAlchemy-0.3.3, psycopg2-2.0.6, pgsql821+postgis121-1 upgrade to at least 0.3.11, the unknown column type of geometry will produce a generically-typed column and a warning message. --~--~-~--~~~---~--~~ 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] Re: Postgis+sqlalchemy - KeyError: geometry
No, the problem appears also with SQLAlchemy-0.3.11 and SQLAlchemy-0.4.2p3. /Library/Frameworks/Python.framework/Versions/2.5/lib/python2.5/site- packages/SQLAlchemy-0.4.2p3-py2.5.egg/sqlalchemy/databases/postgres.py: 509: RuntimeWarning: Did not recognize type 'geometry' of column 'the_geom' warnings.warn(RuntimeWarning(Did not recognize type '%s' of column '%s' % (attype, name))) /Library/Frameworks/Python.framework/Versions/2.5/lib/python2.5/site- packages/SQLAlchemy-0.3.11-py2.5.egg/sqlalchemy/databases/postgres.py: 457: RuntimeWarning: Did not recognize type 'geometry' of column 'the_geom' warnings.warn(RuntimeWarning(Did not recognize type '%s' of column '%s' % (attype, name))) --~--~-~--~~~---~--~~ 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] Re: InvalidRequestError
Thanks for the great support and all the work! Merge is exactly what I had in mind. The documentation for merge() states what I was looking for: This method is useful for bringing in objects which may have been restored from a serialization, such as those stored in an HTTP session, where the object may be present in the session already Thanks, Jan --~--~-~--~~~---~--~~ 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] Re: Postgis+sqlalchemy - KeyError: geometry
With SQLAlchemy-0.4.2p3 I've solved the problem in this (terrible!!!) way: I've added an 'if' into postgres.py file at line 514 and now work correctly. I maje this beacause I don't know how add a column type into sqlalchemy code: else: if attype != 'geometry': warnings.warn(RuntimeWarning(Did not recognize type '%s' of column '%s' % (attype, name))) coltype = sqltypes.NULLTYPE : ) : ) Bye bye mando --~--~-~--~~~---~--~~ 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] Re: Postgis+sqlalchemy - KeyError: geometry
On Jan 20, 2008, at 12:42 PM, Mando wrote: No, the problem appears also with SQLAlchemy-0.3.11 and SQLAlchemy-0.4.2p3. /Library/Frameworks/Python.framework/Versions/2.5/lib/python2.5/site- packages/SQLAlchemy-0.4.2p3-py2.5.egg/sqlalchemy/databases/ postgres.py: 509: RuntimeWarning: Did not recognize type 'geometry' of column 'the_geom' warnings.warn(RuntimeWarning(Did not recognize type '%s' of column '%s' % (attype, name))) /Library/Frameworks/Python.framework/Versions/2.5/lib/python2.5/site- packages/SQLAlchemy-0.3.11-py2.5.egg/sqlalchemy/databases/postgres.py: 457: RuntimeWarning: Did not recognize type 'geometry' of column 'the_geom' warnings.warn(RuntimeWarning(Did not recognize type '%s' of column '%s' % (attype, name))) its only a warning, that theres no Geometry type available in SA. but your program is safe to continue, assuming you havent changed the warnings filter to raise exceptions. info on the warnings filter: http://www.python.org/doc/2.4.2/lib/warning-filter.html --~--~-~--~~~---~--~~ 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] ORM Join with explicit Alias
Hi All, I wish to do an aliased join similar to the last example in the section http://www.sqlalchemy.org/docs/04/ormtutorial.html#datamapping_joins session.query(User).\ ... join('addresses', aliased=True).filter(Address.email_address=='[EMAIL PROTECTED]').\ ... join('addresses', aliased=True).filter(Address.email_address=='[EMAIL PROTECTED]') Except that I want to provide my own Alias for the Address table so I can compare fields from the two aliased address tables, e.g. Address_1 = Address.table.alias() Address_2 = Address.table.alias() session.query(User).\ ... join(Address_1).\ ... join(Address_2).\ ... filter(Address_1.email_addressAddress_2.email_address) This fails because the 'join' function above expects a property to join on, rather than a table or table alias. I've tried to transform it into a select_from query: User.query.select_from(User.table.join(Address_1).join(Address_2))./ ... filter(Address_1.email_addressAddress_2.email_address) but then you lose the ability to add_entities; the following doesn't work: User.query.add_entity(Address_1).select_from(User.table.join(Address_1).join(Address_2))./ ... filter(Address_1.email_addressAddress_2.email_address) Any ideas on how to do this correctly? Thanks, Eoghan --~--~-~--~~~---~--~~ 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] Unique ID's
Hi Guys, I have field that I want to put a unique identifier in. This unique Id i would like to be a composite key or simply a random number. What do you guys suggest for this, is there a particular method which works well for some of you? Thanks in advance Morgan --~--~-~--~~~---~--~~ 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] Re: ORM Join with explicit Alias
On Jan 20, 2008, at 6:52 PM, Eoghan Murray wrote: Hi All, I wish to do an aliased join similar to the last example in the section http://www.sqlalchemy.org/docs/04/ormtutorial.html#datamapping_joins session.query(User).\ ... join('addresses', aliased=True).filter(Address.email_address=='[EMAIL PROTECTED]').\ ... join('addresses', aliased=True).filter(Address.email_address=='[EMAIL PROTECTED]') Except that I want to provide my own Alias for the Address table so I can compare fields from the two aliased address tables, e.g. Address_1 = Address.table.alias() Address_2 = Address.table.alias() session.query(User).\ ... join(Address_1).\ ... join(Address_2).\ ... filter(Address_1.email_addressAddress_2.email_address) This fails because the 'join' function above expects a property to join on, rather than a table or table alias. I've tried to transform it into a select_from query: User .query.select_from(User.table.join(Address_1).join(Address_2))./ ... filter(Address_1.email_addressAddress_2.email_address) but then you lose the ability to add_entities; the following doesn't work: User .query .add_entity (Address_1 ).select_from(User.table.join(Address_1).join(Address_2))./ ... filter(Address_1.email_addressAddress_2.email_address) Any ideas on how to do this correctly? you're almost there; add_entity has an alias argument: session.query(User).\ select_from(users.join(Address_1).join(Address_2)).\ filter(Address_1.c.email_addressAddress_2.c.email_address).\ add_entity(Address, alias=Address_1) Also, you can't use it here since you are comparing between the two address aliases, but in general you can also create aliases with join() using join('addresses', aliased=True); subsequent filter() criterion using the plain Address.table will be adapted to the alias used in the most recent join. This would allow you to use the Address class as a base for filter criterion also. It also would be quite easy for us to add a helper option here to join(), such that you could say query.join('addresses', with_aliases=[Address_1])...I was thinking of that just today. (with_aliases is a list to support query.join(['foo','bar', 'bat'])) --~--~-~--~~~---~--~~ 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] Re: ORM Join with explicit Alias
On Jan 21, 12:12 am, Michael Bayer [EMAIL PROTECTED] wrote: On Jan 20, 2008, at 6:52 PM, Eoghan Murray wrote: Hi All, I wish to do an aliased join similar to the last example in the sectionhttp://www.sqlalchemy.org/docs/04/ormtutorial.html#datamapping_joins session.query(User).\ ... join('addresses', aliased=True).filter(Address.email_address=='[EMAIL PROTECTED]').\ ... join('addresses', aliased=True).filter(Address.email_address=='[EMAIL PROTECTED]') Except that I want to provide my own Alias for the Address table so I can compare fields from the two aliased address tables, e.g. Address_1 = Address.table.alias() Address_2 = Address.table.alias() session.query(User).\ ... join(Address_1).\ ... join(Address_2).\ ... filter(Address_1.email_addressAddress_2.email_address) This fails because the 'join' function above expects a property to join on, rather than a table or table alias. I've tried to transform it into a select_from query: User .query.select_from(User.table.join(Address_1).join(Address_2))./ ... filter(Address_1.email_addressAddress_2.email_address) but then you lose the ability to add_entities; the following doesn't work: User .query .add_entity (Address_1 ).select_from(User.table.join(Address_1).join(Address_2))./ ... filter(Address_1.email_addressAddress_2.email_address) Any ideas on how to do this correctly? you're almost there; add_entity has an alias argument: session.query(User).\ select_from(users.join(Address_1).join(Address_2)).\ filter(Address_1.c.email_addressAddress_2.c.email_address).\ add_entity(Address, alias=Address_1) Yes, this works correctly for me now, thanks! Also, you can't use it here since you are comparing between the two address aliases, but in general you can also create aliases with join() using join('addresses', aliased=True); subsequent filter() criterion using the plain Address.table will be adapted to the alias used in the most recent join. This would allow you to use the Address class as a base for filter criterion also. It also would be quite easy for us to add a helper option here to join(), such that you could say query.join('addresses', with_aliases=[Address_1])...I was thinking of that just today. (with_aliases is a list to support query.join(['foo','bar', 'bat'])) Yes, this would be nice.. Another option to give access to the aliases, (off the top of my head): Address.aliased_columns[0].email_address Address.aliased_columns[1].email_address Eoghan --~--~-~--~~~---~--~~ 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] Re: Unique ID's
Am Montag, 21. Januar 2008 01:16 schrieb Morgan: Hi Guys, I have field that I want to put a unique identifier in. This unique Id i would like to be a composite key or simply a random number. What do you guys suggest for this, is there a particular method which works well for some of you? That's a good question, I asked myself some weeks ago, here's how I solved this: In my case, I have database records that have sequential numbers as primary keys. These keys can be calculated by the database and are unique by design (as the primary index is unique). This record should hold another field, which should be also unique and in a form of a 8-digit number. However, I'd rather not want this number to be sequential, it should look random. The first way would have been to simple generate a number via random.randint(), look into the database, if it's already in and if not, insert it. However, to guarantee that the number is unique, one should create a unique index on this column. In case the number is already there, the database will raise an error, which has to be catched by the application. Another way would be to lock the table after the select, so that the rare case, that another application instance inserts the same number after my select, is avoided. So, the algorithm could look like this (in pseudo code): # Variant 1 with exception handling while 1: num = random.randint() try: insert into db_table (col1, col2, col_num, col3, ) % num except UniqueNum_IndexViolated: continue else: break # Variant 2 with locking while 1: num = random.randint() lock db_table result = select * from db_table where col_num = num if result: continue else: insert into db_table (col1, col2, col_num, col3, ) % num unlock db_table continue My problem with variant (1) was that I could not find out how to lock a whole table with SQLAlchemy, moreover, each insert needs a table lock and a select, which is bad performance-wise. The problem with (2) was that I did not know how to catch this specific exception, as I can't simply except any database error but this specific index violation (which may be different on different databases). My third idea, which I use now, is to calculate my random number out of my sequential, unique primary index, which is generated by the database during the insert. One helpful guy from #sqlalchemy helped me out with the randomization of the sequential number with this algorithm: def mk_arb_seq(id): Return an arbitrary number. This number is calculated out of the given id. For that, it is multiplied by the large prime numberA. Then a modulo operation with prime M where M A. If A is chosen as a non-prime, the sequence is not very arbitrary, therefore a prime is recommended. M = 9989 A = 2760727302517 return str((A*id) % M).zfill(len(str(M))) The last problem with this is that I have no real mathematical proof for that algorithm, that id never maps to one number more than once. However, I simply tested this with a little program and it seems to work. If you use the ORM, don't forget to do a session.flush() after adding the object to the session, as this will calculate the primary index. Then you can simply set col_num = mk_arb_seq(primary_index). Best Regards, Hermann -- [EMAIL PROTECTED] GPG key ID: 299893C7 (on keyservers) FP: 0124 2584 8809 EF2A DBF9 4902 64B4 D16B 2998 93C7 --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---