[sqlalchemy] Re: Feature request: Session.get_local()
A patch containing tests and cleaned up identity key is attached. ~ Daniel Michael Bayer wrote: committed, r2409. the row needs to have a class and entity_name present to determine what mapper you want to use to extract from the row, so i put those as keyword arguments for now. also, I notice the usage of plain old assert for argument checking. should we make this change across the board and get rid of exceptions.ArgumentError ? i feel like we have to go one way or the other with that. also i didnt do any testing of this, we might want to add some tests to test/orm/session.py . On Mar 12, 2007, at 9:21 PM, Daniel Miller wrote: def identity_key(self, *args, **kwargs): Get an identity key Valid call signatures: identity_key(class_, ident, entity_name=None) class_ - mapped class ident - primary key, if the key is composite this is a tuple entity_name - optional entity name. May be given as a positional arg or as a keyword arg. identity_key(instance=instance) instance - object instance (must be given as a keyword arg) identity_key(row=row) row - result proxy row (must be given as a keyword arg) if args: kw = {} if len(args) == 2: class_, ident = args entity_name = kwargs.pop(entity_name, None) assert not kwargs, (unknown keyword arguments: %s % (kwargs.keys(),)) else: assert len(args) == 3, (two or three positional args are accepted, got %s % len(args)) class_, ident, entity_name = args mapper = _class_mapper(class_, entity_name=entity_name) return mapper.instance_key_from_primary_key(ident, entity_name=entity_name) else: try: instance = kwargs.pop(instance) except KeyError: row = kwargs.pop(row) assert not kwargs, (unknown keyword arguments: %s % (kwargs.keys(),)) mapper = # not sure how to get the mapper form a row return mapper.identity_key_from_row(row) else: assert not kwargs, (unknown keyword arguments: %s % (kwargs.keys(),)) mapper = _object_mapper(instance) return mapper.identity_key_from_instance(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 -~--~~~~--~~--~--~--- Index: lib/sqlalchemy/orm/session.py === --- lib/sqlalchemy/orm/session.py (revision 2432) +++ lib/sqlalchemy/orm/session.py (working copy) @@ -452,54 +452,57 @@ identity_key(class\_, ident, entity_name=None) class\_ -mapped class - +mapped class (must be a positional argument) + ident primary key, if the key is composite this is a tuple entity_name -optional entity name. May be given as a -positional arg or as a keyword arg. +optional entity name identity_key(instance=instance) instance object instance (must be given as a keyword arg) -identity_key(row=row, class=class\_, entity_name=None) +identity_key(class\_, row=row, entity_name=None) +class\_ +mapped class (must be a positional argument) + row result proxy row (must be given as a keyword arg) - + +entity_name +optional entity name (must be given as a keyword arg) if args: -kw = {} -if len(args) == 2: +if len(args) == 1: +class_ = args[0] +try: +row = kwargs.pop(row) +except KeyError: +ident = kwargs.pop(ident) +entity_name = kwargs.pop(entity_name, None) +elif len(args) == 2: class_, ident = args entity_name = kwargs.pop(entity_name, None) -assert not kwargs, (unknown keyword arguments: %s -% (kwargs.keys(),)) +elif len(args) == 3: +class_, ident, entity_name = args else: -assert len(args) == 3, (two or three positional args
[sqlalchemy] Re: Feature request: Session.get_local()
Michael Bayer wrote: id rather not have people needing to deal with an actual identity key tuple most of the time. they should be able to say session.identity_map.something(class, pk) and get an instance out of it. What's the use of exposing the identity map if you don't want people to deal with the keys with which it's indexed? You might as well expose an identity_set, except that would be really unhandy as it would be expensive or impossible to do the exact kind of find that I'm asking for. the reason for those big names on mapper is because all of them are used, we need identity keys from instances, rows, primary keys, all of it, and i was myself getting confused since their names were not clear...so i changed the names to be absolutely clear. but also, i dont want people generally dealing with the methods off of Mappers...all the methods you need should be off of Session and Query. so maybe we can put a keyword-oriented identity_key method on Session, or something (identity_key(primary_key=None, instance=None, row=None, etc)). or maybe it can use some kind of multiple-dispatch that detects scalar, tuple, object instance, ResultProxy. but also, i dont think the get an SA identity key step as an interim step to accomplishing something else should really be needed in the usual case. If the session got an identity_key() method that would be great (and eliminate the need for a find method). Here's an implementation: def identity_key(self, *args, **kwargs): Get an identity key Valid call signatures: identity_key(class_, ident, entity_name=None) class_ - mapped class ident - primary key, if the key is composite this is a tuple entity_name - optional entity name. May be given as a positional arg or as a keyword arg. identity_key(instance=instance) instance - object instance (must be given as a keyword arg) identity_key(row=row) row - result proxy row (must be given as a keyword arg) if args: kw = {} if len(args) == 2: class_, ident = args entity_name = kwargs.pop(entity_name, None) assert not kwargs, (unknown keyword arguments: %s % (kwargs.keys(),)) else: assert len(args) == 3, (two or three positional args are accepted, got %s % len(args)) class_, ident, entity_name = args mapper = _class_mapper(class_, entity_name=entity_name) return mapper.instance_key_from_primary_key(ident, entity_name=entity_name) else: try: instance = kwargs.pop(instance) except KeyError: row = kwargs.pop(row) assert not kwargs, (unknown keyword arguments: %s % (kwargs.keys(),)) mapper = # not sure how to get the mapper form a row return mapper.identity_key_from_row(row) else: assert not kwargs, (unknown keyword arguments: %s % (kwargs.keys(),)) mapper = _object_mapper(instance) return mapper.identity_key_from_instance(instance) Note that I didn't implement the part to get a mapper from a row because I'm not sure how to do that. I imagine that's trivial though. ~ Daniel On Mar 10, 8:27 pm, Daniel Miller [EMAIL PROTECTED] wrote: Michael Bayer wrote: my only concern is that you now have more than one way to do it. i need to deal with things in the identity map. do i go look at the session.identity_map ? (which is documented, its part of the public API) oh no, i dont have the exact kind of key to use, now i have to go use a method called find() (which again, does that mean, find it in the database ? where is it looking ?) These are good points. Maybe the problem is in my brain--I've always had a disconnect between the session.identity_map and the mapper.identity_key() function. I guess it's clearly documented that they are compatible and can be used like this: key = MyClass.mapper.identity_key(pk_value) itm = session.identity_map.get(key) It just seemed like that was digging a bit too deep into what I thought were implementation details of the mapper and the session. If those things (i.e. mapper.identity_key and session.identity_map) are clearly documented as part of the interface of SA, and they are meant to work together like that then maybe this proposal isn't even necessary. After all, it's just two lines instead of one. However, upon looking at the documentation, this is all I find on the identity_key method of the Mapper class: def identity_key(self, primary_key) deprecated. a synonym for identity_key_from_primary_key. Now I thought identity_key was OK (if a bit obscure due to lack of documentation), but identity_key_from_primary_key is not so great IMHO. This is not a method name that will come to mind when I'm trying to get the identity key of a given
[sqlalchemy] Re: Feature request: Session.get_local()
Michael Bayer wrote: my only concern is that you now have more than one way to do it. i need to deal with things in the identity map. do i go look at the session.identity_map ? (which is documented, its part of the public API) oh no, i dont have the exact kind of key to use, now i have to go use a method called find() (which again, does that mean, find it in the database ? where is it looking ?) These are good points. Maybe the problem is in my brain--I've always had a disconnect between the session.identity_map and the mapper.identity_key() function. I guess it's clearly documented that they are compatible and can be used like this: key = MyClass.mapper.identity_key(pk_value) itm = session.identity_map.get(key) It just seemed like that was digging a bit too deep into what I thought were implementation details of the mapper and the session. If those things (i.e. mapper.identity_key and session.identity_map) are clearly documented as part of the interface of SA, and they are meant to work together like that then maybe this proposal isn't even necessary. After all, it's just two lines instead of one. However, upon looking at the documentation, this is all I find on the identity_key method of the Mapper class: def identity_key(self, primary_key) deprecated. a synonym for identity_key_from_primary_key. Now I thought identity_key was OK (if a bit obscure due to lack of documentation), but identity_key_from_primary_key is not so great IMHO. This is not a method name that will come to mind when I'm trying to get the identity key of a given object. It's just too long. Would it be OK to un-deprecate identity_key and just state clearly in the documentation that it requires a primary key as it's argument? Change it like this: def identity_key(self, pk=None, instance=None) Return the identity key given a primary key OR an instance Either the pk or instance keyword argument must be supplied. An error will be raised if both instance and pk are given or if both are None. Note that this is backward-compatible with the previous version of identity_key, which took a primary key as its first argument. Then do this: identity_key_from_primary_key - deprecated identity_key_from_instance - deprecated Finally, we also need to clearly document in the section that talks about the identity_map that the keys used in that map may be obtained directly from the mapper of the object by using mapper.identity_key(). If those things were cleared up I would see no reason why we need a session.get_local() or session.find() or whatever... And we have one clear way to do it. What do you think of this? ~ Daniel --~--~-~--~~~---~--~~ 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: postgres and server_side_cursors
Upon reviewing my code this morning it appears that I forgot to fix the scalar method. Updated patch attached. ~ Daniel --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- server_side_cursors.patch Description: Binary data On Jan 23, 2007, at 10:06 PM, Daniel Miller wrote: I think I was the original person who had this problem. I found a bit of time to troubleshoot it and came up with a patch. The problem seems to be in ResultProxy when it does the metadata = cursor.description bit. cursor.description is returning None because the cursor has not had any rows fetched yet. If I do a cursor.fetchone () then cursor.description returns the expected result. So it looks like the solution here is to defer the metadata translation (construction of ResultProxy.props) until after some data has been fetched from the cursor. Patch attached (works for me, but not heavily tested). ~ Daniel --~--~-~--~~~---~--~~ 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- [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/ group/sqlalchemy?hl=en -~--~~~~--~~--~--~--- server_side_cursors.patch On Jan 23, 2007, at 11:22 AM, Michael Bayer wrote: ive heard of this one already, which is why the option is turned off. unfortunately I cant reproduce that here, so until someone wants to figure out whats going on with that, not sure what I can do. we do have a ticket where someone commented that the server side cursor will cause things like rowcount to not function until the first row is fetched. I also do not observe that behavior on my system...asked the guy to post what cases cause that, big surprise, no response. its like our trac system is an oracle of shadowy myths and rumors. so if anyone wants to wrestle with the psycopg2 guys on this, and/or figure out what the issue is, they seem to be generally not excited by server side cursors in the first place, in favor of just using LIMIT on your SQL so that there is little advantage to the server side approach. from my point of view its not a critical issue since its true, you can just use LIMIT as appropriate. --~--~-~--~~~---~--~~ 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- [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/ group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: postgres and server_side_cursors
I think I was the original person who had this problem. I found a bit of time to troubleshoot it and came up with a patch. The problem seems to be in ResultProxy when it does the metadata = cursor.description bit. cursor.description is returning None because the cursor has not had any rows fetched yet. If I do a cursor.fetchone () then cursor.description returns the expected result. So it looks like the solution here is to defer the metadata translation (construction of ResultProxy.props) until after some data has been fetched from the cursor. Patch attached (works for me, but not heavily tested). ~ Daniel --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~--- server_side_cursors.patch Description: Binary data On Jan 23, 2007, at 11:22 AM, Michael Bayer wrote: ive heard of this one already, which is why the option is turned off. unfortunately I cant reproduce that here, so until someone wants to figure out whats going on with that, not sure what I can do. we do have a ticket where someone commented that the server side cursor will cause things like rowcount to not function until the first row is fetched. I also do not observe that behavior on my system...asked the guy to post what cases cause that, big surprise, no response. its like our trac system is an oracle of shadowy myths and rumors. so if anyone wants to wrestle with the psycopg2 guys on this, and/or figure out what the issue is, they seem to be generally not excited by server side cursors in the first place, in favor of just using LIMIT on your SQL so that there is little advantage to the server side approach. from my point of view its not a critical issue since its true, you can just use LIMIT as appropriate. --~--~-~--~~~---~--~~ 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- [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/ group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: new docs new docs new docs
Ladies and Gentlemen: I present to you Michael Bayer, the amazing multitalented developer of SQLAlchemy--getting more done in one week than the average team of developers does in a month!! Not only is he a very fast coder, but he writes comprehensive documentation for his code--an unheard-of combination of willingness and ability in an open-source developer. Seriously, thanks for the excellent library and documentation Mike. It's truly excellent. ~ Daniel On Jan 20, 2007, at 5:40 PM, Michael Bayer wrote: hey list - trying to wrap up as much as i can for the next release, just wanted to highlight some new doc sections and improvements, since i know everyone just loves my docs and their various typos, careless flubs, and great endorsement of the insurance industry: SQLAlchemy is Two Libraries in One http://www.sqlalchemy.org/docs/tutorial.myt#tutorial_twoinone Configuring Logging http://www.sqlalchemy.org/docs/dbengine.myt#dbengine_logging Creating Joins Using selectby() - added a note about the new feature (in the trunk until 0.3.4) which lets you select_by(somerelation=someinstance) http://www.sqlalchemy.org/docs/ datamapping.myt#datamapping_selectrelations_relselectby Working with Large Collections http://www.sqlalchemy.org/docs/ adv_datamapping.myt#advdatamapping_properties_working Mapper Options - complete and alphabetical http://www.sqlalchemy.org/docs/ adv_datamapping.myt#advdatamapping_mapperoptions Relation Options - complete and alphabetical http://www.sqlalchemy.org/docs/ adv_datamapping.myt#advdatamapping_properties_relationoptions Combining Eager Loads with Result Set Mappings http://www.sqlalchemy.org/docs/ adv_datamapping.myt#advdatamapping_resultset_combining --~--~-~--~~~---~--~~ 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: Regression between r2168 and HEAD
Yeah, it seems to be a problem with the server-side cursors. It works fine when I set client_side_cursors=True. After debugging it a bit it seems to be a problem in ResultProxy/RowProxy. I'm using psycopg 2.0.5.1 and PostgreSQL 8.1.5. Sorry I haven't had time to make a full test case, I'm terribly busy with other stuff right now. ~ Daniel On Jan 11, 2007, at 12:52 PM, Michael Bayer wrote: if you could give me a full reproduction case I can see if i get the same result here. also, to test if its the PG cursor change, try sending client_side_cursors=True to your create_engine() statement, which will make it use cursors in the default manner. On Jan 11, 2007, at 8:58 AM, Daniel Miller wrote: import sqlalchemy as sa class Version(object): table = sa.Table(version, meta, sa.Column(id, mu.String, primary_key=True), sa.Column(number, mu.String), ) def assign_mapper(class_, *args, **kw): class_.mapper = sa.mapper(class_, class_.table, *args, **kw) assign_mapper(Version) def verify(version, session=None): Verify the connection and the version of the database if session is None: session = sa.create_session() ver = session.query(Version).get('schema') if ver.number != version: raise WrongDatabaseVersionError(got %r expected %r % (ver.number, version)) --~--~-~--~~~---~--~~ 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] Regression between r2168 and HEAD
After updating to HEAD this morning I got an error. This code has worked with many versions of SA and I've never seen anything like this error before. Here's the relevant code (modified from its original version to fit your screen): import sqlalchemy as sa class Version(object): table = sa.Table(version, meta, sa.Column(id, mu.String, primary_key=True), sa.Column(number, mu.String), ) def assign_mapper(class_, *args, **kw): class_.mapper = sa.mapper(class_, class_.table, *args, **kw) assign_mapper(Version) def verify(version, session=None): Verify the connection and the version of the database if session is None: session = sa.create_session() ver = session.query(Version).get('schema') if ver.number != version: raise WrongDatabaseVersionError(got %r expected %r % (ver.number, version)) And here's the error: Traceback (most recent call last): File /Users/daniel/Code/PyOE/src/orderentry/app.py, line 34, in __init__ self.dbConnect() File /Users/daniel/Code/PyOE/src/orderentry/app.py, line 81, in dbConnect model.verify(__version__) File /Users/daniel/Code/PyOE/src/orderentry/model.py, line 33, in verify ver = session.query(Version).get('schema') File /Users/daniel/Code/SQLAlchemy/lib/sqlalchemy/orm/query.py, line 59, in get return self._get(key, ident, **kwargs) File /Users/daniel/Code/SQLAlchemy/lib/sqlalchemy/orm/query.py, line 372, in _get return self._select_statement(statement, params=params, populate_existing=reload, version_check=(lockmode is not None))[0] File /Users/daniel/Code/SQLAlchemy/lib/sqlalchemy/orm/query.py, line 380, in _select_statement return self.execute(statement, params=params, **kwargs) File /Users/daniel/Code/SQLAlchemy/lib/sqlalchemy/orm/query.py, line 312, in execute return self.instances(result, **kwargs) File /Users/daniel/Code/SQLAlchemy/lib/sqlalchemy/orm/query.py, line 331, in instances self.mapper._instance(context, row, result) File /Users/daniel/Code/SQLAlchemy/lib/sqlalchemy/orm/mapper.py, line 1159, in _instance identitykey = self.identity_key_from_row(row) File /Users/daniel/Code/SQLAlchemy/lib/sqlalchemy/orm/mapper.py, line 750, in identity_key_from_row return (self.class_, tuple([row[column] for column in self.pks_by_table[self.mapped_table]]), self.entity_name) File /Users/daniel/Code/SQLAlchemy/lib/sqlalchemy/engine/ base.py, line 741, in __getitem__ return self.__parent._get_col(self.__row, key) File /Users/daniel/Code/SQLAlchemy/lib/sqlalchemy/engine/ base.py, line 634, in _get_col rec = self._convert_key(key) File /Users/daniel/Code/SQLAlchemy/lib/sqlalchemy/engine/ base.py, line 618, in _convert_key raise exceptions.NoSuchColumnError(Could not locate column in row for column '%s' % str(key)) NoSuchColumnError: Could not locate column in row for column 'version.id' Any idea what's going on here? Thanks. ~ Daniel --~--~-~--~~~---~--~~ 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: [Sqlalchemy-users] named cursor
Hi Mike, I've been lurking here in the shadows for a while...this particular topic looks very interesting to me and I'd love to upgrade to HEAD to try it out. However, I'm wondering what the risk factor is in doing that? Have there been any other major (potentially destabilizing) changes lately? I'm using r2168 right now, and it's been very stable. I've upgraded to HEAD quite a few times over the past year and I've always been impressed with (1) the overall stability of SA and (2) speed with which bugs were fixed when they are found. I'm just asking for a general idea of how stable you feel the trunk is right now. Don't worry, I'll take responsibility for my actions and will in no way hold you responsible for anything that may occur if I decide to upgrade right now--just looking for a general indicator. Thanks. ~ Daniel Michael Bayer wrote: server-side cursors (i.e. named) for all postgres functionality by default is currently in the trunk, and you can turn it off by saying client_side_cursors=True in create_engine(). however one user reported that table reflection breaks, which I cannot reproduce. so can some folks please try out the trunk with postgres and see if we can go with server side cursors by default? ive run the unit tests many times with both settings and i dont perceieve any performance hit from using server side cursors. id like this to be in the next release, but if theres some issue using server side cursors in all cases, then i have to break open the code and make it a lot more complex to detect the conditions where server-side cursors are appropriate. --~--~-~--~~~---~--~~ 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] PickleType with custom pickler
I just looked at SA's PickleType and came up with a couple of issues. 1. dumps/loads It looks like you can provide a custom pickler to PickleType, but the Python Pickler class does not have dumps()/loads() methods. Those methods are only available at the module level. This is a minor issue since it's not that hard to implement a wrapper for pickler/unpickler that supports dumps/loads. However, it may be a good idea to note this issue in the documentation. 2. persistent_id/persistent_load I need to supply a custom pickler that will use persistent_id() and persistent_load(). These pickle extensions are natural requirements in a database environment. They allow objects that will be pickled to hold references to persistent objects and have those links automatically preserved across pickle/unpickle without actually pickling the persistent objects. However, there is no easy way to use these methods with SQLAlchemy--I'm referring specifically to the orm package here. Here's a bit of (untested) code to illustrate: from cStringIO import StringIO from cPickle import Pickle, Unpickle class MyPickler(object): def __init__(self, session, typeMap): self.session = session self.typeMap = typeMap # map class names to types def persistent_id(self, obj): if hasattr(obj, id): # only mapped objects have an id return %s:%s % (type(obj).__name__, obj.id) return None def persistent_load(self, key): name, ident = key.split(:) class_ = self.typeMap[name] return self.session.query(class_).get(ident) def dumps(self, graph): src = StringIO() pickler = Pickler(src) pickler.persistent_id = self.persistent_id pickler.dump(graph) return src.getvalue() def loads(self, data): dst = StringIO(data) unpickler = Unpickler(dst) unpickler.persistent_load = self.persistent_load return unpickler.load() ... t = Table(... Column(pdata, PickleType(pickler=MyPickler(.?.))) Now the obvious flaw here is that MyPickler needs a session at instantiation time, and it uses the same session for every unpickle throughout the entire application. From what I can tell PickleType has no way of getting at the session of the current load/save taking place when the data is selected from/written to the database. I'm not using thread-local sessions, so that won't work, however there are multiple concurrent sessions within my application. My other thought was to use a mapper extension to unpickle on populate_instance and pickle on before_insert/before_update. The session is easier to get there, and I might have been able to hack it somehow, but I had no way to tell the mapper to perform an update if the only thing that changed was the pickle data. Am I missing something? Is there any way to do what I'm trying to do? ~ Daniel --~--~-~--~~~---~--~~ 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] SA 0.3.x: performance issues
Michael Bayer wrote: you need to forward the *actual test program* which you are running. i have tests for performance which generally show very minute speed differences between 0.2 and 0.3. The logging statements can be sped up by making them conditional and maybe removing some. I've noticed that SA is doing a lot of string formatting in logging statements like this: log.debug('%s %s' % ('green', 'apples')) log.debug(( + self.abc + | + self.xyz + ) + msg) The logging package is designed to allow string formatting to be deferred until the log statement is actually written to a log (so it only happens when logging is enabled). Here's how you'd take advantage of that: log.debug('%s %s', 'green', 'apples') log.debug((%s|%s) %s, self.abc, self.xyz, msg) Also, you need to stop writing Perl in Python :) The mapper logging is quite inefficient. Here's a quick example of how that could be improved: class MapperLoggingExample(object): def __init__(self): # ... if logging.is_debug_enabled(self.logger): name = [(, self.class_.__name__, |] if self.entity_name is not None: name.extend([/, self.entity_name]) if self.local_table: name.append(self.local_table.name) else: name.append(self.local_table) if not self._is_primary_mapper(): name.append(|non-primary) name.append() %s) logging_name = .join(name) self.log_fast = lambda msg: self.logger.debug(logging_name, msg) else: self.log_fast = lambda msg: None def log_slow(self, msg): self.logger.debug(( + self.class_.__name__ + | + (self.entity_name is not None and /%s % self.entity_name or ) + (self.local_table and self.local_table.name or str(self.local_table)) + (not self._is_primary_mapper() and |non-primary or ) + ) + msg) # usage example m = MapperLoggingExample() m.log_slow(test message) m.log_fast(test message) According to my tests, log_fast() is about 50 times faster than log_slow() when logging is disabled, and marginally faster when logging is enabled. ~ Daniel --~--~-~--~~~---~--~~ 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: SA 0.3.x: performance issues
Michael Bayer wrote: also, I challenge your assertion that saying x and y or z is a perlish thing (its a C and Java thing if anything); python 2.5 has just added the y if x else z syntax which is essentially an official version of the same thing. Well, I wasn't really talking about 'x and y or z'. I was actually referring to your HUGE incomprehensible one-liner...it wrapped to three lines in my editor. However, the 'x and y or z' idiom is also discouraged because it is NOT the same thing as 'y if x else z'. If it was the same thing then they wouldn't have added that new syntax (which is really ugly IMO, but I digress) to 2.5. The reason they needed a new syntax is because the 'x and y or z' idiom fails if y evaluates to false. Example: x = True y = '' z = 'else' v = x and y or z assert v == y # ERROR! ~ Daniel --~--~-~--~~~---~--~~ 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: Cascade performance
Michael Bayer wrote: i doubt this was any faster in previous releases since the basic metholodgy of cascade hasnt changed Probably wasn't, I've just been testing with larger data sets lately. so ive added your test with an extra assertion that the session in fact contains 611 instances to the test/perf directory, and added an extra argument to the cascade functions called halt_on which indicates to stop cascading if a condition is met; session sets sends the condition as c in self so that cascading along save/update/save-update will cease along a branch if the instance is detected to be in the session already (i.e. assumes all of its child instances are handled). thats rev 2116 and the results are now: Create forward associations ... Created 610 objects in 0.62538 sec Create backward associations ... Created 610 objects in 0.52296 sec Thanks a million Mike! Works like a charm. It's interesting that it's now (slightly) faster to add them the backward way than it is to add them the forward way. I double-checked the results and I get the same behavior on my machine. Is there more room for optimization maybe? ~ Daniel --~--~-~--~~~---~--~~ 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: Cascade performance
Daniel Miller wrote: Lately I've been noticing severe slowness when instantiating new SA objects... Oh yeah, I forgot to mention that many of my class constructors take a parent object as one of their arguments, which explains the slow instantiation. cascade_test.py demonstrates that the problem is not happening during instantiation, but rather when setting the parent attribute on the child object. ~ Daniel --~--~-~--~~~---~--~~ 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: Constructing where-clauses dynamically
BTW, this post started out as a reply to James but grew into a collective summary of my thoughts on the entire thread so far. You've been warned :) James Taylor wrote: But wouldn't the join from orders to items be OUTER in this case since you can have orders with a total 50 but no items. (Your application logic probably forbids this, but it is allowed at the relational level). Gets complicated quick! Yes, that's one of the things I started to dislike as I got more into the details. The 'obj1.obj2.obj3' join syntax does not imply the type of join (i.e. inner, outer, cross, etc.). My initial thought is to say that this type of join syntax would always use inner joins, which is easy enough to remember. One thing I'm not quite clear on is how the join type is specified with the other join syntaxes in SA. There's join_to/join_via and Mike talked about from_obj=[users.join(ordertable).join(...)], but both of those are just as ambiguous as obj1.obj2.obj3 when it comes to the join type (and they're a lot more verbose at that). I guess SelectResults has outerjoin_to()...more on that later. Here is a more complete join syntax proposal: INNER JOIN (join path syntax sugar): User.c.orders User.c.orders.items INNER JOIN (non-sugar): join(User.c.orders) join(User.c.orders.items) join(User, Order, User.c.user_id Order.c.user_id) OUTER JOIN: outerjoin(User.c.orders) outerjoin(User.c.orders.items) outerjoin(User, Order, User.c.user_id Order.c.user_id) RANDOM JOIN: random_join(User.c.orders) Just kidding on that last one :P Each join function requires one or three positional arguments. The single-arg version takes a join path as it's argument, and performs the join type represented by function on all joins along the path. A stand-alone join path is syntax sugar for join(join path). The three-arg version takes two join-able objects and a condition on which to join them. I realize that a two-arg version is also available where SA tries to find a relationship between the first two args, and IMHO that should be deprecated because it is error-prone. To do a join based on a mapper relationship, use a join path. To do an ad-hoc join use the three-argument version. In addition to join paths and classes, the 3-arg version should also accept tables, strings (entity names), select clauses, and other join objects as arguments. When using anything other than a join path as the first argument, three arguments are required. So this: outerjoin( join(User.c.orders), Items, Order.c.order_id == Item.c.order_id ) and would generate this: FROM users INNER JOIN orders ON users.user_id = orders.user_id LEFT OUTER JOIN items ON orders.order_id = items.order_id An optional 'alias' keyword argument may also be specified in a join function. If the single-arg version is used then the alias applies to the last table in the join path. Otherwise the alias applies to the second argument. join(User.c.orders, alias='ox') join( User, Order, User.c.user_id == Order.c.user_id, alias='ox' ) both of these expressions generate the following (pseudo) SQL: FROM users LEFT OUTER JOIN orders ox ON users.user_id = ox.user_id Possible alternative syntax: User.c.join(Order) The biggest problem with this alternative is that it clutters the User.c namespace. Like we were saying earlier: One thing we might want to change is the 'alias' function. Since the 'orders' table may have a column named 'alias', it might be better to make it a stand-alone function like this: alias(c.orders, 'ox') Mike wrote: i think this is the problem when you start munging namespaces together, as opposed to grouping them under explicit prefixes (i.e. 'c'). But 'c' is actually a conglomeration of namespaces including columns, functions, operations, etc. 'c' should only contain columns and relationships and everything else should be moved to a separate package (maybe sqlalchemy.query or even the root sqlalchemy package). I also think something similar to the SelectResults transformative style of creating joins would be useful if it was built-in to Query (with a few name changes such as 'join_to' should be 'join' and 'outerjoin_to' should be 'outer_join'). One thing I don't like about SelectResults is that it gives special attention to a few functions (avg, sum, min, max, etc.). These functions can incorporated into a query with the more versatile 'func.xyz' syntax, and would just add to the confusion about how to use functions. Anyway, there's another brain dump for now. ~ Daniel --~--~-~--~~~---~--~~ 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: Constructing where-clauses dynamically
Michael Bayer wrote: but what happens if i say: q.select(or_(User.c.orders.items.item_name == 'item#4', User.c.orders.items.item_name == 'item #5')) if we naively convert c.orders.items.item_name=='item #4' into user.user_id=orders.user_id and orders.order_id=items.order_id and items.item_name='item #4, then the other clause for item #5 generates into the same thing and you get an inefficient query. i wonder also if some expressions above dont work correctly if the join conditions are repeated like that. Can you give an example of the SQL (including the joins) that would be generated by your statement above? its still better to say: q.select(or_(Item.c.item_name == 'item#4', Item.c.item_name == 'item #5'), from_obj=[c.orders.items]) isnt it ? (User.c.orders.items would be a synonym for query.join_via ('orders', 'items')) Right. It should be possible (although I'm not sure how simple) to combine conditions using the rules for combining logical expressions (i.e. commutative, transitive, etc.). For example: (A == B AND C == D) OR (A == B AND C == E) can be reduced to (A == B) AND (C == D OR C == E) So what we need is a way to take a group of expressions and reduce them to their simplest form before generating the SQL. However, don't most main-stream databases do this kind of optimization anyway? MySQL does (http://dev.mysql.com/doc/refman/5.0/en/where-optimizations.html). Having said all that, it may be simpler to use join_to/join_via in some cases and maybe that's a good reason to keep those functions around. However, I think this new syntax would still be very valuable in many cases. FWIW, an equivalent but slightly more concise version of your query above would be this: q.select(user.c.orders.items.item_name.in_(item#4, item #5)) ~ Daniel --~--~-~--~~~---~--~~ 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: Constructing where-clauses dynamically
Michael Bayer wrote: ok let me rephrase that... i have concerns. i think the concerns could be addressed, and we might be able to add this kind of feature...but i dont want to rush into it singlehandedly. I won't blame you for that. I'll help out as much as possible given my busy schedule. heres the things that should be resolved: - we add non-column properties to SomeClass.c. people will get confused that SomeClass.c has a bunch of stuff on it that mytable.c does not. do we change the name of 'c' ? Part of me say adding extra columns to SomeClass.c wouldn't be so confusing, and would actually be intuitive. For example in the original question that started this thread it seemed logical to me that 'order' should have been in there since it was a mapper property. Sure maybe it's a bit more than meets the eye at first, but I think it's very logical to have mapper properties in SomeClass.c but not have them in table.c. As long as it's well documented I don't think it would be a problem. The only other thing I can think of is to create a function that would return a cols object. In the spirit of python's getattr() function: from sqlalchemy.orm import getcols c = getcols(SomeClass) session.query(SomeClass).select(c.order == order) I often find myself assigning SomeClass.c to a local variable when I'm building a query anyway. It prevents excess verbosity. - people will say things like, SomeClass.c.order order. how do we interpret that ? esp. for composite foreign keys. I assume you're implicitly asking about the choice to use AND or OR to combine multiple comparisons of a composite key. For example (assume Order and SomeClass are related by user_id and quote_id): # given this order = Order(user_id=4, quote_id=5) session.query(SomeClass).select(SomeClass.c.order order) -- Do we generate this? WHERE some_class.user_id 4 AND some_class.quote_id 5 -- or this? WHERE some_class.user_id 4 OR some_class.quote_id 5 I don't think the , =, , and = operators would be very useful on most relationship comparisons; especially those with composite keys. However, the == and != operators are obviously useful in many cases. It's like comparing user-defined Python objects without explicit __gt__ and __lt__ implementations. Python 2.x compares the id() of the objects, while Python 3000 will raise a TypeError (I think that's the right error). The reasoning behind this decision[0] is that unless there is an explicit ordering for a given pair of objects, it doesn't make sense to compare them using the inequality operators. At any rate, I would recommend not implementing those comparison operations for relationships by default (at least not for multi-column relationships). If someone really needed them maybe they could create their own custom relationship subclass? [0] http://mail.python.org/pipermail/python-dev/2005-November/057925.html - is it really as simple as just producing the primary/foreign key columns of both sides ? i fear that a whole class of patterns are going to become apparent after adding that feature, and then people are going to complain about SA being broken until an entire HQL-like layer is added to implement all those features. maybe theyre going to stick SomeClass.c.order into the order_by clause, theyre going to stick it in func() calls, CAST calls, etc. and expecting all this magical behavior. pretty much every other feature of SA proved to be a gargantuan undertaking compared to how easy i thought it would be when i first wrote it :)how would this feature be presented ? Could we implement it as simply as possible for now and see how it's used? As new use cases come up we can either extend the implementation or explain why they are not supported (i.e. too complicated, inconsistent with other parts of SA, etc.). - is this feature also going to figure out all the joins from class A to B, if my query is based on class A and the criterion object is all the way on B? the way select_by, join_to works ? that seems trickier and i can see people expecting that behaivor as well. This is something I thought about immediately when I first imagined the idea. Personally, I don't really like the automagical join behavior because it's not explicit and can lead to obscure bugs that are hard to track down (why is this join suddenly returning the wrong results? ...hours later we discover that a new relationship was added that caused SA to pick a different relationship path for the join). The problem is that a seemingly unrelated (no pun intended) change can cause these magic joins to be interpreted differently. I tried to use that behavior once myself but quickly discarded it when SA used some other relationship path than the one I intended to use. In general I think it's best to require explicit relationship paths at all times and give useful errors when the given path can't be determined using those strict rules. if we