[sqlalchemy] Obtaining the SQL query for a lazy load programmatically
I observe that the query for a lazy load is generated here: https://bitbucket.org/zzzeek/sqlalchemy/src/e1d1d999c9a688f4c8dbbe885438c63d6ef494c6/lib/sqlalchemy/orm/strategies.py?at=master#cl-551 I would like to run queries through an EXPLAIN QUERY PLAN or an ANALYZE programmatically for many properties. Is there an easy way to achieve that? It looks not since it doesn't like the query is exposed. The only way I'm aware of to get it is to cause a lazy load and have logging enabled (or add an event handler which observes the executed statement), but I'd like to know if there is a way to avoid doing that. Thanks, - Peter -- 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/d/optout.
Re: [sqlalchemy] Serializing sqlalchemy declarative instances with yaml
Well I was hoping to just use yaml since yaml understands when two objects refer to the same underlying object. That means you don't have to write any logic to de-duplicate objects through relationships, etc. Since json doesn't have the notion of referencing, that doesn't seem straightforward there. I was also hoping to just use yaml to avoid writing custom dumping code, since it seems in general like a useful capability. So I may yet try and find the underlying bug and fix it. On 24 October 2014 15:29, Jonathan Vanasco jvana...@gmail.com wrote: Usually for this sort of stuff, I serialize the object's data into a JSON dict ( object columns to JSON dict, object relations to a dict, list of dicts, or reference to another object). ( Custom dump/load is needed to handle Timestamp, Floats, etc). You might be able to iterate over the data in YAML and not require custom encoding/decoding. When I need to treat the json data as objects, I'll load them into a custom dict class that will treat attributes as keys. The downside of this is that you don't have all the SqlAlchemy relational stuff or any ancillary methods (though they can be bridged in with more work). The benefit though is that you can get a nearly 1:1 parity between the core needs without much more work. When using a read only context, you can flip between SqlAlchemy objects and dicts. If you need to use the SqlAlchemy model itself, you could load the column/relationship data into it manually. -- 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/d/optout. -- 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/d/optout.
Re: [sqlalchemy] Serializing sqlalchemy declarative instances with yaml
The oddity is that calling `__reduce_ex__` on the instance is fine, but on the class it is not. When serialising a declarative class it finds itself serialising the class type, which fails. This actually fails for the `object`, too (see below). So I think what's happening is that serialisation fails because `_sa_instance_state` (somewhere inside it) contains a class. This is probably a yaml bug, then. In [1]: object().__reduce_ex__(2) Out[1]: (function copy_reg.__newobj__, (object,), None, None, None) In [2]: object.__reduce_ex__(2) --- TypeError Traceback (most recent call last) ipython-input-1-eebec0cadfee in module() 1 object.__reduce_ex__(2) /usr/lib/python2.7/copy_reg.pyc in _reduce_ex(self, proto) 68 else: 69 if base is self.__class__: --- 70 raise TypeError, can't pickle %s objects % base.__name__ 71 state = base(self) 72 args = (self.__class__, base, state) TypeError: can't pickle int objects On 24 October 2014 17:55, Jonathan Vanasco jvana...@gmail.com wrote: On Friday, October 24, 2014 10:39:43 AM UTC-4, Peter Waller wrote: I was also hoping to just use yaml to avoid writing custom dumping code, since it seems in general like a useful capability. So I may yet try and find the underlying bug and fix it. It might not be a bug, and the effect of an implementation feature of SqlAlchemy. I tried (naively) playing around with your example, and thought back to how SqlAlchemy accomplishes much of it's magic by creating custom comparators (and other private methods) on the classes and columns. Playing around with it, the problem seems to be with the SqlAlchemy object's __reduce_ex__ method. If you simply use __reduce__ in yaml, it works. I couldn't figure out what Foo inherits __reduce_ex__ from , or if any of the columns have it. -- 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/d/optout. -- 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/d/optout.
[sqlalchemy] A complicated composite secondary join relationship() property involving four tables
Hi All, I am wondering if it is possible to describe in sqlalchemy the following relationship with the new features introduced since 0.9.2. I've been reading this document and it looks close to what I'm trying to achieve: http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#composite-secondary-joins As does this previous mailing list post: https://groups.google.com/d/topic/sqlalchemy/ov-mYWA7XAM/discussion There is a pre-existing structure to deal with. I would rather not change the structure if it can be avoided, but may have to resort to doing so. Here are the tables: TableName (column, ...) PersonAction (PersonID, ActionID) PersonUnit (PersonID, UnitID) DepartmentUnit (UnitID, DepartmentID) ActionMeaning (DepartmentID, ActionID) It would be nice to be able to go from an ActionMeaning to a PersonAction and back again. To do that, a conversion between DepartmentName and DepartmentID needs to happen. Here are a few of the relationships: One PersonAction: one ActionMeaning One ActionMeaning: many PersonAction One PersonUnit many PersonAction One PersonUnit one DepartmentUnit One DepartmentID: many UnitID (therefore one ActionMeaning: many DepartmentUnit) One ActionMeaning: Many PersonUnit (via DepartmentUnit) I'm using the ORM and I would like to be able to write a property on the PersonAction which allows me to efficiently get to the ActionMeaning (preferably joinedload'ed), and likewise I would like to be able to go from an ActionMeaning to all of the related PersonActions. Is this possible? Or are there alternative tasteful strategies for dealing with this tricky structure? Life would be much easier if I had the DepartmentID on the PersonUnit... Keywords to help anyone searching in the future: Composite relationship between multiple tables Joining multiple tables in a relationship relationship secondary join primaryjoin secondaryjoin Thanks, - Peter -- 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/d/optout.
Re: [sqlalchemy] A complicated composite secondary join relationship() property involving four tables
Outstanding! My puny mind was just beginning to tickle this documentation and was starting to get the idea that this is the right direction. Good to know I'm on track. Is it also possible that having a class which maps to a join* will help? Then rather than having PersonAction and PersonUnit I would just have Person. Would that simplify the relationships? I'll have a play around with this and see where I get. Thanks, - Peter * http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#maptojoin On 20 October 2014 18:26, Michael Bayer mike...@zzzcomputing.com wrote: On Oct 20, 2014, at 1:14 PM, Peter Waller pe...@scraperwiki.com wrote: Hi All, I am wondering if it is possible to describe in sqlalchemy the following relationship with the new features introduced since 0.9.2. I've been reading this document and it looks close to what I'm trying to achieve: http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#composite-secondary-joins As does this previous mailing list post: https://groups.google.com/d/topic/sqlalchemy/ov-mYWA7XAM/discussion There is a pre-existing structure to deal with. I would rather not change the structure if it can be avoided, but may have to resort to doing so. Here are the tables: TableName (column, ...) PersonAction (PersonID, ActionID) PersonUnit (PersonID, UnitID) DepartmentUnit (UnitID, DepartmentID) ActionMeaning (DepartmentID, ActionID) So as far as the patterns at http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html, this one qualifies for http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#relationship-to-non-primary-mapper. The reason is because PersonAction has a direct FK to ActionMeaning (ActionID) as well as towards elements on what would be “secondary” here, PersonUnit/DepartmentUnit. The criteria for that is: we seek to join from A to B, making use of any number of C, D, etc. in between, however there are also join conditions between A and B directly.” So you map ActionMeaning using a non-primary mapper to a join of ActionMeaning, DepartmentUnit, and PersonUnit, then PersonAction can relate to this mapper directly. -- 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/d/optout. -- 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/d/optout.
Re: [sqlalchemy] A complicated composite secondary join relationship() property involving four tables
This is how far I got, before running into a wall:
j = join(ActionMeaning, DepartmentUnit).join(PersonUnit)
disambiguation = {
UnitID: [j.c.PersonUnit_UnitID, j.c.DepartmentUnit_UnitID],
DepartmentID: [j.c.DepartmentUnit_DepartmentID,
j.c.ActionMeaning_DepartmentID],
}
ActionMeaningToPerson = mapper(ActionMeaning, j, non_primary=True,
properties=disambiguation)
PersonAction.meaning = relationship(ActionMeaningToPerson)
ActionMeaning has a DepartmentID -
ForeignKey(DepartmentUnit.DepartmentID)
PersonUnit has UnitID - ForeignKey(DepartmentUnit.UnitID)
PersonAction has PersonID - ForeignKey(PersonUnit.PersonID)
This actually does something when I look at PersonAction.meaning, except
unfortunately I cannot yet figure out where to express the constraint
ActionMeaning.ActionID == PersonAction.ActionID, so I get back all
ActionMeanings with a matching DepartmentID, and ActionID of various sorts.
I've tried expressing it as the relationship(primaryjoin=), but this gave
me:
ArgumentError: Could not locate any relevant foreign key columns for
primary join condition 'ActionMeaning.ActionID =
PersonAction.ActionID' on relationship PersonAction.meaning. Ensure
that referencing columns are associated with a ForeignKey or
ForeignKeyConstraint, or are annotated in the join condition with the
foreign() annotation.
Any hints?
Thanks again!
On 20 October 2014 18:26, Michael Bayer mike...@zzzcomputing.com wrote:
On Oct 20, 2014, at 1:14 PM, Peter Waller pe...@scraperwiki.com wrote:
Hi All,
I am wondering if it is possible to describe in sqlalchemy the
following relationship with the new features introduced since 0.9.2.
I've been reading this document and it looks close to what I'm trying
to achieve:
http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#composite-secondary-joins
As does this previous mailing list post:
https://groups.google.com/d/topic/sqlalchemy/ov-mYWA7XAM/discussion
There is a pre-existing structure to deal with. I would rather not
change the structure if it can be avoided, but may have to resort to
doing so. Here are the tables:
TableName (column, ...)
PersonAction (PersonID, ActionID)
PersonUnit (PersonID, UnitID)
DepartmentUnit (UnitID, DepartmentID)
ActionMeaning (DepartmentID, ActionID)
So as far as the patterns at
http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html, this one
qualifies for
http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#relationship-to-non-primary-mapper.
The reason is because PersonAction has a direct FK to ActionMeaning
(ActionID) as well as towards elements on what would be “secondary” here,
PersonUnit/DepartmentUnit. The criteria for that is: we seek to join from
A to B, making use of any number of C, D, etc. in between, however there
are also join conditions between A and B directly.”
So you map ActionMeaning using a non-primary mapper to a join of
ActionMeaning, DepartmentUnit, and PersonUnit, then PersonAction can relate
to this mapper directly.
--
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/d/optout.
--
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/d/optout.
[sqlalchemy] eager/joined loading a whole graph
Hi All, I have also created a post for this question on StackOverflow: http://stackoverflow.com/questions/3863508/joinedload-eager-loading-whole-sub-graphs-in-sqlalchemy Let's say I have a Task object which can be dependent on other Tasks. Is there a way to sensibly eager/joinedload all of a given set of task's subtasks? Example code: class Task(DeclarativeBase): __tablename__ = 'task' task_id = Column(Integer, primary_key=True) name = Column(String, unique=True) def add_dependencies(self, *tasks): for task in tasks: TaskDependency(dependent=self, dependency=task) return self @property def dependencies(self): return [x.dependency for x in self.dependency_edges] @dependencies.setter def dependencies(self, what): Note: adds dependencies, doesn't remove them self.add_dependencies(*what) @property def dependents(self): return [x.dependent for x in self.dependent_edges] class TaskDependency(DeclarativeBase): __tablename__ = 'task_dependency' dependent_id = Column(Integer, ForeignKey(Task.task_id), primary_key=True) dependency_id = Column(Integer, ForeignKey(Task.task_id), primary_key=True) dependent = relationship(Task, primaryjoin=dependent_id == Task.task_id, backref='dependent_edges') dependency = relationship(Task, primaryjoin=dependency_id == Task.task_id, backref='dependency_edges') def example_task_maker(): make_cheese = Task( name=MAKE_CHEESE, dependencies=[ Task(name=MILK_COWS, dependencies=[ Task(name=BUY_COWS) ]), ] ) def load_task() # How to eagerly load the whole task tree here? DBSession.query(Task).filter(name=MAKE_CHEESE).all() Thanks in advance, - Peter -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: eager/joined loading a whole graph
Ah. To answer my own question, what I was actually after was an adjacency list, of which there are examples to be found here, which I missed: http://www.sqlalchemy.org/docs/05/mappers.html#adjacency-list-relationships I believe this will do what I was after. Sorry for the noise, and thanks anyway. - Peter On 5 October 2010 14:39, Peter Waller peter.wal...@cern.ch wrote: Hi All, I have also created a post for this question on StackOverflow: http://stackoverflow.com/questions/3863508/joinedload-eager-loading-whole-sub-graphs-in-sqlalchemy Let's say I have a Task object which can be dependent on other Tasks. Is there a way to sensibly eager/joinedload all of a given set of task's subtasks? Example code: class Task(DeclarativeBase): __tablename__ = 'task' task_id = Column(Integer, primary_key=True) name = Column(String, unique=True) def add_dependencies(self, *tasks): for task in tasks: TaskDependency(dependent=self, dependency=task) return self @property def dependencies(self): return [x.dependency for x in self.dependency_edges] @dependencies.setter def dependencies(self, what): Note: adds dependencies, doesn't remove them self.add_dependencies(*what) @property def dependents(self): return [x.dependent for x in self.dependent_edges] class TaskDependency(DeclarativeBase): __tablename__ = 'task_dependency' dependent_id = Column(Integer, ForeignKey(Task.task_id), primary_key=True) dependency_id = Column(Integer, ForeignKey(Task.task_id), primary_key=True) dependent = relationship(Task, primaryjoin=dependent_id == Task.task_id, backref='dependent_edges') dependency = relationship(Task, primaryjoin=dependency_id == Task.task_id, backref='dependency_edges') def example_task_maker(): make_cheese = Task( name=MAKE_CHEESE, dependencies=[ Task(name=MILK_COWS, dependencies=[ Task(name=BUY_COWS) ]), ] ) def load_task() # How to eagerly load the whole task tree here? DBSession.query(Task).filter(name=MAKE_CHEESE).all() Thanks in advance, - Peter -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re-using labels
Let's say I write something like: fixed_column = cast(my_column.op(/)(2**32), Integer).label(fixed_column) print select([fixed_column]).where(fixed_column 100) I get: SELECT CAST(my_column / :my_columnn_1 AS INTEGER) AS fixed_column FROM table WHERE CAST(my_column / :my_column_1 AS INTEGER) :param_1 Is there any reason fixed_column is not re-used in the where statement? Is it possible to make this happen? It would make my statements much more readable. Thanks, - Peter -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
