On Sep 15, 2011, at 6:39 AM, Jeff wrote: > SQLAlchemy version 0.7.1 > MySQL Ver 14.12 Distrib 5.0.77 > > We have a series of tables with one to many connections: > A -> B -> C -> D->E etc. > > Script1 has a big for loop over several hundred/thousand values. In > each loop iteration it goes through A,B,C, makes some new entries, > then calls Function1 (passing some ids from A,B,C). > Function1 makes a new entry in D, then calls Function2 (passing ids > from A,B,C,D). > Function2 makes modification to the entry in D and makes several new > entries in E. > > Not far into the loop we get an error saying the MySQL database has > run out of connections: > (Operational Error) (1040, 'Too many connections')
Your scripts call create_engine() essentially in a loop. This isn't really the appropriate usage of create_engine(). The Engine does not represent a single database connection; is an expensive-to-create registry of information about your database and DBAPI as well as a connection pool (see http://www.sqlalchemy.org/docs/core/engines.html for an overview). Dereferencing it will eventually close out connections which were open from it, but not immediately as the garbage collector thread typically needs to find those unreachable objects. The appropriate scope for Engine is once per url per application, at the module level. That means if your application has only one URL, there should be exactly one call to create_engine() in just one place, and the resulting Engine should be placed in a single module made available for other modules to import. Otherwise you're working against the intended design of create_engine(). With that, all functions that call upon the Engine will be calling upon the underlying connection pool so that the total number of connections used by the application can be managed. The guidelines for Session are less stringent, though again generally a single Session is shared among all functions and methods for a particular operation. I didn't read your script carefully but typically a single Session is passed along all functions that need to operate on data, so that all those functions can share the same pool of objects which all interact cleanly, not to mention all within one transaction. The script as it is now creates many new transactions. If you really do want to use a Session inside a function you can forego the usage of sessionmaker as again that function is just a helper for declaring module-level patterns. The Session constructor can be called directly, i.e. session = Session(engine). -- 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.
