[sqlalchemy] Re: Memory leak - is session.close() sufficient?
On Nov 7, 2008, at 6:21 AM, joelanman wrote: Hi, I'm getting a memory leak with my app - the stack is apache2, mod_wsgi, web.py - and then a lot of sqlalchemy and my own code. The issue may well not be with my usage of SQLA - just making sure there's nothing I might be doing wrong with it. At the start of every web request (__init__ for a controller) I open a Session(), and in __del__ I close() it. Is there anything else I ought to do? that is the correct procedure. The Session weak references everything anyway so its hard for it to cause a fast leak. Any pointers on how to chase down my memory problem? one way is to visually inspect your application for any global variables, like dictionaries, caches, etc. and to analyze if they grow arbitrarily. Anything that is global or held across requests is suspect and should be analyzed carefully. Also, any usage of __del__ should be carefully scrutinized, as a cyclical reference on an object that implements __del__ cannot be garbage collected (that you're using __del__ might be the issue here. __del__ is actually never really needed as you can use a weakref callback for cleanup activities). Some third party libraries can also cause leaky behavior, such as Beaker. Try disabling any caching utilities to see if that isolates the issue. The other way is to analyze leaks is to use gc facilities - such as peeking into gc.get_objects() to see what kinds of objects are growing the collection, or gc.set_debug(gc.DEBUG_LEAK) which deals with unreachable objects (i.e. because of __del__).I usually opt for the former since I don't really use __del__ but I use global caches of things quite a bit. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: persistent result set
On Nov 6, 2008, at 8:29 PM, Adam Ryan wrote: Wow, this is great stuff. I'll have to spend some time trying it out. My big question, though, is how to interact with this stored query using and_ and or_? I went off on a different track where I would store a list of filter objects with their respective method (and_, or_), and put them back together in a nested filter beast like so: # filter_list is a list of tuples: ( filter_object, filter_method) nested = None for filter, method in filter_list: if not nested: nested = filter else: nested = method( filter, nested ) query = query.filter( nested ) res = query.all() If instead I stored the query object, how would I then and_ and or_ it? you'd have to pull the _criterion off the Query and combine that into a new Query using and_ or or_. So you might want to just pickle the WHERE criterion instead of the whole Query object if that's all you need. Basically everything is picklable with this new module. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Memory leak - is session.close() sufficient?
Thanks for that - I'll check out those options - I am using Beaker for cache and sessions. In the meantime I found this post about a leak in MySQLdb 1.2.2 when using charset=UTF8, which I am: http://jjinux.blogspot.com/2008/09/python-debugging-memory-leaks.html I'm using SQLA to do all database activity - would this leak be relevant? Also - if you have time, could you explain your point about __del__? If I don't define it and call session.close(), will the session close itself anyway when the controller that called it expires? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Memory leak - is session.close() sufficient?
On Nov 7, 2008, at 10:17 AM, joelanman wrote: Thanks for that - I'll check out those options - I am using Beaker for cache and sessions. OK, with Beaker, if you are caching things on a dynamically generated key, such as a key constructed from arbitrary parameters, I strongly recommend you work with Beaker 1.1 which is not yet released, but is available from mercurial. Previous versions will store an in-memory record for each unique key, and if you have an arbitrary number of keys then you'll have an arbitrary growth in memory. 1.1 has a significant rearrangement of things so that this problem is resolved. Try disabling Beaker entirely to see if that clears the issue. In the meantime I found this post about a leak in MySQLdb 1.2.2 when using charset=UTF8, which I am: http://jjinux.blogspot.com/2008/09/python-debugging-memory-leaks.html I'm using SQLA to do all database activity - would this leak be relevant? I tried out his test program there and indeed it leaks memory like a wellspring, even if you put a cursor.close() in there. The good news is, when using SQLAlchemy with MySQL, you want to connect using ? charset=utf8use_unicode=0 , which disables MySQLdb's conversion of python bytestrings into unicode objects, since SQLA handles this in a more finely-controllable way (not to mention, non-leaking :) ). When I add use_unicode=0 to the test script, the memory leak ends. Also - if you have time, could you explain your point about __del__? If I don't define it and call session.close(), will the session close itself anyway when the controller that called it expires? The session will actually result in everything being closed out if it just falls out of scope. However, this might place some burden on the garbage collector to take care of it and wont necessarily close things out immediately. I don't know what web.py's architecture offers, but usually session management is assembled into a web framework by wrapping requests with a session open/close block, so that its handled in all cases without reliance on gc. This is described at http://www.sqlalchemy.org/docs/05/session.html#unitofwork_contextual_lifespan . --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] how to change table foreign key target schema?
Hi!
I have people_table:
people_table = sa.Table('people', meta.metadata,
sa.Column('id', sa.types.Integer, primary_key=True,
autoincrement=False),
sa.Column('street_id', sa.types.Integer,
sa.ForeignKey('streets.id'), nullable=False),
sa.Column('first_name', sa.types.Unicode(255), nullable=True),
sa.Column('last_name', sa.types.Unicode(255), nullable=False)
)
And I have to create that table at different than public schema.
If I do people_table.tometadata(meta.metadata,
schema='myschema').create() sqlalchemy create table but with street_id
= ForeignKey('streets.id') not ForeignKey('myschema.streets.id')
How to make FK's follow new table schema or how to change foreign key
target on the fly?
With people_table.foreign_keys[0].target_fullname =
'myschema.streets.id' it doesn't work ? I get AttributeError: can't
change attribute
Thanks!
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: how to change table foreign key target schema?
On Nov 7, 2008, at 10:37 AM, sector119 wrote:
Hi!
I have people_table:
people_table = sa.Table('people', meta.metadata,
sa.Column('id', sa.types.Integer, primary_key=True,
autoincrement=False),
sa.Column('street_id', sa.types.Integer,
sa.ForeignKey('streets.id'), nullable=False),
sa.Column('first_name', sa.types.Unicode(255), nullable=True),
sa.Column('last_name', sa.types.Unicode(255), nullable=False)
)
And I have to create that table at different than public schema.
If I do people_table.tometadata(meta.metadata,
schema='myschema').create() sqlalchemy create table but with street_id
= ForeignKey('streets.id') not ForeignKey('myschema.streets.id')
How to make FK's follow new table schema or how to change foreign key
target on the fly?
With people_table.foreign_keys[0].target_fullname =
'myschema.streets.id' it doesn't work ? I get AttributeError: can't
change attribute
to metadata() is not a widely used feature so I'd describe that
behavior as a bug. The ForeignKey should be converted over. As far
as your workaround, set the value on _colspec rather than
target_fullname and that should clear it up.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: how to change table foreign key target schema?
this bug is fixed in 0.5 trunk in r5250.
On Nov 7, 2008, at 10:37 AM, sector119 wrote:
Hi!
I have people_table:
people_table = sa.Table('people', meta.metadata,
sa.Column('id', sa.types.Integer, primary_key=True,
autoincrement=False),
sa.Column('street_id', sa.types.Integer,
sa.ForeignKey('streets.id'), nullable=False),
sa.Column('first_name', sa.types.Unicode(255), nullable=True),
sa.Column('last_name', sa.types.Unicode(255), nullable=False)
)
And I have to create that table at different than public schema.
If I do people_table.tometadata(meta.metadata,
schema='myschema').create() sqlalchemy create table but with street_id
= ForeignKey('streets.id') not ForeignKey('myschema.streets.id')
How to make FK's follow new table schema or how to change foreign key
target on the fly?
With people_table.foreign_keys[0].target_fullname =
'myschema.streets.id' it doesn't work ? I get AttributeError: can't
change attribute
Thanks!
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: persistent result set
OK, thank you. But one last thought: Is storing the query rather than the result really the way? I mean, after a couple dozen complex, expensive change operations, the user could end up with only 4 records. It would be more efficient to just store the indexes rather than redo all the queries over and over again. On the other hand, with only a few simple queries and thousands of resulting records, storing the indexes is obviously a drain. Something about eating cake comes to mind. Thanks again, Adam --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: persistent result set
On Nov 7, 2008, at 12:18 PM, Adam Ryan wrote: OK, thank you. But one last thought: Is storing the query rather than the result really the way? I mean, after a couple dozen complex, expensive change operations, the user could end up with only 4 records. It would be more efficient to just store the indexes rather than redo all the queries over and over again. if its only a few records that you can place into an in_() clause, then yes this is probably more efficient. On the other hand, with only a few simple queries and thousands of resulting records, storing the indexes is obviously a drain. if many records, then yes storing the criterion alone is more efficient :). --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Memory leak - is session.close() sufficient?
Hi, I'm getting a memory leak with my app - the stack is apache2, mod_wsgi, web.py - and then a lot of sqlalchemy and my own code. The issue may well not be with my usage of SQLA - just making sure there's nothing I might be doing wrong with it. At the start of every web request (__init__ for a controller) I open a Session(), and in __del__ I close() it. Is there anything else I ought to do? Any pointers on how to chase down my memory problem? Thanks --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] schema inspection api
To my knowledge, there doesn't exist a tool to extract schema
information from a database with a database independent API. SA does
this to some extent by providing table_names and reflectable methods
on it's Engine class, but I think it would be nice to have something
more comprehensive and fine grained.
It seems to me that ORM is only a part of SA's function. I've used SA's
connection, cursor (ResultProxy) and SQL abstractions without ORM as a
kind of higher level DBAPI with good results. I think that a schema
information API would fit well into SA's theme. The implementation
could make use of the non-ORM features as well as providing for some
other features like table reflection.
How would it fit in? I think it should be usable without requiring the
use of other SA facilities. Maybe like this:
*
import sqlalchemy as sa
from sqlalchemy.database import dbinfo
engine = sa.create_engine('postgres:///')
# or a dbapi connection
# return list of schema names
schema_names = dbinfo.getSchemaNames(engine)
# return list of table names
table_names = dbinfo.getTableNames(engine, schema_name='public')
# return list of view names
view_names = dbinfo.getViewNames(engine, schema_name='public')
# return the SQL statement required to get view names (no connectable)
view_names = dbinfo.getViewNames(schema_name='public')
# Also like this.
info = dblib.PostgresInfoFetcher(engine)
print info.getSchemaNames()
print info.getConstraintInfo(table_name='t1', schema_name='s1')
# Returns the SQL.
print info.getConstraintInfo(table_name='t1', schema_name='s1',
return_statement=True)
*
Note that a method can return records or an SQL statement. I have a
basic implementation of this based on information_schema that works with
Postgresql and MSSQL (and maybe Oracle).
I'd like to get some opinions on this before going any further. Is it
needed? If so, how should it be implemented? Would it be good as a
part of SA or should it be separate?
--Randall
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] SQLAlchemy 0.5.0rc3 Released
Hey list - The 0.5 series is taking its time getting to the final 0.5.0, but its all good as we are honing and refining this thing to perfection. I've already had rc2 on some near-production systems with no issue and its already widely used. This release is still on rc status since we made some very effective changes to memory management within the Session which reduce a good chunk of overhead to the garbage collection process, as well as some subtle Query fixes, both of which will benefit from widespread evaluation (no problems are known to exist). Download SQLA 0.5rc3 at: http://www.sqlalchemy.org/download.html 0.5.0rc3 - features - orm - Added two new hooks to SessionExtension: after_bulk_delete() and after_bulk_update(). after_bulk_delete() is called after a bulk delete() operation on a query. after_bulk_update() is called after a bulk update() operation on a query. - sql - SQL compiler optimizations and complexity reduction. The call count for compiling a typical select() construct is 20% less versus 0.5.0rc2. - Dialects can now generate label names of adjustable length. Pass in the argument label_length=value to create_engine() to adjust how many characters max will be present in dynamically generated column labels, i.e. somecolumn AS somelabel. Any value less than 6 will result in a label of minimal size, consiting of an underscore and a numeric counter. The compiler uses the value of dialect.max_identifier_length as a default. [ticket:1211] - ext - Added a new extension sqlalchemy.ext.serializer. Provides Serializer/Deserializer classes which mirror Pickle/Unpickle, as well as dumps() and loads(). This serializer implements an external object pickler which keeps key context-sensitive objects, including engines, sessions, metadata, Tables/Columns, and mappers, outside of the pickle stream, and can later restore the pickle using any engine/metadata/session provider. This is used not for pickling regular object instances, which are pickleable without any special logic, but for pickling expression objects and full Query objects, such that all mapper/engine/session dependencies can be restored at unpickle time. - oracle - Wrote a docstring for Oracle dialect. Apparently that Ohloh few source code comments label is starting to sting :). - Removed FIRST_ROWS() optimize flag when using LIMIT/OFFSET, can be reenabled with optimize_limits=True create_engine() flag. [ticket:536] - bugfixes and behavioral changes - orm - not equals comparisons of simple many-to-one relation to an instance will not drop into an EXISTS clause and will compare foreign key columns instead. - Removed not-really-working use cases of comparing a collection to an iterable. Use contains() to test for collection membership. - Improved the behavior of aliased() objects such that they more accurately adapt the expressions generated, which helps particularly with self-referential comparisons. [ticket:1171] - Fixed bug involving primaryjoin/secondaryjoin conditions constructed from class-bound attributes (as often occurs when using declarative), which later would be inappropriately aliased by Query, particularly with the various EXISTS based comparators. - Fixed bug when using multiple query.join() with an aliased-bound descriptor which would lose the left alias. - Improved weakref identity map memory management to no longer require mutexing, resurrects garbage collected instance on a lazy basis for an InstanceState with pending changes. - InstanceState object now removes circular references to itself upon disposal to keep it outside of cyclic garbage collection. - relation() won't hide unrelated ForeignKey errors inside of the please specify primaryjoin message when determining join condition. - Fixed bug in Query involving order_by() in conjunction with multiple aliases of the same class (will add tests in [ticket:1218]) - When using Query.join() with an explicit clause for the ON clause, the clause will be aliased in terms of the left side of the join, allowing scenarios like query(Source). from_self().join((Dest, Source.id==Dest.source_id)) to work properly. - polymorphic_union() function respects the key of each Column if they differ from the column's name. - Repaired support for passive-deletes on a many-to-one relation() with delete cascade. [ticket:1183] - Fixed bug in composite types which prevented a primary-key composite
[sqlalchemy] Re: schema inspection api
If anyone wants to toy with this, I posted it here for the meantime. Works with Postgresql and MSSQL for schema_names, table_names, constraints (including foreign keys) and columns. http://www.tnr.cc/dbinfo.py --Randall --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] select where field=max(field)
I am having trouble writing a sqlalchemy query which selects all rows where a field equals the max for that field, eg q = session.query(Snapshot).filter(Snapshot.totalqty==func.max(Snapshot.totalqty)) When I try and get the results of the query, I get the error below. How should I use func.max here? __version__ = 0.5.0rc3 In [54]: len(q.all()) Traceback (most recent call last): File ipython console, line 1, in ? File /home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/orm/query.py, line 994, in all return list(self) File /home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/orm/query.py, line 1082, in __iter__ return self._execute_and_instances(context) File /home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/orm/query.py, line 1085, in _execute_and_instances result = self.session.execute(querycontext.statement, params=self._params, mapper=self._mapper_zero_or_none(), _state=self._refresh_state) File /home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/orm/session.py, line 749, in execute return self.__connection(engine, close_with_result=True).execute( File /home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/engine/base.py, line 826, in execute return Connection.executors[c](self, object, multiparams, params) File /home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/engine/base.py, line 877, in execute_clauseelement return self._execute_compiled(elem.compile(dialect=self.dialect, column_keys=keys, inline=len(params) 1), distilled_params=params) File /home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/engine/base.py, line 889, in _execute_compiled self.__execute_raw(context) File /home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/engine/base.py, line 898, in __execute_raw self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context) File /home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/engine/base.py, line 942, in _cursor_execute self._handle_dbapi_exception(e, statement, parameters, cursor) File /home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/engine/base.py, line 924, in _handle_dbapi_exception raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect) ProgrammingError: (ProgrammingError) (, 'Invalid use of group function') u'SELECT snapshot.symbol AS snapshot_symbol, snapshot.strategy AS snapshot_strategy, snapshot.longshort AS snapshot_longshort, snapshot.datetime AS snapshot_datetime, snapshot.date AS snapshot_date, snapshot.year AS snapshot_year, snapshot.month AS snapshot_month, snapshot.qty AS snapshot_qty, snapshot.totalqty AS snapshot_totalqty, snapshot.price AS snapshot_price, snapshot.possize AS snapshot_possize, snapshot.pnl AS snapshot_pnl, snapshot.realized AS snapshot_realized, snapshot.pnl_hedged AS snapshot_pnl_hedged, snapshot.is_stop AS snapshot_is_stop, snapshot.stop_flag AS snapshot_stop_flag \nFROM snapshot \nWHERE snapshot.totalqty = max(snapshot.totalqty)' [] --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: persistent result set
On Friday 07 November 2008 19:18:35 Adam Ryan wrote: OK, thank you. But one last thought: Is storing the query rather than the result really the way? I mean, after a couple dozen complex, expensive change operations, the user could end up with only 4 records. It would be more efficient to just store the indexes rather than redo all the queries over and over again. On the other hand, with only a few simple queries and thousands of resulting records, storing the indexes is obviously a drain. Something about eating cake comes to mind. u can always create a new table and dump the references into it. for 4 or 4M records, all the same. then, join by it... storing the query is equivalent only as long as the db does not change meanwhile... the 4 records yesterday could grow into 14 today. And this can be defect and/or feature. so it depends who asks.. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: select where field=max(field)
Theres a good tutorial on the topic of GROUP BY from a SQL perspective, here: http://weblogs.sqlteam.com/jeffs/jeffs/archive/2007/07/20/60261.aspx in this case you probably want query.filter(Snapshot.totalqty==func.max(Snapshot.totalqty).select()). On Nov 7, 2008, at 3:22 PM, John Hunter wrote: I am having trouble writing a sqlalchemy query which selects all rows where a field equals the max for that field, eg q = session .query (Snapshot).filter(Snapshot.totalqty==func.max(Snapshot.totalqty)) When I try and get the results of the query, I get the error below. How should I use func.max here? __version__ = 0.5.0rc3 In [54]: len(q.all()) Traceback (most recent call last): File ipython console, line 1, in ? File /home/titan/johnh/dev/lib/python2.4/site-packages/ SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/orm/query.py, line 994, in all return list(self) File /home/titan/johnh/dev/lib/python2.4/site-packages/ SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/orm/query.py, line 1082, in __iter__ return self._execute_and_instances(context) File /home/titan/johnh/dev/lib/python2.4/site-packages/ SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/orm/query.py, line 1085, in _execute_and_instances result = self.session.execute(querycontext.statement, params=self._params, mapper=self._mapper_zero_or_none(), _state=self._refresh_state) File /home/titan/johnh/dev/lib/python2.4/site-packages/ SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/orm/session.py, line 749, in execute return self.__connection(engine, close_with_result=True).execute( File /home/titan/johnh/dev/lib/python2.4/site-packages/ SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/engine/base.py, line 826, in execute return Connection.executors[c](self, object, multiparams, params) File /home/titan/johnh/dev/lib/python2.4/site-packages/ SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/engine/base.py, line 877, in execute_clauseelement return self._execute_compiled(elem.compile(dialect=self.dialect, column_keys=keys, inline=len(params) 1), distilled_params=params) File /home/titan/johnh/dev/lib/python2.4/site-packages/ SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/engine/base.py, line 889, in _execute_compiled self.__execute_raw(context) File /home/titan/johnh/dev/lib/python2.4/site-packages/ SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/engine/base.py, line 898, in __execute_raw self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context) File /home/titan/johnh/dev/lib/python2.4/site-packages/ SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/engine/base.py, line 942, in _cursor_execute self._handle_dbapi_exception(e, statement, parameters, cursor) File /home/titan/johnh/dev/lib/python2.4/site-packages/ SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/engine/base.py, line 924, in _handle_dbapi_exception raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect) ProgrammingError: (ProgrammingError) (, 'Invalid use of group function') u'SELECT snapshot.symbol AS snapshot_symbol, snapshot.strategy AS snapshot_strategy, snapshot.longshort AS snapshot_longshort, snapshot.datetime AS snapshot_datetime, snapshot.date AS snapshot_date, snapshot.year AS snapshot_year, snapshot.month AS snapshot_month, snapshot.qty AS snapshot_qty, snapshot.totalqty AS snapshot_totalqty, snapshot.price AS snapshot_price, snapshot.possize AS snapshot_possize, snapshot.pnl AS snapshot_pnl, snapshot.realized AS snapshot_realized, snapshot.pnl_hedged AS snapshot_pnl_hedged, snapshot.is_stop AS snapshot_is_stop, snapshot.stop_flag AS snapshot_stop_flag \nFROM snapshot \nWHERE snapshot.totalqty = max(snapshot.totalqty)' [] --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: schema inspection api
On Nov 7, 2008, at 1:46 PM, Randall Smith wrote: To my knowledge, there doesn't exist a tool to extract schema information from a database with a database independent API. SA does this to some extent by providing table_names and reflectable methods on it's Engine class, but I think it would be nice to have something more comprehensive and fine grained. It seems to me that ORM is only a part of SA's function. I've used SA's connection, cursor (ResultProxy) and SQL abstractions without ORM as a kind of higher level DBAPI with good results. I think that a schema information API would fit well into SA's theme. The implementation could make use of the non-ORM features as well as providing for some other features like table reflection. How would it fit in? I think it should be usable without requiring the use of other SA facilities. Maybe like this: We did long ago attempt to build an information schema API, which was based on the information schema specification, before realizing that this model hardly works in any current RDBMS (for example, its excruciatingly slow on postgres) and is entirely inconsistent in any case (the queries to be used on MySQL are entirely different than those of PG, and MySQL doesn't even give you enough information to reconstruct a table with all of its constraints). I've looked at your sample code and I see it is based on information schema, so that aspect of it in my experience isn't going to travel very well. But an open API to our existing reflection facilities, adding in things like views and indexes, is a great idea. Since the dialects already have a great deal of inspection logic built in it would make sense that the current table reflection functions would build on top of these functions, since that's already what its doing. So the work I'd be willing to accept in this area would include adding additional Dialect functions which accomplish all the desired functionality of reading views, indexes, etc., building a front-end API on top of it which I'd probably call sqlalchemy.engine.reflection, and the biggest job of all would be comprehensive unit tests, for which I'd even make a new package, tests/reflection/*. We currently have tests/engine/reflection.py which is a pretty old module and could use a good deal of cleanup. I would gladly give commit access to anyone who wanted to take on this task, working in a branch to start which would later be merged to trunk. In particular we have a good deal of MS-SQL bugs regarding reflection, even a new one just today, that are in need of fixing. I dont have regular access to MS-SQL on my end so I rarely get to work with that code. One of the major tedious elements here is that the reflection code is custom for each dialect. Based on my experiences with information schema, I don't think there's any way around this. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: schema inspection api
If anyone wants to toy with this, I posted it here for the meantime. Works with Postgresql and MSSQL for schema_names, table_names, constraints (including foreign keys) and columns. http://www.tnr.cc/dbinfo.py eh, when all these attempts will be combined... here's mine: use as u can http://dbcook.svn.sourceforge.net/viewvc/dbcook/trunk/dbcook/misc/metadata/autoload.py http://dbcook.svn.sourceforge.net/viewvc/dbcook/trunk/dbcook/usage/sa_engine_defs.py usage: something like from sa_engine_defs import Dengine def open( db_type = 'memory', recreate =False, **engine_kargs): 'uses default urls, and can recreate' url,kargs = Dengine.setup( db_type, recreate, **engine_kargs) return sqlalchemy.create_engine( url, **kargs) ciao svil --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: select where field=max(field)
If you are okay with only getting one record in the case of ties you can do session.query(Snapshot).order_by(Snapshot.totalqty.desc()).first() On Fri, Nov 7, 2008 at 12:22 PM, John Hunter [EMAIL PROTECTED] wrote: I am having trouble writing a sqlalchemy query which selects all rows where a field equals the max for that field, eg q = session.query(Snapshot).filter(Snapshot.totalqty==func.max(Snapshot.totalqty)) When I try and get the results of the query, I get the error below. How should I use func.max here? __version__ = 0.5.0rc3 In [54]: len(q.all()) Traceback (most recent call last): File ipython console, line 1, in ? File /home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/orm/query.py, line 994, in all return list(self) File /home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/orm/query.py, line 1082, in __iter__ return self._execute_and_instances(context) File /home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/orm/query.py, line 1085, in _execute_and_instances result = self.session.execute(querycontext.statement, params=self._params, mapper=self._mapper_zero_or_none(), _state=self._refresh_state) File /home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/orm/session.py, line 749, in execute return self.__connection(engine, close_with_result=True).execute( File /home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/engine/base.py, line 826, in execute return Connection.executors[c](self, object, multiparams, params) File /home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/engine/base.py, line 877, in execute_clauseelement return self._execute_compiled(elem.compile(dialect=self.dialect, column_keys=keys, inline=len(params) 1), distilled_params=params) File /home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/engine/base.py, line 889, in _execute_compiled self.__execute_raw(context) File /home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/engine/base.py, line 898, in __execute_raw self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context) File /home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/engine/base.py, line 942, in _cursor_execute self._handle_dbapi_exception(e, statement, parameters, cursor) File /home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/engine/base.py, line 924, in _handle_dbapi_exception raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect) ProgrammingError: (ProgrammingError) (, 'Invalid use of group function') u'SELECT snapshot.symbol AS snapshot_symbol, snapshot.strategy AS snapshot_strategy, snapshot.longshort AS snapshot_longshort, snapshot.datetime AS snapshot_datetime, snapshot.date AS snapshot_date, snapshot.year AS snapshot_year, snapshot.month AS snapshot_month, snapshot.qty AS snapshot_qty, snapshot.totalqty AS snapshot_totalqty, snapshot.price AS snapshot_price, snapshot.possize AS snapshot_possize, snapshot.pnl AS snapshot_pnl, snapshot.realized AS snapshot_realized, snapshot.pnl_hedged AS snapshot_pnl_hedged, snapshot.is_stop AS snapshot_is_stop, snapshot.stop_flag AS snapshot_stop_flag \nFROM snapshot \nWHERE snapshot.totalqty = max(snapshot.totalqty)' [] --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: schema inspection api
Michael Bayer wrote: We did long ago attempt to build an information schema API, which was based on the information schema specification, before realizing that this model hardly works in any current RDBMS (for example, its excruciatingly slow on postgres) and is entirely inconsistent in any case (the queries to be used on MySQL are entirely different than those of PG, and MySQL doesn't even give you enough information to reconstruct a table with all of its constraints). I've looked at your sample code and I see it is based on information schema, so that aspect of it in my experience isn't going to travel very well. I was only using information_schema as a base (literally a base class implementation), knowing that most or all implementations would have quirks or performance issues and require method overrides. However, I would hope that implementations of information_schema would improve over time and that custom method overrides in subclasses could be removed, making the code cleaner and less complex. In short, start with the standard and go from there. But an open API to our existing reflection facilities, adding in things like views and indexes, is a great idea. Since the dialects already have a great deal of inspection logic built in it would make sense that the current table reflection functions would build on top of these functions, since that's already what its doing. So the work I'd be willing to accept in this area would include adding additional Dialect functions which accomplish all the desired functionality of reading views, indexes, etc., building a front-end API on top of it which I'd probably call sqlalchemy.engine.reflection, and the biggest job of all would be comprehensive unit tests, for which I'd even make a new package, tests/reflection/*. We currently have tests/engine/reflection.py which is a pretty old module and could use a good deal of cleanup. I would gladly give commit access to anyone who wanted to take on this task, working in a branch to start which would later be merged to trunk. In particular we have a good deal of MS-SQL bugs regarding reflection, even a new one just today, that are in need of fixing. I dont have regular access to MS-SQL on my end so I rarely get to work with that code. One of the major tedious elements here is that the reflection code is custom for each dialect. Based on my experiences with information schema, I don't think there's any way around this. reflecttable is very monolithic. Breaking it down into smaller components will make it easier to test. I follow you on adding methods to the Dialects and that seems like a good place for them. I don't know what kind of API sqlalchemy.engine.reflection would have. Is this something you could sketch out? I'd like to work on this. I have to do it anyway and would prefer getting it into SA instead of it being standalone. I've got access to Oracle 8, 9 and 10, Postgres ... and MSSQL (Express), which brings me to another topic. A project like SA needs access to all of the supported database systems for testing. Currently, it seems that developers are hampered because they don't have access to these systems. So I was thinking of ways to improve the situation and the idea I like the best is a VPN consisting of donated database setups. There would be a central VPN host and donors would connect their donated database servers to this host. Developers could then connect to the VPN host and access all of the database systems. With proper firewalling (iptables rules on the tun/tap devices) it should be safe for all parties. --Randall --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: select where field=max(field)
On Fri, Nov 7, 2008 at 3:57 PM, Michael Bayer [EMAIL PROTECTED] wrote: Theres a good tutorial on the topic of GROUP BY from a SQL perspective, here: http://weblogs.sqlteam.com/jeffs/jeffs/archive/2007/07/20/60261.aspx in this case you probably want query.filter(Snapshot.totalqty==func.max(Snapshot.totalqty).select()). Indeed, that does work: session.query(Snapshot.symbol).filter(Snapshot.datetime==func.max(Snapshot.datetime).select()).all() and I read the tutorial you pointed me an certainly get the idea that everything in the select list must be contained in the group by clause or used in an aggregate function, but I am still unclear on what the role of the select method is on the filter clause above, and how it pertains to the tutorial message. I'm now having a related problem that perhaps will shed light on my confusions... Here is a query that lists the sum(pnl) for each symbol and strategy in my snapshots table session.query(Snapshot.strategy, Snapshot.symbol, func.sum(Snapshot.pnl)).group_by(Snapshot.strategy, Snapshot.symbol).all() That works fine. But what if I only want to list the rows where the sum(pnl)-15000 ? I tried a few things: session.query(Snapshot.strategy, Snapshot.symbol, func.sum(Snapshot.pnl)).group_by(Snapshot.strategy, Snapshot.symbol).filter(func.sum(Snapshot.pnl)-15000).all() but got the error below raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect) ProgrammingError: (ProgrammingError) (, 'Invalid use of group function') u'SELECT snapshot.strategy AS snapshot_strategy, snapshot.symbol AS snapshot_symbol, sum(snapshot.pnl) AS sum_1 \nFROM snapshot \nWHERE sum(snapshot.pnl) %s GROUP BY snapshot.strategy, snapshot.symbol' [-15000] So I thought I'd try your select magic method, which as noted above I don't really understand how to use. But I get a new error when I call the select method on the sum func, eg In [47]: session.query(Snapshot.strategy, Snapshot.symbol, func.sum(Snapshot.pnl)).group_by(Snapshot.strategy, Snapshot.symbol).filter(-15000func.sum(Snapshot.pnl).select()).all() Traceback (most recent call last): File ipython console, line 1, in ? File string, line 1, in lambda File /home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/orm/query.py, line 52, in generate fn(self, *args[1:], **kw) File /home/titan/johnh/dev/lib/python2.4/site-packages/SQLAlchemy-0.5.0rc3dev_r5205-py2.4.egg/sqlalchemy/orm/query.py, line 624, in filter raise sa_exc.ArgumentError(filter() argument must be of type sqlalchemy.sql.ClauseElement or string) ArgumentError: filter() argument must be of type sqlalchemy.sql.ClauseElement or string Sorry to be flailing around a bit here -- hopefully you can shed some light for me! JDH --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQLAlchemy 0.5.0rc3 Released
Mike. You have absolutely perfect spelling. Better than 99% of the population. But there is just this one, and only one, English word that you spell strangely. You consistently spell propagate as propigate. Is there any way we can get an i/a switch in there? p.s. - Major props on being just around the corner from 0.5. I am in awe of SA as always. Eric --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
