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/
>>> 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 [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
-~----------~----~----~----~------~----~------~--~---
