Re: [sqlalchemy] relationship between declarative base and automap base

[Mike Bayer]

On Fri, Feb 19, 2016 at 7:09 PM, Brian Cherinka  wrote:

> Hi.
>
> I have two database schemas, with a table from each I would like to join.
> The classes for one schema have been created as explicit declarative Bases,
> while the classes for the other were all created via automap Base.  I have
> a foreign key joining the two tables.  Sqlalchemy sees the foreign key, yet
> does not recognize it or use.  Upon import of my Classes, I'm getting this
> error
>
> NoForeignKeysError: Could not determine join condition between parent/child
> tables on relationship Cube.target - there are no foreign keys linking
> these tables.  Ensure that referencing columns are associated with a
> ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.
>
> datadb.Cube is my declarative base and sampledb.MangaTarget is an automap
> base.  Here are the foreign keys on datadb.Cube
>
> In [4]: datadb.Cube.__table__.foreign_keys
> Out[4]:
> {ForeignKey(u'mangadatadb.ifudesign.pk'),
>  ForeignKey(u'mangasampledb.manga_target.pk'),
>  ForeignKey(u'mangadatadb.pipeline_info.pk'),
>  ForeignKey(u'mangadatadb.wavelength.pk')}
>
>
OK but you're assuming that your auto-reflected metadata contains tables
under the schema "mangadatadb" and 'mangasampledb" and that these schemas
are assigned to "sampledb.MangaTarget".  If you're getting NoForeignKeys it
means that these ForeignKey constraint objects are pointing at something
else.




> I've created the relationship via
>
> Cube.target = relationship(sampledb.MangaTarget, backref='cubes')
>
>
> I've also tried explicitly adding a foreign_keys=[Cube.manga_target_pk]
> here, but that didn't work either.
>

That approach can work as long as you also provide the "primaryjoin"
expression explicitly, otherwise it still doesn't know what
Cube.manga_target_pk is supposed to be pointing at.


>
> I tested out the relationships with an explicit declarative Base class for
> MangaTarget and everything works perfectly.  However, explicitly declaring
> all the tables in my sampledb schema is not really an option,
> unfortunately. I'm at a loss here.
>
> If it should be possible, is there a procedure somewhere documented on how
> to get that working?
>

Well when you automap you need to pass the "schema" argument to the
MetaData.reflect() method for it to consider this schema,
automapbase.prepare() doesn't yet have this argument exposed so you
probably need to use MetaData.reflect(schema='whatever') up front.   Then
make sure it has Table objects that have the "schema" argument you expect.
Then when you do automapbase.prepare(), the default naming scheme for
classes does not take schema into account, so if you have the same
tablename in multiple schemas the classes will overwrite each other in the
Base.classes collection.  So there's several stages here that you have to
make sure automap is putting the Tabe and class objects you expect in the
places they go, if it were me I'd step through it a bit with pdb.   The
automap system doesnt have strong support for multiple schemas right now.



>
> Thanks for any help.
>
> Cheers, Brian
>
> --
> 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 sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>

-- 
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] relationship between declarative base and automap base

[Brian Cherinka]

Hi.  

I have two database schemas, with a table from each I would like to join. 
 The classes for one schema have been created as explicit declarative 
Bases, while the classes for the other were all created via automap Base. 
 I have a foreign key joining the two tables.  Sqlalchemy sees the foreign 
key, yet does not recognize it or use.  Upon import of my Classes, I'm 
getting this error

NoForeignKeysError: Could not determine join condition between parent/child 
tables on relationship Cube.target - there are no foreign keys linking 
these tables.  Ensure that referencing columns are associated with a 
ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.

datadb.Cube is my declarative base and sampledb.MangaTarget is an automap 
base.  Here are the foreign keys on datadb.Cube
  
In [4]: datadb.Cube.__table__.foreign_keys
Out[4]:
{ForeignKey(u'mangadatadb.ifudesign.pk'),
 ForeignKey(u'mangasampledb.manga_target.pk'),
 ForeignKey(u'mangadatadb.pipeline_info.pk'),
 ForeignKey(u'mangadatadb.wavelength.pk')}

I've created the relationship via 

Cube.target = relationship(sampledb.MangaTarget, backref='cubes')


I've also tried explicitly adding a foreign_keys=[Cube.manga_target_pk] 
here, but that didn't work either.  

I initially created the constraint in my schema table with 

ALTER TABLE ONLY mangadatadb.cube
ADD CONSTRAINT manga_target_fk
FOREIGN KEY (manga_target_pk) REFERENCES mangasampledb.manga_target(pk)
ON UPDATE CASCADE ON DELETE CASCADE;

Can a relationship be created on a foreign key between a declarative base 
class and an automap base class?  

I tested out the relationships with an explicit declarative Base class for 
MangaTarget and everything works perfectly.  However, explicitly declaring 
all the tables in my sampledb schema is not really an option, 
unfortunately. I'm at a loss here.

If it should be possible, is there a procedure somewhere documented on how 
to get that working?

Thanks for any help.

Cheers, Brian

-- 
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.