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 <mike...@zzzcomputing.com> 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/ >>> 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) table person >>> already exists '\nCREATE TABLE person (\n\tid INTEGER NOT NULL, \n >>> \tname TEXT, \n\tcreated TIMESTAMP, \n\tPRIMARY KEY (id)\n)\n\n' () >> >> > > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---