Hi,
Progress! But still not working 100%. I have three assert statements: one that
tests a "to one" relationship across schemas, one that test a "to one"
relationship within the same scheme, and two that test a "many to many"
relationship across schemas (one assert for each direction).
When I set the search_path to what I'd like it to be (e.g. "things, people")
and use the "connect" event listener as detailed earlier in this thread, the
first two asserts (which broke before) now work. The first of the "many to
many" asserts works, but the reverse does not. However, the relationship
statements (in either direction) *do* seem to work. I can't explain that.
Going for it, I put the event listener into my large project and crossed my
fingers, but it doesn't work. I'm still running into these errors across
schemas:
ArgumentError: Could not determine join condition between parent/child tables
on relationship VisitSpectrum.observation. Specify a 'primaryjoin' expression.
If 'secondary' is present, 'secondaryjoin' is needed as well.
It works when I set the search path to "$user", i.e. no existing table, and
different things break depending on the order search path. As far as I can tell
the listener is setting the search path properly, but I have no idea how
persistent it is or if another connection is being made somehow where it's not.
So I'm getting frustrated.
Attached is my toy model where the relationship assertion breaks.
Demitri
--
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.
#!/usr/bin/python
#from sqlalchemy import *
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.event import listen
from sqlalchemy.pool import Pool
from sqlalchemy.orm import relationship
"""
CREATE TABLE people.band ( id integer primary key, name text, venue_id integer );
CREATE TABLE things.venue ( id integer primary key, name text );
CREATE TABLE things.instrument ( id serial NOT NULL, label text, CONSTRAINT instrument_pk PRIMARY KEY (id))
CREATE TABLE people.musician
(
id serial NOT NULL,
age integer,
band_id integer,
CONSTRAINT musician_pk PRIMARY KEY (id ),
CONSTRAINT band_fk FOREIGN KEY (band_id)
REFERENCES people.band (id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE RESTRICT
);
CREATE TABLE people.musician_to_instrument
(
musician_id integer NOT NULL,
instrument_id integer NOT NULL,
CONSTRAINT musician_to_instrument_pk PRIMARY KEY (musician_id , instrument_id ),
CONSTRAINT instrument_fk FOREIGN KEY (instrument_id)
REFERENCES things.instrument (id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION,
CONSTRAINT musician_fk FOREIGN KEY (musician_id)
REFERENCES people.musician (id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION
)
ALTER TABLE ONLY people.band
ADD CONSTRAINT venue_fk FOREIGN KEY (venue_id) REFERENCES things.venue(id) ON UPDATE CASCADE ON DELETE SET NULL;
"""
def my_on_connect(dbapi_con, connection_record):
'''
dbapi_con - type: psycopg2._psycopg.connection
connection_record - type: sqlalchemy.pool._ConnectionRecord
'''
print "New DBAPI connection:"#, dbapi_con
cursor = dbapi_con.cursor()
cursor.execute("SHOW search_path");
print "old: ",cursor.fetchone()[0];
cursor.execute('SET search_path TO "$user"') #
cursor.execute("SHOW search_path");
print "new: ",cursor.fetchone()[0];
listen(Pool, 'connect', my_on_connect)
#e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
e = create_engine("postgresql://schema_test:schema_test@localhost:9000/schema_test_db", echo=False)
Base = declarative_base(bind=e)
class Band(Base):
__tablename__ = 'band'
__table_args__ = {'autoload' : True, 'schema' : 'people'}
class Venue(Base):
__tablename__ = 'venue'
__table_args__ = {'autoload' : True, 'schema' : 'things'}
class Musician(Base):
__tablename__ = 'musician'
__table_args__ = {'autoload' : True, 'schema' : 'people'}
class Instrument(Base):
__tablename__ = 'instrument'
__table_args__ = {'autoload' : True, 'schema' : 'things'}
class MusicianToInstrument(Base):
__tablename__ = 'musician_to_instrument'
__table_args__ = {'autoload' : True, 'schema' : 'people'}
# Strangely, both of the two statements below work.
#Musician.instruments = relationship(Instrument,
# secondary=MusicianToInstrument.__table__,
# backref="musicians")
#Instrument.musicians = relationship(Musician,
# secondary=MusicianToInstrument.__table__,
# backref="instruments")
assert Band.__table__.c.venue_id.references(Venue.__table__.c.id)
assert Musician.__table__.c.band_id.references(Band.__table__.c.id)
assert MusicianToInstrument.__table__.c.musician_id.references(Musician.__table__.c.id)
# fails
assert MusicianToInstrument.__table__.c.instrument_id.references(Instrument.__table__.c.id)
