Re: [sqlalchemy] Dynamic model creation for temporary table
Thanks for the answer! silly question, is there a reason standard replication techniques provided by the database can't be used here? they might be a little more heavy-handed to set up than some in-app trick but it'll work more solidly. Yes, there is a reason: there are no any standard tools for desired actions. It is not a replication, it is more likely a collecting data from somewhere (generally, from multiple tables in another DBMS). as always, the main issue is concurrency. can you guarantee that exactly one connection at a time will be performing this operation and that during so, no other connections will have any read or write locks of any kind on this table? otherwise it's not going to work. Yes, I can guarantee this in my environment. to implement the scenario, assuming you've figured out how to make sure a connection has exclusive access, you've already spelled it out - just execute the SQL you've described on the connection. The rotation of tables is not a problem. Of course I can do it by constructing simple SQL statement. I just want to simplify life for a client (client is an app for collecting data) - I want it should pass to me only one model (ModelA from my first post). When it will start collecting process it will retrieve from me a model to save data. When it will finish, it just tell me that the process is completed. It is easy to implement by declaring two identical models in client (for working table and for temporary table), but it seems redundant. as far as build a class or whatnot that sort of depends on app architecture, I'd not bother creating a class if there's just one function and no overarching pattern of class construction (see Stop Writing Classes! for the general idea: http://pyvideo.org/video/880/ ) I agree with you. May be I should revise my construction. But I'm still wondering how to create a copy of given SQLA model class (with another table name). -- 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] [SA0.7] Possible feature request for subqueryload?
Hello. Would it be possible to make these two forms session.query(cls).options( subqueryload(cls.foos), subqueryload(cls.foos, Foo.bar), ) and session.query(cls).options( subqueryload_all(cls.foos, Foo.bar) ) completely equivalent, i.e. subqueryload_all being just a shortcut of the former if the paths have the above pattern (i.e. if one subqueryload is subsumed by another)? My motivation: I have a rather complex function (see below) used in several contexts that returns Query.options for class Subject. The code is written to eliminate duplication, but the current 'suboptimal' behaviour of subqueryload (when compared to subqueryload_all) forces me to implement (and maintain) several independent versions of this function. Or am I missing something that could help me (perhaps rephrase the loads differently)? The function (on class Subject) currently looks like this: @classmethod def fetch_options( cls, prefix=None, alias=None, fetch_name=True, fetch_personal=True, fetch_corporate=True, use_joinedload=True, use_innerjoin=True, fetch_address=False, fetch_tags=False, fetch_all=False ): Fetch subject info. Basic usage (note the '*'!): session.query(Subject).options(*Subject.fetch_options()) Arguments: * prefix - Use if Subject is not queried directly, e.g.: session.query(Partner).options( *Subject.fetch_options(prefix=[Partner.subject]) ) * alias - Specify if necessary, e.g.: subj_alias = aliased(Subject) session.query(subj_alias).options( *Subject.fetch_options(alias=subj_alias) ) * fetch_name - Fetch everything for Subject.display_name(partner). This is the default. * fetch_address - Fetch addresses-related info. Implies fetch_name. * fetch_tags - Fetch tag-related info. Can be used on its own. * fetch_personal - Set to False to supress fetching of any info about persons. * fetch_corporate - Set to False to supress fetching of any info about corporations. * fetch_all - Shortcut that implies all above. * use_joinedload - joinedload() is used by default to fetch all 1:1 relationships. If prefix contains 1:N relationship(s), set this to False and subqueryload() will be used instead. * use_innerjoin - One of fetch_personal or fetch_corporate MUST be True. If only one is set, all joinedload() will be INNER by default. Set this to False to force the use of OUTER. from zfp.model.contact import Contact, ContactPersonal, ContactCorporate from zfp.model.tag import TagSubject if fetch_all: fetch_name = True fetch_personal = True fetch_corporate=True fetch_address=True fetch_tags=True elif fetch_address: fetch_name = True assert fetch_personal or fetch_corporate use_innerjoin = use_innerjoin and not(fetch_personal and fetch_corporate) if use_joinedload: def load_op(*args): return joinedload(*args, innerjoin=use_innerjoin) else: def load_op(*args): return subqueryload(*args) if prefix is None: prefix = [] if alias is None: alias = cls options = [] if fetch_name: options.extend([ subqueryload(*prefix + [alias.contacts]), ]) if fetch_personal: options.extend([ load_op(*prefix + [alias.subject_personal]), subqueryload(*prefix + [alias.contacts, Contact.contact_personal]), ]) if fetch_corporate: options.extend([ load_op(*prefix + [alias.subject_corporate]), subqueryload(*prefix + [alias.contacts, Contact.contact_corporate]), ]) if fetch_address: if fetch_personal: options.extend([ load_op(*prefix + [alias.subject_personal, SubjectPersonal.address]), subqueryload(*prefix + [alias.contacts, Contact.contact_personal, ContactPersonal.contact_address]), subqueryload(*prefix + [alias.contacts, Contact.contact_personal, ContactPersonal.permanent_address]), ]) if fetch_corporate: options.extend([ load_op(*prefix + [alias.subject_corporate, SubjectCorporate.address]), subqueryload(*prefix + [alias.contacts, Contact.contact_corporate, ContactCorporate.address]), ]) if fetch_tags: options.extend([
Re: [sqlalchemy] [SA0.7] Possible feature request for subqueryload?
On Sep 25, 2013, at 10:11 AM, Ladislav Lenart lenart...@volny.cz wrote: Hello. Would it be possible to make these two forms session.query(cls).options( subqueryload(cls.foos), subqueryload(cls.foos, Foo.bar), ) and session.query(cls).options( subqueryload_all(cls.foos, Foo.bar) ) completely equivalent, i.e. subqueryload_all being just a shortcut of the former if the paths have the above pattern (i.e. if one subqueryload is subsumed by another)? unless there's some bug I'm unaware of, those two forms are exactly equivalent. XYZload_all(a.b.c) is a shortcut to XYZload(a), XYZLoad(a.b), XYZLoad(a.b.c).In 0.9 I'm working out a new system backing the loader options that will allow more intuitive patterns, e.g. load(cls).subqueryload(foos).subqueryload(bar). My motivation: I have a rather complex function (see below) used in several contexts that returns Query.options for class Subject. The code is written to eliminate duplication, but the current 'suboptimal' behaviour of subqueryload (when compared to subqueryload_all) forces me to implement (and maintain) several independent versions of this function. Or am I missing something that could help me (perhaps rephrase the loads differently)? im not really sure. the API of this function seems a little complex to me, it's exposing details of the persistence mechanism while at the same time trying to hide part of it. The function (on class Subject) currently looks like this: @classmethod def fetch_options( cls, prefix=None, alias=None, fetch_name=True, fetch_personal=True, fetch_corporate=True, use_joinedload=True, use_innerjoin=True, fetch_address=False, fetch_tags=False, fetch_all=False ): Fetch subject info. Basic usage (note the '*'!): session.query(Subject).options(*Subject.fetch_options()) Arguments: * prefix - Use if Subject is not queried directly, e.g.: session.query(Partner).options( *Subject.fetch_options(prefix=[Partner.subject]) ) * alias - Specify if necessary, e.g.: subj_alias = aliased(Subject) session.query(subj_alias).options( *Subject.fetch_options(alias=subj_alias) ) * fetch_name - Fetch everything for Subject.display_name(partner). This is the default. * fetch_address - Fetch addresses-related info. Implies fetch_name. * fetch_tags - Fetch tag-related info. Can be used on its own. * fetch_personal - Set to False to supress fetching of any info about persons. * fetch_corporate - Set to False to supress fetching of any info about corporations. * fetch_all - Shortcut that implies all above. * use_joinedload - joinedload() is used by default to fetch all 1:1 relationships. If prefix contains 1:N relationship(s), set this to False and subqueryload() will be used instead. * use_innerjoin - One of fetch_personal or fetch_corporate MUST be True. If only one is set, all joinedload() will be INNER by default. Set this to False to force the use of OUTER. from zfp.model.contact import Contact, ContactPersonal, ContactCorporate from zfp.model.tag import TagSubject if fetch_all: fetch_name = True fetch_personal = True fetch_corporate=True fetch_address=True fetch_tags=True elif fetch_address: fetch_name = True assert fetch_personal or fetch_corporate use_innerjoin = use_innerjoin and not(fetch_personal and fetch_corporate) if use_joinedload: def load_op(*args): return joinedload(*args, innerjoin=use_innerjoin) else: def load_op(*args): return subqueryload(*args) if prefix is None: prefix = [] if alias is None: alias = cls options = [] if fetch_name: options.extend([ subqueryload(*prefix + [alias.contacts]), ]) if fetch_personal: options.extend([ load_op(*prefix + [alias.subject_personal]), subqueryload(*prefix + [alias.contacts, Contact.contact_personal]), ]) if fetch_corporate: options.extend([ load_op(*prefix + [alias.subject_corporate]), subqueryload(*prefix + [alias.contacts, Contact.contact_corporate]), ]) if fetch_address: if fetch_personal: options.extend([ load_op(*prefix + [alias.subject_personal, SubjectPersonal.address]),
Re: [sqlalchemy] Dynamic model creation for temporary table
On Sep 25, 2013, at 2:15 AM, Aleksandr Kuznetsov aku.ru...@gmail.com wrote: The rotation of tables is not a problem. Of course I can do it by constructing simple SQL statement. I just want to simplify life for a client (client is an app for collecting data) - I want it should pass to me only one model (ModelA from my first post). When it will start collecting process it will retrieve from me a model to save data. When it will finish, it just tell me that the process is completed. It is easy to implement by declaring two identical models in client (for working table and for temporary table), but it seems redundant. I'm assuming we're talking about a Python process that needs to work with this copy of the table in a detailed way, and that the ORM services are valuable here. you can emulate the pattern we use to create a history table if you see http://docs.sqlalchemy.org/en/rel_0_8/orm/examples.html#versioned-objects . signature.asc Description: Message signed with OpenPGP using GPGMail
[sqlalchemy] How to not losing changes on rollback?
Hi. I'd like to know what's the recommended approach to keep the state of the session and the associated objects when session.flush() fails, in order to being able to fix the cause of the problem and retry the operation. For cases with a single object hierarchy, I think using session.merge() to save a copy of the original object could be sufficient, but in the case of multiple independent objects being added, deleted and modified, I'm not sure. Thanks in advance. J. -- 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: How to not losing changes on rollback?
I'd be interested in this too. FWIW, my approach is this: - flush often - encapsulate complex logic in 'savepoints' , roll back to them - fail everything and start from scratch on other errors -- 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] How to not losing changes on rollback?
On Sep 25, 2013, at 12:23 PM, Julio César Gázquez julio_li...@mebamutual.com.ar wrote: Hi. I'd like to know what's the recommended approach to keep the state of the session and the associated objects when session.flush() fails, in order to being able to fix the cause of the problem and retry the operation. For cases with a single object hierarchy, I think using session.merge() to save a copy of the original object could be sufficient, but in the case of multiple independent objects being added, deleted and modified, I'm not sure. typically the logic is developed such that for those cases where errors on flush() are expected, that bit of logic runs within a savepoint, e.g. with session.begin_nested(), and the state necessary to retry the operation is available outside the scope of that operation. As of 0.8, any objects that were not changed within the begin_nested() block will not have their state expired on rollback. The recommendation is to construct the app such that errors on flush are generally never to be expected in the first place (I'm not able to envision how an application can fix the cause of a structural error, I'd make the code not make that mistake in the first place), save for concurrency related situations in which case the entire operation from the start is re-run, taking into account the new state that was established concurrently. signature.asc Description: Message signed with OpenPGP using GPGMail
[sqlalchemy] timing / profiling object loading ?
is there a way to time the time spent 'loading' an object from the database ? i think there might be a bottleneck in a part of my application that is related to the object instantiation. just to be clear , in this flow: a) `query.all()` b) sqlalchemy compiles query c) sqlalchemy to db d) db processing e) sqlalchemy gets results f) results turned into ORM objects i'm concerned with timing step (f) looking at the events system, there seems to be a way to access perobject events -- but not per query -- 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] timing / profiling object loading ?
I tend to use standard python profiling for that http://stackoverflow.com/questions/1171166/how-can-i-profile-a-sqlalchemy-powered-application/1175677#1175677 On Sep 25, 2013, at 4:44 PM, Jonathan Vanasco jonat...@findmeon.com wrote: is there a way to time the time spent 'loading' an object from the database ? i think there might be a bottleneck in a part of my application that is related to the object instantiation. just to be clear , in this flow: a) `query.all()` b) sqlalchemy compiles query c) sqlalchemy to db d) db processing e) sqlalchemy gets results f) results turned into ORM objects i'm concerned with timing step (f) looking at the events system, there seems to be a way to access perobject events -- but not per query -- 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. signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] timing / profiling object loading ?
very good. thanks. i accidentally left a LIMIT off a query. The DB was optimized to return the results in about .003s ; but it seems to have taken about 2 minutes for sqlalchemy to generate ~1300 objects from the rows. -- 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] timing / profiling object loading ?
that seems *very* slow? On Sep 25, 2013, at 5:05 PM, Jonathan Vanasco jonat...@findmeon.com wrote: very good. thanks. i accidentally left a LIMIT off a query. The DB was optimized to return the results in about .003s ; but it seems to have taken about 2 minutes for sqlalchemy to generate ~1300 objects from the rows. -- 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. signature.asc Description: Message signed with OpenPGP using GPGMail
[sqlalchemy] finding sqlalchemy jobs?
Hi folks, apologies for sounding spammy. I'm wondering if anyone has suggestions on how to find remote work doing SQLAlchemy stuff, been wanting to leave the uncertain world of freelancing and do some actual coding for a while. I found some posts tagging SQLAlchemy on Stack Overflow Careers, but if anyone else has suggestions on how to find job posts for specific Python technologies, I'd love to hear them. Thanks! Iain idun...@xornot.com www.xornot.com -- 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] Re: Duplicating primary key value into another column upon insert?
On Tue, Sep 24, 2013 at 5:40 PM, Nicholas Long nick.studioc...@gmail.comwrote: My mind keeps going back to events but of course there's the limitation to modifying Session state while handling various events. ( http://docs.sqlalchemy.org/en/rel_0_7/orm/events.html#sqlalchemy.orm.events.MapperEvents.after_update ) But what about using the SQL Expression API? *Relevant code snippet:* class MyModel(Base): __tablename__ = MyTable priid = Column(Integer(), primary_key=True) secid = Column(Integer()) @listens_for(MyModel, 'after_insert') def mymodel_after_insert(mapper, connection, target): mytable = MyModel.__table__ priid = target.priid statement = (mytable.update() .where(mytable.c.priid == priid) .values(secid=priid)) connection.execute(statement) Full code for reference: http://paste.pound-python.org/show/WVciGm4jCxgvz84jKrZy/ SQLA echo: http://paste.pound-python.org/show/hSyCGisr0X5eupVdyk3f/ The echo looks sane, though I can't be sure how the SQLAchemy internals are affected by this. Thank you for your input, Michael. While I'm not using the 'two-step' approach this time, there will be future column renames that are far more insidious and may require it, so I would also like to know if the above will work as well. :) -- - Ken Lareau -- 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.
