[sqlalchemy] The cost of defer()
Hi, I wrote a query joining a couple of tables, returning over a hundred thousand rows. Since I only needed to access a couple of the attributes of the returned objects for this specific use case, I thought to use a dozen or so Query.options(defer(...)) calls to avoid loading the unneeded columns. But to my surprise, the query became much slower. Profiling attributed almost all the extra time to set_deferred_for_local_state() and LoadDeferredColumns.__init__(): 827855 15.6680.000 27.0680.000 .../SQLAlchemy-0.8.1-py2.6-linux-x86_64.egg/sqlalchemy/orm/strategies.py:184(set_deferred_for_local_state) 827855 10.5240.000 10.5240.000 .../SQLAlchemy-0.8.1-py2.6-linux-x86_64.egg/sqlalchemy/orm/strategies.py:259(__init__) If defer() is so expensive, then it is not very useful. Would it be possible to make it cheaper? Gabor NOTICE: Morgan Stanley is not acting as a municipal advisor and the opinions or views contained herein are not intended to be, and do not constitute, advice within the meaning of Section 975 of the Dodd-Frank Wall Street Reform and Consumer Protection Act. If you have received this communication in error, please destroy all electronic and paper copies and notify the sender immediately. Mistransmission is not intended to waive confidentiality or privilege. Morgan Stanley reserves the right, to the extent permitted under applicable law, to monitor electronic communications. This message is subject to terms available at the following link: http://www.morganstanley.com/disclaimers If you cannot access these links, please notify us by reply message and we will send the contents to you. By messaging with Morgan Stanley you consent to the foregoing. -- 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/groups/opt_out.
Re: [sqlalchemy] The cost of defer()
the path would be to figure out if the logic of a per-query defer option can somehow be linked to the attribute when it hits its normal refresh logic - if those attribute were set up as deferred at the mapper config level (where deferred is usually used), you wouldn't see this overhead since the deferred loader would be the default callable.But if you only need a few attributes why not just go the other way and query for those attributes directly? that would save you way more overhead than even if we removed all overhead from defer(), since the most expensive thing is all the mapper identity map logic that takes place when full entities are loaded. On Jul 11, 2013, at 4:02 AM, Gombas, Gabor gabor.gom...@morganstanley.com wrote: Hi, I wrote a query joining a couple of tables, returning over a hundred thousand rows. Since I only needed to access a couple of the attributes of the returned objects for this specific use case, I thought to use a dozen or so Query.options(defer(…)) calls to avoid loading the unneeded columns. But to my surprise, the query became much slower. Profiling attributed almost all the extra time to set_deferred_for_local_state() and LoadDeferredColumns.__init__(): 827855 15.6680.000 27.0680.000 …/SQLAlchemy-0.8.1-py2.6-linux-x86_64.egg/sqlalchemy/orm/strategies.py:184(set_deferred_for_local_state) 827855 10.5240.000 10.5240.000 …/SQLAlchemy-0.8.1-py2.6-linux-x86_64.egg/sqlalchemy/orm/strategies.py:259(__init__) If defer() is so expensive, then it is not very useful. Would it be possible to make it cheaper? Gabor NOTICE: Morgan Stanley is not acting as a municipal advisor and the opinions or views contained herein are not intended to be, and do not constitute, advice within the meaning of Section 975 of the Dodd-Frank Wall Street Reform and Consumer Protection Act. If you have received this communication in error, please destroy all electronic and paper copies and notify the sender immediately. Mistransmission is not intended to waive confidentiality or privilege. Morgan Stanley reserves the right, to the extent permitted under applicable law, to monitor electronic communications. This message is subject to terms available at the following link: http://www.morganstanley.com/disclaimers If you cannot access these links, please notify us by reply message and we will send the contents to you. By messaging with Morgan Stanley you consent to the foregoing. -- 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/groups/opt_out. -- 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/groups/opt_out.
RE: [sqlalchemy] The cost of defer()
I did need the objects, not just the raw data, otherwise I'd had to duplicate a bunch of existing code which expected full-blown objects to operate on. Modifying the mapper is not really an option unless the majority of the users have the same requirements, otherwise I end up having to add a huge amount of undefer() calls everywhere else (and the query storm caused by missing an undefer() would be much more painful). Maybe the documentation of defer() could mention that the use of the option can in fact reduce performance, because it's not intuitive that loading unneeded data is cheaper than not loading it. From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Michael Bayer Sent: 11 July 2013 15:34 To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] The cost of defer() the path would be to figure out if the logic of a per-query defer option can somehow be linked to the attribute when it hits its normal refresh logic - if those attribute were set up as deferred at the mapper config level (where deferred is usually used), you wouldn't see this overhead since the deferred loader would be the default callable.But if you only need a few attributes why not just go the other way and query for those attributes directly? that would save you way more overhead than even if we removed all overhead from defer(), since the most expensive thing is all the mapper identity map logic that takes place when full entities are loaded. On Jul 11, 2013, at 4:02 AM, Gombas, Gabor gabor.gom...@morganstanley.commailto:gabor.gom...@morganstanley.com wrote: Hi, I wrote a query joining a couple of tables, returning over a hundred thousand rows. Since I only needed to access a couple of the attributes of the returned objects for this specific use case, I thought to use a dozen or so Query.options(defer(...)) calls to avoid loading the unneeded columns. But to my surprise, the query became much slower. Profiling attributed almost all the extra time to set_deferred_for_local_state() and LoadDeferredColumns.__init__(): 827855 15.6680.000 27.0680.000 .../SQLAlchemy-0.8.1-py2.6-linux-x86_64.egg/sqlalchemy/orm/strategies.py:184(set_deferred_for_local_state) 827855 10.5240.000 10.5240.000 .../SQLAlchemy-0.8.1-py2.6-linux-x86_64.egg/sqlalchemy/orm/strategies.py:259(__init__) If defer() is so expensive, then it is not very useful. Would it be possible to make it cheaper? Gabor NOTICE: Morgan Stanley is not acting as a municipal advisor and the opinions or views contained herein are not intended to be, and do not constitute, advice within the meaning of Section 975 of the Dodd-Frank Wall Street Reform and Consumer Protection Act. If you have received this communication in error, please destroy all electronic and paper copies and notify the sender immediately. Mistransmission is not intended to waive confidentiality or privilege. Morgan Stanley reserves the right, to the extent permitted under applicable law, to monitor electronic communications. This message is subject to terms available at the following link: http://www.morganstanley.com/disclaimers If you cannot access these links, please notify us by reply message and we will send the contents to you. By messaging with Morgan Stanley you consent to the foregoing. -- 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.commailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.commailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. -- 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.commailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.commailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. NOTICE: Morgan Stanley is not acting as a municipal advisor and the opinions or views contained herein are not intended to be, and do not constitute, advice within the meaning of Section 975 of the Dodd-Frank Wall Street Reform and Consumer Protection Act. If you have received this communication in error, please destroy all electronic and paper copies and notify the sender immediately. Mistransmission is not intended to waive confidentiality or privilege. Morgan Stanley reserves the right, to the extent permitted under applicable law, to monitor
Re: [sqlalchemy] SQLAlchemy 0.8.2 released
Michael, On 03/07/2013 22:20, Michael Bayer wrote: Hey all - SQLAlchemy release 0.8.2 is now available. 0.8.2 includes several dozen bug fixes and new features, including refinement of some of the new features introduced in 0.8. Areas of improvement include Core, ORM, as well as specific fixes for dialects such as Postgresql, MySQL, Oracle, SQL Server, Firebird and Sybase. Users should carefully review the Changelog (http://docs.sqlalchemy.org/en/latest/changelog/changelog_08.html#change-0.8.2) to note which behaviors and issues are affected. We'd like to thank the many contributors who helped with this release. SQLAlchemy 0.8.2 is available on the Download Page: http://www.sqlalchemy.org/download.html Thanks for adding the retaining flag for Firebird - you are way to fast for me:) Werner -- 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/groups/opt_out.
[sqlalchemy] Generated update command is alway schosing alternative field names
hello,
I've previously defined inserts and updates by hand in my application,
which is working fine, not using SQLAlchemy at the moment.
At this point, I'd like to employ SQLAlchemy to generate these inserts and
updates for me. And that's all.
I mean: just generate the queries for me. I'm *not* going to execute via
SQLAlchemy at this point.
I did the test below:
engine = create_engine('postgresql://localhost/sample')
metadata = MetaData()
metadata.bind = engine
t = metadata.tables['company_valuation_measures']
print(str( t.update().values(trailing_pe=1.0).where(t.c.symbol
=='dummy').where(t.c.date=='dummy') ))
I obtained:
UPDATE company_valuation_measures
SET trailing_pe=%(trailing_pe)s
WHERE company_valuation_measures.symbol = %(symbol_1)s AND
company_valuation_measures.date = %(date_1)s
The trouble is: field names are 'symbol' and 'date', not 'symbol_1', not '
date_1'.
Could someone point out what I'm doing wrong?
Thanks
-- Richard
--
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/groups/opt_out.
[sqlalchemy] Dogpile caching: can't pickle function objects
If I do a query like this:
return PcpPostModel.query.filter_by(id=post_id).options(
FromCache(default)
)
and then later I do another query like this:
PcpPostModel.query.options(FromCache(default)).all()
Any models that were returned by the first query are now of type: class
'dogpile.cache.api.CachedValue'
So then when the second query runs it will except with:
Traceback (most recent call last):
File
/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/runpy.py,
line 162, in _run_module_as_main
__main__, fname, loader, pkg_name)
File
/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/runpy.py,
line 72, in _run_code
exec code in run_globals
File
/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/cProfile.py,
line 199, in module
main()
File
/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/cProfile.py,
line 192, in main
runctx(code, globs, None, options.outfile, options.sort)
File
/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/cProfile.py,
line 49, in runctx
prof = prof.runctx(statement, globals, locals)
File
/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/cProfile.py,
line 140, in runctx
exec cmd in globals, locals
File scripts/benchmark_boutpage_queries.py, line 45, in module
for p in post.related_bouts(4):
File stufff/post/pcp_post_model.py, line 130, in related_bouts
posts = post_query.options(
File
/Users/aelaguiz/workspace/stufff/venv/lib/python2.7/site-packages/sqlalchemy/orm/query.py,
line 2104, in all
return list(self)
File stufff/base/caching_query.py, line 71, in __iter__
return self.get_value(createfunc=createfunc)
File stufff/base/caching_query.py, line 117, in get_value
expiration_time=expiration_time
File
/Users/aelaguiz/workspace/stufff/venv/lib/python2.7/site-packages/dogpile/cache/region.py,
line 588, in get_or_create
async_creator) as value:
File
/Users/aelaguiz/workspace/stufff/venv/lib/python2.7/site-packages/dogpile/core/dogpile.py,
line 160, in __enter__
return self._enter()
File
/Users/aelaguiz/workspace/stufff/venv/lib/python2.7/site-packages/dogpile/core/dogpile.py,
line 100, in _enter
generated = self._enter_create(createdtime)
File
/Users/aelaguiz/workspace/stufff/venv/lib/python2.7/site-packages/dogpile/core/dogpile.py,
line 151, in _enter_create
created = self.creator()
File
/Users/aelaguiz/workspace/stufff/venv/lib/python2.7/site-packages/dogpile/cache/region.py,
line 570, in gen_value
self.backend.set(key, value)
File
/Users/aelaguiz/workspace/stufff/venv/lib/python2.7/site-packages/dogpile/cache/backends/memcached.py,
line 168, in set
**self.set_arguments
File
/Users/aelaguiz/workspace/stufff/venv/lib/python2.7/site-packages/bmemcached/__init__.py,
line 51, in set
returns.append(server.set(key, value, time))
File
/Users/aelaguiz/workspace/stufff/venv/lib/python2.7/site-packages/bmemcached/__init__.py,
line 382, in set
return self._set_add_replace('set', key, value, time)
File
/Users/aelaguiz/workspace/stufff/venv/lib/python2.7/site-packages/bmemcached/__init__.py,
line 358, in _set_add_replace
flags, value = self.serialize(value)
File
/Users/aelaguiz/workspace/stufff/venv/lib/python2.7/site-packages/bmemcached/__init__.py,
line 273, in serialize
value = dumps(value)
File /Users/aelaguiz/workspace/stufff/venv/lib/python2.7/copy_reg.py,
line 70, in _reduce_ex
raise TypeError, can't pickle %s objects % base.__name__
TypeError: can't pickle function objects
Any idea what I can do about this?
Thanks!
Amir
--
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/groups/opt_out.
Re: [sqlalchemy] SQLAlchemy 0.8.2 released
On a tangent... I just noticed that there are several dozen (if not hundreds) of SqlAlchemy projects on PyPi Perhaps SqlAlchemy is now large enough that it should have it's own classifier ? Something like... Topic :: Database :: Front-Ends :: SqlAlchemy Topic :: Database :: SqlAlchmey -- 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/groups/opt_out.
[sqlalchemy] Re: Dogpile caching: can't pickle function objects
I serialize all my cached data into a dict or json before caching, then unserialize into whatever object i need. -- 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/groups/opt_out.
Re: [sqlalchemy] The cost of defer()
well what kind of data are we talking about? defer()'s use case was for binary large objects and such, fields that are many K/Megs in size. if you're deferring a bunch of ints, then yes it's not optimized very well for that. Half of the overhead could be easily fixed here, creating those LoadDeferredColumns objects could be offloaded to a later point.The other half, setting up that callable, I'd have to spend some time reviewing the use cases here. The difference between an attribute that is deferred vs. one that is expired is that if you access some other expired attribute, the deferred attribute will still not load - because the use case is, you really don't want this BLOB column to load unless you touch it specifically. So to get that instruction into the state, don't load these keys even on an unexpire, uses some kind of method call on every state. InstanceState._set_callable could be inlined more here to do less work, instructions up to the loader process just to populate a key in a dictionary maybe, though these reorganizations can destabilize the code. it's not something I'd be comfortable doing in 0.8, the ticket I created (http://www.sqlalchemy.org/trac/ticket/2778) has any potential work here for 0.9. The other way to go here is to provide a query option that explicitly delivers the attribute as expired as opposed to deferred, looking at how that works right now I can give you the recipe below, but it still involves a function call per column so that the InstanceState knows the attribute is expired. from sqlalchemy.orm.strategies import DeferredOption, DeferredColumnLoader class DontLoadColumnOption(DeferredOption): def get_strategy_class(self): return NoColumnLoader class NoColumnLoader(DeferredColumnLoader): def create_row_processor(self, context, path, mapper, row, adapter): if not self.is_class_level: def set_deferred_for_local_state(state, dict_, row): state.callables[self.key] = state return set_deferred_for_local_state, None, None else: return super(NoColumnLoader, self).create_row_processor( context, path, mapper, row, adapter) if __name__ == '__main__': from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class A(Base): __tablename__ = 'a' id = Column(Integer, primary_key=True) x = Column(Integer) y = Column(Integer) z = Column(Integer) q = Column(Integer) e = create_engine(sqlite://, echo=True) Base.metadata.create_all(e) s = Session(e) s.add_all([ A(x=x%d % i, y=y%d % i, z=z%d % i, q=q%d % i) for i in xrange(1000) ]) s.commit() s.close() loaded = s.query(A).options(DontLoadColumnOption(y), DontLoadColumnOption(z)).order_by(A.id).all() for a in loaded: assert 'y' not in a.__dict__ assert 'z' not in a.__dict__ assert 'x' in a.__dict__ assert 'q' in a.__dict__ assert a.z == z%d % (a.id - 1), a.z On Jul 11, 2013, at 10:23 AM, Gombas, Gabor gabor.gom...@morganstanley.com wrote: I did need the objects, not just the raw data, otherwise I’d had to duplicate a bunch of existing code which expected full-blown objects to operate on. Modifying the mapper is not really an option unless the majority of the users have the same requirements, otherwise I end up having to add a huge amount of undefer() calls everywhere else (and the query storm caused by missing an undefer() would be much more painful). Maybe the documentation of defer() could mention that the use of the option can in fact reduce performance, because it’s not intuitive that loading unneeded data is cheaper than not loading it. From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Michael Bayer Sent: 11 July 2013 15:34 To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] The cost of defer() the path would be to figure out if the logic of a per-query defer option can somehow be linked to the attribute when it hits its normal refresh logic - if those attribute were set up as deferred at the mapper config level (where deferred is usually used), you wouldn't see this overhead since the deferred loader would be the default callable.But if you only need a few attributes why not just go the other way and query for those attributes directly? that would save you way more overhead than even if we removed all overhead from defer(), since the most expensive thing is all the mapper identity map logic that takes place when full entities are loaded. On Jul 11, 2013, at 4:02 AM, Gombas, Gabor gabor.gom...@morganstanley.com wrote: Hi, I wrote a query joining a couple of tables, returning over a hundred
[sqlalchemy] Join order determinism
I noticed that between runs my cache hit rate using dogpile query caching could change without any of the underlying data structures changing, after digging in what I found was the join order on my polymorphic classes is not deterministic. Is there any way to ensure a deterministic join order on polymorphic loads? Examples of the queries being generated: Run 1: SELECT entities.id AS entities_id, entities.parent_id AS entities_parent_id, entities.type_id AS entities_type_id, entities.name AS entities_name, entities.created_at AS entities_created_at, entities.hotness AS entities_hotness, entities.hotness_dirty AS entities_hotness_dirty, entities.modified_at AS entities_modified_at, entities.up_votes AS entities_up_votes, entities.down_votes AS entities_down_votes, categories.id AS categories_id, videos.id AS videos_id, videos.video_type_id AS videos_video_type_id, videos.poster_id AS videos_poster_id, videos.key AS videos_key, clubs.id AS clubs_id, brands.id AS brands_id, pcp_points.id AS pcp_points_id, pcp_points.poster_id AS pcp_points_poster_id, pcp_points.subject_id AS pcp_points_subject_id, pcp_points.is_point AS pcp_points_is_point, pcp_points.body AS pcp_points_body, pcp_points.origin_url AS pcp_points_origin_url, tags.id AS tags_id, pcp_vote_targets.id AS pcp_vote_targets_id, stufff_images.id AS stufff_images_id, stufff_images.image_id AS stufff_images_image_id, stufff_images.poster_id AS stufff_images_poster_id, stufff_images.source AS stufff_images_source, link_posts.id AS link_posts_id, link_posts.url AS link_posts_url, link_posts.edited_at AS link_posts_edited_at, link_posts.poster_id AS link_posts_poster_id, users.id AS users_id, users.email AS users_email, users.username AS users_username, users.first_name AS users_first_name, users.last_name AS users_last_name, users.picture AS users_picture, users.origin_url AS users_origin_url, users.city AS users_city, users.state AS users_state, users.country AS users_country, users.gender AS users_gender, users.password AS users_password, users.bio AS users_bio, users.email_verified AS users_email_verified, users.email_validation AS users_email_validation, users.temp_password AS users_temp_password, users.temp_password_expiry AS users_temp_password_expiry, users.active AS users_active, users.admin AS users_admin, users.reputation AS users_reputation, text_posts.id AS text_posts_id, text_posts.body AS text_posts_body, text_posts.edited_at AS text_posts_edited_at, text_posts.poster_id AS text_posts_poster_id, products.id AS products_id, pcp_posts.id AS pcp_posts_id, pcp_posts.poster_id AS pcp_posts_poster_id, pcp_posts.subject_id_1 AS pcp_posts_subject_id_1, pcp_posts.subject_id_2 AS pcp_posts_subject_id_2, pcp_posts.vote_id_1 AS pcp_posts_vote_id_1, pcp_posts.vote_id_2 AS pcp_posts_vote_id_2, pcp_posts.top_point_id AS pcp_posts_top_point_id, comments.id AS comments_id, comments.body AS comments_body, comments.poster_id AS comments_poster_id, anon_1.users_id AS anon_1_users_id, anon_1.entities_id AS anon_1_entities_id, anon_1.entities_parent_id AS anon_1_entities_parent_id, anon_1.entities_type_id AS anon_1_entities_type_id, anon_1.entities_name AS anon_1_entities_name, anon_1.entities_created_at AS anon_1_entities_created_at, anon_1.entities_hotness AS anon_1_entities_hotness, anon_1.entities_hotness_dirty AS anon_1_entities_hotness_dirty, anon_1.entities_modified_at AS anon_1_entities_modified_at, anon_1.entities_up_votes AS anon_1_entities_up_votes, anon_1.entities_down_votes AS anon_1_entities_down_votes, anon_1.users_email AS anon_1_users_email, anon_1.users_username AS anon_1_users_username, anon_1.users_first_name AS anon_1_users_first_name, anon_1.users_last_name AS anon_1_users_last_name, anon_1.users_picture AS anon_1_users_picture, anon_1.users_origin_url AS anon_1_users_origin_url, anon_1.users_city AS anon_1_users_city, anon_1.users_state AS anon_1_users_state, anon_1.users_country AS anon_1_users_country, anon_1.users_gender AS anon_1_users_gender, anon_1.users_password AS anon_1_users_password, anon_1.users_bio AS anon_1_users_bio, anon_1.users_email_verified AS anon_1_users_email_verified, anon_1.users_email_validation AS anon_1_users_email_validation, anon_1.users_temp_password AS anon_1_users_temp_password, anon_1.users_temp_password_expiry AS anon_1_users_temp_password_expiry, anon_1.users_active AS anon_1_users_active, anon_1.users_admin AS anon_1_users_admin, anon_1.users_reputation AS anon_1_users_reputation, anon_2.users_id AS anon_2_users_id, anon_2.entities_id AS anon_2_entities_id, anon_2.entities_parent_id AS anon_2_entities_parent_id, anon_2.entities_type_id AS anon_2_entities_type_id, anon_2.entities_name AS anon_2_entities_name, anon_2.entities_created_at AS anon_2_entities_created_at, anon_2.entities_hotness AS anon_2_entities_hotness, anon_2.entities_hotness_dirty AS anon_2_entities_hotness_dirty, anon_2.entities_modified_at AS
Re: [sqlalchemy] The cost of defer()
please try out this patch: http://www.sqlalchemy.org/trac/attachment/ticket/2778/2778.patch which refactors this particular system to not require the production of a new object per instance, which is the slowest part of this, and also inlines the work of assembling the callable. This should give you 50% or more method call improvement. if this is enough, this might be OK for 0.8. On Jul 11, 2013, at 1:40 PM, Michael Bayer mike...@zzzcomputing.com wrote: well what kind of data are we talking about? defer()'s use case was for binary large objects and such, fields that are many K/Megs in size. if you're deferring a bunch of ints, then yes it's not optimized very well for that. Half of the overhead could be easily fixed here, creating those LoadDeferredColumns objects could be offloaded to a later point.The other half, setting up that callable, I'd have to spend some time reviewing the use cases here. The difference between an attribute that is deferred vs. one that is expired is that if you access some other expired attribute, the deferred attribute will still not load - because the use case is, you really don't want this BLOB column to load unless you touch it specifically. So to get that instruction into the state, don't load these keys even on an unexpire, uses some kind of method call on every state. InstanceState._set_callable could be inlined more here to do less work, instructions up to the loader process just to populate a key in a dictionary maybe, though these reorganizations can destabilize the code. it's not something I'd be comfortable doing in 0.8, the ticket I created (http://www.sqlalchemy.org/trac/ticket/2778) has any potential work here for 0.9. The other way to go here is to provide a query option that explicitly delivers the attribute as expired as opposed to deferred, looking at how that works right now I can give you the recipe below, but it still involves a function call per column so that the InstanceState knows the attribute is expired. from sqlalchemy.orm.strategies import DeferredOption, DeferredColumnLoader class DontLoadColumnOption(DeferredOption): def get_strategy_class(self): return NoColumnLoader class NoColumnLoader(DeferredColumnLoader): def create_row_processor(self, context, path, mapper, row, adapter): if not self.is_class_level: def set_deferred_for_local_state(state, dict_, row): state.callables[self.key] = state return set_deferred_for_local_state, None, None else: return super(NoColumnLoader, self).create_row_processor( context, path, mapper, row, adapter) if __name__ == '__main__': from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.ext.declarative import declarative_base Base = declarative_base() class A(Base): __tablename__ = 'a' id = Column(Integer, primary_key=True) x = Column(Integer) y = Column(Integer) z = Column(Integer) q = Column(Integer) e = create_engine(sqlite://, echo=True) Base.metadata.create_all(e) s = Session(e) s.add_all([ A(x=x%d % i, y=y%d % i, z=z%d % i, q=q%d % i) for i in xrange(1000) ]) s.commit() s.close() loaded = s.query(A).options(DontLoadColumnOption(y), DontLoadColumnOption(z)).order_by(A.id).all() for a in loaded: assert 'y' not in a.__dict__ assert 'z' not in a.__dict__ assert 'x' in a.__dict__ assert 'q' in a.__dict__ assert a.z == z%d % (a.id - 1), a.z On Jul 11, 2013, at 10:23 AM, Gombas, Gabor gabor.gom...@morganstanley.com wrote: I did need the objects, not just the raw data, otherwise I’d had to duplicate a bunch of existing code which expected full-blown objects to operate on. Modifying the mapper is not really an option unless the majority of the users have the same requirements, otherwise I end up having to add a huge amount of undefer() calls everywhere else (and the query storm caused by missing an undefer() would be much more painful). Maybe the documentation of defer() could mention that the use of the option can in fact reduce performance, because it’s not intuitive that loading unneeded data is cheaper than not loading it. From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Michael Bayer Sent: 11 July 2013 15:34 To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] The cost of defer() the path would be to figure out if the logic of a per-query defer option can somehow be linked to the attribute when it hits its normal refresh logic - if those attribute were set up as deferred at the mapper config level (where deferred is usually used), you wouldn't see this
Re: [sqlalchemy] Generated update command is alway schosing alternative field names
On Thu, Jul 11, 2013 at 4:30 PM, Richard Gomes rgomes.i...@gmail.com wrote:
hello,
I've previously defined inserts and updates by hand in my application, which
is working fine, not using SQLAlchemy at the moment.
At this point, I'd like to employ SQLAlchemy to generate these inserts and
updates for me. And that's all.
I mean: just generate the queries for me. I'm not going to execute via
SQLAlchemy at this point.
I did the test below:
engine = create_engine('postgresql://localhost/sample')
metadata = MetaData()
metadata.bind = engine
t = metadata.tables['company_valuation_measures']
print(str(
t.update().values(trailing_pe=1.0).where(t.c.symbol=='dummy').where(t.c.date=='dummy')
))
I obtained:
UPDATE company_valuation_measures
SET trailing_pe=%(trailing_pe)s
WHERE company_valuation_measures.symbol = %(symbol_1)s AND
company_valuation_measures.date = %(date_1)s
The trouble is: field names are 'symbol' and 'date', not 'symbol_1', not
'date_1'.
Could someone point out what I'm doing wrong?
SQLAlchemy uses bind parameters when executing SQL - ie. the values
don't get substituted into the SQL string, but get passed to the
underlying DBAPI module separately. This is generally what you want,
as bind parameters avoid potential SQL-injection security holes.
There is a recipe on the wiki for getting the SQL string with the
parameters inserted, but you should read the warning at the top
carefully and fully understand the dangers:
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/BindsAsStrings
Hope that helps,
Simon
--
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/groups/opt_out.
Re: [sqlalchemy] Generated update command is alway schosing alternative field names
On Jul 11, 2013, at 11:30 AM, Richard Gomes rgomes.i...@gmail.com wrote:
hello,
I've previously defined inserts and updates by hand in my application, which
is working fine, not using SQLAlchemy at the moment.
At this point, I'd like to employ SQLAlchemy to generate these inserts and
updates for me. And that's all.
I mean: just generate the queries for me. I'm not going to execute via
SQLAlchemy at this point.
I did the test below:
engine = create_engine('postgresql://localhost/sample')
metadata = MetaData()
metadata.bind = engine
t = metadata.tables['company_valuation_measures']
print(str(
t.update().values(trailing_pe=1.0).where(t.c.symbol=='dummy').where(t.c.date=='dummy')
))
I obtained:
UPDATE company_valuation_measures
SET trailing_pe=%(trailing_pe)s
WHERE company_valuation_measures.symbol = %(symbol_1)s AND
company_valuation_measures.date = %(date_1)s
The trouble is: field names are 'symbol' and 'date', not 'symbol_1', not
'date_1'.
Could someone point out what I'm doing wrong?
symbol_1 and date_1 are generated bound parameter names, which SQLAlchemy
will match up to those dummy names which you passed in. since you aren't
interested in the automatic linkage of dummy to binds and you're looking for
binds that have a specific name, you can use bindparam():
t.update().values(...).where(t.c.symbol==bindparam('symbol')).where(t.c.date==bindparam('date'))
however, the update() construct might complain on this as insert()/update()
both reserve the column name binds for the SET/VALUES clause. you may need to
name them something else (like symbol_where or something like that).
--
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/groups/opt_out.
Re: [sqlalchemy] Dogpile caching: can't pickle function objects
On Jul 11, 2013, at 11:43 AM, Amir Elaguizy aelag...@gmail.com wrote: If I do a query like this: return PcpPostModel.query.filter_by(id=post_id).options( FromCache(default) ) and then later I do another query like this: PcpPostModel.query.options(FromCache(default)).all() Any models that were returned by the first query are now of type: class 'dogpile.cache.api.CachedValue' CachedValue is a tuple-based container that contains the actual result you want to work with as well as the time that the value was placed in the cache. Take a look at the contents of CachedValue. So then when the second query runs it will except with: File /Users/aelaguiz/workspace/stufff/venv/lib/python2.7/copy_reg.py, line 70, in _reduce_ex raise TypeError, can't pickle %s objects % base.__name__ TypeError: can't pickle function objects Any idea what I can do about this? something in your model class, or your mapping, or perhaps within some SQLAlchemy construct in use, is a callable function that isn't picklable. SQLAlchemy itself goes through a lot of trouble to not embed functions in mapped instances, however in some cases it's difficult to avoid. You'd need to provide full detail on your classes/mappings, most preferably a fully contained, runnable example, in order to determine where this callable is present. -- 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/groups/opt_out.
Re: [sqlalchemy] SQLAlchemy 0.8.2 released
hey i like that idea. have any friends at pypi ? On Jul 11, 2013, at 1:13 PM, Jonathan Vanasco jvana...@gmail.com wrote: On a tangent... I just noticed that there are several dozen (if not hundreds) of SqlAlchemy projects on PyPi Perhaps SqlAlchemy is now large enough that it should have it's own classifier ? Something like... Topic :: Database :: Front-Ends :: SqlAlchemy Topic :: Database :: SqlAlchmey -- 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/groups/opt_out. -- 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/groups/opt_out.
Re: [sqlalchemy] Dogpile caching: can't pickle function objects
Michael, Thanks for the reply. I understand what you're saying and can go search for that. I wonder if you could take a look at my question about join order determinism in polymorphic queries? Thanks, Amir On Thursday, July 11, 2013 11:09:50 AM UTC-7, Michael Bayer wrote: On Jul 11, 2013, at 11:43 AM, Amir Elaguizy aela...@gmail.comjavascript: wrote: If I do a query like this: return PcpPostModel.query.filter_by(id=post_id).options( FromCache(default) ) and then later I do another query like this: PcpPostModel.query.options(FromCache(default)).all() Any models that were returned by the first query are now of type: class 'dogpile.cache.api.CachedValue' CachedValue is a tuple-based container that contains the actual result you want to work with as well as the time that the value was placed in the cache. Take a look at the contents of CachedValue. So then when the second query runs it will except with: File /Users/aelaguiz/workspace/stufff/venv/lib/python2.7/copy_reg.py, line 70, in _reduce_ex raise TypeError, can't pickle %s objects % base.__name__ TypeError: can't pickle function objects Any idea what I can do about this? something in your model class, or your mapping, or perhaps within some SQLAlchemy construct in use, is a callable function that isn't picklable. SQLAlchemy itself goes through a lot of trouble to not embed functions in mapped instances, however in some cases it's difficult to avoid. You'd need to provide full detail on your classes/mappings, most preferably a fully contained, runnable example, in order to determine where this callable is present. -- 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/groups/opt_out.
Re: [sqlalchemy] Join order determinism
when you say between runs, you mean whole new processes with new mappers, right? there are some memoized sets involved in polymorphic loading, those sets should not change order as the program runs but across runs there may be some changes in order.to improve this I'd need you to provide a simple test case on a new trac ticket - here's kind of a guess as to what might resolve it, if you want to try: --- a/lib/sqlalchemy/orm/mapper.py +++ b/lib/sqlalchemy/orm/mapper.py @@ -1798,7 +1798,7 @@ class Mapper(_InspectionAttr): while stack: item = stack.popleft() descendants.append(item) -stack.extend(item._inheriting_mappers) +stack.extend(sorted(item._inheriting_mappers, key=lambda m: m.class_.__name__)) return util.WeakSequence(descendants) def polymorphic_iterator(self): On Jul 11, 2013, at 2:00 PM, Amir Elaguizy aelag...@gmail.com wrote: I noticed that between runs my cache hit rate using dogpile query caching could change without any of the underlying data structures changing, after digging in what I found was the join order on my polymorphic classes is not deterministic. Is there any way to ensure a deterministic join order on polymorphic loads? Examples of the queries being generated: Run 1: SELECT entities.id AS entities_id, entities.parent_id AS entities_parent_id, entities.type_id AS entities_type_id, entities.name AS entities_name, entities.created_at AS entities_created_at, entities.hotness AS entities_hotness, entities.hotness_dirty AS entities_hotness_dirty, entities.modified_at AS entities_modified_at, entities.up_votes AS entities_up_votes, entities.down_votes AS entities_down_votes, categories.id AS categories_id, videos.id AS videos_id, videos.video_type_id AS videos_video_type_id, videos.poster_id AS videos_poster_id, videos.key AS videos_key, clubs.id AS clubs_id, brands.id AS brands_id, pcp_points.id AS pcp_points_id, pcp_points.poster_id AS pcp_points_poster_id, pcp_points.subject_id AS pcp_points_subject_id, pcp_points.is_point AS pcp_points_is_point, pcp_points.body AS pcp_points_body, pcp_points.origin_url AS pcp_points_origin_url, tags.id AS tags_id, pcp_vote_targets.id AS pcp_vote_targets_id, stufff_images.id AS stufff_images_id, stufff_images.image_id AS stufff_images_image_id, stufff_images.poster_id AS stufff_images_poster_id, stufff_images.source AS stufff_images_source, link_posts.id AS link_posts_id, link_posts.url AS link_posts_url, link_posts.edited_at AS link_posts_edited_at, link_posts.poster_id AS link_posts_poster_id, users.id AS users_id, users.email AS users_email, users.username AS users_username, users.first_name AS users_first_name, users.last_name AS users_last_name, users.picture AS users_picture, users.origin_url AS users_origin_url, users.city AS users_city, users.state AS users_state, users.country AS users_country, users.gender AS users_gender, users.password AS users_password, users.bio AS users_bio, users.email_verified AS users_email_verified, users.email_validation AS users_email_validation, users.temp_password AS users_temp_password, users.temp_password_expiry AS users_temp_password_expiry, users.active AS users_active, users.admin AS users_admin, users.reputation AS users_reputation, text_posts.id AS text_posts_id, text_posts.body AS text_posts_body, text_posts.edited_at AS text_posts_edited_at, text_posts.poster_id AS text_posts_poster_id, products.id AS products_id, pcp_posts.id AS pcp_posts_id, pcp_posts.poster_id AS pcp_posts_poster_id, pcp_posts.subject_id_1 AS pcp_posts_subject_id_1, pcp_posts.subject_id_2 AS pcp_posts_subject_id_2, pcp_posts.vote_id_1 AS pcp_posts_vote_id_1, pcp_posts.vote_id_2 AS pcp_posts_vote_id_2, pcp_posts.top_point_id AS pcp_posts_top_point_id, comments.id AS comments_id, comments.body AS comments_body, comments.poster_id AS comments_poster_id, anon_1.users_id AS anon_1_users_id, anon_1.entities_id AS anon_1_entities_id, anon_1.entities_parent_id AS anon_1_entities_parent_id, anon_1.entities_type_id AS anon_1_entities_type_id, anon_1.entities_name AS anon_1_entities_name, anon_1.entities_created_at AS anon_1_entities_created_at, anon_1.entities_hotness AS anon_1_entities_hotness, anon_1.entities_hotness_dirty AS anon_1_entities_hotness_dirty, anon_1.entities_modified_at AS anon_1_entities_modified_at, anon_1.entities_up_votes AS anon_1_entities_up_votes, anon_1.entities_down_votes AS anon_1_entities_down_votes, anon_1.users_email AS anon_1_users_email, anon_1.users_username AS anon_1_users_username, anon_1.users_first_name AS anon_1_users_first_name, anon_1.users_last_name AS anon_1_users_last_name, anon_1.users_picture AS anon_1_users_picture, anon_1.users_origin_url AS anon_1_users_origin_url, anon_1.users_city AS anon_1_users_city, anon_1.users_state AS anon_1_users_state, anon_1.users_country AS
Re: [sqlalchemy] Join order determinism
Michael, That works! Amir On Thursday, July 11, 2013 11:17:27 AM UTC-7, Michael Bayer wrote: when you say between runs, you mean whole new processes with new mappers, right? there are some memoized sets involved in polymorphic loading, those sets should not change order as the program runs but across runs there may be some changes in order.to improve this I'd need you to provide a simple test case on a new trac ticket - here's kind of a guess as to what might resolve it, if you want to try: --- a/lib/sqlalchemy/orm/mapper.py +++ b/lib/sqlalchemy/orm/mapper.py @@ -1798,7 +1798,7 @@ class Mapper(_InspectionAttr): while stack: item = stack.popleft() descendants.append(item) -stack.extend(item._inheriting_mappers) +stack.extend(sorted(item._inheriting_mappers, key=lambda m: m.class_.__name__)) return util.WeakSequence(descendants) def polymorphic_iterator(self): On Jul 11, 2013, at 2:00 PM, Amir Elaguizy aela...@gmail.comjavascript: wrote: I noticed that between runs my cache hit rate using dogpile query caching could change without any of the underlying data structures changing, after digging in what I found was the join order on my polymorphic classes is not deterministic. Is there any way to ensure a deterministic join order on polymorphic loads? Examples of the queries being generated: Run 1: SELECT entities.id AS entities_id, entities.parent_id AS entities_parent_id, entities.type_id AS entities_type_id, entities.nameAS entities_name, entities.created_at AS entities_created_at, entities.hotness AS entities_hotness, entities.hotness_dirty AS entities_hotness_dirty, entities.modified_at AS entities_modified_at, entities.up_votes AS entities_up_votes, entities.down_votes AS entities_down_votes, categories.id AS categories_id, videos.id AS videos_id, videos.video_type_id AS videos_video_type_id, videos.poster_id AS videos_poster_id, videos.key AS videos_key, clubs.id AS clubs_id, brands.id AS brands_id, pcp_points.id AS pcp_points_id, pcp_points.poster_id AS pcp_points_poster_id, pcp_points.subject_id AS pcp_points_subject_id, pcp_points.is_point AS pcp_points_is_point, pcp_points.body AS pcp_points_body, pcp_points.origin_url AS pcp_points_origin_url, tags.id AS tags_id, pcp_vote_targets.id AS pcp_vote_targets_id, stufff_images.id AS stufff_images_id, stufff_images.image_id AS stufff_images_image_id, stufff_images.poster_id AS stufff_images_poster_id, stufff_images.source AS stufff_images_source, link_posts.id AS link_posts_id, link_posts.url AS link_posts_url, link_posts.edited_at AS link_posts_edited_at, link_posts.poster_id AS link_posts_poster_id, users.id AS users_id, users.email AS users_email, users.username AS users_username, users.first_name AS users_first_name, users.last_name AS users_last_name, users.picture AS users_picture, users.origin_url AS users_origin_url, users.city AS users_city, users.state AS users_state, users.country AS users_country, users.gender AS users_gender, users.password AS users_password, users.bio AS users_bio, users.email_verified AS users_email_verified, users.email_validation AS users_email_validation, users.temp_password AS users_temp_password, users.temp_password_expiry AS users_temp_password_expiry, users.active AS users_active, users.admin AS users_admin, users.reputation AS users_reputation, text_posts.id AS text_posts_id, text_posts.body AS text_posts_body, text_posts.edited_at AS text_posts_edited_at, text_posts.poster_id AS text_posts_poster_id, products.id AS products_id, pcp_posts.id AS pcp_posts_id, pcp_posts.poster_id AS pcp_posts_poster_id, pcp_posts.subject_id_1 AS pcp_posts_subject_id_1, pcp_posts.subject_id_2 AS pcp_posts_subject_id_2, pcp_posts.vote_id_1 AS pcp_posts_vote_id_1, pcp_posts.vote_id_2 AS pcp_posts_vote_id_2, pcp_posts.top_point_id AS pcp_posts_top_point_id, comments.id AS comments_id, comments.body AS comments_body, comments.poster_id AS comments_poster_id, anon_1.users_id AS anon_1_users_id, anon_1.entities_id AS anon_1_entities_id, anon_1.entities_parent_id AS anon_1_entities_parent_id, anon_1.entities_type_id AS anon_1_entities_type_id, anon_1.entities_name AS anon_1_entities_name, anon_1.entities_created_at AS anon_1_entities_created_at, anon_1.entities_hotness AS anon_1_entities_hotness, anon_1.entities_hotness_dirty AS anon_1_entities_hotness_dirty, anon_1.entities_modified_at AS anon_1_entities_modified_at, anon_1.entities_up_votes AS anon_1_entities_up_votes, anon_1.entities_down_votes AS anon_1_entities_down_votes, anon_1.users_email AS anon_1_users_email, anon_1.users_username AS anon_1_users_username, anon_1.users_first_name AS anon_1_users_first_name, anon_1.users_last_name AS anon_1_users_last_name, anon_1.users_picture AS anon_1_users_picture, anon_1.users_origin_url AS anon_1_users_origin_url,
Re: [sqlalchemy] Join order determinism
just that, huh. the tricky thing is its difficult to ensure that a set() doesn't find its way in there at some point and mess the order up again. open up a ticket for this one I'd need to come up with a test. On Jul 11, 2013, at 2:19 PM, Amir Elaguizy aelag...@gmail.com wrote: Michael, That works! Amir On Thursday, July 11, 2013 11:17:27 AM UTC-7, Michael Bayer wrote: when you say between runs, you mean whole new processes with new mappers, right? there are some memoized sets involved in polymorphic loading, those sets should not change order as the program runs but across runs there may be some changes in order.to improve this I'd need you to provide a simple test case on a new trac ticket - here's kind of a guess as to what might resolve it, if you want to try: --- a/lib/sqlalchemy/orm/mapper.py +++ b/lib/sqlalchemy/orm/mapper.py @@ -1798,7 +1798,7 @@ class Mapper(_InspectionAttr): while stack: item = stack.popleft() descendants.append(item) -stack.extend(item._inheriting_mappers) +stack.extend(sorted(item._inheriting_mappers, key=lambda m: m.class_.__name__)) return util.WeakSequence(descendants) def polymorphic_iterator(self): On Jul 11, 2013, at 2:00 PM, Amir Elaguizy aela...@gmail.com wrote: I noticed that between runs my cache hit rate using dogpile query caching could change without any of the underlying data structures changing, after digging in what I found was the join order on my polymorphic classes is not deterministic. Is there any way to ensure a deterministic join order on polymorphic loads? Examples of the queries being generated: Run 1: SELECT entities.id AS entities_id, entities.parent_id AS entities_parent_id, entities.type_id AS entities_type_id, entities.name AS entities_name, entities.created_at AS entities_created_at, entities.hotness AS entities_hotness, entities.hotness_dirty AS entities_hotness_dirty, entities.modified_at AS entities_modified_at, entities.up_votes AS entities_up_votes, entities.down_votes AS entities_down_votes, categories.id AS categories_id, videos.id AS videos_id, videos.video_type_id AS videos_video_type_id, videos.poster_id AS videos_poster_id, videos.key AS videos_key, clubs.id AS clubs_id, brands.id AS brands_id, pcp_points.id AS pcp_points_id, pcp_points.poster_id AS pcp_points_poster_id, pcp_points.subject_id AS pcp_points_subject_id, pcp_points.is_point AS pcp_points_is_point, pcp_points.body AS pcp_points_body, pcp_points.origin_url AS pcp_points_origin_url, tags.id AS tags_id, pcp_vote_targets.id AS pcp_vote_targets_id, stufff_images.id AS stufff_images_id, stufff_images.image_id AS stufff_images_image_id, stufff_images.poster_id AS stufff_images_poster_id, stufff_images.source AS stufff_images_source, link_posts.id AS link_posts_id, link_posts.url AS link_posts_url, link_posts.edited_at AS link_posts_edited_at, link_posts.poster_id AS link_posts_poster_id, users.id AS users_id, users.email AS users_email, users.username AS users_username, users.first_name AS users_first_name, users.last_name AS users_last_name, users.picture AS users_picture, users.origin_url AS users_origin_url, users.city AS users_city, users.state AS users_state, users.country AS users_country, users.gender AS users_gender, users.password AS users_password, users.bio AS users_bio, users.email_verified AS users_email_verified, users.email_validation AS users_email_validation, users.temp_password AS users_temp_password, users.temp_password_expiry AS users_temp_password_expiry, users.active AS users_active, users.admin AS users_admin, users.reputation AS users_reputation, text_posts.id AS text_posts_id, text_posts.body AS text_posts_body, text_posts.edited_at AS text_posts_edited_at, text_posts.poster_id AS text_posts_poster_id, products.id AS products_id, pcp_posts.id AS pcp_posts_id, pcp_posts.poster_id AS pcp_posts_poster_id, pcp_posts.subject_id_1 AS pcp_posts_subject_id_1, pcp_posts.subject_id_2 AS pcp_posts_subject_id_2, pcp_posts.vote_id_1 AS pcp_posts_vote_id_1, pcp_posts.vote_id_2 AS pcp_posts_vote_id_2, pcp_posts.top_point_id AS pcp_posts_top_point_id, comments.id AS comments_id, comments.body AS comments_body, comments.poster_id AS comments_poster_id, anon_1.users_id AS anon_1_users_id, anon_1.entities_id AS anon_1_entities_id, anon_1.entities_parent_id AS anon_1_entities_parent_id, anon_1.entities_type_id AS anon_1_entities_type_id, anon_1.entities_name AS anon_1_entities_name, anon_1.entities_created_at AS anon_1_entities_created_at, anon_1.entities_hotness AS anon_1_entities_hotness, anon_1.entities_hotness_dirty AS anon_1_entities_hotness_dirty, anon_1.entities_modified_at AS anon_1_entities_modified_at, anon_1.entities_up_votes AS anon_1_entities_up_votes, anon_1.entities_down_votes AS anon_1_entities_down_votes,
Re: [sqlalchemy] Join order determinism
http://www.sqlalchemy.org/trac/ticket/2779 On Thursday, July 11, 2013 11:23:32 AM UTC-7, Michael Bayer wrote: just that, huh. the tricky thing is its difficult to ensure that a set() doesn't find its way in there at some point and mess the order up again. open up a ticket for this one I'd need to come up with a test. On Jul 11, 2013, at 2:19 PM, Amir Elaguizy aela...@gmail.comjavascript: wrote: Michael, That works! Amir On Thursday, July 11, 2013 11:17:27 AM UTC-7, Michael Bayer wrote: when you say between runs, you mean whole new processes with new mappers, right? there are some memoized sets involved in polymorphic loading, those sets should not change order as the program runs but across runs there may be some changes in order.to improve this I'd need you to provide a simple test case on a new trac ticket - here's kind of a guess as to what might resolve it, if you want to try: --- a/lib/sqlalchemy/orm/mapper.py +++ b/lib/sqlalchemy/orm/mapper.py @@ -1798,7 +1798,7 @@ class Mapper(_InspectionAttr): while stack: item = stack.popleft() descendants.append(item) -stack.extend(item._inheriting_mappers) +stack.extend(sorted(item._inheriting_mappers, key=lambda m: m.class_.__name__)) return util.WeakSequence(descendants) def polymorphic_iterator(self): On Jul 11, 2013, at 2:00 PM, Amir Elaguizy aela...@gmail.com wrote: I noticed that between runs my cache hit rate using dogpile query caching could change without any of the underlying data structures changing, after digging in what I found was the join order on my polymorphic classes is not deterministic. Is there any way to ensure a deterministic join order on polymorphic loads? Examples of the queries being generated: Run 1: SELECT entities.id AS entities_id, entities.parent_id AS entities_parent_id, entities.type_id AS entities_type_id, entities.nameAS entities_name, entities.created_at AS entities_created_at, entities.hotness AS entities_hotness, entities.hotness_dirty AS entities_hotness_dirty, entities.modified_at AS entities_modified_at, entities.up_votes AS entities_up_votes, entities.down_votes AS entities_down_votes, categories.id AS categories_id, videos.id AS videos_id, videos.video_type_id AS videos_video_type_id, videos.poster_id AS videos_poster_id, videos.key AS videos_key, clubs.id AS clubs_id, brands.id AS brands_id, pcp_points.id AS pcp_points_id, pcp_points.poster_id AS pcp_points_poster_id, pcp_points.subject_id AS pcp_points_subject_id, pcp_points.is_point AS pcp_points_is_point, pcp_points.body AS pcp_points_body, pcp_points.origin_url AS pcp_points_origin_url, tags.id AS tags_id, pcp_vote_targets.id AS pcp_vote_targets_id, stufff_images.id AS stufff_images_id, stufff_images.image_id AS stufff_images_image_id, stufff_images.poster_id AS stufff_images_poster_id, stufff_images.source AS stufff_images_source, link_posts.id AS link_posts_id, link_posts.url AS link_posts_url, link_posts.edited_at AS link_posts_edited_at, link_posts.poster_id AS link_posts_poster_id, users.id AS users_id, users.email AS users_email, users.username AS users_username, users.first_name AS users_first_name, users.last_name AS users_last_name, users.picture AS users_picture, users.origin_url AS users_origin_url, users.city AS users_city, users.state AS users_state, users.country AS users_country, users.gender AS users_gender, users.password AS users_password, users.bio AS users_bio, users.email_verified AS users_email_verified, users.email_validation AS users_email_validation, users.temp_password AS users_temp_password, users.temp_password_expiry AS users_temp_password_expiry, users.active AS users_active, users.admin AS users_admin, users.reputation AS users_reputation, text_posts.id AS text_posts_id, text_posts.body AS text_posts_body, text_posts.edited_at AS text_posts_edited_at, text_posts.poster_id AS text_posts_poster_id, products.id AS products_id, pcp_posts.id AS pcp_posts_id, pcp_posts.poster_id AS pcp_posts_poster_id, pcp_posts.subject_id_1 AS pcp_posts_subject_id_1, pcp_posts.subject_id_2 AS pcp_posts_subject_id_2, pcp_posts.vote_id_1 AS pcp_posts_vote_id_1, pcp_posts.vote_id_2 AS pcp_posts_vote_id_2, pcp_posts.top_point_id AS pcp_posts_top_point_id, comments.id AS comments_id, comments.body AS comments_body, comments.poster_id AS comments_poster_id, anon_1.users_id AS anon_1_users_id, anon_1.entities_id AS anon_1_entities_id, anon_1.entities_parent_id AS anon_1_entities_parent_id, anon_1.entities_type_id AS anon_1_entities_type_id, anon_1.entities_name AS anon_1_entities_name, anon_1.entities_created_at AS anon_1_entities_created_at, anon_1.entities_hotness AS anon_1_entities_hotness, anon_1.entities_hotness_dirty AS anon_1_entities_hotness_dirty, anon_1.entities_modified_at AS anon_1_entities_modified_at,
Re: [sqlalchemy] Generated update command is alway schosing alternative field names
Hello Michael,
Thanks a lot for your help :)
I've followed your directions. it works.
Regarding the reserved column names (now I remember I saw this
yesterday) ... it does not happen because I'm restricting the field
names which appear in the SET clause, so that there's no collision
between what appears in the WHERE clause and what appears in the SET clause.
This is the entire recipe, for the records:
from sqlalchemy import create_engine, MetaData, bindparam
engine = create_engine('postgresql://localhost/sample'
postgresql://localhost/sample%27)
metadata = MetaData()
metadata.bind = engine
metadata.reflect()
t = metadata.tables['company_valuation_measures']
print(str(
t.update().values(trailing_pe='dummy').where(t.c.symbol==bindparam('symbol')).where(t.c.date==bindparam('date'))
))
It prints
UPDATE company_valuation_measures SET trailing_pe=%(trailing_pe)s WHERE
company_valuation_measures.symbol = %(symbol)s AND
company_valuation_measures.date = %(date)s
Cheers
Richard Gomes
http://rgomes.info
+44(77)9955-6813
On 11/07/13 19:06, Michael Bayer wrote:
On Jul 11, 2013, at 11:30 AM, Richard Gomes rgomes.i...@gmail.com
mailto:rgomes.i...@gmail.com wrote:
hello,
I've previously defined inserts and updates by hand in my
application, which is working fine, not using SQLAlchemy at the moment.
At this point, I'd like to employ SQLAlchemy to generate these
inserts and updates for me. And that's all.
I mean: just generate the queries for me. I'm /*not*/ going to
execute via SQLAlchemy at this point.
I did the test below:
engine = create_engine('postgresql://localhost/sample'
postgresql://localhost/sample%27)
metadata = MetaData()
metadata.bind = engine
t = metadata.tables['company_valuation_measures']
print(str(
t.update().values(trailing_pe=1.0).where(t.c.symbol=='dummy').where(t.c.date=='dummy')
))
I obtained:
UPDATE company_valuation_measures
SET trailing_pe=%(trailing_pe)s
WHERE company_valuation_measures.symbol = %(symbol_1)s AND
company_valuation_measures.date = %(date_1)s
The trouble is: field names are 'symbol' and 'date', not 'symbol_1',
not 'date_1'.
Could someone point out what I'm doing wrong?
symbol_1 and date_1 are generated bound parameter names, which
SQLAlchemy will match up to those dummy names which you passed in.
since you aren't interested in the automatic linkage of dummy to
binds and you're looking for binds that have a specific name, you can
use bindparam():
t.update().values(...).where(t.c.symbol==bindparam('symbol')).where(t.c.date==bindparam('date'))
however, the update() construct might complain on this as
insert()/update() both reserve the column name binds for the
SET/VALUES clause. you may need to name them something else (like
symbol_where or something like that).
--
You received this message because you are subscribed to a topic in the
Google Groups sqlalchemy group.
To unsubscribe from this topic, visit
https://groups.google.com/d/topic/sqlalchemy/DtqNcKvr0Yo/unsubscribe.
To unsubscribe from this group and all its topics, 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/groups/opt_out.
--
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/groups/opt_out.
[sqlalchemy] Re: best practice for SqlAlchemy and webapps ?
Mike, thanks again. I finally found time to integrate your recommendations. https://github.com/jvanasco/pyramid_sqlassist -- 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/groups/opt_out.
[sqlalchemy] Retreiving datetime(6) value using sqlalchemy
Hi all, I have been writing to my database using func.now(6) for datetime(6) valued columns and it has worked perfectly. However, I've recently run into an issue with querying for these values. It would appear that every time I query for the values in datetime(6) columns it returns None. Examples of datetime(6) values: - '2013-07-10 17:22:49.113604' - '2013-07-10 17:55:08.920235' Attempts at retrieving datetime(6) values from the database: *python:* print self.engine.execute(select now()).scalar() print self.engine.execute(select now(6)).scalar() *output:* 2013-07-11 16:33:04 None and same thing happens when retrieving an entire row from a table. I didn't think it was necessary to go into that sort of detail as I'm hoping this is an easy fix, but if need be I can show parts of my schema and datamodel along with queries and results for those records. Any insight would be great. Thanks, Matt -- 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/groups/opt_out.
[sqlalchemy] Re: Retreiving datetime(6) value using sqlalchemy
Sorry, forgot to mention this is for mysql. http://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html On Thursday, July 11, 2013 4:38:03 PM UTC-4, Matt wrote: Hi all, I have been writing to my database using func.now(6) for datetime(6) valued columns and it has worked perfectly. However, I've recently run into an issue with querying for these values. It would appear that every time I query for the values in datetime(6) columns it returns None. Examples of datetime(6) values: - '2013-07-10 17:22:49.113604' - '2013-07-10 17:55:08.920235' Attempts at retrieving datetime(6) values from the database: *python:* print self.engine.execute(select now()).scalar() print self.engine.execute(select now(6)).scalar() *output:* 2013-07-11 16:33:04 None and same thing happens when retrieving an entire row from a table. I didn't think it was necessary to go into that sort of detail as I'm hoping this is an easy fix, but if need be I can show parts of my schema and datamodel along with queries and results for those records. Any insight would be great. Thanks, Matt -- 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/groups/opt_out.
