thank you michael, very helpful points.
in the end i'll just use PostgreSQL native schema since i'm not able to get
the multi-connection thing working. for that i use a common ``
DeclarativeBase`` and ``__table_args__ = {'schema': 'market'}`` or
``__table_args__
= {'schema': 'site'}``.
i'm still interested in the solution
of
http://docs.sqlalchemy.org/en/rel_0_7/orm/session.html#simple-vertical-partitioning
since it toke me some time and i don't like having such an open issue ;)
this is my original scenario, having two ``DeclarativeBase``, one for the ``
site``, one for the ``market``. As you said, i changed the relationship to
``user = sa.orm.relationship(User, foreign_keys=[User.id])`` which changes
the exception to::
sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column
'product_items.user_id' could not find table 'users' with which to generate
a foreign key to target column 'id'
this happens even though the schema is also assigned in the ``User`` class::
@timestampable()
class User(DeclarativeBase):
# necessary to create references from the market to this users
__table_args__ = {'schema': SITE_DATABASE_SCHEMA_NAME}
id = sa.Column(sa.BigInteger,
Sequence('users_seq_id', optional=True),
primary_key=True)
here is the changed code in in the ``market``::
def initialize_sql(config, engine, user_engine=None):
session_maker = sessionmaker()
DBSession = scoped_session(session_maker)
DBSession.configure(bind=engine)
DBSession.configure(binds={
ProductItem: engine,
User: user_engine
})
log.debug('bind market engine')
Base.metadata.bind = engine
# danger: here we try to autogenerate the schema
# Base.metadata.create_all(engine)
# db-session per request
# @see:
http://docs.pylonsproject.org/projects/pyramid_cookbook/en/latest/database/sqlalchemy.html#using-a-non-global-session
def db(request):
log.debug('add DB session to request')
maker = request.registry.dbmaker
session = maker()
def cleanup(request):
session.close()
request.add_finished_callback(cleanup)
return session
config.registry.dbmaker = session_maker
config.set_request_property(db, name='dbsession', reify=True)
# for pyramid 1.4 use: ``config.add_request_method(db, reify=True)``
class ProductItem(Base):
"""
cross-db references:
-
http://stackoverflow.com/questions/6433592/cross-database-join-in-sqlalchemy
- http://markmail.org/message/z5tdtlcuoth2osqm
schemas:
http://www.postgresql.org/docs/9.1/static/ddl-schemas.html
"""
__tablename__ = 'product_items'
__table_args__ = {'schema': MARKET_DATABASE_SCHEMA_NAME}
id = sa.Column(sa.BigInteger,
Sequence('place_seq_id', optional=True),
primary_key=True)
name = Column(Unicode(255), unique=True)
user_id = sa.Column(
sa.BigInteger,
sa.ForeignKey('%s.%s.id' % (SITE_DATABASE_SCHEMA_NAME,
User.__tablename__)),
primary_key=True
)
user = sa.orm.relationship(User, foreign_keys=[User.id])
do you have a hint how to tell sqlalchemy to choose ``site.users`` rather
than ``users`` as table?
thanks a lot,
andi
On Friday, 26 April 2013 00:32:32 UTC+2, Michael Bayer wrote:
>
>
> On Apr 25, 2013, at 10:21 AM, Andi Blake
> <[email protected]<javascript:>>
> wrote:
>
> hi all,
>
> i have a webapp with an existing database-model ``site``, including users.
> in a second service i create a new database-model ``market``, but still
> want to access the users (which works via separate engine).
>
> goal: i want to create a relation from the ``market``-model to the
> ``site``-model. e.g. referencing a ``User`` instance from the
> ``site``-model to the ``market``-model. like this::
>
>
> SITE_DATABASE_SCHEMA_NAME = 'site'
> MARKET_DATABASE_SCHEMA_NAME = 'market'
>
> def initialize_sql(engine, user_engine = None):
> DBSession = scoped_session(sessionmaker(bind=engine))
> DBSession.configure(bind=engine)
> log.debug('bind market engine')
> Base.metadata.bind = engine
> # danger: here we try to autogenerate the schema
> # Base.metadata.create_all(engine)
>
> if user_engine:
> log.debug('bind user engine')
> DeclarativeBase.metadata.bind = user_engine
>
> class ProductItem(Base):
> """
> cross-db references:
> -
> http://stackoverflow.com/questions/6433592/cross-database-join-in-sqlalchemy
> - http://markmail.org/message/z5tdtlcuoth2osqm
>
> schemas:
> http://www.postgresql.org/docs/9.1/static/ddl-schemas.html
> """
> __tablename__ = 'product_items'
> __table_args__ = {'schema': MARKET_DATABASE_SCHEMA_NAME}
>
> id = sa.Column(sa.BigInteger,
> Sequence('place_seq_id', optional=True),
> primary_key=True)
> name = Column(Unicode(255), unique=True)
> user_id = sa.Column(
> sa.BigInteger,
> sa.ForeignKey('%s.%s.id' % (SITE_DATABASE_SCHEMA_NAME,
> User.__tablename__)),
> primary_key=True
> )
> # this one fails
> * user = sa.orm.relationship("User")*
>
>
> question:
>
> - is this possible?
> - how can i attach the missing ``User`` class to the known classes for
> that sqlalchemy session? now of course i get::
>
> sqlalchemy.exc.InvalidRequestError: When initializing mapper
> Mapper|ProductItem|product_items, expression 'User' failed to locate a name
> ("name 'User' is not defined"). If this is a class name, consider adding
> this relationship() to the <class
> 'if_market.models.ProductItem.ProductItem'> class after both dependent
> classes have been defined.
>
>
>
> there's two different concepts happening here.
>
> One is that the relationship("User") can't find a class named "User".
> You'd need to define a "User" class in addition to "ProductItem" using the
> same Base, or if on a different Base then you need to say
> relationship(User) without the quotes. This is not really related to the
> concept of multiple engines so much (it seems like it is, but it isn't at
> any fundamental level).
>
> The other is there's a multiple connection use case here, but it's not
> clear what the boundaries are since the term "database-model" is ambiguous.
>
>
> If you're on a system like Postgresql, and your two sets of tables are in
> different schemas within the same database, a single connection can refer
> to both sets of tables at once, by specifying a schema name for the
> "remote" set of tables (or for both). A query would look like "SELECT *
> FROM someschema.mytable". Postgresql and other databases also feature
> extensions to access tables in other databases and even remote servers all
> from one connection. These techniques are worth looking into if you
> really need to perform queries against both sets of tables simultaneously.
>
> If you can deal with running more than one connection at a time, and
> having separate SELECT statements emitted for different connections, then
> you can configure a Session with an engine per mapper. We call this
> "vertical partitioning" and there's some background on that here:
> http://docs.sqlalchemy.org/en/rel_0_8/orm/session.html#simple-vertical-partitioning.
>
> It's a little bit of a DIY-route to travel.
>
> I think the important thing is to be familiar with the concepts of tables,
> schemas, databases, and connections, and to look at the problem from that
> perspective first. Then applying an object-oriented "model" around all
> that is after you've determined how the actual database access is to happen
> first.
>
>
>
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
