Re: [sqlalchemy] foreign key to *any* other class?
Thanks Michael, very useful. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Beware of DateTime and sqlite [Problem solved]
Hi This is to warn people working with sqlalchemy and sqlite and date/time columns. I know, the topic has already been discussed, but I found it difficult to relate this topic with the error messages I got when trying the following: transactions_table = Table('transactions', metadata, ... Column('enter_date', DateTime)) on a sqlite database created originaly by gnucash, a free accounting application. This means, I don't use sqlalchemy to put data in but only to take data out. The above definition worked on mysql but failed on sqlite, with the following exception: Traceback (most recent call last): File /p/python/exp/of_sqlalchemy/minimal-gc.py, line 50, in module filter(Transaction.description.like(unicode(desc))).all() File /usr/local/python2.6/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/orm/query.py, line 1267, in all return list(self) File /usr/local/python2.6/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/orm/query.py, line 1422, in instances rows = [process[0](context, row) for row in fetch] File /usr/local/python2.6/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/orm/query.py, line 2032, in main return _instance(row, None) File /usr/local/python2.6/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/orm/mapper.py, line 1711, in _instance populate_state(state, dict_, row, isnew, only_load_props) File /usr/local/python2.6/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/orm/mapper.py, line 1596, in populate_state populator(state, dict_, row, isnew=isnew, **flags) File /usr/local/python2.6/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/orm/strategies.py, line 120, in new_execute dict_[key] = row[col] File /usr/local/python2.6/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/engine/base.py, line 1348, in __getitem__ return self.__parent._get_col(self.__row, key) File /usr/local/python2.6/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/engine/base.py, line 1620, in _get_col return processor(row[index]) File /usr/local/python2.6/lib/python2.6/site-packages/SQLAlchemy-0.5.6-py2.6.egg/sqlalchemy/databases/sqlite.py, line 183, in process return fn(*[int(x or 0) for x in regexp.match(value).groups()]) AttributeError: 'NoneType' object has no attribute 'groups' I had difficulties relating this message to a datetime problem. It was fixed by changing the column type to unicode ( sqlite stores dates as strings ): transactions_table = Table('transactions', metadata, ... Column('enter_date', Unicode(50)) Conclusion: Be careful with dates and sqlite Peter -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] predefined,dynamic query
hi all! i'm creating user interface for search in database, and i wondered of any of you knows how to generate query dynamically, without using 'eval ()' (slow) or 'engine.execute(my query)' (loosing flexability and efficiency - am i right?) the aim is to manage complex query (with or, and, join,multiple tables, private-user's tables) efficiently and without letting the user be aware of implementaion. anyone? :) thanks tom -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Sharding - same table definitions yet different (but predictable) table names... thousands of them
Thanks for the quick response, Michael. We are mapping the classes dynamically, and we patched an in-house copy of SQLAlchemy 0.5.6 with the stricter mutex from SQLAlchemy 0.6 to fix the sporadic mappers failed to compile errors we were seeing under production load. We also added a Least Recently Used cache for these dynamically mapped tables because we needed the memory usage to max out at something reasonable. When I get a chance, I'll play with UsageRecipes/EntityName to see if differs from our current approach in any significant ways. In the mean time, I'm stuck on another sharding question... which I might ask shortly ;) Thanks again for your time, --diana On Fri, Jan 8, 2010 at 10:32 AM, Michael Bayer mike...@zzzcomputing.com wrote: how does your application want to reference these tables ? while this might be a little memory consuming I'd probably look into generating mappers, Tables, and classes dynamically ala the recipe http://www.sqlalchemy.org/trac/wiki/UsageRecipes/EntityName . If the table is chosen based on the attributes of the class, I'd create new instances of the class using a factory function: for i in range(1, M): table = Table(circle_%d % i, ...) my_registry_of_classes[Circle_%d % i] = \ map_class_to_some_table(CircleBase, table, Circle_%d % i) def myobject(circle): return my_registry_of_classes[Circle_%d% circle]() x = myobject(circle=5) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] why query_chooser rather than shard_chooser in this case?
Hello again, I'm getting errors in a certain case which lead me to suspect that I'm missing some big picture sharding concept, so to better understand sharding I'm playing with the SQLAlchemy sharding unit tests (sqlalchemy/test/orm/sharding/test_shard.py). Here's one of the investigative tests I've added in order to better understand query_chooser: def test_read(self): session = create_session() query = session.query(WeatherLocation) print get tokyo: # query_chooser returns: ['asia'] tokyo = query.filter_by(city='Tokyo').filter_by (continent='Asia').first() print access tokyo: # query_chooser returns: ['north_america', 'asia', 'europe', 'south_america'] assert tokyo.city == Tokyo My question: If we already have an instance of tokyo from the 'get tokyo' code snippet, why is a new query_cls being instantiated to rerfesh the tokyo object on access (thus having to traverse all 4 shards) rather than using shard_chooser and the got instance to compute the shard based on its continent value? Is there some way I can optimize this case, perhaps by setting the shard_id somewhere, so that 4 queries aren't executed in this case? Hope that was clear enough. Thanks again for your time, --diana -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Oracle, ownership and unrecognized type warnings
Sorry for not getting back sooner. -- Jeffrey D Peterson Webmaster Crary Industries, Inc. -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Michael Bayer Sent: Wednesday, January 06, 2010 1:23 PM To: sqlalchemy@googlegroups.com Subject: Re: [sqlalchemy] Oracle, ownership and unrecognized type warnings crary_web wrote: For production I will need to connect as user webserv who has no ownership at all only select grants, and will only have access to views. Currently, with what I have deciphered for myself, I can't do this. I cannot reflect a view at all, it complains about primary keys which I can understand, but is there a way around this as it's not practical to ask our DBA to put pks on the hundreds of views I will possibly need to access, if he can at all. its true that there's no built in functionality to reflect views. In the case of your views, just create Table instances manually, specifying the view names, column names, and column types explicitly. Set the primary_key=True flag on those columns which you'd like to consider as part of the primary key within your application. No database change is necessary. So, does doing this actually create a new table in the DB? I tried this, and in my instance it failed, but I could see it trying to run a create table command. Is this the way it works? Is there no other way? The user I connect with will not have privileges to do this. If you need to reflect an actual table in the DB, and you'd like to override what columns are considered as part of the primary key within your application, you specify those columns explicitly as in http://www.sqlalchemy.org/docs/05/metadata.html#overriding-reflected-columns. I can reflect a table as long as I connect as the table owner which as I stated before I will not be able to do. What configuration flag am I missing or parameter I am not passing to make this ok? pass the schema='someowner' flag to each Table object. This worked, for the case of tables. FYI, using megrok.rdb you must include the class property __table_args__ as a dictionary i.e. Class ReflectedTable(megrok.rdb.Model): megrok.rdb.reflected() __table_args__ = {'schema':'someowner'} -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] why query_chooser rather than shard_chooser in this case?
diana wrote: Hello again, I'm getting errors in a certain case which lead me to suspect that I'm missing some big picture sharding concept, so to better understand sharding I'm playing with the SQLAlchemy sharding unit tests (sqlalchemy/test/orm/sharding/test_shard.py). Here's one of the investigative tests I've added in order to better understand query_chooser: def test_read(self): session = create_session() query = session.query(WeatherLocation) print get tokyo: # query_chooser returns: ['asia'] tokyo = query.filter_by(city='Tokyo').filter_by (continent='Asia').first() print access tokyo: # query_chooser returns: ['north_america', 'asia', 'europe', 'south_america'] assert tokyo.city == Tokyo My question: If we already have an instance of tokyo from the 'get tokyo' code snippet, why is a new query_cls being instantiated to rerfesh the tokyo object on access (thus having to traverse all 4 shards) rather than using shard_chooser and the got instance to compute the shard based on its continent value? You just got a new tokyo from the DB, and I assume no inherited tables are in effect, the session is brand new, so no SQL should be emitted when accessing tokyo.city, which I am assuming is a textual field. The key city should be present in tokyo.__dict__, and no Session should be accessed.Nothing I can see from the above code indicates a second SQL should be emitted. of course the details of the mapping might say something totally different (i.e. deferred(), joined table inhertance, etc.) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Oracle, ownership and unrecognized type warnings
Jeff Peterson wrote: its true that there's no built in functionality to reflect views. In the case of your views, just create Table instances manually, specifying the view names, column names, and column types explicitly. Set the primary_key=True flag on those columns which you'd like to consider as part of the primary key within your application. No database change is necessary. So, does doing this actually create a new table in the DB? no. SQLAlchemy never creates tables unless you tell it to. By create, i meant, sometable = Table(...), i.e., you are creating a Python object in your application which represents the structure of a table or view that is present in the database. *Not* calling table.create(). -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] why query_chooser rather than shard_chooser in this case?
A, deferred (new to me), thanks! in sqlalchemy/test/orm/sharding/test_shard.py: mapper(WeatherLocation, weather_locations, properties={ 'reports':relation(Report, backref='location'), 'city': deferred(weather_locations.c.city), }) When I comment out the deferred property, it behaves as I would suspect (one query_chooser call). Ok, that answers Question #1. Question #2 similar, but w/ session.add(). I'll send a new email for Question #2. Thanks, --diana On Mon, Jan 11, 2010 at 3:25 PM, Michael Bayer mike...@zzzcomputing.com wrote: of course the details of the mapping might say something totally different (i.e. deferred(), joined table inhertance, etc.) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] why query_chooser rather than shard_chooser in this case?
Again, this investigative test is loosely based on SQLAlchemy's sharding test: sqlalchemy/test/orm/sharding/test_shard.py def test_update(self): print \n session = create_session() query = session.query(WeatherLocation) # query_chooser returns: ['asia'] print get tokyo: tokyo = query.filter_by(city='Tokyo').filter_by(continent='Asia').first() # no new SQL print access tokyo: assert tokyo.city == Tokyo # no new SQL print change tokyo: tokyo.city = Tokyo_city_name_changed # uses shard_chooser by instance print save tokyo: session.add(tokyo) session.commit() # query_chooser returns: ['north_america', 'asia', 'europe', 'south_america'] print access tokyo 2: assert tokyo.city == Tokyo_city_name_changed My question #2: If we already have an instance of tokyo from the 'save tokyo' code snippet, why is a new query_cls being instantiated to refresh the tokyo object in 'access tokyo 2' (thus having to traverse all 4 shards) rather than using shard_chooser and the got instance to compute the shard based on its continent value? Is there some way I can optimize this case, perhaps by setting the shard_id somewhere, so that 4 queries aren't executed in this case? Thanks, --diana On Mon, Jan 11, 2010 at 3:38 PM, Diana Clarke diana.joan.cla...@gmail.com wrote: Question #2 similar, but w/ session.add(). I'll send a new email for Question #2. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Oracle, ownership and unrecognized type warnings
Hmmm, must be something megrok.rdb is doing I am not seeing, I will look into it. -- Jeffrey D Peterson Webmaster Crary Industries, Inc. -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Michael Bayer Sent: Monday, January 11, 2010 2:31 PM To: sqlalchemy@googlegroups.com Subject: RE: [sqlalchemy] Oracle, ownership and unrecognized type warnings Jeff Peterson wrote: its true that there's no built in functionality to reflect views. In the case of your views, just create Table instances manually, specifying the view names, column names, and column types explicitly. Set the primary_key=True flag on those columns which you'd like to consider as part of the primary key within your application. No database change is necessary. So, does doing this actually create a new table in the DB? no. SQLAlchemy never creates tables unless you tell it to. By create, i meant, sometable = Table(...), i.e., you are creating a Python object in your application which represents the structure of a table or view that is present in the database. *Not* calling table.create(). -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] Oracle, ownership and unrecognized type warnings
OK, it is definitely megrok.rdb, the last thing it does is call metadata.create_all() so, I will email the megrok folks. Thanks a bunch. -- Jeffrey D Peterson Webmaster Crary Industries, Inc. -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Jeff Peterson Sent: Monday, January 11, 2010 3:14 PM To: sqlalchemy@googlegroups.com Subject: RE: [sqlalchemy] Oracle, ownership and unrecognized type warnings Hmmm, must be something megrok.rdb is doing I am not seeing, I will look into it. -- Jeffrey D Peterson Webmaster Crary Industries, Inc. -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalch...@googlegroups.com] On Behalf Of Michael Bayer Sent: Monday, January 11, 2010 2:31 PM To: sqlalchemy@googlegroups.com Subject: RE: [sqlalchemy] Oracle, ownership and unrecognized type warnings Jeff Peterson wrote: its true that there's no built in functionality to reflect views. In the case of your views, just create Table instances manually, specifying the view names, column names, and column types explicitly. Set the primary_key=True flag on those columns which you'd like to consider as part of the primary key within your application. No database change is necessary. So, does doing this actually create a new table in the DB? no. SQLAlchemy never creates tables unless you tell it to. By create, i meant, sometable = Table(...), i.e., you are creating a Python object in your application which represents the structure of a table or view that is present in the database. *Not* calling table.create(). -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] why query_chooser rather than shard_chooser in this case?
Diana Clarke wrote: Again, this investigative test is loosely based on SQLAlchemy's sharding test: sqlalchemy/test/orm/sharding/test_shard.py def test_update(self): print \n session = create_session() query = session.query(WeatherLocation) # query_chooser returns: ['asia'] print get tokyo: tokyo = query.filter_by(city='Tokyo').filter_by(continent='Asia').first() # no new SQL print access tokyo: assert tokyo.city == Tokyo # no new SQL print change tokyo: tokyo.city = Tokyo_city_name_changed # uses shard_chooser by instance print save tokyo: session.add(tokyo) session.commit() # query_chooser returns: ['north_america', 'asia', 'europe', 'south_america'] print access tokyo 2: assert tokyo.city == Tokyo_city_name_changed My question #2: If we already have an instance of tokyo from the 'save tokyo' code snippet, why is a new query_cls being instantiated to refresh the tokyo object in 'access tokyo 2' (thus having to traverse all 4 shards) rather than using shard_chooser and the got instance to compute the shard based on its continent value? Is there some way I can optimize this case, perhaps by setting the shard_id somewhere, so that 4 queries aren't executed in this case? well there's two things, one left over from previous. one is that commit() expires all attributes in the session. that is why new SQL is emitted. check the docs for rationale there. but also, the loading of deferred attributes as earlier and expired attributes here does have the primary key, so its a bug that shard_chooser is being run here, since the internal function doing that is calling query._get(), whereas ShardedQuery is being simple and only overriding get(). You might want to change ShardedQuery to override _get() instead (which leads me further towards pulling the trigger of moving shard.py out to examples altogether for 06, since it really is not supportable as a core element, just FYI). Its also possibly worth it to get your ShardChooser to the point where it can recognize what is effectively a get() based on filtering criterion. You can do this by imitating the approach in the example FindContinent chooser in examples/sharding/attribute_shard.py. Thanks, --diana On Mon, Jan 11, 2010 at 3:38 PM, Diana Clarke diana.joan.cla...@gmail.com wrote: Question #2 similar, but w/ session.add(). I'll send a new email for Question #2. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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 sqlalch...@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] why query_chooser rather than shard_chooser in this case?
Thanks, Michael. This will take me a bit to digest, and I'm about to start the second shift as wife and mother... tomorrow maybe. Thanks again for the quick responses -- greatly exceeding expectations! Cheers, --diana On Mon, Jan 11, 2010 at 5:14 PM, Michael Bayer mike...@zzzcomputing.com wrote: well there's two things, one left over from previous. one is that commit() expires all attributes in the session. that is why new SQL is emitted. check the docs for rationale there. but also, the loading of deferred attributes as earlier and expired attributes here does have the primary key, so its a bug that shard_chooser is being run here, since the internal function doing that is calling query._get(), whereas ShardedQuery is being simple and only overriding get(). You might want to change ShardedQuery to override _get() instead (which leads me further towards pulling the trigger of moving shard.py out to examples altogether for 06, since it really is not supportable as a core element, just FYI). Its also possibly worth it to get your ShardChooser to the point where it can recognize what is effectively a get() based on filtering criterion. You can do this by imitating the approach in the example FindContinent chooser in examples/sharding/attribute_shard.py. Thanks, --diana -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] pylons SQLAlchemy memory
And now for a question about a completely different app (no sharding, very simple). I haven't got a sufficient response from the pylons group, so I'm trying here. The question: http://groups.google.com/group/pylons-discuss/browse_thread/thread/cb48d0ea2b084159 Things I've tried/considered: --- mysql utf8 memory issues --- different mapping approaches (base declarative, reflective, etc) --- various debug options and non-production standard settings --- beaker (although I'm not sure that I successfully turned it all off, I'll keep trying) --- indexes --- added tests for old-school classes (ones that don't extend object and aren't garbage collected) --- I've played with dozer (added to middleware.py, didn't find much -- old-school classed, maybe?) You wouldn't know it from my deluge of recent questions, but in all my years at this I've only asked these three threads worth of questions, plus an X11 one years and years ago on the the OpenBSD group. Anyway, I do apologize for having to ask so much from you and this group -- perhaps I'll be able to start giving back soon by answering some of the questions. Sorry, --diana -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] pylons SQLAlchemy memory
diana wrote: And now for a question about a completely different app (no sharding, very simple). I haven't got a sufficient response from the pylons group, so I'm trying here. The question: http://groups.google.com/group/pylons-discuss/browse_thread/thread/cb48d0ea2b084159 Things I've tried/considered: --- mysql utf8 memory issues --- different mapping approaches (base declarative, reflective, etc) --- various debug options and non-production standard settings --- beaker (although I'm not sure that I successfully turned it all off, I'll keep trying) --- indexes --- added tests for old-school classes (ones that don't extend object and aren't garbage collected) --- I've played with dozer (added to middleware.py, didn't find much -- old-school classed, maybe?) You wouldn't know it from my deluge of recent questions, but in all my years at this I've only asked these three threads worth of questions, plus an X11 one years and years ago on the the OpenBSD group. Anyway, I do apologize for having to ask so much from you and this group -- perhaps I'll be able to start giving back soon by answering some of the questions. the Python VM doesn't shrink very much in size once it's grown. GC can let go of everything but the VM size is there for good. Your query of 90K rows is growing it because MySQLdb fully buffers result sets before making them available. this is a MySQLdb limitation. Sorry, --diana -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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 sqlalch...@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] pylons SQLAlchemy memory
Le lundi 11 janvier 2010 à 15:55 -0800, diana a écrit : And now for a question about a completely different app (no sharding, very simple). I haven't got a sufficient response from the pylons group, so I'm trying here. The question: http://groups.google.com/group/pylons-discuss/browse_thread/thread/cb48d0ea2b084159 Well if you only want to count entries, use Query.count(), not Query.all(). It will be much more efficient, both on the DB side and on the Python side. Even if you use the entries one by one but don't need to keep them in memory afterwards, just use the iterative form (`for row in query: ...`). Regardless, you are fetching 9 objects and witnessing a 160MB increase in memory. This gives approximately 1.7KB per objects. Depending on the size and complexity of each row this is not necessarily surprising. Python will generally not be as memory-efficient as hand-tailored structures written in C, since there is a lot of genericity and flexibility in most Python datatypes. Objects in general can be quite big, because they are based on dictionaries (dict objects) which are themselves big. As for releasing memory, try to call gc.collect() after you have released the last reference to the result set. I'd be a bit surprised if SQLAlchemy created reference cycles, though -- and would be inclined to consider it a bug ;-) -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] error handling for sessionmaker function
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi. I'm updating some of my code to SQLALchemy 0.6, and I have noted a problem with the sessionmaker function. The problem is a compatibility one: old versions use the transactional parameter, new ones the autocommit parameter. Usually, to handle these problems I use the try/except method: try: return orm.sessionmaker(bind=bind, autocommit=autocommit) except TypeError: # COMPAT: SQLAlchemy 0.4.x, deprecated in 0.5.x transactional = not autocommit return orm.sessionmaker(bind=bind, transactional=transactional) However this does not work, since error is raise only ewhen the actual Session instance is created. As far as can understand, the sessionmaker function supports keyword arguments since user can specify a custom session class to use. Can error handling be improved? Thanks Manlio -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAktLvVYACgkQscQJ24LbaUSrRQCfab1w/JR+KUNdAo188hEn4NgK Rf8AoIJR/iGu0xHGTUv09Z3A6sjeydFg =w5ts -END PGP SIGNATURE- -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] pylons SQLAlchemy memory
On Mon, Jan 11, 2010 at 7:07 PM, Antoine Pitrou solip...@pitrou.net wrote: Le lundi 11 janvier 2010 à 15:55 -0800, diana a écrit : Well if you only want to count entries, use Query.count(), not Query.all(). Yup, I don't actually do this in a real app. I was just doing this (in a hello world app) as an exercise to illustrate a point, and to better understand pylons (and, as I'm just learning, the Python VM). Thanks Antoine, --diana -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] pylons SQLAlchemy memory
we have a full set of tests that ensure SQLA itself has no unreleased memory issues or excessive cycles and they've been in our trunk for several years, and we also nailed a few remaining corner cases over the past year which correspond to highly unusual usage patterns, so I'm very confident that there's no issues within SQLA itself. On Jan 11, 2010, at 7:17 PM, Diana Clarke wrote: On Mon, Jan 11, 2010 at 7:07 PM, Antoine Pitrou solip...@pitrou.net wrote: Le lundi 11 janvier 2010 à 15:55 -0800, diana a écrit : Well if you only want to count entries, use Query.count(), not Query.all(). Yup, I don't actually do this in a real app. I was just doing this (in a hello world app) as an exercise to illustrate a point, and to better understand pylons (and, as I'm just learning, the Python VM). Thanks Antoine, --diana -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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 sqlalch...@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] error handling for sessionmaker function
On Mon, Jan 11, 2010 at 4:07 PM, Manlio Perillo manlio.peri...@gmail.com wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi. I'm updating some of my code to SQLALchemy 0.6, and I have noted a problem with the sessionmaker function. The problem is a compatibility one: old versions use the transactional parameter, new ones the autocommit parameter. Usually, to handle these problems I use the try/except method: try: return orm.sessionmaker(bind=bind, autocommit=autocommit) except TypeError: # COMPAT: SQLAlchemy 0.4.x, deprecated in 0.5.x transactional = not autocommit return orm.sessionmaker(bind=bind, transactional=transactional) However this does not work, since error is raise only ewhen the actual Session instance is created. As far as can understand, the sessionmaker function supports keyword arguments since user can specify a custom session class to use. Can error handling be improved? How about: try: orm.create_session(autocommit=autocommit) except TypeError: # COMPAT: SQLAlchemy 0.4.x, deprecated in 0.5.x transactional = not autocommit return orm.sessionmaker(bind=bind, transactional=transactional) else: return orm.sessionmaker(bind=bind, autocommit=autocommit) Creating and disposing a session via create_session() in this way isn't particularly expensive and won't initiate any database connections or activity. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] error handling for sessionmaker function
On Jan 11, 2010, at 7:07 PM, Manlio Perillo wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi. I'm updating some of my code to SQLALchemy 0.6, and I have noted a problem with the sessionmaker function. The problem is a compatibility one: old versions use the transactional parameter, new ones the autocommit parameter. Usually, to handle these problems I use the try/except method: try: return orm.sessionmaker(bind=bind, autocommit=autocommit) except TypeError: # COMPAT: SQLAlchemy 0.4.x, deprecated in 0.5.x transactional = not autocommit return orm.sessionmaker(bind=bind, transactional=transactional) However this does not work, since error is raise only ewhen the actual Session instance is created. As far as can understand, the sessionmaker function supports keyword arguments since user can specify a custom session class to use. Can error handling be improved? i think instead of putting try/excepts all over the place in an attempt to achieve compat with 0.4 thorugh 0.6, just look at sqlalchemy.__version__ to determine the correct API. 0.4 also has severe performance issues so I'd urge everyone to drop it if they haven't already. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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] pylons SQLAlchemy memory
I never really suspected that this was a SQLAlchemy issue, which was why I didn't originally post this question to the SQLAlchemy group. I apologize if it came across that way. Time for me to do my python VM homework... My apologies, --diana On Mon, Jan 11, 2010 at 8:15 PM, Michael Bayer mike...@zzzcomputing.com wrote: we have a full set of tests that ensure SQLA itself has no unreleased memory issues or excessive cycles and they've been in our trunk for several years, and we also nailed a few remaining corner cases over the past year which correspond to highly unusual usage patterns, so I'm very confident that there's no issues within SQLA itself. On Jan 11, 2010, at 7:17 PM, Diana Clarke wrote: On Mon, Jan 11, 2010 at 7:07 PM, Antoine Pitrou solip...@pitrou.net wrote: Le lundi 11 janvier 2010 à 15:55 -0800, diana a écrit : Well if you only want to count entries, use Query.count(), not Query.all(). Yup, I don't actually do this in a real app. I was just doing this (in a hello world app) as an exercise to illustrate a point, and to better understand pylons (and, as I'm just learning, the Python VM). Thanks Antoine, --diana -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@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 sqlalch...@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 sqlalch...@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.