[sqlalchemy] Re: Python's reserved keywords as column names
Thanks to all of you. Dict-style mapping declaration helped. -- 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.
Re: [sqlalchemy] add if relationship is involved
Thanks. That's a bug, and it is fixed in r4bc3ace1f2f0.
You can download r4bc3ace1f2f0 by going to:
http://hg.sqlalchemy.org/sqlalchemy/archive/4bc3ace1f2f0.tar.gz
or the latest default:
http://hg.sqlalchemy.org/sqlalchemy/archive/default.tar.gz
The fix will be in 0.6.5.
A simplified version of your code which illustrates the issue is as follows:
from sqlalchemy.orm import *
from sqlalchemy import *
class Mother(object):
pass
class Son(object):
pass
class YoungSon(Son):
pass
metadata = MetaData()
mothers_table = Table('mothers', metadata,
Column('id',Integer, primary_key=True),
Column('son', Integer, ForeignKey('sons.id')),
)
sons_table = Table('sons', metadata,
Column('id',Integer, primary_key=True),
)
mapper(Mother, mothers_table, properties={
'_son':relation(Son, backref=backref('_mother', uselist=False))
})
son_mapper = mapper(Son, sons_table)
mapper(YoungSon, inherits=Son)
s = Son()
m = Mother()
m._son = s
s2 = YoungSon()
s2._mother = m
On Sep 12, 2010, at 10:45 PM, Ernst Arnold wrote:
Hi Michael
I made a simplified but I think for the ORM equivalent example which I attach.
In ExamplesTest.py I commented:
# essential: ORM canot cope if this comes after s._mother = m
It seems if that condition is not met then there is indefinite
recursion. Whether that could/should be avoided by the ORM I do not
know.
I just noticed a mistake. Need to add to ExamplesOrm.py:
mapper(OldSon, inherits=son_mapper, polymorphic_identity='oldson')
This did not change the test results.
Thanks for your time
Ernst
Michael Bayer:
Its impossible for me to assist you further without the benefit of a
complete example of what you're doing, including both tables, both mappers,
an example of their manipulation. If you attach a test case make sure its
self contained and does not rely on external libraries other than
SQLAlchemy.There should not be recursion issues in SQLAlchemy but its
possible that in some cases they are unavoidable, if things are configured
to point to themselves in some way (though I cannot picture how you'd be
doing that without full details).
--
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.
Examples.pyExamplesOrm.pyExamplesTest.py
--
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.
Re: [sqlalchemy] passive_deletes/updates with sqlite
On Sep 12, 2010, at 11:02 PM, alex bodnaru wrote: thanks a lot michael. indeed it works, but it seems counter-intuitive a little since passive_* should in my opinion be on the side of the on * cascade it describes. anyway, it's great, and hope to make it work with elixir too. If you said session.delete(my_other_object), no action is needed if it references some particular my_object.It is only if you session.delete(my_object) that SQLAlchemy needs to do something - and for that, it must load those objects which reference my_object. It does this simply by using the one-to-many collection on my_object which states how to load my_other_objects with an association.So the one-to-many collection is what's needed here - if you only specified the other direction, SQLAlchemy would need to derive the forwards collection from it and apply it to the o2m parent class internally. Right now the internals are simple such that loading is only possible in the direction in which the relationship() was configured. It is certainly possible to alter relationship() to be able to load collections or associations in both directions so that a many-to-one relationship could transparently load from the other direction, though internally this would require doing pretty much what backref does explicitly - placing some linkage to the relationship on the target class of the many-to-one which could react to deletions of that target class. So the current implementation is much simpler than that, so the passive_updates flag goes onto the side of the relationship that ultimately needs it, so that SQLAlchemy doesn't need to generate the backref which is easy enough for the user to specify, and in very rare conditions may even be configured differently than the reverse direction. The original design of cascade is taken from Hibernate, probably the most widely used object relational tool in history. They too tend to define ORM-level information about the foreign key, including the CASCADE attributes on the primary key side: http://docs.jboss.org/hibernate/stable/core/reference/en/html/mapping.html#mapping-declaration-key http://docs.jboss.org/hibernate/stable/core/reference/en/html/collections.html#collections-mapping -- 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. -- 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] Oracle setinputsizes usage (just making sure)
I just want to make sure that what I am reading at http://www.sqlalchemy.org/docs/dialects/oracle.html#connecting is correct when connecting to an Oracle database using sqa. When I was using cx_Oracle directly I would have to use cursor.setinputseizes when I am using bind variables (especially when I have a rather large CLOB data structure). It seems that sqa does this automatically when I connect to the database. Am I reading it correctly or is there another step that I need to complete? Thanks, Kevin 43rd Law of Computing: Anything that can go wr fortune: Segmentation violation -- Core dumped -- 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] (Hopefully) simple problem with backrefs not being loaded when eagerloading.
I'm sure I'm missing something simple here, and any pointers in the right direction would be greatly appreciated. Take for instance the following code: session = Session() parents = session.query(Parent).options(joinedload(Parent.children)).all() session.close() print parents[0].children # This works print parents[0].children[0].parent # This gives a lazy loading error Adding the following loop before closing the session works (and doesn't hit the DB): for p in parents: for c in p.children: c.parent As far as I can tell, the mapping is correct since: * It all works fine if I leave the session open * If I don't use joinedload, and leave the session open it lazyloads correctly I'm surprised that: * It doesn't set both sides of the relation, considering it apparently knows about them * It complains that the session is closed despite not actually requiring an open session (no SQL is sent to the DB for c.parent) These apprent do-nothing loops are starting to clutter the code. There must be a better way. Thanks Jon -- Jon Siddle, CoreFiling Limited Software Tools Developer http://www.corefiling.com Phone: +44-1865-203192 -- 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.
Re: [sqlalchemy] Oracle setinputsizes usage (just making sure)
On Sep 13, 2010, at 8:26 AM, Kevin Mills wrote: I just want to make sure that what I am reading at http://www.sqlalchemy.org/docs/dialects/oracle.html#connecting is correct when connecting to an Oracle database using sqa. When I was using cx_Oracle directly I would have to use cursor.setinputseizes when I am using bind variables (especially when I have a rather large CLOB data structure). It seems that sqa does this automatically when I connect to the database. Am I reading it correctly or is there another step that I need to complete? yes, we're calling it, since many datatypes simply don't work with cx_oracle if you don't. Though if you're having issues with CLOB, that would make sense as we are currently excluding STRING, UNICODE, NCLOB, CLOB from the list of types. Examining why this is the case, it seems like STRING has issues when you use it with RETURNING, and perhaps an assumption was made at some point to lump NCLOB and CLOB in there but its likely that this is inappropriate (we have tests with CLOB which succeed - butI'm going to guess that none of them have an input string more than 4k). If this is the case please let me know we'll add some CLOB tests. Here's a patch to get you going if this is indeed the case: diff -r 2cc844408821 lib/sqlalchemy/dialects/oracle/cx_oracle.py --- a/lib/sqlalchemy/dialects/oracle/cx_oracle.py Mon Sep 13 02:39:39 2010 -0400 +++ b/lib/sqlalchemy/dialects/oracle/cx_oracle.py Mon Sep 13 10:53:10 2010 -0400 @@ -257,7 +257,7 @@ # on String, including that outparams/RETURNING # breaks for varchars self.set_input_sizes(quoted_bind_names, - exclude_types=self.dialect._cx_oracle_string_types + exclude_types=self.dialect._exclude_setinputsizes ) # if a single execute, check for outparams @@ -426,8 +426,7 @@ getattr(self.dbapi, name, None) for name in names ]).difference([None]) -self._cx_oracle_string_types = types(STRING, UNICODE, NCLOB, CLOB) -self._cx_oracle_unicode_types = types(UNICODE, NCLOB) +self._exclude_setinputsizes = types(STRING, UNICODE) self._cx_oracle_binary_types = types(BFILE, CLOB, NCLOB, BLOB) self.supports_unicode_binds = self.cx_oracle_ver = (5, 0) self.supports_native_decimal = self.cx_oracle_ver = (5, 0) @@ -435,7 +434,7 @@ if self.cx_oracle_ver is None: # this occurs in tests with mock DBAPIs -self._cx_oracle_string_types = set() +self._exclude_setinputsizes = set() self._cx_oracle_with_unicode = False elif self.cx_oracle_ver = (5,) and not hasattr(self.dbapi, 'UNICODE'): # cx_Oracle WITH_UNICODE mode. *only* python -- 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.
Re: [sqlalchemy] passive_deletes/updates with sqlite
On 09/13/2010 08:32 AM, Michael Bayer wrote: On Sep 12, 2010, at 11:02 PM, alex bodnaru wrote: thanks a lot michael. indeed it works, but it seems counter-intuitive a little since passive_* should in my opinion be on the side of the on * cascade it describes. anyway, it's great, and hope to make it work with elixir too. If you said session.delete(my_other_object), no action is needed if it references some particular my_object.It is only if you session.delete(my_object) that SQLAlchemy needs to do something - and for that, it must load those objects which reference my_object. It does this simply by using the one-to-many collection on my_object which states how to load my_other_objects with an association.So the one-to-many collection is what's needed here - if you only specified the other direction, SQLAlchemy would need to derive the forwards collection from it and apply it to the o2m parent class internally. Right now the internals are simple such that loading is only possible in the direction in which the relationship() was configured. It is certainly possible to alter relationship() to be able to load collections or associations in both directions so that a many-to-one relationship could transparently load from the other direction, though internally this would require doing pretty much what backref does explicitly - placing some linkage to the relationship on the target class of the many-to-one which could react to deletions of that target class. So the current implementation is much simpler than that, so the passive_updates flag goes onto the side of the relationship that ultimately needs it, so that SQLAlchemy doesn't need to generate the backref which is easy enough for the user to specify, and in very rare conditions may even be configured differently than the reverse direction. The original design of cascade is taken from Hibernate, probably the most widely used object relational tool in history. They too tend to define ORM-level information about the foreign key, including the CASCADE attributes on the primary key side: http://docs.jboss.org/hibernate/stable/core/reference/en/html/mapping.html#mapping-declaration-key http://docs.jboss.org/hibernate/stable/core/reference/en/html/collections.html#collections-mapping -- 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. hope my approach isn't too simplist, but onetomany is usually implemented in rdbms by an manytoone column or a few of them, with or without ri clauses: thus, a foreign key or an index. conversely, a manytoone relation has an implicit onetomany one (or an explicit onetoone). hereinafter, anything you would define on one end, at least in terms of ri, should be at the end it needs to be there. the example i've given with elixir (look at the sql echo) shows the onetomany updates the foreign key to null, not knowing they wouldn't be found in the cascading delete. i'm searching the exact point elixir should give the passive_deletes to the sa relation, thus to force it to give it to the right side of it. best regards, alex -- 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.
Re: [sqlalchemy] (Hopefully) simple problem with backrefs not being loaded when eagerloading.
On Sep 13, 2010, at 8:48 AM, Jon Siddle wrote: I'm sure I'm missing something simple here, and any pointers in the right direction would be greatly appreciated. Take for instance the following code: session = Session() parents = session.query(Parent).options(joinedload(Parent.children)).all() session.close() print parents[0].children # This works print parents[0].children[0].parent # This gives a lazy loading error Adding the following loop before closing the session works (and doesn't hit the DB): for p in parents: for c in p.children: c.parent As far as I can tell, the mapping is correct since: * It all works fine if I leave the session open * If I don't use joinedload, and leave the session open it lazyloads correctly I'm surprised that: * It doesn't set both sides of the relation, considering it apparently knows about them This relationship is satisfied as you request it, and it works by looking in the current Session's identity map for the primary key stored by the many-to-one. The operation falls under the realm of lazyloading even though no SQL is emitted. If you consider that Child may have many related many-to-ones, all of which may already be in the Session, it would be quite wasteful for the ORM to assume that you're going to be working with the object in a detached state and that you need all of them. The Session's default assumption is that you're going to be leaving it around while you work with the objects contained, and in that way you interact with the database for as long as you deal with its objects, which represent proxies to the underlying transaction. When objects are detached, for reasons like caching and serialization to other places, normally you'd merge() them back when you want to use them again. So if it were me I'd normally be looking to not be closing the session. However, when working with detached objects is necessary, two approaches here you can use. One is a general approach that can load anything related, which is to load them in a @reconstructor. This is illustrated at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/ImmediateLoading .It won't issue any extra SQL for the many-to-ones that are present in the session already. In the specific case you have above, you can also use a trick which is to use contains_eager(): parents = session.query(Parent).options(joinedload(Parent.children), contains_eager(Parent.children, Child.parent)).all() the above approach requires that Parent is one of the entities that you're requesting explicitly - i.e. if you were saying joinedload(foo, bar, bat), it would be kind of impossible to target bat.hohos with contains_eager() due to the aliasing. this will do the get() of the Parent as you run through. -- 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.
Re: [sqlalchemy] passive_deletes/updates with sqlite
On Sep 13, 2010, at 11:16 AM, alex bodnaru wrote: hope my approach isn't too simplist, but onetomany is usually implemented in rdbms by an manytoone column or a few of them, with or without ri clauses: thus, a foreign key or an index. conversely, a manytoone relation has an implicit onetomany one (or an explicit onetoone). if you read what I wrote, I was explaining, that we architecturally choose not to generate the implicit reverse direction when it isn't specified by the user. And that this decision is not too controversial since Hibernate made the same one. the example i've given with elixir (look at the sql echo) shows the onetomany updates the foreign key to null, not knowing they wouldn't be found in the cascading delete. i'm searching the exact point elixir should give the passive_deletes to the sa relation, thus to force it to give it to the right side of it. right - Elixir has a more abstracted layer of user configuration, which is basically what you're asking SQLAlchemy to build into it. Id rather make things simpler on the inside, not more magical. -- 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.
Re: [sqlalchemy] (Hopefully) simple problem with backrefs not being loaded when eagerloading.
On Sep 13, 2010, at 11:45 AM, Michael Bayer wrote: In the specific case you have above, you can also use a trick which is to use contains_eager(): parents = session.query(Parent).options(joinedload(Parent.children), contains_eager(Parent.children, Child.parent)).all() the above approach requires that Parent is one of the entities that you're requesting explicitly - i.e. if you were saying joinedload(foo, bar, bat), it would be kind of impossible to target bat.hohos with contains_eager() due to the aliasing. so let me also back that up, that we've always planned on adding an immediateload option that would just fire off any lazyloader as the query fetches results.A really short patch that adds immediateload() is at http://www.sqlalchemy.org/trac/ticket/1914 and hopefully will be in 0.6.5 pending further testing. -- 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.
Re: [sqlalchemy] (Hopefully) simple problem with backrefs not being loaded when eagerloading.
On 13/09/10 16:45, Michael Bayer wrote: On Sep 13, 2010, at 8:48 AM, Jon Siddle wrote: I'm sure I'm missing something simple here, and any pointers in the right direction would be greatly appreciated. Take for instance the following code: session = Session() parents = session.query(Parent).options(joinedload(Parent.children)).all() session.close() print parents[0].children # This works print parents[0].children[0].parent # This gives a lazy loading error Adding the following loop before closing the session works (and doesn't hit the DB): for p in parents: for c in p.children: c.parent As far as I can tell, the mapping is correct since: * It all works fine if I leave the session open * If I don't use joinedload, and leave the session open it lazyloads correctly I'm surprised that: * It doesn't set both sides of the relation, considering it apparently knows about them This relationship is satisfied as you request it, and it works by looking in the current Session's identity map for the primary key stored by the many-to-one. The operation falls under the realm of lazyloading even though no SQL is emitted. If you consider that Child may have many related many-to-ones, all of which may already be in the Session, it would be quite wasteful for the ORM to assume that you're going to be working with the object in a detached state and that you need all of them. I'm not sure I see what you're saying here. I've explicitly asked for all children relating to parent and these are correctly queried and loaded. While they are being added to the parent.children list, why not also set each child.parent since this is known? I don't see how this is wasteful, but I may be missing something. I'm not suggesting it should touch relations that I haven't explicitly told it to eagerly load. The likes of Hibernate (yes, it's a very different beast) load both sides of the relation at once. The Session's default assumption is that you're going to be leaving it around while you work with the objects contained, and in that way you interact with the database for as long as you deal with its objects, which represent proxies to the underlying transaction. When objects are detached, for reasons like caching and serialization to other places, normally you'd merge() them back when you want to use them again. So if it were me I'd normally be looking to not be closing the session. I'm closing the session before I forward the objects to the view template in a web application. The template has no business doing database operations, and the controller *should* make sure all DB work has been done. In my case, I know I'll never need to write back to the DB. However, when working with detached objects is necessary, two approaches here you can use. One is a general approach that can load anything related, which is to load them in a @reconstructor. This is illustrated at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/ImmediateLoading .It won't issue any extra SQL for the many-to-ones that are present in the session already. In the specific case you have above, you can also use a trick which is to use contains_eager(): parents = session.query(Parent).options(joinedload(Parent.children), contains_eager(Parent.children, Child.parent)).all() This seems to address my problem directly. It's still a bit redundant, but from my initial tests it seems to solve my problem. the above approach requires that Parent is one of the entities that you're requesting explicitly - i.e. if you were saying joinedload(foo, bar, bat), it would be kind of impossible to target bat.hohos with contains_eager() due to the aliasing. I'm only interested in making sure both sides of the same relation are loaded; so this isn't a problem at all. so let me also back that up, that we've always planned on adding an immediateload option that would just fire off any lazyloader as the query fetches results.A really short patch that adds immediateload() is athttp://www.sqlalchemy.org/trac/ticket/1914 and hopefully will be in 0.6.5 pending further testing. We'll have to support 0.5 for some time, but it's good to know a shortcut is coming. Thanks a lot for your help. Jon -- Jon Siddle, CoreFiling Limited Software Tools Developer http://www.corefiling.com Phone: +44-1865-203192 -- 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] paranoia - does flush ensure a unique id?
Hi All, Give the following model: from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.types import Integer Base = declarative_base() class Model(Base): __tablename__='test' id = Column(Integer, primary_key=True) col = Column(Integer) ...the following code will give obj an id: obj = Model() session.add(obj) session.flush() What ensures obj.id will be unique and will it always be unique, even in the case of high volumes of parallel writes to the database? Does it depend on the back end? Are any backends known not to work this way? Also, if I have a session extension which uses the value of obj.id to populate obj.col in the after_flush method of the session, is that a legit thing to do, and will id always be available to use, and unique as above? cheers, Chris -- 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.
Re: [sqlalchemy] (Hopefully) simple problem with backrefs not being loaded when eagerloading.
On Sep 13, 2010, at 12:26 PM, Jon Siddle wrote: This relationship is satisfied as you request it, and it works by looking in the current Session's identity map for the primary key stored by the many-to-one. The operation falls under the realm of lazyloading even though no SQL is emitted. If you consider that Child may have many related many-to-ones, all of which may already be in the Session, it would be quite wasteful for the ORM to assume that you're going to be working with the object in a detached state and that you need all of them. I'm not sure I see what you're saying here. I've explicitly asked for all children relating to parent and these are correctly queried and loaded. While they are being added to the parent.children list, why not also set each child.parent since this is known? because you didn't specify it, and it takes a palpable amount of additional overhead to do so as well as a palpable amount of complexity to decide if it should do so based on the logic you'd apply here, when in 99% of the cases it is not needed. I don't see how this is wasteful, but I may be missing something. Child may have parent, foo, bar, bat attached to it, all many-to-ones. Which ones should it assume the user wants to load ? If you are loading 1 rows, and each Child object has three many-to-ones on it, and suppose it takes 120 function calls to look at a relationship, determine the values to send to query._get(), look in the identity map, etc., that is 3 x 1 x 120 = 3.6 million function calls, by default, almost never needed since normally they are all just there in the current session, without the user asking to do so. There is nothing special about Child.parent just because Parent.children is present up the chain.While Hibernate may have decided that the complexity and overhead of adding this decisionmaking was worth it, they have many millions more function calls to burn with the Java VM in any case than we do in Python, and they also have a much higher bar to implement lazyloading since their native class instrumentation is itself a huge beast. In our case it is simply unnecessary. Any such automatic decisionmaking you can be sure quickly leads to many uncomfortable edge cases and thorny situations, causing needless surprise issues for users who don't really need such a behavior in the first place. As I've mentioned, you will have an option to tell it which many-to-ones you'd like it to spend time pre-populating using the upcoming immedateload option. The Session's default assumption is that you're going to be leaving it around while you work with the objects contained, and in that way you interact with the database for as long as you deal with its objects, which represent proxies to the underlying transaction. When objects are detached, for reasons like caching and serialization to other places, normally you'd merge() them back when you want to use them again. So if it were me I'd normally be looking to not be closing the session. I'm closing the session before I forward the objects to the view template in a web application. The template has no business doing database operations, I disagree with this interpretation of abstraction. That's like saying that pushing the button in an elevator means you're now in charge of starting up the elevator motors and instructing them how many feet to travel. The template is not doing database operations, it is working with high level objects that you've sent it, and knows nothing of a database. That these objects may be doing database calls behind the scenes to lazily fetch additional data is known as the proxy pattern. It is one of the most fundamental patterns in object oriented software design. Separation of concerns is about what kinds of source code and awareness of systems live in various places - it has nothing to do operational timing or initiation. The pre-load scenario is certainly valid if you're trying to render from an object graph that loads from a cache and doesn't want to do any additional database calls. But this is strictly an issue of optimization, not correct software design. -- 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: Updating a detached object
If I merge the updated channel like you can see in this piece of code it's working: def insertXML(channels, strXml): Insert a new channel given XML string channel = Channel() session = rdb.Session() channel.fromXML(strXml) fillChannelTemplate(channel, channels) for item in channel.items: if item.id == 0: item.id = None break session.merge(channel) for chan in channels: if chan.id == channel.id: chan.items.append(item) break My problem is I'm using channels, it's a list of channels which I save it in HTTP session object. The channels list is a detached object which I get using joinload option. So in this case, I update the object correctly in database, but It isn't persistent in channels if I do this: for chan in channels: if chan.id == channel.id: chan.items.append(item) break Do you have any idea how I can solve this problem? or another approach? Thanks! On Sep 10, 5:09 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Sep 10, 2010, at 2:57 PM, Alvaro Reinoso wrote: Hello guys, I have this table: class Channel(rdb.Model): rdb.metadata(metadata) rdb.tablename(channels) id = Column(id, Integer, primary_key=True) title = Column(title, String(100)) hash = Column(hash, String(50)) runtime = Column(runtime, Float) items = relationship(MediaItem, secondary=channel_items, order_by=MediaItem.position, backref=channels) I have a list of channels, but they are detached objects. I get them using joinedload option because I maniputale those objects sometimes. When I do that, I update the object. This time, I'm trying to add a new item to a detached channel object. This is the code: def insertXML(channels, strXml): Insert a new channel given XML string channel = Channel() session = rdb.Session() result = channel.fromXML(strXml) fillChannelTemplate(channel, channels) if channel.id == 0: session.add(channel) session.flush() channels.append(channel) else: for chan in channels: if chan.id == channel.id: chan.runtime = channel.runtime chan.modified = datetime.date.today() for item in channel.items: if item.id == 0: chan.items.append(item) session.merge(chan) The item is inserted in the database, but It doesn't create the relation in channel_items. Besides, I get this error: FlushError: New instance Channel at 0xb75eeec with identity key (class 'zeppelinlib.channel.ChannelTest.Channel', (152,)) conflicts with persistent instance Channel at 0xb598dec anytime you have that error you should be using merge() to merge state into that which is already existing, the return value from merge() is then what you need to use for your new state. I see you tried using merge earlier but your issue is not clear. -- 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.
Re: [sqlalchemy] Re: Updating a detached object
On Sep 13, 2010, at 2:13 PM, Alvaro Reinoso wrote: If I merge the updated channel like you can see in this piece of code it's working: def insertXML(channels, strXml): Insert a new channel given XML string channel = Channel() session = rdb.Session() channel.fromXML(strXml) fillChannelTemplate(channel, channels) for item in channel.items: if item.id == 0: item.id = None break session.merge(channel) for chan in channels: if chan.id == channel.id: chan.items.append(item) break My problem is I'm using channels, it's a list of channels which I save it in HTTP session object. The channels list is a detached object which I get using joinload option. So in this case, I update the object correctly in database, but It isn't persistent in channels if I do this: for chan in channels: if chan.id == channel.id: chan.items.append(item) break Do you have any idea how I can solve this problem? or another approach? here: session.merge(channel) use the return value of merge(): channel = session.merge(channel) the returned channel plus all children is the fully merged result. Thanks! On Sep 10, 5:09 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Sep 10, 2010, at 2:57 PM, Alvaro Reinoso wrote: Hello guys, I have this table: class Channel(rdb.Model): rdb.metadata(metadata) rdb.tablename(channels) id = Column(id, Integer, primary_key=True) title = Column(title, String(100)) hash = Column(hash, String(50)) runtime = Column(runtime, Float) items = relationship(MediaItem, secondary=channel_items, order_by=MediaItem.position, backref=channels) I have a list of channels, but they are detached objects. I get them using joinedload option because I maniputale those objects sometimes. When I do that, I update the object. This time, I'm trying to add a new item to a detached channel object. This is the code: def insertXML(channels, strXml): Insert a new channel given XML string channel = Channel() session = rdb.Session() result = channel.fromXML(strXml) fillChannelTemplate(channel, channels) if channel.id == 0: session.add(channel) session.flush() channels.append(channel) else: for chan in channels: if chan.id == channel.id: chan.runtime = channel.runtime chan.modified = datetime.date.today() for item in channel.items: if item.id == 0: chan.items.append(item) session.merge(chan) The item is inserted in the database, but It doesn't create the relation in channel_items. Besides, I get this error: FlushError: New instance Channel at 0xb75eeec with identity key (class 'zeppelinlib.channel.ChannelTest.Channel', (152,)) conflicts with persistent instance Channel at 0xb598dec anytime you have that error you should be using merge() to merge state into that which is already existing, the return value from merge() is then what you need to use for your new state. I see you tried using merge earlier but your issue is not clear. -- 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. -- 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: Updating a detached object
Yes, I've done that. I doesn't work either. for chan in channels: if chan.id == channel.id: chan = session.merge(channel) break On Sep 13, 2:27 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Sep 13, 2010, at 2:13 PM, Alvaro Reinoso wrote: If I merge the updated channel like you can see in this piece of code it's working: def insertXML(channels, strXml): Insert a new channel given XML string channel = Channel() session = rdb.Session() channel.fromXML(strXml) fillChannelTemplate(channel, channels) for item in channel.items: if item.id == 0: item.id = None break session.merge(channel) for chan in channels: if chan.id == channel.id: chan.items.append(item) break My problem is I'm using channels, it's a list of channels which I save it in HTTP session object. The channels list is a detached object which I get using joinload option. So in this case, I update the object correctly in database, but It isn't persistent in channels if I do this: for chan in channels: if chan.id == channel.id: chan.items.append(item) break Do you have any idea how I can solve this problem? or another approach? here: session.merge(channel) use the return value of merge(): channel = session.merge(channel) the returned channel plus all children is the fully merged result. Thanks! On Sep 10, 5:09 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Sep 10, 2010, at 2:57 PM, Alvaro Reinoso wrote: Hello guys, I have this table: class Channel(rdb.Model): rdb.metadata(metadata) rdb.tablename(channels) id = Column(id, Integer, primary_key=True) title = Column(title, String(100)) hash = Column(hash, String(50)) runtime = Column(runtime, Float) items = relationship(MediaItem, secondary=channel_items, order_by=MediaItem.position, backref=channels) I have a list of channels, but they are detached objects. I get them using joinedload option because I maniputale those objects sometimes. When I do that, I update the object. This time, I'm trying to add a new item to a detached channel object. This is the code: def insertXML(channels, strXml): Insert a new channel given XML string channel = Channel() session = rdb.Session() result = channel.fromXML(strXml) fillChannelTemplate(channel, channels) if channel.id == 0: session.add(channel) session.flush() channels.append(channel) else: for chan in channels: if chan.id == channel.id: chan.runtime = channel.runtime chan.modified = datetime.date.today() for item in channel.items: if item.id == 0: chan.items.append(item) session.merge(chan) The item is inserted in the database, but It doesn't create the relation in channel_items. Besides, I get this error: FlushError: New instance Channel at 0xb75eeec with identity key (class 'zeppelinlib.channel.ChannelTest.Channel', (152,)) conflicts with persistent instance Channel at 0xb598dec anytime you have that error you should be using merge() to merge state into that which is already existing, the return value from merge() is then what you need to use for your new state. I see you tried using merge earlier but your issue is not clear. -- 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 athttp://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 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.
Re: [sqlalchemy] Re: Updating a detached object
On Sep 13, 2010, at 2:31 PM, Alvaro Reinoso wrote: Yes, I've done that. I doesn't work either. for chan in channels: if chan.id == channel.id: chan = session.merge(channel) break i dont understand the context of that code (what's channel) ? This is how a basic merge works: def merge_new_data(some_xml): my_objects = parse_xml(some_xml) # at this point, every object in my_objects should # have a primary key, as well as every child of every element, # all the way down. Existing primary keys must be fully populated, # that's your job. This is the intricate part, obviously. But you don't # merge anything here, just get the PKs filled in. # then you merge the whole thing. merge() cascades along all # relationships. The rule is simple - if PK is present and exists in the DB, it # updates. otherwise, it inserts. for obj in my_objects: Session.merge(obj) Session.commit() # done On Sep 13, 2:27 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Sep 13, 2010, at 2:13 PM, Alvaro Reinoso wrote: If I merge the updated channel like you can see in this piece of code it's working: def insertXML(channels, strXml): Insert a new channel given XML string channel = Channel() session = rdb.Session() channel.fromXML(strXml) fillChannelTemplate(channel, channels) for item in channel.items: if item.id == 0: item.id = None break session.merge(channel) for chan in channels: if chan.id == channel.id: chan.items.append(item) break My problem is I'm using channels, it's a list of channels which I save it in HTTP session object. The channels list is a detached object which I get using joinload option. So in this case, I update the object correctly in database, but It isn't persistent in channels if I do this: for chan in channels: if chan.id == channel.id: chan.items.append(item) break Do you have any idea how I can solve this problem? or another approach? here: session.merge(channel) use the return value of merge(): channel = session.merge(channel) the returned channel plus all children is the fully merged result. Thanks! On Sep 10, 5:09 pm, Michael Bayer mike...@zzzcomputing.com wrote: On Sep 10, 2010, at 2:57 PM, Alvaro Reinoso wrote: Hello guys, I have this table: class Channel(rdb.Model): rdb.metadata(metadata) rdb.tablename(channels) id = Column(id, Integer, primary_key=True) title = Column(title, String(100)) hash = Column(hash, String(50)) runtime = Column(runtime, Float) items = relationship(MediaItem, secondary=channel_items, order_by=MediaItem.position, backref=channels) I have a list of channels, but they are detached objects. I get them using joinedload option because I maniputale those objects sometimes. When I do that, I update the object. This time, I'm trying to add a new item to a detached channel object. This is the code: def insertXML(channels, strXml): Insert a new channel given XML string channel = Channel() session = rdb.Session() result = channel.fromXML(strXml) fillChannelTemplate(channel, channels) if channel.id == 0: session.add(channel) session.flush() channels.append(channel) else: for chan in channels: if chan.id == channel.id: chan.runtime = channel.runtime chan.modified = datetime.date.today() for item in channel.items: if item.id == 0: chan.items.append(item) session.merge(chan) The item is inserted in the database, but It doesn't create the relation in channel_items. Besides, I get this error: FlushError: New instance Channel at 0xb75eeec with identity key (class 'zeppelinlib.channel.ChannelTest.Channel', (152,)) conflicts with persistent instance Channel at 0xb598dec anytime you have that error you should be using merge() to merge state into that which is already existing, the return value from merge() is then what you need to use for your new state. I see you
[sqlalchemy] Postgresql Partition / INHERIT and table names....
Suppose this is my table:
a_table = Table(
'a_table', metadata,
Column('ts',Integer, index=True, nullable=False),
Column('country',String, index=True, nullable=False),
Column('somestat',Integer,nullable=False),
PrimaryKeyConstraint('ts','country',name='summary_pk'),
)
then:
print select([a_table]).where(a_table.c.country=='de')
SELECT a_table.ts, a_table.country, a_table.somestat
FROM a_table
WHERE a_table.country = %(country_1)s
Suppose further that in the Postgres, there are several PARTITIONS
INHERIT
from this main table, and I want to query them directly... what is a
sensible
way to do this? I can figure out *which* partition to use, based on
outside
code, so it doesn't need to be very smart.
As a final answer I want something like:
print SOMETHING
SELECT a_table_PARITION1.ts, a_table_PARITION1.country,
a_table_PARITION1.somestat
FROM a_table_PARITION1
WHERE a_table_PARITION1.country = %(country_1)s
In my head, it is like oh, use a_table, except sub in a new value
for
a_table.name TEMPORARILY.
--
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: Postgresql Partition / INHERIT and table names....
Thank you!
I figured a compile visitor might be the right way in, but had no idea
of how to do it!
Some tutorials just on the visitors would probably explain a lot about
how PG works!
Cheers!
GL
On Sep 13, 2:14 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On Sep 13, 2010, at 2:48 PM, Gregg Lind wrote:
Suppose this is my table:
a_table = Table(
'a_table', metadata,
Column('ts',Integer, index=True, nullable=False),
Column('country',String, index=True, nullable=False),
Column('somestat',Integer,nullable=False),
PrimaryKeyConstraint('ts','country',name='summary_pk'),
)
then:
print select([a_table]).where(a_table.c.country=='de')
SELECT a_table.ts, a_table.country, a_table.somestat
FROM a_table
WHERE a_table.country = %(country_1)s
Suppose further that in the Postgres, there are several PARTITIONS
INHERIT
from this main table, and I want to query them directly... what is a
sensible
way to do this? I can figure out *which* partition to use, based on
outside
code, so it doesn't need to be very smart.
As a final answer I want something like:
print SOMETHING
SELECT a_table_PARITION1.ts, a_table_PARITION1.country,
a_table_PARITION1.somestat
FROM a_table_PARITION1
WHERE a_table_PARITION1.country = %(country_1)s
In my head, it is like oh, use a_table, except sub in a new value
for
a_table.name TEMPORARILY.
this is not very hard so I created a recipe for this case:
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/PartitionTable
--
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
athttp://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 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.
Re: [sqlalchemy] Re: Postgresql Partition / INHERIT and table names....
On Mon, Sep 13, 2010 at 3:39 PM, Gregg Lind gregg.l...@gmail.com wrote: So, there is a slight wart here: q = select(Partition(t1,'myt1')) q.append_column(Partition(t1,'myt1').c.data) will give: from myt1,myt1 I think this is an artifact of the 'alias' heritage. Ideas? Don't instantiate Partition twice: p = Partition(t1, 'myt1') q = select(p) q.append_column(p.c.data) -- Jon -- 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] internationalization of content
Hi all, I'm lookin for a good solution to internationalize the content of my application. that is provide many translations for the database content (as opposed to the translation of the application itself with babel/gettext for template and code messages). Has anyone tried ti implement this ? a working solution ? willing to participate in a effort to provide a solution ? regards NiL -- 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.
Re: [sqlalchemy] sqlalchemy and desktop apps
I'm not sure to what extent it works at present, but someone ported OpenERP server to work with SQLAlchemy: http://openerp-team.blogspot.com/2009/08/open-erp-server-with-mysql.html And some parts of OpenERP look like they use SQLAlchemy: http://doc.openerp.com/bi/architecture/architecture.html?highlight=sqlalchemy http://doc.openerp.com/bi/architecture/openobject_module.html?highlight=sqlalchemy https://blueprints.launchpad.net/openobject-server/+spec/saserver In May 2009 the following page said 30) The ORM layer is being ported to SQL Alchemy. http://openerp-team.blogspot.com/2009/05/r-planning-of-may_15.html Tim -- 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.
