[sqlalchemy] migrating to 0.4: session handling
Hello, I am porting my code to SA 0.4 and cannot figure out whether or not I should work correctly. I have most of my db-related code united under a single DatabaseFacade class which is then bound to SA session via property: class DatabaseFacade(object): ... session = property(fget=lambda self: Session()) Session is setup as: Session = scoped_session(sessionmaker(autoflush=True, transactional=True)) and configured later on. Here is how I use it: def create_draft(self, **kw): p = WordpressPost(**kw) self.session.save(p) self.session.commit() return p Since self.session is a property it calls Session() repeatedly. It seems to work but is it OK, from transactional/performance point of view? Do I need to change it to something like: s = self.session # obtain new session s.save(p) s.commit() I also have a transactional_method() decorator which does session().begin() and then commit() or rollback() depending on whether exception occured or not. I also noticed that session.save() fails if I try to save a persistent object so I am forced to change every such save() call to save_or_update(). I don't mind but why it's not mentioned in whatsnew40/migration guide? Another error I am now getting is: InvalidRequestError: Instance '[EMAIL PROTECTED]' is with key (class ' doupy.model.objects.Invoice', (73L,), None) already persisted with a different identity Any ideas how to fix this? Method impl. looks lke this (edited for brevity): @transactional_method() def create_invoice(self, wpuser, **kw): invoice = Invoice(wpuser, public_id=str(next_id), **kw) self.session.save(invoice) return invoice Btw, is it possible to retrieve metadata if you have an engine or configured session object? I haven't found a way so ended up storing it in a module global when session is configured. -- Max http://maxischenko.in.ua // http://www.linkedin.com/in/maksim --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: migrating to 0.4: session handling
On 28 дек, 11:20, Max Ischenko [EMAIL PROTECTED] wrote: Another error I am now getting is: InvalidRequestError: Instance '[EMAIL PROTECTED]' is with key (class ' doupy.model.objects.Invoice', (73L,), None) already persisted with a different identity Any ideas how to fix this? Method impl. looks lke this (edited for brevity): @transactional_method() def create_invoice(self, wpuser, **kw): invoice = Invoice(wpuser, public_id=str(next_id), **kw) self.session.save(invoice) return invoice Randomly put db.session.clear() before create_invoice() call fixed it. Black magic. ;-/ --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: filter_by() related table columns
On Dec 28, 2007, at 9:01 AM, [EMAIL PROTECTED] wrote:
theres a certain magical behavior in 0.3 which we've removed in
filter_by(), which is that when you say description it searches
downwards through orderstatus to find it. 0.4 wants you to be
explicit and say
session
.query
(PurchaseOrder
).join('orderstatus').filter_by(description='Shipped').all().
Is there anyway to turn this magic back on in the .4 release or has
it totally been removed. We have quite a few existing queries that use
the concept of searching by related table columns and it would be
quite an undertaking to change all of these. Thanks for you help.
obviously you'll have to migrate your code at some point but I believe
you can call query._legacy_filter_by(**kwargs) to get the old behavior
right now.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Is there a way to replace object in DB?
On Dec 28, 2007, at 5:50 AM, Denis S. Otkidach wrote: Sure, I can get an object from DB and copy data from new one. But there is a lot of object types, so have to invent yet another meta description for it (while it already exists in sqlalchemy). And requirements changes often, so I have to change scheme in 2 places. This is not good and error prone. Why I have to invent new description when there is already one from sqlalchemy mapping? Can't I use it for my purpose? Something like merge(objFromDB, newObj) will solve the problem. session.merge() does copy the attributes of one object into another. theres some bugs with dont_load that have been fixed in trunk so try out the trunk if you have problems. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: filter_by() related table columns
theres a certain magical behavior in 0.3 which we've removed in
filter_by(), which is that when you say description it searches
downwards through orderstatus to find it. 0.4 wants you to be
explicit and say
session
.query
(PurchaseOrder
).join('orderstatus').filter_by(description='Shipped').all().
Is there anyway to turn this magic back on in the .4 release or has
it totally been removed. We have quite a few existing queries that use
the concept of searching by related table columns and it would be
quite an undertaking to change all of these. Thanks for you help.
--~--~-~--~~~---~--~~
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 [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Is there a way to replace object in DB?
On Dec 28, 2007 1:00 AM, Rick Morrison [EMAIL PROTECTED] wrote: Here's the idiom that should work: def ensure_object(sess, id): o = sess.Query(ModelObject).get(id)# if found, o is now loaded into session if not o: o = ModelObject(1, u'title') sess.save(o) sess.flush() return o This is not what I need: in your example object is not updated with new data. Let me describe the problem. There is a data, that must exist in DB. I have a setup script that gets such data from other source as model objects and pushes it to DB. No problems to run this script ones. But a life changes and the new must-have data appear. No problems to run it several times if pushed data can't be changed. Unfortunately they can be changed, so I have to replace it. I can't delete them before inserting since there are foreign key references. Sure, I can get an object from DB and copy data from new one. But there is a lot of object types, so have to invent yet another meta description for it (while it already exists in sqlalchemy). And requirements changes often, so I have to change scheme in 2 places. This is not good and error prone. Why I have to invent new description when there is already one from sqlalchemy mapping? Can't I use it for my purpose? Something like merge(objFromDB, newObj) will solve the problem. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Fancy transaction questions
I'm using SQLAlchemy 0.4 inside a Pylons application with a SQLite backend. I'm having trouble getting SQLAlchemy transactions to behave how I want. Here's what I want to do, in pseudo-code: begin transaction create new row1 in table1 save row1 do some other (non-database) stuff if condition: modify some columns in row1 create new row2 in table2, with a foreign key column pointing to the row1.id save row1 save row2 if errors: rollback transaction else: commit transaction For complicated reasons, it's not feasible to do all the database work in a single place. :( I'm having a bunch of problems: Problem #1: I can't get the id of row1 until I commit the transaction. I'd like to be able to get, before I commit, the id that row1 will have after the commit, so that I can enter it in row2. Or is there some way of telling SQLAlchemy that a particular column in row2 should point to whatever the id of of row1 will be when the commit happens? (I know such a thing is possible, in theory, as I have worked with a homegrown ORM using PostgreSQL that did this. Maybe this is a shortcoming of SQLite?) Problem #2: If I commit before creating row2, in order to get the id of row1, any changes I make to row1 after I commit are immediately reflected in the database -- regardless of whether I commit or rollback at the end. If I try to re-save row1 after making changes, I get a traceback saying that row1 is already persistent, like this: class 'sqlalchemy.exceptions.InvalidRequestError': Instance '[EMAIL PROTECTED]' is already persistent Is there a way to make this object non-persistent again? I want to be able to rollback all changes made to row1 and row2 together, if there's an error. Problem #3: Even if I do this in two separate transactions, and re-query for row1 by id the second time I need it, SQLAlchemy returns an instance that is already persistent. Probably this is some sort of caching that SQLAlchemy is doing, which I will be very grateful in general, but is there a way to turn it off for one query? Thanks in advance. I am new to SQLAlchemy (and SQLite and Pylons) so please forgive me if I'm missing something obvious. -matt --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Fancy transaction questions
Im assuming you're writing an ORM centric application for my answers below. If not, the answers would be slightly different. On Dec 28, 2007, at 8:02 PM, Glypho Phobet wrote: Problem #1: I can't get the id of row1 until I commit the transaction. I'd like to be able to get, before I commit, the id that row1 will have after the commit, so that I can enter it in row2. Or is there some way of telling SQLAlchemy that a particular column in row2 should point to whatever the id of of row1 will be when the commit happens? why cant you get the id until commit happens ? with the ORM you just issue a flush() anytime you want and it will insert records/get new ids. im assuming you are also using session.begin() and commit() to frame the larger transaction; within those, you can issue as many flush() calls as you like and they participate in the same transactionso you can do any number of persists, loads, deletes, whatever, without ever having to commit anything. you can also issue SQL if you wanted to execute postgres sequences or something like that. Problem #2: If I commit before creating row2, in order to get the id of row1, any changes I make to row1 after I commit are immediately reflected in the database -- regardless of whether I commit or rollback at the end. If I try to re-save row1 after making changes, I get a traceback saying that row1 is already persistent, like this: class 'sqlalchemy.exceptions.InvalidRequestError': Instance '[EMAIL PROTECTED]' is already persistent Is there a way to make this object non-persistent again? I want to be able to rollback all changes made to row1 and row2 together, if there's an error. if a transaction fails, you need to remove (or repair, if thats feasable) whatever offending objects are present in the session. if your process is going to re-do everything and recreate objects, you need to clear the whole session using session.clear()..this is typically the best approach after a transaction fails. usually within a web application a failed transaction means youre going to report an error and end the request..if you are retrying wihtin one request and doing everything again (which is unusual), just do a clear(). Problem #3: Even if I do this in two separate transactions, and re-query for row1 by id the second time I need it, SQLAlchemy returns an instance that is already persistent. Probably this is some sort of caching that SQLAlchemy is doing, which I will be very grateful in general, but is there a way to turn it off for one query? the session always returns the same instance for a particular primary key once its loaded or persisted. if you want to remove that instance, use session.expunge(theinstance). or as above session.clear() to clear the whole thing. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
