OK, thanks, so does that mean that mapping one class against multiple tables in different databases is something very exotic and probably bad practice? Or is there some elegant way to achieve this? Thanks,
Jan. On Apr 16, 4:42 pm, Michael Bayer <[email protected]> wrote: > if the issue is, youre doing a JOIN across two tables that are in > different databases, that's not going to work. you cant issue a JOIN > across two different databases unless both of those tables are > accessible using schemas or remote database links within the same > process. > > On Apr 16, 2009, at 10:35 AM, JanW wrote: > > > > > Oops, yes of course. > > Sorry, I copied the error message from the wrong terminal. > > The relevant error message would be this one: > > > Traceback (most recent call last): > > File "demo.py", line 51, in <module> > > result = Person.query().all() > > File "/Library/Frameworks/Python.framework/Versions/2.5/lib/ > > python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/ > > query.py", line 1186, in all > > return list(self) > > File "/Library/Frameworks/Python.framework/Versions/2.5/lib/ > > python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/ > > query.py", line 1280, in __iter__ > > return self._execute_and_instances(context) > > File "/Library/Frameworks/Python.framework/Versions/2.5/lib/ > > python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/ > > query.py", line 1283, in _execute_and_instances > > result = self.session.execute(querycontext.statement, > > params=self._params, mapper=self._mapper_zero_or_none()) > > File "/Library/Frameworks/Python.framework/Versions/2.5/lib/ > > python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/orm/ > > session.py", line 755, in execute > > clause, params or {}) > > File "/Library/Frameworks/Python.framework/Versions/2.5/lib/ > > python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/engine/ > > base.py", line 824, in execute > > return Connection.executors[c](self, object, multiparams, params) > > File "/Library/Frameworks/Python.framework/Versions/2.5/lib/ > > python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/engine/ > > base.py", line 874, in _execute_clauseelement > > return self.__execute_context(context) > > File "/Library/Frameworks/Python.framework/Versions/2.5/lib/ > > python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/engine/ > > base.py", line 896, in __execute_context > > self._cursor_execute(context.cursor, context.statement, > > context.parameters[0], context=context) > > File "/Library/Frameworks/Python.framework/Versions/2.5/lib/ > > python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/engine/ > > base.py", line 950, in _cursor_execute > > self._handle_dbapi_exception(e, statement, parameters, cursor, > > context) > > File "/Library/Frameworks/Python.framework/Versions/2.5/lib/ > > python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/engine/ > > base.py", line 931, in _handle_dbapi_exception > > raise exc.DBAPIError.instance(statement, parameters, e, > > connection_invalidated=is_disconnect) > > sqlalchemy.exc.OperationalError: (OperationalError) no such table: > > person u'SELECT address.person_id AS address_person_id, person.id AS > > person_id, person.name AS person_name, person.created AS > > person_created, address.created AS address_created, address.id AS > > address_id, address.street AS address_street, address.number AS > > address_number \nFROM person JOIN address ON address.person_id = > > person.id' [] > > > On Apr 16, 4:22 pm, Michael Bayer <[email protected]> wrote: > >> that error is just a table already exists. pass checkfirst=True to > >> your table.create() call. > > >> On Apr 16, 2009, at 6:21 AM, JanW wrote: > > >>> Hi all, > > >>> I've playing around with SQLAlchemy for a few months, being very > >>> happy > >>> with the results. > >>> As my adventures are getting gradually more complex I feel I've > >>> finally run into a problem where I am just stuck, so any help from > >>> the > >>> SQLAlchemy community would be greatly appreciated. > > >>> In essence I am trying to combine "Vertical Partitioning" with > >>> "Mapping a Class against Multiple Tables". In this case I want to > >>> map > >>> a class against multiple tables that are located in different > >>> databases. > > >>> Below is a minimal version of a script I am trying to build. In my > >>> actual project the situation is more complex and I'm not really > >>> working with persons and addresses (they would fit better in the > >>> same > >>> database). This is just a toy example to illustrate my problem. > > >>> I suspect my problem lies in my "j = join(...)" statement. "join()" > >>> probably expects tables from the same database but I have no idea > >>> how > >>> to tie this in with vertical partitioning. > > >>> It could very well be that I'm trying to take this too far and > >>> that I > >>> should just work with different classes all tied to their own > >>> database > >>> and then implement the logic between the classes separately. > > >>> Any ideas? > >>> Many thanks, > > >>> Jan. > > >>> ====== dummy code: > >>> from datetime import datetime > > >>> from sqlalchemy import * > >>> from sqlalchemy.orm import * > > >>> # Define SQLite databases > >>> person_engine = create_engine('sqlite:///person_db.sqlite') > >>> address_engine = create_engine('sqlite:///address_db.sqlite') > > >>> # Define database structure > >>> metadata=MetaData() > >>> person_table = Table( > >>> 'person', metadata, > >>> Column('id', Integer, primary_key=True), > >>> Column('name', Text), > >>> Column('created', DateTime, default=datetime.now), > >>> ) > >>> address_table = Table( > >>> 'address', metadata, > >>> Column('id', Integer, primary_key=True), > >>> Column('street', Text), > >>> Column('number', Integer), > >>> Column('person_id', Integer, index=True), > >>> Column('created', DateTime, default=datetime.now), > >>> ) > >>> person_table.create(bind=person_engine) > >>> address_table.create(bind=address_engine) > > >>> # Session, 2 tables bound to different engine > >>> Session = scoped_session(sessionmaker(binds={ > >>> person_table: person_engine, > >>> address_table: address_engine, > >>> } > >>> )) > >>> session = Session() > > >>> # 1 Class should map information from 2 databases > >>> class Person(object): > >>> pass > > >>> # Map Person class to 2 tables in different databases > >>> j = join(person_table, address_table, > >>> address_table.c.person_id==person_table.c.id) > >>> person_mapper = Session.mapper( > >>> Person, > >>> j, > >>> properties = { > >>> 'person_id': [address_table.c.person_id, person_table.c.id] > >>> } > >>> ) > > >>> # database is empty, so don't expect any results > >>> result = Person.query().all() > > >>> ====== resulting error: > >>> Traceback (most recent call last): > >>> File "demo.py", line 26, in <module> > >>> person_table.create(bind=person_engine) > >>> File "/Library/Frameworks/Python.framework/Versions/2.5/lib/ > >>> python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/ > >>> schema.py", line 386, in create > >>> self.metadata.create_all(bind=bind, checkfirst=checkfirst, > >>> tables= > >>> [self]) > >>> File "/Library/Frameworks/Python.framework/Versions/2.5/lib/ > >>> python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/ > >>> schema.py", line 1765, in create_all > >>> bind.create(self, checkfirst=checkfirst, tables=tables) > >>> File "/Library/Frameworks/Python.framework/Versions/2.5/lib/ > >>> python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/ > >>> engine/ > >>> base.py", line 1129, in create > >>> self._run_visitor(self.dialect.schemagenerator, entity, > >>> connection=connection, **kwargs) > >>> File "/Library/Frameworks/Python.framework/Versions/2.5/lib/ > >>> python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/ > >>> engine/ > >>> base.py", line 1158, in _run_visitor > >>> visitorcallable(self.dialect, conn, **kwargs).traverse(element) > >>> File "/Library/Frameworks/Python.framework/Versions/2.5/lib/ > >>> python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/sql/ > >>> visitors.py", line 89, in traverse > >>> return traverse(obj, self.__traverse_options__, > >>> self._visitor_dict) > >>> File "/Library/Frameworks/Python.framework/Versions/2.5/lib/ > >>> python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/sql/ > >>> visitors.py", line 200, in traverse > >>> return traverse_using(iterate(obj, opts), obj, visitors) > >>> File "/Library/Frameworks/Python.framework/Versions/2.5/lib/ > >>> python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/sql/ > >>> visitors.py", line 194, in traverse_using > >>> meth(target) > >>> File "/Library/Frameworks/Python.framework/Versions/2.5/lib/ > >>> python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/sql/ > >>> compiler.py", line 797, in visit_metadata > >>> self.traverse_single(table) > >>> File "/Library/Frameworks/Python.framework/Versions/2.5/lib/ > >>> python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/sql/ > >>> visitors.py", line 79, in traverse_single > >>> return meth(obj) > >>> File "/Library/Frameworks/Python.framework/Versions/2.5/lib/ > >>> python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/sql/ > >>> compiler.py", line 836, in visit_table > >>> self.execute() > >>> File "/Library/Frameworks/Python.framework/Versions/2.5/lib/ > >>> python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/ > >>> engine/ > >>> base.py", line 1812, in execute > >>> return self.connection.execute(self.buffer.getvalue()) > >>> File "/Library/Frameworks/Python.framework/Versions/2.5/lib/ > >>> python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/ > >>> engine/ > >>> base.py", line 824, in execute > >>> return Connection.executors[c](self, object, multiparams, params) > >>> File "/Library/Frameworks/Python.framework/Versions/2.5/lib/ > >>> python2.5/site-packages/SQLAlchemy-0.5.2-py2.5.egg/sqlalchemy/ > >>> engine/ > >>> base.py", line 888, in _execute_text > >>> return self.__execute_context(context) > >>> File "/Library/Frameworks/Python.framework/Versions/2.5/lib/ > > ... > > read more » --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
