Connections and the session

[dcg...@gmail.com]

Hello there,

I have the following code structure (more or less) in several processes:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine(some_mysql_dburi)
session = sessionmaker(bind=engine)()

while True:
   query = session.query(...).filter(...)
   # do something here from that query, update, insert new rows, etc.
   session.commit()

I am getting a too many connections error and it's not all the time, so I'm not sure what would it be. Although there are several processes running, I don't think at some time I'm connected to mysql server more than the amount of connections allowed, which by default it's about 150 connections I think, and I'm not modifying the default value. So I must have some errors in this layout of my code. Here are some of the questions about the session and the connection(s) within I'd like to ask:
1- How many opened connections are maintained by a single session object? I read somewhere it's only one, but, here it's my next
2- Does the session close the connection being used or requests for another? If it requests for a new one, does it close the previous (i.e., return it to the engine pool)?
3- Should I call session.close() right after the session.commit() statement? If have to, do I have to put the session creation inside the while? I read that when the session gets garbage collected the connection(s) is(are) closed, so I could do this, but I don't know if it is a good use of the session.

I read the docs many times and I didn't find anything solid that answers that questions to me, any help on the subject will be very appreciated, thanks in advance. 

 






  

[Michael Bayer]

dcg...@gmail.com wrote:

> Hello there,
>
> I have the following code structure (more or less) in several processes:
>
> from sqlalchemy import create_engine
> from sqlalchemy.orm import sessionmaker
>
> engine = create_engine(some_mysql_dburi)
> session = sessionmaker(bind=engine)()
>
> while True:
> query = session.query(...).filter(...)
> # do something here from that query, update, insert new rows, etc.
> session.commit()
>
> I am getting a too many connections error and it's not all the time, so I'm not sure what would it be. Although there are several processes running, I don't think at some time I'm connected to mysql server more than the amount of connections allowed, which by default it's about 150 connections I think, and I'm not modifying the default value. So I must have some errors in this layout of my code. Here are some of the questions about the session and the connection(s) within I'd like to ask:
> 1- How many opened connections are maintained by a single session object? I read somewhere it's only one, but, here it's my next

if only one engine is associated with it, them just one connection.

> 2- Does the session close the connection being used or requests for another?

assuming the session isn’t in “autocommit” mode, the connection stays open
until you call commit(), rollback(), or close().

> If it requests for a new one, does it close the previous (i.e., return it to the engine pool)?

just one connection at a time yup

> 3- Should I call session.close() right after the session.commit() statement?

it’s not necessary, however all objects that are associated with that
session are still able to make it start up a new transaction if you keep
using those objects and hit upon unloaded attributes. close() would prevent
that.

> If have to, do I have to put the session creation inside the while?

not the way it is above; after that commit(), the Session there isn’t
connected to anything. Unless the objects associated with it are being used
in other threads, or something like that.

> I read that when the session gets garbage collected the connection(s) is(are) closed,

that happens also but the commit() is enough (and close() makes it sure).

> so I could do this, but I don't know if it is a good use of the session.
>
> I read the docs many times and I didn't find anything solid that answers that questions to me, any help on the subject will be very appreciated, thanks in advance.

the best section is this one:
http://docs.sqlalchemy.org/en/rel_0_9/orm/session_basics.html#when-do-i-construct-a-session-when-do-i-commit-it-and-when-do-i-close-it.

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[David Chavez]

Thank you so much, knowing this, I assume that I have some debugging
ahead, thank you again.

> You received this message because you are subscribed to a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy/kD7hl_jkCQI/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to