On Mar 6, 2008, at 1:23 AM, Jonathan Vanasco wrote:
> > i'm porting an app to Pylons/SQLalchemy and am a bit lost with some of > the concepts. > > in my app, i have 5 unique db connections/handles that have specific > user permissions for tables in postgres > : read , write , config , log , session > > for the sake of simplicity, lets just say that every table/class will > be associated to the db. > > > obviously, I'll have a separate engine item for each db connection > param > > my question is this: > > i. do i make 5 separate Session objects with orm.scoped_session , or > just 1 ? > ii. do i make 5 separate MetaData objects for use with > sqlalchemy.Table() and orm.mapper() , or just 1? the impression I'm getting here is that the five database connections are all talking to the same database, just with different permissions. Is that the case ? If so, it seems like this would be highly dependent on what you're trying to achieve. Under what circumstances do you need to talk to one particular database and not another ? My answers below are based on this assumption but I'm not sure if I'm understanding correctly. > I'm trying to grasp exactly how the Session and MetaData work. MetaData is a dictionary which stores a bunch of Table objects. It can optionally be associated with a single engine (database connection scheme) so that statements can be executed against its Table objects without the explicit engine needed. Having MetaData as a collection serves exactly three purposes - it serves as a base collection for issuing mass CREATE/DROP statements, which does not seem to be a requirement here, it allows SA to be aware of a ForeignKey dependency between two Tables using a string-based foreign key identifier instead of passing an explicit Column object, and it allows the full set of tables to be associated with a single engine all at once. An application that switches between database connections which all correspond to the same set of Table objects does not want to rely upon engine-bound metadata and instead would use explicit engine access (or would allow the ORM Session to handle it, if the ORM is being used). A Session is an object that stores ORM-mapped objects which have been loaded from a database and can issue SQL back to the DB representing changes. It requires a source of database connections, which is usually a single engine or alternatively an individual connection from an engine, or it can be bound to multiple connection sources which are mapped in a dictionary to individual mapped classes and/or Table objects. When using multiple connection sources, its expected that the individual tables being accessed reside under different connection sources and the Session requires knowledge of which Table is at hand for each SQL statement emitted, so that it can choose the correct datasource - with normal ORM use this is implicit. If I'm understanding your case correctly you'd probably want to have five different session configurations each mapped to an engine, and each Session would be a regular single-engine session. This based on the assumption that a single connection stereotype can access all tables. > > some examples/docs/tutorials suggest i use a sep connection for each > db, others suggest multiple. i'm trying to figure out the most- > correct way to handle this. the session-connected-to-multiple-engines case is used in two specific circumstances - when you have your data partitioned "vertically" across multiple databases, meaning different tables exist only in one database or another, and you'd like the Session to "transparently" handle this divergence, or when you have partitioned your data "horizontally", which means subsets of rows for a particular table are stored in that same table as represented across multiple databases (and again you'd like the Session to handle it). Both scenarios present limitations that are not present when dealing with tables that are all present in a single database. If in your case, all tables are accessible via one connection, neither of these scenarios apply. with some more detail I can advise more specifically. - mike --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected] 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 -~----------~----~----~----~------~----~------~--~---
