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