[sqlalchemy] Removing a session (Really)
Hi Michael I'm having an issue with memory usage that I would appreciate some insight.. I have a fairly straight forward process, that works perfectly as far as it delivering the desired updates in the DB etc, however, it accumulates memory usage (just like a leak) and I can not find a way to release it. I have a multi threaded process. Each thread creates a scopedsession from the standard global Session = scoped_session(sessionmaker()) construct. Each thread does some work using mapped objects to update some and add some rows into the (MySQL on Windows) DB. All this works perfectly and as expected. Due to various reasons, I flush/commit after each row is updated/inserted. After the batch of updates is complete, I come back and session.remove() (In an attempt to dump the session) and then wait for a while and do the entire thing again. At the start of each run, I create a new session=Session() and do the updates and return and session.remove(). To me, I would assume that the memory would be the session's cache of objects that are being managed - which I can understand. What I can't understand is why when I delete *everything* e.g. del engine, del meta, del session and even stop the thread, the memory is still consumed.I must stop the entire process before the memory is returned to the system. After around 10 hours of running, I've used 2Gb+ of memory and everything crashes. BTW: I have created a version of my code that does everything EXCEPT the SA part(s), and no memory is being used at all. (Just checking that it wasn't my own code causing the issue!) i.e. It loops over the other database (non SQL) reading all the data that I WOULD use to update the SQL database using SA. When SA is not involved, nothing is happening with the memory. Any hint on how I can a) see what is being held and b) dump it! I'm using 0.6.3 and Python 2.6.3 on Windows. BTW: I tried to update to latest 7.x and Python 2.7.2 however that broke everything in a spectacular way - I'll leave that one for another day.. Cheers Warwick -- 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] slow get query - somes taking 1.5 sec
I'm seeing a simple get taking a considerable amount of time. a = session.query(Annotation).get(annotation.id) This line can take anywhere between 0.15 secs to all the way up to 1.5 secs ... The query sqlalchemy produces is below. If I execute the query in pgadmin, it consistently runs in 47 msecs. Any ideas? It seems to run longer if I issue the 'get' right after an update and another select query. If I wait a little bit (5 secs), then it seems to run closer to the 0.15 secs. SELECT (SELECT users.username FROM users WHERE annotations.user_id = users.id) AS created_by, annotations.id AS annotations_id, annotations.version AS annotations_version, annotations.message AS annotations_message, annotations.image_filename AS annotations_image_filename, annotations.user_id AS annotations_user_id, annotations.camera_id AS annotations_camera_id, annotations.game_id AS annotations_game_id, annotations.item_id AS annotations_item_id, annotations.creation_datetime AS annotations_creation_datetime, annotations.modified_datetime AS annotations_modified_datetime, annotations.camera_datetime AS annotations_persistent, anon_1.items_id AS anon_1_items_id, anon_1.items_type AS anon_1_items_type, anon_1.items_user_id AS anon_1_items_user_id, anon_1.items_game_id AS anon_1_items_game_id, anon_1.people_item_id AS anon_1_people_item_id, anon_1.vehicles_item_id AS anon_1_vehicles_item_id, anon_1.articles_item_id AS anon_1_articles_item_id, anon_1.relationships_item_id AS anon_1_relationships_item_id, anon_1.relationships_lower_item_id AS anon_1_relationships_lower_item_id, anon_1.relationships_higher_item_id AS anon_1_relationships_higher_item_id, anon_2.attributes_id AS anon_2_attributes_id, anon_2.attributes_name AS anon_2_attributes_name, anon_2.attributes_type AS anon_2_attributes_type, anon_2.attributes_annotation_id AS anon_2_attributes_annotation_id, anon_2.attributes_suspicion_level AS anon_2_attributes_suspicion_level, anon_2.vehicle_attributes_attributes_id AS anon_2_vehicle_attributes_attributes_id, anon_2.vehicle_attributes_vehicle_make AS anon_2_vehicle_attributes_vehicle_model, anon_2.relationship_attributes_attributes_id AS anon_2_relationship_attributes_attributes_id, anon_2.relationship_attributes_action AS anon_2_relationship_attributes_action, anon_2.police_report_attributes_attributes_id AS anon_2_police_report_attributes_attributes_id, anon_2.police_report_attributes_first_name AS anon_2_article_attributes_attributes_id, anon_2.article_attributes_stationary AS anon_2.person_attributes_attributes_id AS anon_2_person_attributes_attributes_id, anon_2.person_attributes_eye_color AS anon_2_person_attributes_eye_color, anon_2.person_attributes_hair_color AS anon_2_person_attributes_hair_color, anon_2.person_attributes_gender AS anon_2_person_attributes_gender, anon_2.person_attributes_height AS anon_2_person_attributes_height, anon_2.person_attributes_age AS anon_2_person_attributes_age, anon_2.person_attributes_build AS anon_2_person_attributes_build, anon_2.person_attributes_luggage AS anon_2_person_attributes_luggage, anon_2.person_attributes_mobility AS anon_2_person_attributes_mobility, anon_2.flight_checkin_attributes_attributes_id AS anon_2_flight_checkin_attributes_attributes_id, anon_2.flight_checkin_attributes_airline AS anon_2_flight_checkin_attributes_start_datetime_of_flight, anon_2.flight_checkin_attributes_end_datetime_of_flight AS anon_2_flight_checkin_attributes_end_datetime_of_flight, anon_2.flight_checkin_attributes_first_name AS anon_2_flight_checkin_attributes_first_name, anon_2.flight_checkin_attributes_last_name AS anon_2_lpr_attributes_attributes_id, anon_2.lpr_attributes_start_datetime AS anon_2_lpr_attributes_start_datetime, anon_2.lpr_attributes_end_datetime AS anon_2_lpr_attributes_end_datetime, anon_2.lpr_attributes_license_plate AS anon_2_lpr_attributes_license_plate, anon_2.lpr_attributes_action AS anon_2_lpr_attributes_action, anon_2.lpr_attributes_station AS anon_2_lpr_attributes_station, anon_2.gate_boarding_attributes_attributes_id AS anon_2_gate_boarding_attributes_attributes_id, anon_2.gate_boarding_attributes_airline AS anon_2_rmv_attributes_attributes_id, anon_2.rmv_attributes_first_name AS anon_2_rmv_attributes_first_name, anon_2.rmv_attributes_last_name AS anon_2_rmv_attributes_last_name, anon_2.rmv_attributes_address AS anon_2_rmv_attributes_address, anon_2.rmv_attributes_min_height_feet AS anon_2.rmv_attributes_min_age AS anon_2_rmv_attributes_min_age, anon_2.rmv_attributes_max_age AS anon_2_rmv_attributes_max_age, anon_2.rmv_attributes_eye_color AS anon_2_rmv_attributes_eye_color, anon_2.rmv_attributes_gender AS anon_2_rmv_attributes_gender, anon_2.rmv_attributes_license_plate AS anon_2_rmv_attributes_license_plate, anon_2.rmv_attributes_vehicle_color AS anon_2_rmv_attributes_vehicle_color, anon_2.rmv_attributes_vehicle_model AS anon_2_rmv_attributes_vehicle_model, anon_2.security_checkpoint_attributes_attributes_id AS
Re: [sqlalchemy] slow get query - somes taking 1.5 sec
You have a tremendous amount of LEFT OUTER JOINS in there, and its hard to tell but it seems like you're doing lots of with_polymorphic queries as well as lazy='joined' styles of relationships, making for a very cumbersome query. That it returns the first result quickly (that's your 47 msec) but takes a lot longer to actually return rows (thats your .15 sec) suggests it returns a lot of rows. I'd turn off all the with_polymorphic stuff, as well as not using any lazy='joined' for collections, lazy='subquery' is a better choice for collections but even then, usually better as a query time option rather than a fixed mapping configuration. On Jun 26, 2011, at 10:25 AM, nospam wrote: I'm seeing a simple get taking a considerable amount of time. a = session.query(Annotation).get(annotation.id) This line can take anywhere between 0.15 secs to all the way up to 1.5 secs ... The query sqlalchemy produces is below. If I execute the query in pgadmin, it consistently runs in 47 msecs. Any ideas? It seems to run longer if I issue the 'get' right after an update and another select query. If I wait a little bit (5 secs), then it seems to run closer to the 0.15 secs. SELECT (SELECT users.username FROM users WHERE annotations.user_id = users.id) AS created_by, annotations.id AS annotations_id, annotations.version AS annotations_version, annotations.message AS annotations_message, annotations.image_filename AS annotations_image_filename, annotations.user_id AS annotations_user_id, annotations.camera_id AS annotations_camera_id, annotations.game_id AS annotations_game_id, annotations.item_id AS annotations_item_id, annotations.creation_datetime AS annotations_creation_datetime, annotations.modified_datetime AS annotations_modified_datetime, annotations.camera_datetime AS annotations_persistent, anon_1.items_id AS anon_1_items_id, anon_1.items_type AS anon_1_items_type, anon_1.items_user_id AS anon_1_items_user_id, anon_1.items_game_id AS anon_1_items_game_id, anon_1.people_item_id AS anon_1_people_item_id, anon_1.vehicles_item_id AS anon_1_vehicles_item_id, anon_1.articles_item_id AS anon_1_articles_item_id, anon_1.relationships_item_id AS anon_1_relationships_item_id, anon_1.relationships_lower_item_id AS anon_1_relationships_lower_item_id, anon_1.relationships_higher_item_id AS anon_1_relationships_higher_item_id, anon_2.attributes_id AS anon_2_attributes_id, anon_2.attributes_name AS anon_2_attributes_name, anon_2.attributes_type AS anon_2_attributes_type, anon_2.attributes_annotation_id AS anon_2_attributes_annotation_id, anon_2.attributes_suspicion_level AS anon_2_attributes_suspicion_level, anon_2.vehicle_attributes_attributes_id AS anon_2_vehicle_attributes_attributes_id, anon_2.vehicle_attributes_vehicle_make AS anon_2_vehicle_attributes_vehicle_model, anon_2.relationship_attributes_attributes_id AS anon_2_relationship_attributes_attributes_id, anon_2.relationship_attributes_action AS anon_2_relationship_attributes_action, anon_2.police_report_attributes_attributes_id AS anon_2_police_report_attributes_attributes_id, anon_2.police_report_attributes_first_name AS anon_2_article_attributes_attributes_id, anon_2.article_attributes_stationary AS anon_2.person_attributes_attributes_id AS anon_2_person_attributes_attributes_id, anon_2.person_attributes_eye_color AS anon_2_person_attributes_eye_color, anon_2.person_attributes_hair_color AS anon_2_person_attributes_hair_color, anon_2.person_attributes_gender AS anon_2_person_attributes_gender, anon_2.person_attributes_height AS anon_2_person_attributes_height, anon_2.person_attributes_age AS anon_2_person_attributes_age, anon_2.person_attributes_build AS anon_2_person_attributes_build, anon_2.person_attributes_luggage AS anon_2_person_attributes_luggage, anon_2.person_attributes_mobility AS anon_2_person_attributes_mobility, anon_2.flight_checkin_attributes_attributes_id AS anon_2_flight_checkin_attributes_attributes_id, anon_2.flight_checkin_attributes_airline AS anon_2_flight_checkin_attributes_start_datetime_of_flight, anon_2.flight_checkin_attributes_end_datetime_of_flight AS anon_2_flight_checkin_attributes_end_datetime_of_flight, anon_2.flight_checkin_attributes_first_name AS anon_2_flight_checkin_attributes_first_name, anon_2.flight_checkin_attributes_last_name AS anon_2_lpr_attributes_attributes_id, anon_2.lpr_attributes_start_datetime AS anon_2_lpr_attributes_start_datetime, anon_2.lpr_attributes_end_datetime AS anon_2_lpr_attributes_end_datetime, anon_2.lpr_attributes_license_plate AS anon_2_lpr_attributes_license_plate, anon_2.lpr_attributes_action AS anon_2_lpr_attributes_action, anon_2.lpr_attributes_station AS anon_2_lpr_attributes_station, anon_2.gate_boarding_attributes_attributes_id AS anon_2_gate_boarding_attributes_attributes_id, anon_2.gate_boarding_attributes_airline AS anon_2_rmv_attributes_attributes_id,
Re: [sqlalchemy] Add helper methods to a model class
On Jun 25, 2011, at 10:59 PM, Arthur Kopatsy wrote: Hi, I have seen a few related questions but never a clear answer. I have a set of core models defined using a declarative form. These models are used by multiple applications. In each application we however want to extend this model class with helper methods. Since these methods are application specific, they cannot be define in the model itself. I have tries multiple ways with no success and all pretty gross in my opinion: 1. Subclass the original model, catch the SA load event and set __class__ to whatever I want. Problem: mapper was getting confused if the original model and the subclass had the same name. 2. Modify __bases__ and and my helper class to inject methods. Problem: it fails with model inheritance. 3. Monkey patch the module itself (models.MyModel = MyCustomModel). Problem: Mapper fails right away (class not mapped) 4. Monkey patching the class and add methods and attributes: best solution so far. What is the recommended way? I would love to be able to subclass the model and tell the mapper to use that new class instead... If these applications run in different process spaces, and if you are able to identify which application is running at module import time, you can achieve this effect through careful organization of imports: model/__init__.py model/some_model.py model/app1/__init__.py model/app1/helpers.py model/app2/__init__.py model/app2/helpers.py in model/__init__.py: if app == 'app1': from model.app1 import helpers elif app == 'app2': from model.app2 import helpers in model/some_model.py: from model import helpers class SomeWidget(Base, helpers.Widget): # declarations class SomeFoober(Base, helpers.SomeFoober): # declarations This might not be too different from your inject __bases__ approach. That should also work, if you're having trouble with inheritance you need to inject into __bases__ only at the appropriate points, checking each target class as to whether or not it's already part of an inheritance hierarchy. Another approach is to declare the model as mixins, then the individual applications declare the actual mapped models using those mixins. This is basically the same idea in the opposite direction. However that approach is more appropriate if the model itself also varies among applications (I have an app that does this). In this case it appears the variability is just on the some methods to be mixed in side. Thank you Arthur -- 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. -- 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.
Re: [sqlalchemy] Removing a session (Really)
On Jun 26, 2011, at 4:31 AM, Warwick Prince wrote: Hi Michael I'm having an issue with memory usage that I would appreciate some insight.. I have a fairly straight forward process, that works perfectly as far as it delivering the desired updates in the DB etc, however, it accumulates memory usage (just like a leak) and I can not find a way to release it. I have a multi threaded process. Each thread creates a scopedsession from the standard global Session = scoped_session(sessionmaker()) construct. Each thread does some work using mapped objects to update some and add some rows into the (MySQL on Windows) DB. All this works perfectly and as expected. Due to various reasons, I flush/commit after each row is updated/inserted. After the batch of updates is complete, I come back and session.remove() (In an attempt to dump the session) and then wait for a while and do the entire thing again. At the start of each run, I create a new session=Session() and do the updates and return and session.remove(). To me, I would assume that the memory would be the session's cache of objects that are being managed - which I can understand. What I can't understand is why when I delete *everything* e.g. del engine, del meta, del session and even stop the thread, the memory is still consumed.I must stop the entire process before the memory is returned to the system. After around 10 hours of running, I've used 2Gb+ of memory and everything crashes. By the memory is still consumed, if you're talking about the memory of your process, that's Python's behavior - once the size of memory usage grows to X, it stays at X no matter what you dereference within the process. So the key is to manage how large a collection ever gets filled up in the first place.The only true measure of python objects being leaked is the size of gc.get_objects(). If that size is managed, that's as far as Python code can go towards managing memory. So I'm assuming you just mean the size of the process. If you're dealing with large numbers of rows being loaded into memory, you'd need to cut down on the maximum size of objects loaded at once. The Session does not strongly reference anything, except for that which is present in the .new and .dirty collections. If those are empty, it is not strongly referencing anything, and as long as gc is enabled, the number of objects in memory will be managed.Older versions of Session in 0.5, 0.4 and such were not as good at this, but in 0.6, 0.7 it's quite solid, there is a whole suite of unit tests that ensure SQLAlchemy components like Engine, Session, schema, etc. do not leak memory under a variety of setup/teardown situations. But it seems like you're don't yet know if you're experiencing a problem at the Python object level. i.e. It loops over the other database (non SQL) reading all the data that I WOULD use to update the SQL database using SA. When SA is not involved, nothing is happening with the memory. note that DBAPIs, particularly older versions of MySQLdb, may have memory leaks, and most DBAPIs when asked to fetch a result will load the full set of results into memory before fetchone() is ever called, thus causing a great unconditional increase in the size of memory if you are fetching very large result sets. Any hint on how I can a) see what is being held and b) dump it! gc.get_objects() -- 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.
Re: [sqlalchemy] SQLAlchemy 0.6.5 (and 0.6.8) SessionExtension after_flush doesn't gets called
The flush events only fire off if there's actually something to be flushed. It would be inefficient for the events to be emitted for every flush() as flush is in fact called a great number of times, on every query, assuming autoflush enabled. For this reason a flush() with a session that has no change events of any kind quickly checks some flags and returns. Think of before_flush() really being called before_flush_on_pending_changes() if that helps. I'll check the docstrings to see if any clarification is needed. On Jun 25, 2011, at 4:33 AM, kost BebiX wrote: I've created this question at stackoverflow http://stackoverflow.com/questions/6476652/sqlalchemy-0-6-5-and-0-6-8-sessionextension-after-flush-doesnt-gets-called , but I thought maybe I should also ask here. I don't get it, but this code doesn't call after_flush/before_flush/after_flush_postexec # -*- coding: utf-8 -*- from sqlalchemy.orm import scoped_session, sessionmaker from sqlalchemy.orm.interfaces import SessionExtension class AfterFlushExtension(SessionExtension): def before_commit(self, session): print before_commit def after_commit(self, session): print after_commit def before_flush(self, session, flush_context, instances): print ' before_flush' def after_flush(self, session, flush_context): print ' after_flush' def after_flush_postexec(self, session, flush_context): print ' after_flush_postexec' session = scoped_session(sessionmaker(extension=AfterFlushExtension())) session.flush() session.commit() And a result: $ python ~/Dropbox/playground/python/sqlalchemy_hook_test/main.py before_commit after_commit Thank you. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/40uHGUoHJ1sJ. 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. -- 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: Add helper methods to a model class
Thanks Michael. However, I also want my queries to return objects with the helpers. The rebasing method does achieve this but I am not sure how the method you are describing will. Using mixin is interesting but it will force my applications to map them and therefore know about the table layout. Also, it potentially allows applications to add columns to the models which I do not want. Arthur On Jun 26, 9:26 am, Michael Bayer mike...@zzzcomputing.com wrote: On Jun 25, 2011, at 10:59 PM, Arthur Kopatsy wrote: Hi, I have seen a few related questions but never a clear answer. I have a set of core models defined using a declarative form. These models are used by multiple applications. In each application we however want to extend this model class with helper methods. Since these methods are application specific, they cannot be define in the model itself. I have tries multiple ways with no success and all pretty gross in my opinion: 1. Subclass the original model, catch the SA load event and set __class__ to whatever I want. Problem: mapper was getting confused if the original model and the subclass had the same name. 2. Modify __bases__ and and my helper class to inject methods. Problem: it fails with model inheritance. 3. Monkey patch the module itself (models.MyModel = MyCustomModel). Problem: Mapper fails right away (class not mapped) 4. Monkey patching the class and add methods and attributes: best solution so far. What is the recommended way? I would love to be able to subclass the model and tell the mapper to use that new class instead... If these applications run in different process spaces, and if you are able to identify which application is running at module import time, you can achieve this effect through careful organization of imports: model/__init__.py model/some_model.py model/app1/__init__.py model/app1/helpers.py model/app2/__init__.py model/app2/helpers.py in model/__init__.py: if app == 'app1': from model.app1 import helpers elif app == 'app2': from model.app2 import helpers in model/some_model.py: from model import helpers class SomeWidget(Base, helpers.Widget): # declarations class SomeFoober(Base, helpers.SomeFoober): # declarations This might not be too different from your inject __bases__ approach. That should also work, if you're having trouble with inheritance you need to inject into __bases__ only at the appropriate points, checking each target class as to whether or not it's already part of an inheritance hierarchy. Another approach is to declare the model as mixins, then the individual applications declare the actual mapped models using those mixins. This is basically the same idea in the opposite direction. However that approach is more appropriate if the model itself also varies among applications (I have an app that does this). In this case it appears the variability is just on the some methods to be mixed in side. Thank you Arthur -- 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 athttp://groups.google.com/group/sqlalchemy?hl=en. -- 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.
Re: [sqlalchemy] Re: Add helper methods to a model class
On Jun 26, 2011, at 2:51 PM, Arthur Kopatsy wrote: Thanks Michael. However, I also want my queries to return objects with the helpers. The rebasing method does achieve this but I am not sure how the method you are describing will. It absolutely does because the class you define with Base + helpers.some mixin is the class that gets mapped.SomeWidget is the class that's mapped. Any instance of SomeWidget, returned by Query, will include both Base as well as the helpers.Widget in its __mro__. Using mixin is interesting but it will force my applications to map them and therefore know about the table layout. Well yes that is a different approach which is not appropriate here. I had the opposite issue that you do 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: Raising exceptions from TypeDecorator.process_bind_param()
Michael, On 22 июн, 17:48, Michael Bayer mike...@zzzcomputing.com wrote: On Jun 22, 2011, at 3:41 AM, Anton wrote: On 22 июн, 01:31, Michael Bayer mike...@zzzcomputing.com wrote: On Jun 21, 2011, at 7:04 PM, Anton wrote: On 22 июн, 00:47, Michael Bayer mike...@zzzcomputing.com wrote: I added StatementError after having too often a custom type or other kind of error happen deep inside the preparation for execution with no indication what statement or parameter caused the issue. It's an enhancement, and the original exception is associated with the new one as orig. In Python 3 this works even more nicely as you can see we're doing raise x from e, and you get both stacktraces. What about the StatementError is not working for you ? I'd advise against doing things like business-level validations in types. In my case it's more like sanity check than business-logic. And the reason to do this checks in type is that I want to delay it as much as possible. It would be acceptable to have another exception instead of custom, but the problem is that it is not easy to understand, what's went wrong looking at the stack trace. It doesn't show at the last line its original exception, only StatementError: 'query text' [params list]. well Python 3 fixes this problem entirely by allowing exception chains. What text would you prefer in StatementError ? Ideally it would work like it's used to in 0.5 and 0.6. Maybe, we may do it like this: + if isinstance(e, exc.SQLAlchemyError): + # don't suppress or reraise already handled exceptions + return Well, actually I'd like every exception that happens in an execution to include information about the statement, regardless of if a SQLAlchemy construct raised it or not. The very common use case is when a script is emitting hundreds of statements through a flush, then way deep inside, there's one bound value that blows up. A stack trace like that leads up to a mapper and the unit of work but otherwise gives no context at all about where this offending value might be. It's unfortunate we have to wait for Python 3 for chained exceptions, but the practice of reraising an exception as another one, including context, which then links to the original as the cause, is an extremely common and useful practice - it's why Python 3 added it. Its unfortunate we disagree on this so I've added a mixin for you in r876ac91fd699 called DontWrapMixin. StatementError also displays the original exception class. Unfortunately this approach doesn't work with python 2.4 which doesn't allow multiple inherritance for exception classes, where one of parent classes is new-style: TypeError: exceptions must be classes, instances, or strings (deprecated), not PathOverflowError. We may avoid such problem if we make DontWrapMixin derive from Exception (which is old-style in 2.4 and new-style in 2.5+). -- Anton -- 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.
Re: [sqlalchemy] Removing a session (Really)
Excellent - thanks :-) Warwick On 27/06/2011, at 2:37 AM, Michael Bayer wrote: On Jun 26, 2011, at 4:31 AM, Warwick Prince wrote: Hi Michael I'm having an issue with memory usage that I would appreciate some insight.. I have a fairly straight forward process, that works perfectly as far as it delivering the desired updates in the DB etc, however, it accumulates memory usage (just like a leak) and I can not find a way to release it. I have a multi threaded process. Each thread creates a scopedsession from the standard global Session = scoped_session(sessionmaker()) construct. Each thread does some work using mapped objects to update some and add some rows into the (MySQL on Windows) DB. All this works perfectly and as expected. Due to various reasons, I flush/commit after each row is updated/inserted. After the batch of updates is complete, I come back and session.remove() (In an attempt to dump the session) and then wait for a while and do the entire thing again. At the start of each run, I create a new session=Session() and do the updates and return and session.remove(). To me, I would assume that the memory would be the session's cache of objects that are being managed - which I can understand. What I can't understand is why when I delete *everything* e.g. del engine, del meta, del session and even stop the thread, the memory is still consumed.I must stop the entire process before the memory is returned to the system. After around 10 hours of running, I've used 2Gb+ of memory and everything crashes. By the memory is still consumed, if you're talking about the memory of your process, that's Python's behavior - once the size of memory usage grows to X, it stays at X no matter what you dereference within the process. So the key is to manage how large a collection ever gets filled up in the first place. The only true measure of python objects being leaked is the size of gc.get_objects(). If that size is managed, that's as far as Python code can go towards managing memory. So I'm assuming you just mean the size of the process. If you're dealing with large numbers of rows being loaded into memory, you'd need to cut down on the maximum size of objects loaded at once. The Session does not strongly reference anything, except for that which is present in the .new and .dirty collections. If those are empty, it is not strongly referencing anything, and as long as gc is enabled, the number of objects in memory will be managed.Older versions of Session in 0.5, 0.4 and such were not as good at this, but in 0.6, 0.7 it's quite solid, there is a whole suite of unit tests that ensure SQLAlchemy components like Engine, Session, schema, etc. do not leak memory under a variety of setup/teardown situations. But it seems like you're don't yet know if you're experiencing a problem at the Python object level. i.e. It loops over the other database (non SQL) reading all the data that I WOULD use to update the SQL database using SA. When SA is not involved, nothing is happening with the memory. note that DBAPIs, particularly older versions of MySQLdb, may have memory leaks, and most DBAPIs when asked to fetch a result will load the full set of results into memory before fetchone() is ever called, thus causing a great unconditional increase in the size of memory if you are fetching very large result sets. Any hint on how I can a) see what is being held and b) dump it! gc.get_objects() -- 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. -- 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.
Re: [sqlalchemy] Re: Raising exceptions from TypeDecorator.process_bind_param()
On Jun 26, 2011, at 6:13 PM, Anton wrote: Unfortunately this approach doesn't work with python 2.4 which doesn't allow multiple inherritance for exception classes, where one of parent classes is new-style: TypeError: exceptions must be classes, instances, or strings (deprecated), not PathOverflowError. We may avoid such problem if we make DontWrapMixin derive from Exception (which is old-style in 2.4 and new-style in 2.5+). was hoping you didn't care about 2.4 :). Try r69d2da905578 where the mixin is simply not a new style class if 2.4 is detected. -- 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: slow get query - somes taking 1.5 sec
Yes, I'm using polymorphic mappers. It's actually only 1 row - querying the object by id. I have lazy=false for any referenced objects to the one I'm querying for. On Jun 26, 12:16 pm, Michael Bayer mike...@zzzcomputing.com wrote: You have a tremendous amount of LEFT OUTER JOINS in there, and its hard to tell but it seems like you're doing lots of with_polymorphic queries as well as lazy='joined' styles of relationships, making for a very cumbersome query. That it returns the first result quickly (that's your 47 msec) but takes a lot longer to actually return rows (thats your .15 sec) suggests it returns a lot of rows. I'd turn off all the with_polymorphic stuff, as well as not using any lazy='joined' for collections, lazy='subquery' is a better choice for collections but even then, usually better as a query time option rather than a fixed mapping configuration. On Jun 26, 2011, at 10:25 AM, nospam wrote: I'm seeing a simple get taking a considerable amount of time. a = session.query(Annotation).get(annotation.id) This line can take anywhere between 0.15 secs to all the way up to 1.5 secs ... The query sqlalchemy produces is below. If I execute the query in pgadmin, it consistently runs in 47 msecs. Any ideas? It seems to run longer if I issue the 'get' right after an update and another select query. If I wait a little bit (5 secs), then it seems to run closer to the 0.15 secs. SELECT (SELECT users.username FROM users WHERE annotations.user_id = users.id) AS created_by, annotations.id AS annotations_id, annotations.version AS annotations_version, annotations.message AS annotations_message, annotations.image_filename AS annotations_image_filename, annotations.user_id AS annotations_user_id, annotations.camera_id AS annotations_camera_id, annotations.game_id AS annotations_game_id, annotations.item_id AS annotations_item_id, annotations.creation_datetime AS annotations_creation_datetime, annotations.modified_datetime AS annotations_modified_datetime, annotations.camera_datetime AS annotations_persistent, anon_1.items_id AS anon_1_items_id, anon_1.items_type AS anon_1_items_type, anon_1.items_user_id AS anon_1_items_user_id, anon_1.items_game_id AS anon_1_items_game_id, anon_1.people_item_id AS anon_1_people_item_id, anon_1.vehicles_item_id AS anon_1_vehicles_item_id, anon_1.articles_item_id AS anon_1_articles_item_id, anon_1.relationships_item_id AS anon_1_relationships_item_id, anon_1.relationships_lower_item_id AS anon_1_relationships_lower_item_id, anon_1.relationships_higher_item_id AS anon_1_relationships_higher_item_id, anon_2.attributes_id AS anon_2_attributes_id, anon_2.attributes_name AS anon_2_attributes_name, anon_2.attributes_type AS anon_2_attributes_type, anon_2.attributes_annotation_id AS anon_2_attributes_annotation_id, anon_2.attributes_suspicion_level AS anon_2_attributes_suspicion_level, anon_2.vehicle_attributes_attributes_id AS anon_2_vehicle_attributes_attributes_id, anon_2.vehicle_attributes_vehicle_make AS anon_2_vehicle_attributes_vehicle_model, anon_2.relationship_attributes_attributes_id AS anon_2_relationship_attributes_attributes_id, anon_2.relationship_attributes_action AS anon_2_relationship_attributes_action, anon_2.police_report_attributes_attributes_id AS anon_2_police_report_attributes_attributes_id, anon_2.police_report_attributes_first_name AS anon_2_article_attributes_attributes_id, anon_2.article_attributes_stationary AS anon_2.person_attributes_attributes_id AS anon_2_person_attributes_attributes_id, anon_2.person_attributes_eye_color AS anon_2_person_attributes_eye_color, anon_2.person_attributes_hair_color AS anon_2_person_attributes_hair_color, anon_2.person_attributes_gender AS anon_2_person_attributes_gender, anon_2.person_attributes_height AS anon_2_person_attributes_height, anon_2.person_attributes_age AS anon_2_person_attributes_age, anon_2.person_attributes_build AS anon_2_person_attributes_build, anon_2.person_attributes_luggage AS anon_2_person_attributes_luggage, anon_2.person_attributes_mobility AS anon_2_person_attributes_mobility, anon_2.flight_checkin_attributes_attributes_id AS anon_2_flight_checkin_attributes_attributes_id, anon_2.flight_checkin_attributes_airline AS anon_2_flight_checkin_attributes_start_datetime_of_flight, anon_2.flight_checkin_attributes_end_datetime_of_flight AS anon_2_flight_checkin_attributes_end_datetime_of_flight, anon_2.flight_checkin_attributes_first_name AS anon_2_flight_checkin_attributes_first_name, anon_2.flight_checkin_attributes_last_name AS anon_2_lpr_attributes_attributes_id, anon_2.lpr_attributes_start_datetime AS anon_2_lpr_attributes_start_datetime, anon_2.lpr_attributes_end_datetime AS anon_2_lpr_attributes_end_datetime, anon_2.lpr_attributes_license_plate AS anon_2_lpr_attributes_license_plate,
