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