[sqlalchemy] Re: Full connection pool close
On 29 Gen, 21:03, Rick Morrison rickmorri...@gmail.com wrote: I then added a wait option which simply sleeps for brief period after closing the SA connections, and then does the connection count check. With a 1/2 second delay between the closing of the SA connection pool and the check for all connections closed, I get pretty reliable results for closing all connections. Please try the attached test on your machine and see if you get similar results. Great Rick! Thanks! I've run the test for a good number ( tons ) of times and i've had some bad closing results ( more or less 30% ) with a 1/2 a second delay. With 0.9 secs i had no problems at all! Cheers! Fabio --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Full connection pool close
I worked up a test case that simulates your usage, and checks the number of
open MSSQL connections using a call to the system stored proc sp_who, so
it can run in a more automated fashion.
I originally got mixed results on this, it would pass about 50% of the time
and fail about 50% of the time.
So I then added some options that would force a GC collection (the idea
being to force any finalizers for the pyodbc sockets to close them), which
increased the percentage of the time the test would pass, but not eliminate
the failures.
I then added a wait option which simply sleeps for brief period after
closing the SA connections, and then does the connection count check. With a
1/2 second delay between the closing of the SA connection pool and the check
for all connections closed, I get pretty reliable results for closing all
connections.
Please try the attached test on your machine and see if you get similar
results.
Rick
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
# -*- encoding: utf-8
This test checks that SQLAlchemy fully closes all pooled pyodbc connection when we dispose() the engine.
For this test to be meaningful, this test should be the only process opening and closing connections on the server.
# Import pyodbc first and force off ODBC connection pooling
import pyodbc
pyodbc.pooling = False
import sqlalchemy as sa
import sqlalchemy.orm
user = ''
pwd = ''
dsn = ''
dbname = 'satest'
server = 'localhost'
wait_time = .5
force_gc = False
# establish a baseline # of connections
c_pyodbc = pyodbc.connect('DRIVER={Sql Server}; SERVER=%s; DATABASE=%s; UID=%s; PWD=%s' %(server, dbname, user, pwd))
initial_connections = c_pyodbc.execute('sp_who %s' % user).fetchall()
# open the pooled connections from SA
dburi = 'mssql://%s:%...@%s' % (user, pwd, dsn)
sa_engine = sa.create_engine(dburi, echo=False, strategy='threadlocal')
sa_Session = sa.orm.scoped_session( sa.orm.sessionmaker( bind=sa_engine ) )
metadata = sa.MetaData(sa_engine)
sa_session = sa_Session()
t = sa.Table(closetest, metadata,
sa.Column('id', sa.INT, primary_key=True),
sa.Column('nm', sa.VARCHAR(20))
)
# TODO: try with autoload table
# exercise the connection
metadata.create_all()
t.insert().execute(nm='test')
tlist = t.select().execute().fetchall()
assert len(tlist) == 1
metadata.drop_all()
# close the connection
sa_session.close()
sa_Session.close_all()
sa_engine.dispose()
del sa_session
del sa_Session
del sa_engine
if wait_time:
import time
time.sleep(wait_time)
if force_gc:
import gc
gc.collect()
# ensure the number of connections has not grown
post_test_connections = c_pyodbc.execute('sp_who %s' % user).fetchall()
try:
assert len(post_test_connections) == len(initial_connections)
print 'Passed'
except:
print 'Open connections!: ', len(post_test_connections), len(initial_connections)
[sqlalchemy] Re: Full connection pool close
On 24 Gen, 23:31, Rick Morrison rickmorri...@gmail.com wrote: Oh... i didn't explain myself... I mean that it's already empty at the first cycle of the loop... It would be normal to not enter the loop if you haven't yet opened any connections, as connections are opened on demand. Make sure your program issues at least one query during this test. If you are already issuing queries, then bundle up this as a simple test case as you can make, and we'll have a look at it. I was already issuing some queries... ( that's why sql server profiler tells me that there's an opened connection ). Here's a more complete example: import pyodbc pyodbc.pooling = False import sqlalchemy as sa sa_engine=sa.create_engine(dbUri, echo=False, strategy='threadlocal') sa_Session = sa.orm.scoped_session( sessionmaker( bind=sa_engine ) ) metadata = sa.MetaData(sa_engine) sa_session = sa_Session() # The table definition... could be any table anyway.. #stations = sa.Table(Stations, metadata, #sa.Column(name, sa.VARCHAR(20), primary_key=True), #sa.Column(description, sa.String(200)), #sa.Column(priority, sa.SmallInteger()), #autoload=aload) stations.select().execute().fetchall() #Sql Server Profilers tells me that a connection is opened sa_session.close() sa_Session.close_all() sa_engine.dispose() del sa_session del sa_Session del sa_engine PS: Is there any method, function, class or whatever in sqlalchemy to get all opened connection pool to the DB ? In this case my sqlalchemy connection is closed but che conn pool il still alive at the db --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Full connection pool close
On 23 Gen, 23:43, Rick Morrison rickmorri...@gmail.com wrote: From your earlier post: a_session.close() sa_Session.close_all() sa_engine.dispose() del sa_engine but it does not close the connection! Here's Engine.dispose (line 1152, engine/base.py) def dispose(self): self.pool.dispose() self.pool = self.pool.recreate() ..and here's QueuePool.dispose (the default pool, line 646, pool.py) def dispose(self): while True: try: conn = self._pool.get(False) conn.close() except Queue.Empty: break self._overflow = 0 - self.size() if self._should_log_info: self.log(Pool disposed. + self.status()) So the normal path would be to indeed close the connection (but not necessarily to delete the connection itself, it just falls out of scope). Can you trace into the dispose() call and verify that these are being run? Hey, seems that you've got the problem. conn = self._pool.get( False ) is the problem It raises an Empty error...: File E:\test.py, line 175, in module Data.sa_engine.dispose() File c:\Python25\Lib\site-packages\sqlalchemy-0.4.8-py2.5.egg \sqlalchemy\engine\base.py, line 1133, in dispose self.pool.dispose() File C:\Python25\Lib\site-packages\sqlalchemy-0.4.8-py2.5.egg \sqlalchemy\pool.py, line 626, in dispose conn = self._pool.get(False) File c:\Python25\Lib\site-packages\sqlalchemy-0.4.8-py2.5.egg \sqlalchemy\queue.py, line 140, in get raise Empty --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Full connection pool close
Hey, seems that you've got the problem. conn = self._pool.get( False ) is the problem It raises an Empty error...: It's supposed to; that's the exit condition for the while True loop. It does make it at least once through the loop, though right? Enough to close any connections you may have open? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Full connection pool close
On 24 Gen, 21:27, Rick Morrison rickmorri...@gmail.com wrote: Hey, seems that you've got the problem. conn = self._pool.get( False ) is the problem It raises an Empty error...: It's supposed to; that's the exit condition for the while True loop. It does make it at least once through the loop, though right? Enough to close any connections you may have open? Oh... i didn't explain myself... I mean that it's already empty at the first cycle of the loop... --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Full connection pool close
Oh... i didn't explain myself... I mean that it's already empty at the first cycle of the loop... It would be normal to not enter the loop if you haven't yet opened any connections, as connections are opened on demand. Make sure your program issues at least one query during this test. If you are already issuing queries, then bundle up this as a simple test case as you can make, and we'll have a look at it. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Full connection pool close
On 21 Gen, 16:18, Michael Bayer mike...@zzzcomputing.com wrote: On Jan 21, 2009, at 5:22 AM, Smoke wrote: Hi, I'm not a SQLAchemy expert ( just an average user... ). I have an application that's causing me some problems... It's a monitoring application that connects to a MS Sql Server, so it's always on. Sometimes happens that casualy I have a DBAPIError with pyodbc. The error is something like [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_DBC failed After the first time I have this error every other DB operation generates this Error. So.. what I would like to do is completely close ( kill ) che active connection pool and recreate it. My code is somethink like this: sa_engine=sa.create_engine(dbUri, echo=False, strategy='threadlocal') sa_Session = sa.orm.scoped_session( sessionmaker(bind=sa_engine, autoflush=False) ) metadata = sa.MetaData(sa_engine) sa_session = sa_Session() and then: sa_session.close() sa_Session.close_all() sa_engine.dispose() del sa_session del sa_Session del sa_engine But after executing this Sql Server Profiler tells me that the connection is still opened. The only way is to kill the application. My sqlalchemy.__version__ is 0.4.8 assuming no other connections are checked out, that would close all connections. its possible the DBAPI is just not able to close the connection fully. try with a raw pyodbc application to see if this is the case. Other things to try are to use the NullPool with create_engine() which doesn't pool connections. Its also extremely unlikely that you should be using strategy='threadlocal' unless you are calling begin()/commit() from your Engine directly so you might want to take that out. So... i've tried raw pyodbc and i keeps che connection alive too if you just import pyodbc and create a connection instance. To make pyodbc close the connection is settine pyodbc.pooling = False. The people from pyodbc told me they will check out this behavior Using NullPool open and immediatly close the connection after doing the sql stuff, so it should be ok. I'll try and check if it doesn't affect my app behavior. About threadlocal.. yeap.. my fault! :P Thanks very much for the answer. Fabio --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Full connection pool close
To make pyodbc close the connection is settine pyodbc.pooling = False. Whoa, I didn't know pyodbc automatically used ODBC connection pooling. Seems like we should be turning that off if the user is using SQLA pooling. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Full connection pool close
I believe this is a setting you establish when you create the DSN yourself, no ? On Jan 23, 2009, at 12:27 PM, Rick Morrison wrote: To make pyodbc close the connection is settine pyodbc.pooling = False. Whoa, I didn't know pyodbc automatically used ODBC connection pooling. Seems like we should be turning that off if the user is using SQLA pooling. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Full connection pool close
Good question, I don't know the answer. But even if it were a DSN option, it's likely to be an optional one. In the absence of an explicit setting, shouldn't we default to having the setting off, not on? It sounds as if the pyodbc default is 'on'. I would argue for forcing it off anyway, even if set on: this potential double-layered pooling would make trying to do any cohesive state management strategy on the connections just about impossible, and would also effectively render any SQLA pool size settings rather meaningless. On Fri, Jan 23, 2009 at 12:51 PM, Michael Bayer mike...@zzzcomputing.comwrote: I believe this is a setting you establish when you create the DSN yourself, no ? On Jan 23, 2009, at 12:27 PM, Rick Morrison wrote: To make pyodbc close the connection is settine pyodbc.pooling = False. Whoa, I didn't know pyodbc automatically used ODBC connection pooling. Seems like we should be turning that off if the user is using SQLA pooling. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Full connection pool close
On 23 Gen, 19:21, Rick Morrison rickmorri...@gmail.com wrote: Good question, I don't know the answer. But even if it were a DSN option, it's likely to be an optional one. In the absence of an explicit setting, shouldn't we default to having the setting off, not on? It sounds as if the pyodbc default is 'on'. Well... to me it seems the default is on.. mkleehammer, the pyodbc maintainer told me that, anyway, even if it's on it should close the connection anyway when you do the con.close() and then del con ( to me this behavior seems the most correct.. it's not nice to have dead connection opened until the process is dead ). You can read his answer here: http://groups.google.com/group/pyodbc/browse_thread/thread/320eab14f6f8830f I would argue for forcing it off anyway, even if set on: this potential double-layered pooling would make trying to do any cohesive state management strategy on the connections just about impossible, and would also effectively render any SQLA pool size settings rather meaningless. On Fri, Jan 23, 2009 at 12:51 PM, Michael Bayer mike...@zzzcomputing.comwrote: I believe this is a setting you establish when you create the DSN yourself, no ? On Jan 23, 2009, at 12:27 PM, Rick Morrison wrote: To make pyodbc close the connection is settine pyodbc.pooling = False. Whoa, I didn't know pyodbc automatically used ODBC connection pooling. Seems like we should be turning that off if the user is using SQLA pooling. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Full connection pool close
his answer here: http://groups.google.com/group/pyodbc/browse_thread/thread/320eab14f6f8830f You say in that thread that you're already turning off the setting by issuing: import pyodbc pyodbc.pooling = False before you ever open an SQLAlchemy connection. Is that still the case? That would imply that the connection is being held open by SQLAlchemy, not the ODBC connection pooling. So Mike's original advice about using the NullPool should close the connections when you're done with them -- did that work for you? Mike / Jason: Wasn't there also some sort of verify connection feature that was added in the 0.5 series that would issue a do-nothing query on a connection when it was checked out from a pool just to make sure the connection was still working? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Full connection pool close
pyodbc has the pooling implemented in Python ??? that seems weird ? On Jan 23, 2009, at 2:46 PM, Rick Morrison wrote: his answer here: http://groups.google.com/group/pyodbc/browse_thread/thread/320eab14f6f8830f You say in that thread that you're already turning off the setting by issuing: import pyodbc pyodbc.pooling = False before you ever open an SQLAlchemy connection. Is that still the case? That would imply that the connection is being held open by SQLAlchemy, not the ODBC connection pooling. So Mike's original advice about using the NullPool should close the connections when you're done with them -- did that work for you? Mike / Jason: Wasn't there also some sort of verify connection feature that was added in the 0.5 series that would issue a do- nothing query on a connection when it was checked out from a pool just to make sure the connection was still working? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Full connection pool close
On Fri, Jan 23, 2009 at 3:05 PM, Michael Bayer mike...@zzzcomputing.comwrote: pyodbc has the pooling implemented in Python ??? that seems weird ? How did you get that idea from this thread? My read on it is that it uses ODBC connection pooling. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Full connection pool close
OK, it should use whatever is set on the ODBC DSN then. im not sure that pyodbc should have an opinion about it. is there a way to set pyodbc.pooling = None or some equivalent ? fyi I have MS SQL 2008 installed on a VM finally so i will be kicking MS's ass for the new 0.6 refactor.0.5 is still you guys. On Jan 23, 2009, at 3:09 PM, Rick Morrison wrote: On Fri, Jan 23, 2009 at 3:05 PM, Michael Bayer mike...@zzzcomputing.com wrote: pyodbc has the pooling implemented in Python ??? that seems weird ? How did you get that idea from this thread? My read on it is that it uses ODBC connection pooling. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Full connection pool close
On 23 Gen, 20:46, Rick Morrison rickmorri...@gmail.com wrote:
his answer here:
http://groups.google.com/group/pyodbc/browse_thread/thread/320eab14f6...
You say in that thread that you're already turning off the setting by
issuing:
import pyodbc
pyodbc.pooling = False
before you ever open an SQLAlchemy connection.
Is that still the case?
Yes. I've tried with raw pyodbc and the code:
import pyodbc
pyodbc.pooling = False
c = pyodbc.connect('DRIVER={SQL
Server};SERVER=localhost;DATABASE=DB_TEST;UID=sa;PWD=pass') # This
opens a real connection
c.close()
del c
closes the connection.
So i've tried another session like this:
import pyodbc
pyodbc.pooling = False
sa_engine=sa.create_engine(dbUri, echo=False, strategy='threadlocal')
sa_Session = sa.orm.scoped_session( sessionmaker(bind=sa_engine,
autoflush=False) )
metadata = sa.MetaData(sa_engine)
sa_session = sa_Session()
# Some queries in here ( and is only when i fire the first query that
Sql Server Profilers tells me that a connection is opened )
sa_session.close()
sa_Session.close_all()
sa_engine.dispose()
del sa_session
del sa_Session
del sa_engine
but it does not close the connection!
That would imply that the connection is being held
open by SQLAlchemy, not the ODBC connection pooling.
mmm.. Yes... that wath i thought at first too. But it's just a case
that pyodbc with pooling = True have the same behavior?
So Mike's original
advice about using the NullPool should close the connections when you're
done with them -- did that work for you?
Yes. But it's behavior is a little bit different. With NullPool every
database action opens and closes a connection. So basically the
connection is created every time i need it.
With pyodbc.pooling = False ( in raw pyodbc, and that's what i'd
expect with SA too... but maybe i'm wrong ) it opens a connection ( c
= pyodbc.connect('bla bla') ) and keeps it alive until I do c.close()
Mike / Jason: Wasn't there also some sort of verify connection feature
that was added in the 0.5 series that would issue a do-nothing query on a
connection when it was checked out from a pool just to make sure the
connection was still working?
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Full connection pool close
OK, it should use whatever is set on the ODBC DSN then. im not sure that pyodbc should have an opinion about it. Eh? is there a way to set pyodbc.pooling = None or some equivalent ? It's pyodbc.pooling = False, as appears many times upthread From the OP's description, it sounds like SA is somehow not forcefully closing the DBAPI connection (perhaps not disposing of the connection using del). --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Full connection pool close
On 23 Gen, 21:24, Rick Morrison rickmorri...@gmail.com wrote: OK, it should use whatever is set on the ODBC DSN then. im not sure that pyodbc should have an opinion about it. Eh? is there a way to set pyodbc.pooling = None or some equivalent ? It's pyodbc.pooling = False, as appears many times upthread From the OP's description, it sounds like SA is somehow not forcefully closing the DBAPI connection (perhaps not disposing of the connection using del). it's seems that it's not using close() at all, because my connection was closed before doing del con... --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Full connection pool close
From your earlier post: a_session.close() sa_Session.close_all() sa_engine.dispose() del sa_engine but it does not close the connection! Here's Engine.dispose (line 1152, engine/base.py) def dispose(self): self.pool.dispose() self.pool = self.pool.recreate() ..and here's QueuePool.dispose (the default pool, line 646, pool.py) def dispose(self): while True: try: conn = self._pool.get(False) conn.close() except Queue.Empty: break self._overflow = 0 - self.size() if self._should_log_info: self.log(Pool disposed. + self.status()) So the normal path would be to indeed close the connection (but not necessarily to delete the connection itself, it just falls out of scope). Can you trace into the dispose() call and verify that these are being run? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Full connection pool close
On Jan 21, 2009, at 5:22 AM, Smoke wrote: Hi, I'm not a SQLAchemy expert ( just an average user... ). I have an application that's causing me some problems... It's a monitoring application that connects to a MS Sql Server, so it's always on. Sometimes happens that casualy I have a DBAPIError with pyodbc. The error is something like [Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_DBC failed After the first time I have this error every other DB operation generates this Error. So.. what I would like to do is completely close ( kill ) che active connection pool and recreate it. My code is somethink like this: sa_engine=sa.create_engine(dbUri, echo=False, strategy='threadlocal') sa_Session = sa.orm.scoped_session( sessionmaker(bind=sa_engine, autoflush=False) ) metadata = sa.MetaData(sa_engine) sa_session = sa_Session() and then: sa_session.close() sa_Session.close_all() sa_engine.dispose() del sa_session del sa_Session del sa_engine But after executing this Sql Server Profiler tells me that the connection is still opened. The only way is to kill the application. My sqlalchemy.__version__ is 0.4.8 assuming no other connections are checked out, that would close all connections. its possible the DBAPI is just not able to close the connection fully. try with a raw pyodbc application to see if this is the case. Other things to try are to use the NullPool with create_engine() which doesn't pool connections. Its also extremely unlikely that you should be using strategy='threadlocal' unless you are calling begin()/commit() from your Engine directly so you might want to take that out. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
