[sqlalchemy] Session remove/close MySQL
Hi all, Actually, i have some problem closing my session... I tried using scopedsession with session.remove I tried using normal session with session.close But in both cases, the Mysql session stay open. Why closing session has no effet on current Mysql connections ? -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] What is the best way to store runtime information in model?
Is it bad to use one session within app and never close it? On Friday, January 31, 2014 9:04:01 AM UTC+4, Pavel Aborilov wrote: I need to have access to this state in a whole life of app, but as I undestand it's not a good way to use one session all time. Proper way, to open session, do all my stuff with DB, then close session. But then I lost my state. In java I have DAO layer and business object, that store all my db field and all my states regardless of session. but with SA I already have session, DBO object and Manager object. I dont want to create so much layers, I think its not much pythonic. On Friday, January 31, 2014 12:51:41 AM UTC+4, Michael Bayer wrote: On Jan 30, 2014, at 1:58 PM, Pavel Aborilov abor...@gmail.com wrote: Hi! What is the best way to store runtime information in model? attributes and columns have an .info property you can use, if this is per-attribute User.fullname.info[‘some_info’] = ‘bar’ otherwise certainly, store any additional state on your object as needed, it’s a regular Python object, “self._online = 0”, sure, thats great If I get object from session like user = session.query(User).get(1) change state user.online = 1 and after session.close() I have detached object Do I always have to do expunge(user) after commit() and before close() you never need to use expunge() and generally the Session is mostly intended to be in progress when you work with your objects. when you call .close(), you should be done using all your objects - they’d either be gone, or stored away in some kind of cache or something if you’re moving them to another Session. basically if you use the session as it is in the ORM tutorial, that’s the main way to use it. The Session is always there when you’re using objects. Is there any other ways? all kinds but you need to be more aware of object lifecycle if you’re coming up with your own system. what is the most used practice, to create DAO layer or session it self work like DAO layer? the Session itself is probably not suitable as a *large* scale DAO, for simple things sure, but if your app has lots of complex use cases then its better to have functions that represent those specific use cases directly. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Session remove/close MySQL
On Fri, Feb 7, 2014 at 9:28 AM, Christian Démolis christiandemo...@gmail.com wrote: Hi all, Actually, i have some problem closing my session... I tried using scopedsession with session.remove I tried using normal session with session.close But in both cases, the Mysql session stay open. Why closing session has no effet on current Mysql connections ? SQLAlchemy maintains a pool of connections to the database. When you start a session, it checks a connection out from the pool, and when you close the session, it returns it to the pool. There are various configuration parameters you can use to control how the pool works. See the docs at http://docs.sqlalchemy.org/en/rel_0_9/core/pooling.html Hope that helps, Simon -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] What is the best way to store runtime information in model?
you don’t need the session to be open to access object state. when you close the session, the objects that were in it become detached. if you are referring to them elsewhere, they still work fine. they just won’t know how to go out and access a database. if that’s all you need, you’re done. the session being open represents an open transaction to the database, so the “state” you get from it is the “state” within a transaction - the objects within the session act as proxies for that state.I’m sure you don’t want your application to have one transaction open permanently. if you need those detached objects to also know about how to access a database when you use them, then you need to re-associate them with the current transaction (session.add() would do this). However, if lots of threads are all hoping to do the same thing, then you really need to *copy* them as needed within each thread and associate them with that session’s thread. The best way to do this is with session.merge(obj, load=False). What you’ve now built is an in-memory object cache. For a good example on how to build a full Query-level cached object system see http://docs.sqlalchemy.org/en/rel_0_9/orm/examples.html#module-examples.dogpile_caching . On Feb 7, 2014, at 7:07 AM, Pavel Aborilov abori...@gmail.com wrote: Is it bad to use one session within app and never close it? On Friday, January 31, 2014 9:04:01 AM UTC+4, Pavel Aborilov wrote: I need to have access to this state in a whole life of app, but as I undestand it's not a good way to use one session all time. Proper way, to open session, do all my stuff with DB, then close session. But then I lost my state. In java I have DAO layer and business object, that store all my db field and all my states regardless of session. but with SA I already have session, DBO object and Manager object. I dont want to create so much layers, I think its not much pythonic. On Friday, January 31, 2014 12:51:41 AM UTC+4, Michael Bayer wrote: On Jan 30, 2014, at 1:58 PM, Pavel Aborilov abor...@gmail.com wrote: Hi! What is the best way to store runtime information in model? attributes and columns have an .info property you can use, if this is per-attribute User.fullname.info[‘some_info’] = ‘bar’ otherwise certainly, store any additional state on your object as needed, it’s a regular Python object, “self._online = 0”, sure, thats great If I get object from session like user = session.query(User).get(1) change state user.online = 1 and after session.close() I have detached object Do I always have to do expunge(user) after commit() and before close() you never need to use expunge() and generally the Session is mostly intended to be in progress when you work with your objects. when you call .close(), you should be done using all your objects - they’d either be gone, or stored away in some kind of cache or something if you’re moving them to another Session. basically if you use the session as it is in the ORM tutorial, that’s the main way to use it. The Session is always there when you’re using objects. Is there any other ways? all kinds but you need to be more aware of object lifecycle if you’re coming up with your own system. what is the most used practice, to create DAO layer or session it self work like DAO layer? the Session itself is probably not suitable as a *large* scale DAO, for simple things sure, but if your app has lots of complex use cases then its better to have functions that represent those specific use cases directly. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] Session remove/close MySQL
On Feb 7, 2014, at 11:01 AM, Claudio Freire klaussfre...@gmail.com wrote: I've had similar issues with 0.7.10. SA opens an implicit transaction, incorrect, DBAPI does this, please see: http://www.python.org/dev/peps/pep-0249/#commit there is no “explicit transaction” in DBAPI. The docs have tried very hard to emphasize this as it is misleading to new users, but its outside of SQLAlchemy. and neither Session.remove nor Session.close really roll back the transaction session.rollback() rolls back the transaction. if you don’t call that, session.close() is as though you just closed the connection and did nothing, IMHO as it should be. The connection pool, if in use, will then not actually “close” the connection if it is to remained pooled, it calls rollback() as part of the pool release mechanism. Recent versions of SQLAlchemy allow this to show up in the engine logs like any other rollback, so you probably wouldn’t have noticed. (even though they should, I've had lots of experimental evidence that it does not always do it). So, what I suggest, is issuing a session.commit() or session.rollback() (according to your transactional needs) before the session.close/remove -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out. signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] Session remove/close MySQL
I knew I should've been more explicit On Fri, Feb 7, 2014 at 1:07 PM, Michael Bayer mike...@zzzcomputing.com wrote: On Feb 7, 2014, at 11:01 AM, Claudio Freire klaussfre...@gmail.com wrote: I've had similar issues with 0.7.10. SA opens an implicit transaction, incorrect, DBAPI does this, please see: http://www.python.org/dev/peps/pep-0249/#commit Ok, yeah. The point is, it's open. and neither Session.remove nor Session.close really roll back the transaction No, but the connection pool should. (reset_on_return, which I have enabled) The connection pool, if in use, will then not actually close the connection if it is to remained pooled, it calls rollback() as part of the pool release mechanism. Recent versions of SQLAlchemy allow this to show up in the engine logs like any other rollback, so you probably wouldn't have noticed. And *this* is what was not happening. Somehow, transactions remained open on the database (I checked). -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] from_statement, TextAsFrom and stored procedures
On Wed, Feb 5, 2014 at 7:28 PM, Michael Bayer mike...@zzzcomputing.comwrote: OK great, added some more rules in 5c188f6c1ce85eaace27f052. Awesome, thanks! My tests all passed on my end. As far as “names line up with the result set names”, I’m not sure what you mean there, the .columns() method is always matching up names. With that checkin, all the tests in your sample suite pass, so feel free to give it a check, I’d like to get this totally right for when 0.9.3 comes out. Gotcha: I thought that even querying a plain text() object would give you the right ORM objects back as long as the columns were in the right positional order. Looks like that's not the case, which is probably for the best; I think the more liberal behavior would have a large risk of causing silent bugs. As for *why* I thought that: I didn't realize until just now that ORM is designed to handle labels when they're in the specific form tablename_columnname. That's why I thought a text query with result set names in that form was being mapped by position, because I didn't know ORM was smart enough to find columns by name in that form :) I wrote one more test that failed (but I'm pretty sure it doesn't matter): I was under the impression that passing Label objects to .columns() would allow you to map *arbitrary* result set column names to ORM attributes, and that seems to not be the case (and was never the case, AFAIK). That kind of mapping would be cool, and might not even be that hard since the columns in the RowProxy ._keymap values seem to have the original ORM columns in their .proxy_sets. That said, the only reason I can think of for someone to try that is if they did something truly nuts like a join with two columns with the same name from two tables which *also* have the same name, from two different schemas, with a stored procedure, into ORM. As long as the tablename_columname form works, I think our use case is covered, so feel free to say wontfix. But if you're interested, I added the new test to my suite: https://gist.github.com/garaden/8835587 I hope I'm not harassing you too much about the TextAsFrom feature! I feel like if I asked any other ORM to be this flexible they would either laugh or cry. SQLAlchemy is the first ORM I've worked with since using Rails as an intern, and I'm spoiled now with how awesome it is :) -Matt -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] Session remove/close MySQL
On Fri, Feb 7, 2014 at 2:35 PM, Michael Bayer mike...@zzzcomputing.com wrote: The connection pool, if in use, will then not actually close the connection if it is to remained pooled, it calls rollback() as part of the pool release mechanism. Recent versions of SQLAlchemy allow this to show up in the engine logs like any other rollback, so you probably wouldn't have noticed. And *this* is what was not happening. Somehow, transactions remained open on the database (I checked). that kind of thing generally happens to people when they aren't cleaning up their sessions, or are using awkward engine/connection patterns. the pool has had a lot of bugs fixed but I haven't seen a bug where the pool isn't emitting the rollback when the connection is marked closed. There was an awkward pattern involved: using the session's connection as returned by Session.connection() manually to issue some textual SQL. Other than that, normal thread-local session stuff. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] from_statement, TextAsFrom and stored procedures
On Feb 7, 2014, at 1:00 PM, Matt Phipps matt.the.m...@gmail.com wrote: I wrote one more test that failed (but I'm pretty sure it doesn't matter): I was under the impression that passing Label objects to .columns() would allow you to map arbitrary result set column names to ORM attributes, and that seems to not be the case (and was never the case, AFAIK). That kind of mapping would be cool, and might not even be that hard since the columns in the RowProxy ._keymap values seem to have the original ORM columns in their .proxy_sets. yeah I thought this would work but it requires a proxy_set change, which I’d like to make but has me nervous: class A(Base): __tablename__ = 'a' id = Column(Integer, primary_key=True) data = Column(String) result = sess.query(A).from_statement( text(SELECT id AS x, data AS y FROM a). columns(A.id.label(x), A.data.label(y)) ).all() I’ve added two different patches to http://www.sqlalchemy.org/trac/ticket/2932#comment:5 which is reopened.both patches work but i think the second one is more of the right idea. it works like this too but this renders the subquery, something else to think about maybe: A1 = aliased(text(SELECT id AS x, data AS y FROM a).columns(A.id.label(x), A.data.label(y))) result = sess.query(A1).all() as does this: stmt = text(SELECT id AS x, data AS y FROM a).columns(A.id.label(x), A.data.label(y)) result = sess.query(A).select_entity_from(stmt).all() That said, the only reason I can think of for someone to try that is if they did something truly nuts like a join with two columns with the same name from two tables which also have the same name, from two different schemas, with a stored procedure, into ORM. well I really hate enforced naming conventions so making this work would be a breakthrough way of finally getting over that, I like it. I think this can be done. also, the change greatly increases performance as the lookup in ResultProxy doesn’t need a KeyError now. So I really want to try to make it work. I’m just trying to think of, what are the implications if the text() is then transformed into an alias() and such, but I think it might be consistent with how a Table acts right now. I think its cool: stmt = select([A.id, A.data]) result = sess.query(A).from_statement(stmt).all() # works stmt = select([A.id, A.data]).alias().select() result = sess.query(A).from_statement(stmt).all() # you get the same column error I hope I'm not harassing you too much about the TextAsFrom feature! I feel like if I asked any other ORM to be this flexible they would either laugh or cry. SQLAlchemy is the first ORM I've worked with since using Rails as an intern, and I'm spoiled now with how awesome it is :) its great, this feature is going to be much better and important than how it started a few months ago. I’ve added a lot of new thoughts to that ticket. signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] Session remove/close MySQL
On Feb 7, 2014, at 11:16 AM, Claudio Freire klaussfre...@gmail.com wrote: and neither Session.remove nor Session.close really roll back the transaction No, but the connection pool should. (reset_on_return, which I have enabled) reset_on_return is on by default. the pool has always emitted a rollback, the reset_on_return feature was added for some folks who either wanted to do nothing for MySQL/MyISAM, or wanted it to do a commit() on SQL Server. The connection pool, if in use, will then not actually close the connection if it is to remained pooled, it calls rollback() as part of the pool release mechanism. Recent versions of SQLAlchemy allow this to show up in the engine logs like any other rollback, so you probably wouldn't have noticed. And *this* is what was not happening. Somehow, transactions remained open on the database (I checked). that kind of thing generally happens to people when they aren’t cleaning up their sessions, or are using awkward engine/connection patterns. the pool has had a lot of bugs fixed but I haven’t seen a bug where the pool isn’t emitting the rollback when the connection is marked closed. signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [sqlalchemy] Session remove/close MySQL
On Fri, Feb 7, 2014 at 11:51 AM, Simon King si...@simonking.org.uk wrote: On Fri, Feb 7, 2014 at 9:28 AM, Christian Démolis christiandemo...@gmail.com wrote: Hi all, Actually, i have some problem closing my session... I tried using scopedsession with session.remove I tried using normal session with session.close But in both cases, the Mysql session stay open. Why closing session has no effet on current Mysql connections ? SQLAlchemy maintains a pool of connections to the database. When you start a session, it checks a connection out from the pool, and when you close the session, it returns it to the pool. There are various configuration parameters you can use to control how the pool works. See the docs at http://docs.sqlalchemy.org/en/rel_0_9/core/pooling.html I've had similar issues with 0.7.10. SA opens an implicit transaction, and neither Session.remove nor Session.close really roll back the transaction (even though they should, I've had lots of experimental evidence that it does not always do it). So, what I suggest, is issuing a session.commit() or session.rollback() (according to your transactional needs) before the session.close/remove -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
Re: [sqlalchemy] from_statement, TextAsFrom and stored procedures
Sounds great; I agree avoiding the naming convention is ideal. For my project the only reason we're using a text clause is to call a stored procedure, which definitely can't go in a subquery, so I'm not sure how well I can weigh in on the aliasing stuff. -Matt On Fri, Feb 7, 2014 at 1:43 PM, Michael Bayer mike...@zzzcomputing.comwrote: On Feb 7, 2014, at 1:00 PM, Matt Phipps matt.the.m...@gmail.com wrote: I wrote one more test that failed (but I'm pretty sure it doesn't matter): I was under the impression that passing Label objects to .columns() would allow you to map *arbitrary* result set column names to ORM attributes, and that seems to not be the case (and was never the case, AFAIK). That kind of mapping would be cool, and might not even be that hard since the columns in the RowProxy ._keymap values seem to have the original ORM columns in their .proxy_sets. yeah I thought this would work but it requires a proxy_set change, which I’d like to make but has me nervous: class A(Base): __tablename__ = 'a' id = Column(Integer, primary_key=True) data = Column(String) result = sess.query(A).from_statement( text(SELECT id AS x, data AS y FROM a). columns(A.id.label(x), A.data.label(y)) ).all() I’ve added two different patches to http://www.sqlalchemy.org/trac/ticket/2932#comment:5 which is reopened. both patches work but i think the second one is more of the right idea. it works like this too but this renders the subquery, something else to think about maybe: A1 = aliased(text(SELECT id AS x, data AS y FROM a).columns(A.id.label(x), A.data.label(y))) result = sess.query(A1).all() as does this: stmt = text(SELECT id AS x, data AS y FROM a).columns(A.id.label(x), A.data.label(y)) result = sess.query(A).select_entity_from(stmt).all() That said, the only reason I can think of for someone to try that is if they did something truly nuts like a join with two columns with the same name from two tables which *also* have the same name, from two different schemas, with a stored procedure, into ORM. well I really hate enforced naming conventions so making this work would be a breakthrough way of finally getting over that, I like it. I think this can be done. also, the change greatly increases performance as the lookup in ResultProxy doesn’t need a KeyError now. So I really want to try to make it work. I’m just trying to think of, what are the implications if the text() is then transformed into an alias() and such, but I think it might be consistent with how a Table acts right now. I think its cool: stmt = select([A.id, A.data]) result = sess.query(A).from_statement(stmt).all() # works stmt = select([A.id, A.data]).alias().select() result = sess.query(A).from_statement(stmt).all() # you get the same column error I hope I'm not harassing you too much about the TextAsFrom feature! I feel like if I asked any other ORM to be this flexible they would either laugh or cry. SQLAlchemy is the first ORM I've worked with since using Rails as an intern, and I'm spoiled now with how awesome it is :) its great, this feature is going to be much better and important than how it started a few months ago. I’ve added a lot of new thoughts to that ticket. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
