[sqlalchemy] Re: self referential eagerload issue
Patch works like a charm! Thanks once again, Alex --~--~-~--~~~---~--~~ 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] Bug in attribute access?
I had the following error since the update to SQLAlchemy 0.5.1 (we had to upgrade because it fixed a bug we had in the previous version :P). I'm not really sure how it manifest itself but here is the traceback: Traceback (most recent call last): [...] File string, line 4, in __init__ File /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.1-py2.5.egg/sqlalchemy/orm/attributes.py, line 880, in initialize_instance return manager.events.original_init(*mixed[1:], **kwargs) File [..]/model/business/yield_term_structure.py, line 57, in __init__ settlement_days, tenor, rolling_convention) File string, line 6, in __init__ File [...]/model/business/yield_term_structure.py, line 35, in __init__ self.currency = currency File /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.1-py2.5.egg/sqlalchemy/orm/attributes.py, line 151, in __set__ self.impl.set(instance_state(instance), value, None) AttributeError: 'NoneType' object has no attribute 'set' Do you have any hints? -- Lawrence, http://oluyede.org - http://twitter.com/lawrenceoluyede It is difficult to get a man to understand something when his salary depends on not understanding it - Upton Sinclair --~--~-~--~~~---~--~~ 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: INSERT ... ON DUPLICATE KEY UPDATE
Thank you for the reply. However, this solution (though I'm ready to use it) would create a lot of SQL queries comparing it with simple INSERT ... ON DUPLICATE KEY UPDATE. On the other hand, I admit the INSERT ... IN DUPLICATE KEY UPDATE might not be available in other DBs. I would like the application would be independent of the database engine bellow. So... is there some way how to achieve this while keeping number of SQL queries low? :-) (The number of objects handled this way is about 20 000.) Thanks c. --~--~-~--~~~---~--~~ 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: Bug in attribute access?
fixed in trunk, for 0.5.2. On Jan 23, 2009, at 4:44 AM, Lawrence Oluyede wrote: I had the following error since the update to SQLAlchemy 0.5.1 (we had to upgrade because it fixed a bug we had in the previous version :P). I'm not really sure how it manifest itself but here is the traceback: Traceback (most recent call last): [...] File string, line 4, in __init__ File /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.1-py2.5.egg/ sqlalchemy/orm/attributes.py, line 880, in initialize_instance return manager.events.original_init(*mixed[1:], **kwargs) File [..]/model/business/yield_term_structure.py, line 57, in __init__ settlement_days, tenor, rolling_convention) File string, line 6, in __init__ File [...]/model/business/yield_term_structure.py, line 35, in __init__ self.currency = currency File /usr/lib/python2.5/site-packages/SQLAlchemy-0.5.1-py2.5.egg/ sqlalchemy/orm/attributes.py, line 151, in __set__ self.impl.set(instance_state(instance), value, None) AttributeError: 'NoneType' object has no attribute 'set' Do you have any hints? -- Lawrence, http://oluyede.org - http://twitter.com/lawrenceoluyede It is difficult to get a man to understand something when his salary depends on not understanding it - Upton Sinclair --~--~-~--~~~---~--~~ 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] question on using not_
Hi there, I have a table tblPerson that has a m:n relation with a table tblFlag using an association table tblCompany_has_Flag now I would like to find all persons, that do not have assigned any of list of flags. what I have tried among other things is the following: s = session.query(tblPerson) s = s.join(tblCompany_has_Flag, tblFlag) s = s.join(tblCompany) s = s.filter(not_(tblFlag.__table__.c.name.in_(['a','b']))) however this does not create a valid sql statement. I would be grateful if somebody could tell me how to build the query correctly. thanks robert --~--~-~--~~~---~--~~ 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: Bug in attribute access?
On Fri, Jan 23, 2009 at 2:43 PM, Michael Bayer mike...@zzzcomputing.com wrote: fixed in trunk, for 0.5.2. Ok, thank you. Do you have an estimated date of release? Thank you for the hard work! -- Lawrence, http://oluyede.org - http://twitter.com/lawrenceoluyede It is difficult to get a man to understand something when his salary depends on not understanding it - Upton Sinclair --~--~-~--~~~---~--~~ 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: Bug in attribute access?
possibly today. On Jan 23, 2009, at 8:52 AM, Lawrence Oluyede wrote: On Fri, Jan 23, 2009 at 2:43 PM, Michael Bayer mike...@zzzcomputing.com wrote: fixed in trunk, for 0.5.2. Ok, thank you. Do you have an estimated date of release? Thank you for the hard work! -- Lawrence, http://oluyede.org - http://twitter.com/lawrenceoluyede It is difficult to get a man to understand something when his salary depends on not understanding it - Upton Sinclair --~--~-~--~~~---~--~~ 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: Bug in attribute access?
On Fri, Jan 23, 2009 at 2:55 PM, Michael Bayer mike...@zzzcomputing.com wrote: possibly today. Awesome. -- Lawrence, http://oluyede.org - http://twitter.com/lawrenceoluyede It is difficult to get a man to understand something when his salary depends on not understanding it - Upton Sinclair --~--~-~--~~~---~--~~ 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: INSERT ... ON DUPLICATE KEY UPDATE
camlost wrote: Thank you for the reply. However, this solution (though I'm ready to use it) would create a lot of SQL queries comparing it with simple INSERT ... ON DUPLICATE KEY UPDATE. On the other hand, I admit the INSERT ... IN DUPLICATE KEY UPDATE might not be available in other DBs. I would like the application would be independent of the database engine bellow. So... is there some way how to achieve this while keeping number of SQL queries low? :-) (The number of objects handled this way is about 20 000.) Sure, if your process will be the only one inserting and changing these rows. Working through your 20k python objects in batches of 1000 or whatever size you like, collect the key values from the python objects. Run a database select to see which of those keys are present in the database, and then divide your batch into two parts: data needing insert and data needing update. If you've got write contention for this data you'd need to work more granularly (likely row by row) instead, keeping in mind the database engine's transaction model and ideally taking advantage of any tools the db engine provides (like ON DUPLICATE or sql's MERGE) . Performance and engine agnosticism may be mutually exclusive here. --~--~-~--~~~---~--~~ 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: MSSQL Reflection Error
Greg wrote: I think I found one that might help. Where can I upload this HTML file it generated? In case you don't get a better answer: open the file in your browser, then cut paste the text the browser displays (or take a screenshot of the browser window and upload that as a gif or jpg). -- Don Dwiggins Advanced Publishing Technology --~--~-~--~~~---~--~~ 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: MSSQL Reflection Error
Uh, did you guys not see my last message in this thread? --~--~-~--~~~---~--~~ 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] Mixing synonyms and properties with reflected tables..
It appears this doesn't currently work as expected (ver 0.5rc2, Python
2.5) when the tables are reflected (autoload = True).
I have two reflected (legacy) tables that define similar properties with
different names and different units (both are time, one is in seconds
the other in minutes). e.g:
class A(object):
pass
class B(object):
pass
a = A()
a.timeunits = 60 # A.timeunit is in seconds
b = B()
b.time_units = 1 # B.time_unit is in minutes
I'd like to present a coherent interface for these two class in my ORM
layer. Both classes should appear to have a property 'timeunit' that is
expressed in seconds. Both classes should be able to use 'timeunits' in
filter expressions and suchlike and (A.timeunits = 60) == (B.timeunits =
60) == (B.time_units * 60).
Reading this example:
http://www.sqlalchemy.org/docs/04/mappers.html#advdatamapping_mapper_overriding
I tried the following kind of approach:
class B(object):
def _set_time_unit(self, value):
self.timeunit = value / float(60)
def _get_time_unit(self)
return self.timeunit = value * 60
time_unit = property(_get_time_unit, _set_time_unit)
Mapper(B, table_b, properties = { time_unit : synonym(timeunit,
map_column = True})
But what happens (if you add a few prints and whatnot to illustrate) is
that the property setter is never called either when objects are being
instanced from the database or when setting the property of an existing
instance of B. Seems that the reflection mechanism has hijacked (via
it's own descriptors I'm imagining) attribute access in a way that makes
it impossible for this kind of symmetrical translation to occur.
I would have thought this might be a relatively common problem with
legacy databases so I'd thought check with the list to see if a solution
or workaround had already been found. Couldn't find an obvious parallel
in the list archives so I'm appealing to the group memory for some help
on this.
Am I just getting this wrong or have I just come against something that
just can't currently be done ? I can thing of alternative schemes that
might work but they seem to lose me the ability to use the synonym as
spelled in filter expressions
Thanks in advance,
Toby Bradshaw
--
Ideaworks 3d,
London, UK.
--~--~-~--~~~---~--~~
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: MSSQL Reflection Error
Yeah, I can get it to you, but in the reply box I'm not seeing anything here about attachments. There's just Send, Discard, Add Cc, or Edit Subject. I am set up to use this group only through the browser. On Jan 23, 12:35 pm, Don Dwiggins d...@dondwiggins.net wrote: Greg wrote: I think I found one that might help. Where can I upload this HTML file it generated? In case you don't get a better answer: open the file in your browser, then cut paste the text the browser displays (or take a screenshot of the browser window and upload that as a gif or jpg). -- Don Dwiggins Advanced Publishing Technology --~--~-~--~~~---~--~~ 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: MSSQL Reflection Error
Thanks, I'll try this out and let you know how it goes. On Jan 23, 12:43 pm, Rick Morrison rickmorri...@gmail.com wrote: Uh, did you guys not see my last message in this thread? --~--~-~--~~~---~--~~ 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: Mixing synonyms and properties with reflected tables..
On Jan 23, 2009, at 1:43 PM, Toby Bradshaw wrote: But what happens (if you add a few prints and whatnot to illustrate) is that the property setter is never called either when objects are being instanced from the database or when setting the property of an existing instance of B. Seems that the reflection mechanism has hijacked (via it's own descriptors I'm imagining) attribute access in a way that makes it impossible for this kind of symmetrical translation to occur. I would have thought this might be a relatively common problem with legacy databases so I'd thought check with the list to see if a solution or workaround had already been found. Couldn't find an obvious parallel in the list archives so I'm appealing to the group memory for some help on this. Am I just getting this wrong or have I just come against something that just can't currently be done ? I can thing of alternative schemes that might work but they seem to lose me the ability to use the synonym as spelled in filter expressions the synonym() construct and the mapped attribute it creates represents a proxy to the mapped column only in the context of accessing and setting values within the python application space. It is not invoked when the database populates the value of the mapped column itself. the general idea of synonym is that the column-based information stays represented on the mapped instance in the identical manner that it does within the database, and the synonym-based attribute interprets application-level values into the column representation. it should definitely be invoked when setting the property of an existing instance of B, so if that's not working you can share with us exactly how you are configuring things, since you might be missing some detail. --~--~-~--~~~---~--~~ 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: MSSQL Reflection Error
The commit mentioned earlier fixed the issue. Thanks for all the 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 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] deferred column_property bug in 0.5.1?
I was debugging some stuff recently and noticed different behavior in one of my applications than I was expecting. The behavior is the undesirable execution of some sql. The sql is used to populate the value of a column_property. The column_property has been deferred, however, and the property isn't even being accessed. I narrowed it down to a strange interaction with transactions: First, I'm setting up my session like this, if it matters. DBSession = scoped_session( sessionmaker(bind=engine, autoflush=False, autocommit=True) ) ... and later I have a mapper defined with one of the properties being: 'user_count': column_property(..., deferred=True) Assume the property is on an 'Account' object. For apps which do *not* demonstrate this behavior, they typically do this: sess = DBSession() sess.begin() sess.rollback() or sess.commit() In this case, the app does this: sess = DBSession() sess.begin() accounts = get_the_accounts(...) sess.rollback() for account in accounts: # ANY access to the account object here causes it to be re-loaded, # including *all* columns and properties, even those that were # deferred. print account.anything Thus, accesses to objects loaded by a session which has been rolled back or committed not only re-load the object but all of the deferred attributes as well. I even tried using deferred(...) on a standard column and that, too, was loaded when I didn't expect it to be. If I remove all begin/commit/rollback things work as expected. -- Jon --~--~-~--~~~---~--~~ 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: deferred column_property bug in 0.5.1?
Ive made an adjustment in r5720 such that _expire_state() will exclude unloaded, deferred attribute names from the list of attributes to expire. So deferreds won't load in that specific case. Otherwise, explicitly expiring the deferred attribute or expiring when the deferred attribute was loaded will load the attribute on access, as will session.refresh(). session.refresh() might warrant adjustment as well but that's not a very common operation compared to full instance expiration. On Jan 23, 2009, at 6:20 PM, Jon Nelson wrote: I was debugging some stuff recently and noticed different behavior in one of my applications than I was expecting. The behavior is the undesirable execution of some sql. The sql is used to populate the value of a column_property. The column_property has been deferred, however, and the property isn't even being accessed. I narrowed it down to a strange interaction with transactions: First, I'm setting up my session like this, if it matters. DBSession = scoped_session( sessionmaker(bind=engine, autoflush=False, autocommit=True) ) ... and later I have a mapper defined with one of the properties being: 'user_count': column_property(..., deferred=True) Assume the property is on an 'Account' object. For apps which do *not* demonstrate this behavior, they typically do this: sess = DBSession() sess.begin() sess.rollback() or sess.commit() In this case, the app does this: sess = DBSession() sess.begin() accounts = get_the_accounts(...) sess.rollback() for account in accounts: # ANY access to the account object here causes it to be re-loaded, # including *all* columns and properties, even those that were # deferred. print account.anything Thus, accesses to objects loaded by a session which has been rolled back or committed not only re-load the object but all of the deferred attributes as well. I even tried using deferred(...) on a standard column and that, too, was loaded when I didn't expect it to be. If I remove all begin/commit/rollback things work as expected. -- Jon --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
