Re: [sqlalchemy] SQLAlchemy best practise
I think the session about sessions do that explanation: http://www.sqlalchemy.org/docs/orm/session.html 2011/6/6 Liju lij...@gmail.com I'm new to SQLAlchemy and loving it. But reading all documentation online makes me wonder if there are any best practice documentation for sqlalchemy out there ? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Why we don't have a contains_eager_all?
Hi,
Just a easy question, why we don't have a contains_eager_all like we do with
joinedload?
By the way, I don't exactly understand why we shouldn't use the _all version
always.
Example (from
http://www.sqlalchemy.org/docs/orm/loading.html?highlight=contains_eager#contains-eager
):
query(User).options(contains_eager('orders', 'items'))
With this we are only loading the items of the orders objects. But to access
it, I must pass through the orders relation anyway, but without another:
contains_eager('orders')
It would make a new select, so I didn't get whats the use case of eager
loading the deepest children without loading all the way to it too.
(or maybe I'm wrong and it's behavior is like what I said and I'm missing
something in my tests)
Thanks in advance,
--
Bonus question: Wouldnt be nice if the joinedload (and all the variants)
could be used in the Query object? Ex:
query(User).joinedload('orders').all() is much more readable than:
query(User).options(joinedload('orders').all()
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Why we don't have a contains_eager_all?
Hmm, nice, I'm still with the 0.6.7, so that's why I missed it.
Thanks again for the superfast-effective answer.
2011/5/26 Michael Bayer mike...@zzzcomputing.com
On May 26, 2011, at 11:02 AM, Israel Ben Guilherme Fonseca wrote:
Hi,
Just a easy question, why we don't have a contains_eager_all like we do
with joinedload?
contains_eager() should always act in an all context since there's little
use otherwise. This was fixed in 0.7 and is ticket #2032:
http://www.sqlalchemy.org/trac/ticket/2032
07Migration is updated.
By the way, I don't exactly understand why we shouldn't use the _all
version always.
You might want to load a list of A, each has a collection of B. But you
don't want the B's by default. But, if you do in fact load a particular
collection of B, you'd like them to eagerly load their C.
We're falling victim a bit to favoring a rare use case over a common one
here, but that's how things have worked out and its not really worth
changing around at this point.
Bonus question: Wouldnt be nice if the joinedload (and all the variants)
could be used in the Query object? Ex:
query(User).joinedload('orders').all() is much more readable than:
query(User).options(joinedload('orders').all()
Again this is how things have worked out over the years, but also options()
does have a use in that you can also make your own MapperOption objects,
without any need to subclass Query. So there is some consistency in that
options() allows external functions to enter in and modify the state of
Query, without Query having any awareness of them.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Subselect that references the outer select
Hi, I'm trying to construct a query that have a subquery, and that subquery references the outer query attribute. It's almost working actually: Intended select: select * from curso c join matricula m on c.id_curso = m.id_curso where m.id_aluno = 1 and m.data = (select max(sub.data) from matricula sub where sub.id_aluno = m.id_aluno) Query: alias = aliased(Matricula) subquery = session.query(func.max(alias.data)).filter(alias.id_curso == Matricula.id_curso).subquery() lista = session.query(Curso) \ .join(Matricula) \ .filter(Matricula.id_aluno == 1) \ .filter(Matricula.data == subquery) \ .all() Result select: SELECT curso.id_curso AS curso_id_curso, curso.area AS curso_area, curso.carga_horaria AS curso_carga_horaria, curso.ementa AS curso_ementa, curso.nome AS curso_nome FROM curso JOIN matricula ON curso.id_curso = matricula.id_curso WHERE matricula.id_aluno = 1 AND matricula.data = (SELECT max(matricula_1.data) AS max_1 FROM matricula AS matricula_1, matricula WHERE matricula.id_curso = matricula_1.id_curso) The only problem here is: the subselect is using two references to matricula in the from clause: FROM matricula AS matricula_1, matricula this guy shouldn't exist. I just want the matricula from the outer select. Fixing that, and i'm done. But how can I do that? Thanks in advance -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Subselect that references the outer select
I did a bit more of digging in the docs and found the 'select_from' method. I thought that it would force the FROM statement to use ONLY what I pass as argument. But it didn't. session.query(func.max(alias.data)).select_from(alias).filter(alias.id_curso == Matricula.id_curso).subquery() It stills give-me two matricula in the from clause. The == Matricula.id_curso, is still enforcing the another matricula in the query. 2011/5/26 Israel Ben Guilherme Fonseca israel@gmail.com Hi, I'm trying to construct a query that have a subquery, and that subquery references the outer query attribute. It's almost working actually: Intended select: select * from curso c join matricula m on c.id_curso = m.id_curso where m.id_aluno = 1 and m.data = (select max(sub.data) from matricula sub where sub.id_aluno = m.id_aluno) Query: alias = aliased(Matricula) subquery = session.query(func.max(alias.data)).filter(alias.id_curso == Matricula.id_curso).subquery() lista = session.query(Curso) \ .join(Matricula) \ .filter(Matricula.id_aluno == 1) \ .filter(Matricula.data == subquery) \ .all() Result select: SELECT curso.id_curso AS curso_id_curso, curso.area AS curso_area, curso.carga_horaria AS curso_carga_horaria, curso.ementa AS curso_ementa, curso.nome AS curso_nome FROM curso JOIN matricula ON curso.id_curso = matricula.id_curso WHERE matricula.id_aluno = 1 AND matricula.data = (SELECT max(matricula_1.data) AS max_1 FROM matricula AS matricula_1, matricula WHERE matricula.id_curso = matricula_1.id_curso) The only problem here is: the subselect is using two references to matricula in the from clause: FROM matricula AS matricula_1, matricula this guy shouldn't exist. I just want the matricula from the outer select. Fixing that, and i'm done. But how can I do that? Thanks in advance -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: Subselect that references the outer select
I saw that, but I'm not using a subquery in the from clause. Maybe I wasn't clear enough. Example: outeruser = aliased(User) inneruser = aliased(User) innerselect = session.query(inneruser.id).filter(inneruser.id == outeruser.id).subquery() At this point I already have a problem, the generated from clause is something like: from user as user_2, user as user_1 I didnt want the other user_2, because the filter statement is actually referencing the user of the outerselect: outerselect = session.query(outeruser).filter(outeruser.id == innerselect) I expected that the innerselect referenced the id of the outer select. (That example was really a useless scenario, I'll try to make a better one later) 2011/5/26 Michael Bayer mike...@zzzcomputing.com take a look at http://www.sqlalchemy.org/docs/orm/tutorial.html#using-subqueries and note that when a subquery is used as a FROM clause, it acts like a table. Use the .c. attribute. On May 26, 2011, at 3:34 PM, Israel Ben Guilherme Fonseca wrote: I did a bit more of digging in the docs and found the 'select_from' method. I thought that it would force the FROM statement to use ONLY what I pass as argument. But it didn't. session.query(func.max(alias.data)).select_from(alias).filter(alias.id_curso == Matricula.id_curso).subquery() It stills give-me two matricula in the from clause. The == Matricula.id_curso, is still enforcing the another matricula in the query. 2011/5/26 Israel Ben Guilherme Fonseca israel@gmail.com Hi, I'm trying to construct a query that have a subquery, and that subquery references the outer query attribute. It's almost working actually: Intended select: select * from curso c join matricula m on c.id_curso = m.id_curso where m.id_aluno = 1 and m.data = (select max(sub.data) from matricula sub where sub.id_aluno = m.id_aluno) Query: alias = aliased(Matricula) subquery = session.query(func.max(alias.data)).filter(alias.id_curso == Matricula.id_curso).subquery() lista = session.query(Curso) \ .join(Matricula) \ .filter(Matricula.id_aluno == 1) \ .filter(Matricula.data == subquery) \ .all() Result select: SELECT curso.id_curso AS curso_id_curso, curso.area AS curso_area, curso.carga_horaria AS curso_carga_horaria, curso.ementa AS curso_ementa, curso.nome AS curso_nome FROM curso JOIN matricula ON curso.id_curso = matricula.id_curso WHERE matricula.id_aluno = 1 AND matricula.data = (SELECT max(matricula_1.data) AS max_1 FROM matricula AS matricula_1, matricula WHERE matricula.id_curso = matricula_1.id_curso) The only problem here is: the subselect is using two references to matricula in the from clause: FROM matricula AS matricula_1, matricula this guy shouldn't exist. I just want the matricula from the outer select. Fixing that, and i'm done. But how can I do that? Thanks in advance -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: Subselect that references the outer select
Sweet, it's working. :) Now let's wait for the 0.8. 2011/5/26 Michael Bayer mike...@zzzcomputing.com On May 26, 2011, at 5:03 PM, Israel Ben Guilherme Fonseca wrote: I saw that, but I'm not using a subquery in the from clause. Maybe I wasn't clear enough. Example: outeruser = aliased(User) inneruser = aliased(User) innerselect = session.query(inneruser.id).filter(inneruser.id == outeruser.id).subquery() At this point I already have a problem, the generated from clause is something like: from user as user_2, user as user_1 I didnt want the other user_2, because the filter statement is actually referencing the user of the outerselect: outerselect = session.query(outeruser).filter(outeruser.id == innerselect) I expected that the innerselect referenced the id of the outer select. oh then you're looking for correlation: innerselect = session.query(inneruser.id).filter(inneruser.id == outeruser.id).correlate(outeruser) outerselect = session.query(outeruser).filter(outeruser.id == innerselect.as_scalar()) for some reason the Query is disabling auto-correlation upon subquery(), .statement or as_scalar(). Sort of wish I had noticed that before releasing 0.7. Will add a ticket to possibly change that default for 0.8, see you in a year. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Declarative: Joined Inheritance + Two Tables To One Object
I have the following: class Person(Base): __tablename__ = 'pessoa' id = Column(id_person), Integer, primary_key = True) name = Column(String) class Teacher(Person): __tablename__ = 'teacher' id = Column(id_teacher, Integer, ForeignKey(Person.id), primary_key=True) info = Column(String) class Salary(Base): __tablename__ = 'salary' id = Column(String) value = Column(Numeric) That's ok, but I wanted to merge the Salary and Teacher objects following the guide: http://www.sqlalchemy.org/docs/06/orm/mapper_config.html#mapping-a-class-against-multiple-tables Am I forced to map the Teacher and Salary in the non-declarative mode to achieve this? It's nice to keep things declarative, because it automatically create the __init__ method with the columns as parameters. I have another classes that have relationships to those classes (and they are declarative too), and things get nasty when I mix declarative with the standard way. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Declarative: Joined Inheritance + Two Tables To One Object
D'oh, I figured myself, It was very easy. I just followed the guide again and it worked. One question though. Let's use the guide example for this: Let's say that AddressUser inherits(joined inheritance) from another class, and that class have a id with the same name (user_id), I get a warning like this: Implicitly combining column address.user_id with column superclass.user_id under attribute 'user_id'. This usage will be prohibited in 0.7. Please configure one or more attributes for these same-named columns explicitly. All ids are indeed, the same id so it seems ok for me. Why is it being prohibited? Issued with advanced cases, or just to avoid hard-to-debug errors? 2011/5/23 Israel Ben Guilherme Fonseca israel@gmail.com I have the following: class Person(Base): __tablename__ = 'pessoa' id = Column(id_person), Integer, primary_key = True) name = Column(String) class Teacher(Person): __tablename__ = 'teacher' id = Column(id_teacher, Integer, ForeignKey(Person.id), primary_key=True) info = Column(String) class Salary(Base): __tablename__ = 'salary' id = Column(String) value = Column(Numeric) That's ok, but I wanted to merge the Salary and Teacher objects following the guide: http://www.sqlalchemy.org/docs/06/orm/mapper_config.html#mapping-a-class-against-multiple-tables Am I forced to map the Teacher and Salary in the non-declarative mode to achieve this? It's nice to keep things declarative, because it automatically create the __init__ method with the columns as parameters. I have another classes that have relationships to those classes (and they are declarative too), and things get nasty when I mix declarative with the standard way. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] SQLAlchemy 0.7.0 Released
May the force be with SQLAlchemy. :) 2011/5/20 Michael Bayer mike...@zzzcomputing.com The SQLAlchemy project is pleased to announce version 0.7.0 of SQLAlchemy, the first production release within the 0.7.0 series. 0.7 represents the past year's worth of development, streamlining APIs, adding new features, solidifying the core and improving performance. Key highlights of version 0.7 include: - New event system applies a consistent and flexible approach to the task of extending SQLAlchemy, both within the core and the ORM. The previous system of ad-hoc extension and listener classes is replaced by a single function event.listen() which can apply listeners to a wide variety of hooks. - A new extension allows easy creation of hybrid attributes, attributes that provide Python expression behavior at the instance level and SQL expression behavior at the declarative (class) level. - A new system of building so-called mutable attributes, column-mapped values which can change their value in-place. Detection of changes now uses change events and solves the previous issue of full unit-of-work scans for such attributes. - Major speed improvements, including batching of INSERT statements when possible, greatly reduced codepaths for many key operations. - C extensions, battle tested after a year of use in 0.6, now build by default on install for CPython platforms. - Pypy is fully supported. - Dialect support for Psycopg2 on Python 3, Drizzle, pymysql added. - Documentation and example updates, including a modernized, declarative version of polymorphic associations. - Many, many core and ORM behavioral improvements. See the almost-full list at 07Migration. Thanks to everyone who's contributed via code, bug reports, infrastructure support, production testing. SQLAlchemy 0.7.0 links: Download: http://www.sqlalchemy.org/download.html Whats New + Migration: http://www.sqlalchemy.org/trac/wiki/07Migration Changelog: http://www.sqlalchemy.org/changelog/CHANGES_0_7_0 -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Queries issued with 'passive_deletes'
I didn't get why issuing selects for the children objects when passive_deletes=False. Wouldn't be better just issue direct deletes, and maybe using subselects in the where clause of these deletes (for nested associations) when approriate? It would solve the overhead problem of the selecting large collections, and it would mimic the ON DELETE CASCADE that is expected to exist when using passive_delete=True for databases that don't support this feature. Thanks in advance for the explanation, Israel -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Queries issued with 'passive_deletes'
Well, now it does make sense. :) Thanks for the explanation. 2011/5/9 Michael Bayer mike...@zzzcomputing.com On May 9, 2011, at 7:57 AM, Israel Ben Guilherme Fonseca wrote: I didn't get why issuing selects for the children objects when passive_deletes=False. Wouldn't be better just issue direct deletes, and maybe using subselects in the where clause of these deletes (for nested associations) when approriate? It would solve the overhead problem of the selecting large collections, and it would mimic the ON DELETE CASCADE that is expected to exist when using passive_delete=True for databases that don't support this feature. Thanks in advance for the explanation, ON DELETE CASCADE is provided by all databases that SQLAlchemy supports - see referential integrity in this chart: http://en.wikipedia.org/wiki/Comparison_of_relational_database_management_systems#Fundamental_features the only exception being, MySQL MyISAM. SQLite added foreign keys some releases ago (they are optional but can be enabled). Given that ON DELETE CASCADE is already provided by all databases and should be used when lots of cascading deletes are needed, the ratio of usefulness to effort, which would be significant in that it involves a significantly more complex approach within the unit of work internals as well as a lot of new tests, doesn't place a feature like this in high priority. It would not be possible for this behavior to be used in all cases, it would only be an optimizing case when its possible.Consider the case where cycles exist - parent-child-subchild-subsubchild, and suppose some rows in child reference subsubchild. The UOW detects the potential for cycles based on the graph of mappings, and when it has access to all the individual rows (like the database does when ON DELETE CASCADE works) breaks cycles into individual groups so that rows are deleted in the proper order. A query like DELETE FROM subsubchild WHERE parent_id in (SELECT id from subchild where parent_id in (SELECT id from child where parent_id=x)) otherwise would fail. The current behavior also has the advantage that objects presently in the Session, but without their collection-based relationships loaded and linking them together in memory, are appropriately updated state-wise, as their collection membership is determined before being marked cascaded members as deleted after a flush.While passive_deletes=True turns this off, some applications with passive_deletes=False may be relying upon this. Changing the cascade behavior to not be aware of individual rows when cycles don't exist mean that the state management of individual objects in a session will change based on mappings. An application someday removes a relationship that was linking subsubchild to child, and suddenly the Session begins to not mark subsubchild objects as deleted during a cascade, instead waiting until commit() is called and all attributes are expired.This is a subtle side effect arising from seemingly unrelated mapping adjustments - this makes it tougher for us to make this new optimization a default behavior.Whereas the difference in behavior between passive_deletes=True|False is much easier to understand and anticipate. So there's potential for surprises, new complexity, many more tests needed, feature is only an optimization, and will probably have to remain optional in all cases, all of which is redundant versus pretty much every database's own ability to do so more efficiently and predictably via ON DELETE CASCADE. And you can even use query.delete() if you really need to delete lots of things quickly and you don't have CASCADE immediately available. This also might be a good addition for the FAQ which is currently being cleaned up. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Two Objects, One Table and the inverse
Hi, Is it possible to break a table in two Python objects? Ex: Python objects: class Person: name = Column(String) class Address: street = Column(String) city = Column(String) Table: table Person name varchar city varchar street varchar How can i make the connection between the two? And is it possible to make the inverse? (table Person and Address to a single Python object Person) Thks in advance, -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Two Objects, One Table and the inverse
Thks for the insight Michael. With the @property solution, its not possible to make queries like session.query(Person).filter(Person.address.street=Something) right? Cascade Saving/Updating in that instance should be a problem too. I tested the composite solution, it's nice but the queries get a bit complex to do when we want to restrict fields of the composite Object (because the need of comparator and when we want only one field of the composite object in the restriction not all of them). Not very straight forward. What do you say about it? I really think that in real-world situations i would never want to make table become two objects, it's much more easy(implementation and understanding) to use just one object. I'm doing a paper about ORM solutions and that's why i'm asking about this specific feature. I'll give a look in the inverse solution later. Thanks again! 2011/4/5 Michael Bayer mike...@zzzcomputing.com On Apr 5, 2011, at 9:06 AM, Israel Ben Guilherme Fonseca wrote: Hi, Is it possible to break a table in two Python objects? Ex: Python objects: class Person: name = Column(String) class Address: street = Column(String) city = Column(String) Table: table Person name varchar city varchar street varchar How can i make the connection between the two? You could map Person and Address both to the table, excluding/including columns from each using mapper include_properties/exclude_properties (see the docs), however relationship() would not be able to handle the join condition of this table, which is table.id==table.id. So in the practical sense, you'd just do this: class Person(object): @property def address(self): return Address(self.street, self.city) or similarly use a composite (search the mapping documentation for composite) to achieve the Address class composed of that subset of columns.If you choose composite I'd note 0.7 improves upon the scalability of the composite feature significantly (for small datasets in memory it doesn't matter though). And is it possible to make the inverse? (table Person and Address to a single Python object Person) you can map to a join of the two tables as described at http://www.sqlalchemy.org/docs/orm/mapper_config.html#mapping-a-class-against-multiple-tables . -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Session: close(), remove(), expire_all() and expunge_all()
Thank you very much for the answers. No doubts for now. 2011/3/18 Michael Bayer mike...@zzzcomputing.com On Mar 18, 2011, at 2:55 PM, Israel Ben Guilherme Fonseca wrote: Hi everybody, I'm new with SQLAlchemy and I'm trying to understand better the Session object. First of all, why the expire_all() exists? The purpose is to remove any database-loaded state from all current objects so that the next access of any attribute, or any query execution, will retrieve new state, freshening those objects which are still referenced outside of the session with the most recent available state. It is called automatically upon commit() or rollback() assuming an autocommit=False session, so that when the transaction, and its isolated environment, come to an end, subsequent accesses of those objects will acquire new data from whatever other transactions were committed subsequent to the previous transaction. expire_all() itself is useful when: - the session is used in autocommit=True mode, and new changes from other transactions are desired. - against a database that does not support transactions, or perhaps within a weakly isolated transaction, again to load changes from other transactions or connections. - when SQL statements have been executed against the current transaction using execute() which may have changed significant portions of loaded state on the database. Shouldn't it be always better to expunge_all() instead? If it is expired, at the use of the instance another query would be issued to reattach it anyway, so why keep useless instances in the identity map (maybe i'm wrong about this fact, i dont know exactly how the identity map works). They're not useless at all if you are performing operations upon them which span the scope of multiple transactions, or have any of the above use cases, and don't wish to re-establish a full graph of objects in memory. In-memory objects are essentially proxy objects to an underlying database transaction. The Session mediates this relationship. Second, when we should call the close() method? when you wish to release the transactional and connection pool resources of the Session and remove all objects. I dont think if i get it at all. Let's say that I have the following DAO: PersonDAO: def insert(self, person): session = Session() session.add(person) session.commit() // why the commit starts a new transaction? should'n it only start again the next database access? the commit ends the current transaction, and starts a new transaction from the Session's point of view. However, no SQL or transactional directives are emitted until the first SQL statement is emitted via the Session (either via execute(), query() iteration, or flush). So there is no new database transaction if you commit then cease to use that Session further. session.close() // is it necessary? Not strictly although it removes any state left over in the session, thereby establishing any remaining objects as detached. This is desirable since you might want to ensure that subsequent operations on those objects don't re-emit new SQL. Is the a Session instance directly associated to a Connection in the pool? Yes. This is documented here: http://www.sqlalchemy.org/docs/orm/session.html#what-does-the-session-do With the default pool size of 5, after 5 call of the insert method, the connection_overflow would be used? No. A single session uses one Connection per Engine at a time. By default, it keeps one connection open until rollback(), commit(), or close() is called. http://www.sqlalchemy.org/docs/orm/session.html#managing-transactionsillustrates this. What's the time to a Session expire? it expires things when commit or rollback is called.This is also in the above docs. And just to finish. Why do we use remove() for the scoped_session instead of the close()? scoped_session offers all of the methods of the underlying Session via a proxy pattern, so you can call close() on scoped_session, which calls close() on the actual session, or you can call remove(), which emits close() then removes the Session object itself from the registry. The latter has the advantage that any particular state established on the session, such as a Connection-based bind (see the example in http://www.sqlalchemy.org/docs/orm/session.html#joining-a-session-into-an-external-transaction), or other particular constructor options, are discarded. Shouldn't it be semantically identical only overrided for the contextual stuff? this would be scoped_session.close(). The distinction is discussed to some degree at http://www.sqlalchemy.org/docs/orm/session.html#lifespan-of-a-contextual-session. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com
[sqlalchemy] Session: close(), remove(), expire_all() and expunge_all()
Hi everybody, I'm new with SQLAlchemy and I'm trying to understand better the Session object. First of all, why the expire_all() exists? Shouldn't it be always better to expunge_all() instead? If it is expired, at the use of the instance another query would be issued to reattach it anyway, so why keep useless instances in the identity map (maybe i'm wrong about this fact, i dont know exactly how the identity map works). Second, when we should call the close() method? I dont think if i get it at all. Let's say that I have the following DAO: PersonDAO: def insert(self, person): session = Session() session.add(person) session.commit() // why the commit starts a new transaction? should'n it only start again the next database access? session.close() // is it necessary? Is the a Session instance directly associated to a Connection in the pool? With the default pool size of 5, after 5 call of the insert method, the connection_overflow would be used? What's the time to a Session expire? And just to finish. Why do we use remove() for the scoped_session instead of the close()? Shouldn't it be semantically identical only overrided for the contextual stuff? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
