[sqlalchemy] Automap sometimes does not create the collection for table classes

[Serrano Pereira]

Hello,

I'm experiencing what looks like a bug in SQLAlchemy 0.9.1 through 0.9.8. I 
use Python 2.7.6. This is the situation:

I have an SQLite database with a many-to-many relationship. This 
relationship is created using a secondary table, with a left and a right 
foreign key column. I make use of sqlalchemy.ext.automap to load the table 
classes for an existing database:

 engine = sqlalchemy.create_engine('sqlite:///{0}'.format(DB_PATH), echo=
True)
 Session = sessionmaker(bind=engine)
 session = Session()
 metadata = sqlalchemy.MetaData()
 metadata.reflect(bind=engine)
 Base = automap_base(metadata=metadata)
 Base.prepare()
 
 Photo = Base.classes.photos
 Taxon = Base.classes.taxa
 Rank = Base.classes.ranks


Because of the many-to-many relationship between Photo and Taxon, I can 
access the Photo.taxa_collection attribute to get the related taxa for a 
photo. The problem is that in some cases the collection attributes are not 
created. For example, if I set the echo attribute for 
sqlalchemy.create_engine() to False or True (differs per SQLAlchemy 
version), the script will crash with this error:

Traceback (most recent call last):
  File ./sqla_bug_test_a.py, line 149, in module
main()
  File ./sqla_bug_test_a.py, line 126, in main
q = get_photos_with_taxa(session, metadata)
  File ./sqla_bug_test_a.py, line 140, in get_photos_with_taxa
join(Photo.taxa_collection).join(Taxon.ranks).\
AttributeError: type object 'photos' has no attribute 'taxa_collection'

The collection attributes are not created in this case. The same sometimes 
happens when I import certain Python modules (e.g. logging or argparse) before 
sqlalchemy is imported. Sometimes it has to be a combination of these two.

I have created a Python script that demonstrates the problem: 
https://gist.github.com/figure002/2d98ce8532668f9f1bc1
In the comment section below I've included download links to test SQLite 
databases. In the script I also included some test results for different 
versions of SQLAlchemy, though the results aren't always consistent.

Is this indeed a bug and should I file a bug report? Is there a workaround for 
this problem?

Regards,
Serrano

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] Automap sometimes does not create the collection for table classes

[Michael Bayer]

 On Oct 20, 2014, at 5:44 AM, Serrano Pereira serrano.pere...@gmail.com 
 wrote:
 
 Hello,
 
 I'm experiencing what looks like a bug in SQLAlchemy 0.9.1 through 0.9.8. I 
 use Python 2.7.6. This is the situation:

automap here configures photos_collection and taxa_collection as a 
relationship() with a backref in the other direction.  Which name is the 
relationship and which is the backref is not deterministic; you can make it 
deterministic if you experiment with the PYTHONHASHSEED environment variable.

When taxa_collection is the backref, Photo.taxa_collection does not exist until 
the mappers configure themselves.So after prepare() I’d advise to call 
sqlalchemy.orm.configure_mappers() which allows this example to work in all 
cases.


-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] A complicated composite secondary join relationship() property involving four tables

[Peter Waller]

Hi All,

I am wondering if it is possible to describe in sqlalchemy the
following relationship with the new features introduced since 0.9.2.
I've been reading this document and it looks close to what I'm trying
to achieve:

 http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#composite-secondary-joins

As does this previous mailing list post:

 https://groups.google.com/d/topic/sqlalchemy/ov-mYWA7XAM/discussion

There is a pre-existing structure to deal with. I would rather not
change the structure if it can be avoided, but may have to resort to
doing so. Here are the tables:

TableName (column, ...)

 PersonAction (PersonID, ActionID)
 PersonUnit (PersonID, UnitID)
 DepartmentUnit (UnitID, DepartmentID)
 ActionMeaning (DepartmentID, ActionID)

It would be nice to be able to go from an ActionMeaning to a
PersonAction and back again. To do that, a conversion between
DepartmentName and DepartmentID needs to happen.

Here are a few of the relationships:

One PersonAction: one ActionMeaning
One ActionMeaning: many PersonAction
One PersonUnit many  PersonAction
One PersonUnit one DepartmentUnit
One DepartmentID: many UnitID
  (therefore one ActionMeaning: many DepartmentUnit)
One ActionMeaning: Many PersonUnit (via DepartmentUnit)

I'm using the ORM and I would like to be able to write a property on
the PersonAction which allows me to efficiently get to the
ActionMeaning (preferably joinedload'ed), and likewise I would like to
be able to go from an ActionMeaning to all of the related
PersonActions.

Is this possible? Or are there alternative tasteful strategies for
dealing with this tricky structure?

Life would be much easier if I had the DepartmentID on the PersonUnit...

Keywords to help anyone searching in the future:

Composite relationship between multiple tables
Joining multiple tables in a relationship
relationship secondary join primaryjoin secondaryjoin

Thanks,

- Peter

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] A complicated composite secondary join relationship() property involving four tables

[Michael Bayer]

 On Oct 20, 2014, at 1:14 PM, Peter Waller pe...@scraperwiki.com wrote:
 
 Hi All,
 
 I am wondering if it is possible to describe in sqlalchemy the
 following relationship with the new features introduced since 0.9.2.
 I've been reading this document and it looks close to what I'm trying
 to achieve:
 
 http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#composite-secondary-joins
 
 As does this previous mailing list post:
 
 https://groups.google.com/d/topic/sqlalchemy/ov-mYWA7XAM/discussion
 
 There is a pre-existing structure to deal with. I would rather not
 change the structure if it can be avoided, but may have to resort to
 doing so. Here are the tables:
 
 TableName (column, ...)
 
 PersonAction (PersonID, ActionID)
 PersonUnit (PersonID, UnitID)
 DepartmentUnit (UnitID, DepartmentID)
 ActionMeaning (DepartmentID, ActionID)

So as far as the patterns at 
http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html, this one 
qualifies for 
http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#relationship-to-non-primary-mapper.
  The reason is because PersonAction has a direct FK to ActionMeaning 
(ActionID) as well as towards elements on what would be “secondary” here, 
PersonUnit/DepartmentUnit.  The criteria for that is: we seek to join from A 
to B, making use of any number of C, D, etc. in between, however there are also 
join conditions between A and B directly.”

So you map ActionMeaning using a non-primary mapper to a join of ActionMeaning, 
DepartmentUnit, and PersonUnit, then PersonAction can relate to this mapper 
directly.

-- 
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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] A complicated composite secondary join relationship() property involving four tables

[Peter Waller]

Outstanding! My puny mind was just beginning to tickle this
documentation and was starting to get the idea that this is the
right direction. Good to know I'm on track.

Is it also possible that having a class which maps to a join* will
help? Then rather than having PersonAction and PersonUnit I would just
have Person. Would that simplify the relationships?

I'll have a play around with this and see where I get.

Thanks,

- Peter

* http://docs.sqlalchemy.org/en/rel_0_9/orm/mapper_config.html#maptojoin

On 20 October 2014 18:26, Michael Bayer mike...@zzzcomputing.com wrote:

 On Oct 20, 2014, at 1:14 PM, Peter Waller pe...@scraperwiki.com wrote:

 Hi All,

 I am wondering if it is possible to describe in sqlalchemy the
 following relationship with the new features introduced since 0.9.2.
 I've been reading this document and it looks close to what I'm trying
 to achieve:

 http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#composite-secondary-joins

 As does this previous mailing list post:

 https://groups.google.com/d/topic/sqlalchemy/ov-mYWA7XAM/discussion

 There is a pre-existing structure to deal with. I would rather not
 change the structure if it can be avoided, but may have to resort to
 doing so. Here are the tables:

 TableName (column, ...)

 PersonAction (PersonID, ActionID)
 PersonUnit (PersonID, UnitID)
 DepartmentUnit (UnitID, DepartmentID)
 ActionMeaning (DepartmentID, ActionID)

 So as far as the patterns at 
 http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html, this one 
 qualifies for 
 http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#relationship-to-non-primary-mapper.
   The reason is because PersonAction has a direct FK to ActionMeaning 
 (ActionID) as well as towards elements on what would be “secondary” here, 
 PersonUnit/DepartmentUnit.  The criteria for that is: we seek to join from A 
 to B, making use of any number of C, D, etc. in between, however there are 
 also join conditions between A and B directly.”

 So you map ActionMeaning using a non-primary mapper to a join of 
 ActionMeaning, DepartmentUnit, and PersonUnit, then PersonAction can relate 
 to this mapper directly.

 --
 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 http://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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] A complicated composite secondary join relationship() property involving four tables

[Peter Waller]

This is how far I got, before running into a wall:

 j = join(ActionMeaning, DepartmentUnit).join(PersonUnit)
 disambiguation = {
   UnitID: [j.c.PersonUnit_UnitID,  j.c.DepartmentUnit_UnitID],
   DepartmentID: [j.c.DepartmentUnit_DepartmentID,
j.c.ActionMeaning_DepartmentID],
 }
 ActionMeaningToPerson = mapper(ActionMeaning, j, non_primary=True,
properties=disambiguation)
 PersonAction.meaning = relationship(ActionMeaningToPerson)

ActionMeaning has a DepartmentID -
ForeignKey(DepartmentUnit.DepartmentID)
PersonUnit has UnitID - ForeignKey(DepartmentUnit.UnitID)
PersonAction has PersonID - ForeignKey(PersonUnit.PersonID)

This actually does something when I look at PersonAction.meaning, except
unfortunately I cannot yet figure out where to express the constraint
ActionMeaning.ActionID == PersonAction.ActionID, so I get back all
ActionMeanings with a matching DepartmentID, and ActionID of various sorts.

I've tried expressing it as the relationship(primaryjoin=), but this gave
me:


ArgumentError: Could not locate any relevant foreign key columns for
primary join condition 'ActionMeaning.ActionID =
PersonAction.ActionID' on relationship PersonAction.meaning.  Ensure
that referencing columns are associated with a ForeignKey or
ForeignKeyConstraint, or are annotated in the join condition with the
foreign() annotation.


Any hints?

Thanks again!

On 20 October 2014 18:26, Michael Bayer mike...@zzzcomputing.com wrote:


  On Oct 20, 2014, at 1:14 PM, Peter Waller pe...@scraperwiki.com wrote:
 
  Hi All,
 
  I am wondering if it is possible to describe in sqlalchemy the
  following relationship with the new features introduced since 0.9.2.
  I've been reading this document and it looks close to what I'm trying
  to achieve:
 
 
 http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#composite-secondary-joins
 
  As does this previous mailing list post:
 
  https://groups.google.com/d/topic/sqlalchemy/ov-mYWA7XAM/discussion
 
  There is a pre-existing structure to deal with. I would rather not
  change the structure if it can be avoided, but may have to resort to
  doing so. Here are the tables:
 
  TableName (column, ...)
 
  PersonAction (PersonID, ActionID)
  PersonUnit (PersonID, UnitID)
  DepartmentUnit (UnitID, DepartmentID)
  ActionMeaning (DepartmentID, ActionID)

 So as far as the patterns at
 http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html, this one
 qualifies for
 http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#relationship-to-non-primary-mapper.
 The reason is because PersonAction has a direct FK to ActionMeaning
 (ActionID) as well as towards elements on what would be “secondary” here,
 PersonUnit/DepartmentUnit.  The criteria for that is: we seek to join from
 A to B, making use of any number of C, D, etc. in between, however there
 are also join conditions between A and B directly.”

 So you map ActionMeaning using a non-primary mapper to a join of
 ActionMeaning, DepartmentUnit, and PersonUnit, then PersonAction can relate
 to this mapper directly.

 --
 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 http://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 http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

Re: [sqlalchemy] A complicated composite secondary join relationship() property involving four tables

[Michael Bayer]

 On Oct 20, 2014, at 3:23 PM, Peter Waller pe...@scraperwiki.com wrote:
 
 This is how far I got, before running into a wall:
 
  j = join(ActionMeaning, DepartmentUnit).join(PersonUnit)
  disambiguation = {
UnitID: [j.c.PersonUnit_UnitID,  j.c.DepartmentUnit_UnitID],
DepartmentID: [j.c.DepartmentUnit_DepartmentID, 
  j.c.ActionMeaning_DepartmentID],
  }
  ActionMeaningToPerson = mapper(ActionMeaning, j, non_primary=True, 
  properties=disambiguation)
  PersonAction.meaning = relationship(ActionMeaningToPerson)
 
 ActionMeaning has a DepartmentID - ForeignKey(DepartmentUnit.DepartmentID)
 PersonUnit has UnitID - ForeignKey(DepartmentUnit.UnitID)
 PersonAction has PersonID - ForeignKey(PersonUnit.PersonID)
 
 This actually does something when I look at PersonAction.meaning, except 
 unfortunately I cannot yet figure out where to express the constraint 
 ActionMeaning.ActionID == PersonAction.ActionID, so I get back all 
 ActionMeanings with a matching DepartmentID, and ActionID of various sorts.
 
 I've tried expressing it as the relationship(primaryjoin=), but this gave me:
 
 
 ArgumentError: Could not locate any relevant foreign key columns for primary 
 join condition 'ActionMeaning.ActionID = PersonAction.ActionID' on 
 relationship PersonAction.meaning. 

your primaryjoin has to be a lot more than that.   it’s at least three 
different column combinations joined together by AND, so you’d be using and_() 
and within that ActionMeaningToPerson.x == PersonAction.y, 
ActionMeaningToPerson.q == PersonAction.p, etc.  (x/y/q/p are the columns you 
have there, DepartmentID, UnitID, etc.).  Everything should be in terms of 
those two classes.   ActionMeaningToPerson is like a big horizontal table now.

as for foreign, this usually has to be finessed in this kind of case.  if this 
is one-to-many, then I’d put the foreign() annotation on the 
ActionMeaningToPerson side, on those columns that act like a foreign key.   See 
the second part of 
http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#creating-custom-foreign-conditions
 
http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#creating-custom-foreign-conditions
 for what that looks like.



 Ensure that referencing columns are associated with a ForeignKey or 
 ForeignKeyConstraint, or are annotated in the join condition with the 
 foreign() annotation.
 
 
 Any hints?
 
 Thanks again!
 
 On 20 October 2014 18:26, Michael Bayer mike...@zzzcomputing.com 
 mailto:mike...@zzzcomputing.com wrote:
 
  On Oct 20, 2014, at 1:14 PM, Peter Waller pe...@scraperwiki.com 
  mailto:pe...@scraperwiki.com wrote:
 
  Hi All,
 
  I am wondering if it is possible to describe in sqlalchemy the
  following relationship with the new features introduced since 0.9.2.
  I've been reading this document and it looks close to what I'm trying
  to achieve:
 
  http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#composite-secondary-joins
   
  http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#composite-secondary-joins
 
  As does this previous mailing list post:
 
  https://groups.google.com/d/topic/sqlalchemy/ov-mYWA7XAM/discussion 
  https://groups.google.com/d/topic/sqlalchemy/ov-mYWA7XAM/discussion
 
  There is a pre-existing structure to deal with. I would rather not
  change the structure if it can be avoided, but may have to resort to
  doing so. Here are the tables:
 
  TableName (column, ...)
 
  PersonAction (PersonID, ActionID)
  PersonUnit (PersonID, UnitID)
  DepartmentUnit (UnitID, DepartmentID)
  ActionMeaning (DepartmentID, ActionID)
 
 So as far as the patterns at 
 http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html 
 http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html, this one 
 qualifies for 
 http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#relationship-to-non-primary-mapper
  
 http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#relationship-to-non-primary-mapper.
   The reason is because PersonAction has a direct FK to ActionMeaning 
 (ActionID) as well as towards elements on what would be “secondary” here, 
 PersonUnit/DepartmentUnit.  The criteria for that is: we seek to join from A 
 to B, making use of any number of C, D, etc. in between, however there are 
 also join conditions between A and B directly.”
 
 So you map ActionMeaning using a non-primary mapper to a join of 
 ActionMeaning, DepartmentUnit, and PersonUnit, then PersonAction can relate 
 to this mapper directly.
 
 --
 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 
 mailto:sqlalchemy%2bunsubscr...@googlegroups.com.
 To post to this group, send email to sqlalchemy@googlegroups.com 
 mailto:sqlalchemy@googlegroups.com.
 Visit this group at http://groups.google.com/group/sqlalchemy 
 http://groups.google.com/group/sqlalchemy.
 For more options,