Re: [sqlalchemy] SQLAlchemy orm attribute post-update hook?
What do you think about the pattern I've implemented for this purpose using
metaclasses?
https://gist.github.com/aelaguiz/7691751
I've also pasted the code here but it's nicer to look at on github via the
link above:
import logging
import sqlalchemy as sqla
import sqlalchemy.ext.declarative as decl
from .signals import signaler
log = logging.getLogger(ucratejoy)
class _hooked(object):
def __init__(self, validate_func, normalize_func, field_name,
private_name):
self.validate_func = validate_func
self.normalize_func = normalize_func
self.field_name = field_name
self.private_name = private_name
def __get__(self, instance, owner):
if not instance:
return getattr(owner, self.private_name)
val = getattr(instance, self.private_name)
return val
def __set__(self, instance, val):
namespace = instance.__class__.__name__ + . + self.field_name
if self.normalize_func:
val = self.normalize_func(val)
if self.validate_func:
assert self.validate_func(val)
old_value = None
if hasattr(instance, self.private_name):
old_value = getattr(instance, self.private_name)
signaler.signal(namespace + :before_update, instance=instance,
new_value=val, old_value=old_value)
setattr(instance, self.private_name, val)
signaler.signal(namespace + :after_update, instance=instance,
new_value=val, old_value=old_value)
class DispatchingModelMeta(decl.DeclarativeMeta):
def __new__(cls, name, bases, attrs):
new_attrs = {}
for key, val in attrs.iteritems():
if isinstance(val, sqla.Column):
log.debug(u{} Column {} {}.format(name, key, val))
if not val.name:
val.name = key
val.key = key
validator_name = 'validate_' + key
normalize_name = 'normalize_' + key
private_name = '_' + key
validator_func = None
normalize_func = None
if validator_name in attrs:
validator_func = attrs[validator_name]
if normalize_name in attrs:
normalize_func = attrs[normalize_name]
new_attrs[private_name] = val
new_attrs[key] = _hooked(validate_func=validator_func,
normalize_func=normalize_func, field_name=key, private_name=private_name)
else:
new_attrs[key] = val
return super(DispatchingModelMeta, cls).__new__(cls, name, bases,
new_attrs)
On Wednesday, November 27, 2013 6:26:58 PM UTC-6, Michael Bayer wrote:
On Nov 27, 2013, at 12:48 PM, Amir Elaguizy aela...@gmail.comjavascript:
wrote:
If I have a model like:
class Test(Base):
value = sqlalchemy.Column(db.String)
and I have a function like:
def on_value_change(model, oldValue):
# Do stuff
I'd like on_value_change called *after* Test.value has been changed
yeah there’s been a bit of discussion about that but it isn’t present in a
simple way. attribute mechanics already take up a lot of overhead and add
lots of complexity so adding an “after” event isn’t something I’m in a
hurry to do.
In the rare occasions that I need this, sometimes what I will do is, just
set the value within the before event, then work with it - I haven’t done
this much so YMMV:
@event.listens_for(A.data, set)
def set(target, value, oldvalue, initiator):
target.__dict__['data'] = value
work_with(target)
return value
the reason __dict__ is used is otherwise you trigger an endless loop with
the event. The reason doing things in this way is dangerous (and why
it’s extra hard to make this work) is that if you pass around “target” to
other parts of your app, which are themselves doing things with attributes,
now you have a nesting pattern going on that can easily enter more endless
recursion types of issues.
usually what I’ll do is just stick to simple things and use a descriptor
like a synonym or a hybrid to set the value, which does what it needs after
the set event. that’s pretty much the normal Python way of doing this sort
of thing in any case.Attribute events are in particular tailored
towards validating / processing the immediate value given, not so much
calling out into the bigger ecosystem of the application, as it is already
occurring within a critical part of the attribute mechanics.
--
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] SQLAlchemy orm attribute post-update hook?
If I have a model like: class Test(Base): value = sqlalchemy.Column(db.String) and I have a function like: def on_value_change(model, oldValue): # Do stuff I'd like on_value_change called *after* Test.value has been changed I know it's possible to do it *before* Test.value has changed and choose to accept or reject the change via an orm event. However, the problem with that is that since the change has not been applied to Test yet I have to pass around the new value in addition to passing around the instance of Test. Is it possible to do 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.
[sqlalchemy] Join textual query to SA query?
Hey guys, Questions about the following code in which I'm trying to take a textqual query and join it to a query builder query. 1) What is the correct way to do the in for the list of ids in the first query? My current way doesn't work and I'm not able to find a real good example 2) How can I Join complicated query 2 with complicated query 1. Essentially join query 2 on sm.StufffModel.id == query1.id Complicated query 1: image_res = db.session.query(id, depth, parent_id, name, s3_key).from_statement( WITH RECURSIVE graph(root_id, id, name, parent_id) AS ( SELECT e.id, e.id, e.name, e.parent_id, 1 as depth FROM entities e UNION ALL SELECT graph.root_id, e.id, e.name, e.parent_id, depth + 1 FROM graph JOIN entities e ON e.parent_id=graph.id ) SELECT g.id,g.depth, g.parent_id, name, ii.s3_key FROM graph g JOIN entity_map em ON g.id=em.left_id JOIN stufff_images si ON em.right_id=si.id JOIN image_instance ii ON si.image_id=ii.image_id WHERE root_id in (:ids) AND ii.width=120 ORDER BY depth ASC LIMIT 1; ).params(ids=,.join([str(i) for i in ids]))) Complicated query 2: query = db.session.query( sm.StufffModel.id, sm.EntityTypesModel.type, sm.StufffModel.hotness, sm.StufffModel.created_at, sm.StufffModel.name) query = query.join(sm.EntityTypesModel) query = query.filter(sm.StufffModel.id.in_(ids)) res = query.all() 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.
[sqlalchemy] Query caching allows two objects representing same underlying data in session?
I'm having this weird problem using the query caching recipes in which two instances of a model representing the same underlying dataset will both get into the session. I know this is happening because I put all of the models in a set() and there are two instances with the same underlying database row id. I was under the impression that the session itself would handle the case that an object coming from the query cache is already in the session, preventing duplication. Is this not the case? 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] Query compilation cost
Awesome Michael it has given a speedup actually outside of caching even, which is great. How do I do the equivelant of this though? compiled = stmt.compile() params = compiled.params # here we return the key as a long string. our key mangler # set up with the region will boil it down to an md5. return .join( [str(compiled)] + [str(params[k]) for k in sorted(params)]) I have the baked query context in the cache, and getting a string statement from that is easy. How do I get the parameters out, to use as unique identifiers? Thanks! Amir On Friday, July 12, 2013 4:53:37 PM UTC-7, Michael Bayer wrote: On Jul 12, 2013, at 7:03 PM, Amir Elaguizy aela...@gmail.comjavascript: wrote: I'd like a way to avoid the cost of repeatedly compiling the same query, especially in the context of relationship caching. Specifically now that I have object caching in place, I have created my own keys where it is possible. However there are still some cases where I cannot. In these cases I'm falling back to the query based key mechanism: stmt = query.with_labels().statement compiled = stmt.compile() params = compiled.params However this is incredibly slow and what I'm finding is that I'm paying the cost for compiling the same query with just the parameters themselves changing. I'd like to create an in memory query cache in which the compiled queries are stored and the parameters are switched out, or something like that. See the recipe at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/BakedQuery which will get you most of the way there, and there are plans at some point to make this into a fully supported feature. -- 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] Query compilation cost
I'd like a way to avoid the cost of repeatedly compiling the same query, especially in the context of relationship caching. Specifically now that I have object caching in place, I have created my own keys where it is possible. However there are still some cases where I cannot. In these cases I'm falling back to the query based key mechanism: stmt = query.with_labels().statement compiled = stmt.compile() params = compiled.params However this is incredibly slow and what I'm finding is that I'm paying the cost for compiling the same query with just the parameters themselves changing. I'd like to create an in memory query cache in which the compiled queries are stored and the parameters are switched out, or something like that. Any thoughts? 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.
[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.
[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] 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
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
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
[sqlalchemy] YCombinator startup seeks SQLAlchemy performance contract engineer
Hey guys, I'm Amir - Founder of Toutpost. We're a YCombinator 2013 company. We're growing very fast but are having performance issues. We have never scaled an application on SQLAlchemy before so we think that our usage of relationships is suboptimal. It's preventing us from implementing a decent caching strategy as well. Technical overview is below my sig. We're all engineers, we built the stack ourselves. You wouldn't be working with non-technical people - just coders working with coders (Two ex-Zynga CTOs running the company). We're looking for help on an immediate basis and can pay fairly well. We really need someone who is available immediately. If interested please e-mail me: a...@toutpost.com Thanks! Amir Elaguizy Founder of Toutpost a...@toutpost.com *Technical overview of the issue:* We're paying massive costs in CPU time building the models, even though our actual time in pgsql is not significant. We've tried out the dogpile caching strategy but because of the number of relationships and polymorphic models in use - caching a single model or relationship doesn't give solid gains. The biggest gains we were able to get were when we pickle'd the entire objects to memcache, but then we have a problem of not being able to use relationships due to them being detached from the session when deserializing from memcache. But in order to get those relationships into memcache, we would essentially have to joinedload in every relationship on the model - which is prohibitively expensive. So we'd like someone who can optimize our usage of SQLAlchemy, which probably means working on our schema some. Then we'd like them to implement a caching strategy as well. The goal would be to have surprisingly fast page loads, right now we're seeing about 1 request/second locally, less on heroku. We'd like to be able to handle 5-8 requests per second per worker thread in order to have a seriously performant site. -- 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] nested inheritance / polymorphic relationships
I didn't intend to use the word reflected in the Programming sense, I meant
in the traditional sense: is represented by.
That sentence was confusing, sorry!
I was saying class B, C, and D are all defined using that same pattern.
They each have their own class.
Amir
On Tuesday, June 4, 2013, Michael Bayer wrote:
On Jun 4, 2013, at 1:55 AM, Amir Elaguizy aelag...@gmail.comjavascript:;
wrote:
Hi there,
I have a tree that looks like this, reflected via polymorphic
inheritance:
what do we mean reflected here, are you reflecting tables from the
database, that is,
http://docs.sqlalchemy.org/en/rel_0_8/core/schema.html#metadata-reflection?
That works great, like:
class BaseModel(db.Model): # Table A in diagram
__tablename__ = entities
id = db.Column(db.BigInteger, primary_key=True, nullable=False,
server_default=func.nextval('guid_seq'))
type_id = db.Column(db.SmallInteger,
db.ForeignKey(EntityTypesModel.id))
__mapper_args__ = {
'polymorphic_identity':'entity',
'polymorphic_on':type_id,
'with_polymorphic':'*'
}
this is why I question the word reflected because I don't see you using
reflection there.
class BrandModel(BaseModel): # Table B, C, D in diagram
__tablename__ = 'brands'
id = db.Column(db.BigInteger, db.ForeignKey(StufffModel.id),
primary_key=True, nullable=False)
name = db.Column(db.String, nullable=False)
__mapper_args__ = {
'polymorphic_identity':ET_BRAND,
}
Im confused by this as well - are you saying that you map the same class
to B, C, and D rows? That would be unusual. It wouldn't work at all on
the persistence side as SQLAlchemy could not know which of B, C, or D you
wish for a particular BrandModel to be persisted towards.
The problem is I need to reflect something more like this:
A
/ | \
B C D
/ \
EF
Where D is not only a polymorphic child of A but also the polymorphic
parents of E F.
It seems like I have to choose, D can either be a polymorphic child or
it can be a parent - it can't be both.
Do I have any options here?
SQLAlchemy can represent inheritance hierarchies of any depth.However,
because you are assigning a single subclass to all of B, C, and D that
might be why there's an issue here, you'd need to assign a distinct
subclass of BaseModel to at least D, and then another subclass of D_Model
to handle E and F.
Preferably, you'd produce distinct classes for all six tables.
--
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/pI62wMDb6M4/unsubscribe?hl=en
.
To unsubscribe from this group and all its topics, send an email to
sqlalchemy+unsubscr...@googlegroups.com javascript:;.
To post to this group, send email to sqlalchemy@googlegroups.comjavascript:;
.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
[sqlalchemy] Re: nested inheritance / polymorphic relationships
Just to tie this off, I ended up flattening the tree so it looks like:
A
/ | \\
B C E F
D is now gone and the functionality it provided is in the children (E
F). I'll probably make the common parts a mixin or something.
Unfortunate but I couldn't spend more time on this particular issue.
On Monday, June 3, 2013 10:55:15 PM UTC-7, Amir Elaguizy wrote:
Hi there,
I have a tree that looks like this, reflected via polymorphic inheritance:
A
/ | \
B C D
That works great, like:
class BaseModel(db.Model): # Table A in diagram
__tablename__ = entities
id = db.Column(db.BigInteger, primary_key=True, nullable=False,
server_default=func.nextval('guid_seq'))
type_id = db.Column(db.SmallInteger,
db.ForeignKey(EntityTypesModel.id))
__mapper_args__ = {
'polymorphic_identity':'entity',
'polymorphic_on':type_id,
'with_polymorphic':'*'
}
class BrandModel(BaseModel): # Table B, C, D in diagram
__tablename__ = 'brands'
id = db.Column(db.BigInteger, db.ForeignKey(StufffModel.id),
primary_key=True, nullable=False)
name = db.Column(db.String, nullable=False)
__mapper_args__ = {
'polymorphic_identity':ET_BRAND,
}
The problem is I need to reflect something more like this:
A
/ | \
B C D
/ \
EF
Where D is not only a polymorphic child of A but also the polymorphic
parents of E F.
It seems like I have to choose, D can either be a polymorphic child or it
can be a parent - it can't be both.
Do I have any options here?
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
