Results of instrumenting a class with a relationship on based on reflected tables is not consistent - impl set to Scalar when referenced key is non-unique/non-primary and uselist was not explicitly specified at relationship construction.

[Former user]

I am running into this issue on 0.6 with an in memory sqlite database (for testing purposes).

The situation occurs under the following circumstances:

I generate a set of tables, relationships and foreign keys from a XML document. The tests for this pass, everything is great. Next, I would like to be able to chain-reflect tables from the database, starting with the main table and loading all tables referenced using foreign keys, create classes for them, map them, and create the relationships between them. After creating the proper schema items with the XML loader, I attempt the chain load process. The chain reflection works properly, the classes are mapped properly, however I run into this issue when I call orm.class_mapper(from_class).add_property(relationship_key, relation).

Specifically, when I debug the process, the relationship is created properly, self.uselist is set to none. In addition, the referenced key in the relationship is neither unique nor primary. When I add the property to the mapper I get the following warning:

/usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/orm/mapper.py:1809: SAWarning: Multiple rows returned with uselist=False for eagerly-loaded attribute 'SalesOrder.lines' populator(state, dict_, row)

You can get test code for this issue via "bzr branch lp:pydatastep", then run /tests/test_loaders.py, it's the only failure, the XML loading test is present directly above it, just to demonstrate that it's not garbage in. You can inspect the created relationship or step into the mapper update process by placing a breakpoint at line 126 of /struqtural/database/relationship.py.

[Former user]

I have confirmed after testing that this is a result of incorrect scalar/list inference on the part of sqlalchemy. This occurs when working with two related tables, where each table has a foreign key pointing to the other. If you delete all foreign key references from the table then re-add them one at a time as you area creating relationships, everything works as you would expect.

I should mention that when applying uselist=False to a relationship, it also applies uselist=False to the backref, which really should be a configurable behavior.

[Mike Bayer]

Sorry, I don't have bzr installed and I don't have the resources to work with a full application. If you can please attach a single file simple test case that illustrates the issue succinctly, I can work with that.

It doesn't sound much like a bug, the error message you refer to is valid - uselist=False is intended only for many-to-one/one-to-one relationships. By default it is set to "True" for a many-to-one, and "False" for all others, where one-to-many/many-to-one is detected via the natural organization of foreign keys - so using default settings, it is impossible to have multiple rows received for a uselist=False. That message only occurs if you've manually configured it on a relationship that is not truly one-to-one or many-to-one, i.e. where the adequate constraints are not in place within your data to ensure a "one" on the remote side.

Regarding backref, uselist=False is not transferred. If you have a many-to-one/one-to-many relationship, the many-to-one side is uselist=False by definition. Setting uselist=False only makes sense on the one-to-many side, which makes it a one-to-one. Also the properties of the backref are entirely configurable using the backref function: http://www.sqlalchemy.org/docs/reference/orm/mapping.html?highlight=backref#sqlalchemy.orm.backref , as well as just using two distinct relationships with the back_populates option.

[Mike Bayer]

Sorry, second paragraph, second half of the first sentence, I meant "by default it is set to "False" for a many to one, and "True" for all others".

[Mike Bayer]

Replying to guest:

I have confirmed after testing that this is a result of incorrect scalar/list inference on the part of sqlalchemy. This occurs when working with two related tables, where each table has a foreign key pointing to the other. If you delete all foreign key references from the table then re-add them one at a time as you area creating relationships, everything works as you would expect.

That is the expected behavior. If you have two tables with mutually dependent foreign keys, that must be configured upon relationship manually. See the example at http://www.sqlalchemy.org/docs/mappers.html#rows-that-point-to-themselves-mutually-dependent-rows . I'm not aware of any way that SQLAlchemy could "guess" which side is which, so this is not a bug.

[Mike Bayer]

Though I will note that if you have a mutual FK situation, relationship() should raise an error without primaryjoin being specified. I'll leave this ticket open for a bit if you can provide a succinct test case.

[Mike Bayer]

mmmm nope:

from sqlalchemy import *
from sqlalchemy.orm import *

m = MetaData()
t1 = Table('t1',
    m,
    Column('id', Integer, primary_key=True),
    Column('t2_id', Integer, ForeignKey('t2.id'))
)

t2 = Table('t2',
    m,
    Column('id', Integer, primary_key=True),
    Column('t1_id', Integer, ForeignKey('t1.id'))
)


class A(object):pass
class B(object):pass

mapper(A, t1, properties={'t2s':relationship(B, backref='t1')})
mapper(B, t2, properties={'t1s':relationship(A, backref='t2')})
compile_mappers()

output:

Traceback (most recent call last):
  File "test.py", line 23, in <module>
    compile_mappers()
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/__init__.py", line 907, in compile_mappers
    m.compile()
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/mapper.py", line 803, in compile
    mapper._post_configure_properties()
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/mapper.py", line 832, in _post_configure_properties
    prop.init()
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/interfaces.py", line 482, in init
    self.do_init()
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/properties.py", line 829, in do_init
    self._determine_joins()
  File "/Users/classic/dev/sqlalchemy/lib/sqlalchemy/orm/properties.py", line 920, in _determine_joins
    "many-to-many relationship, 'secondaryjoin' is needed as well." % (self))
sqlalchemy.exc.ArgumentError: Could not determine join condition between parent/child tables on relationship A.t2s.  Specify a 'primaryjoin' expression.  If this is a many-to-many relationship, 'secondaryjoin' is needed as well.

[Former user]

Example of the problem

[Former user]

I've worked around this in my code but I figured I'd help you squash this. Note that it only occurs if you append the foreign keys to the columns AFTER creating the tables. If you build the tables with the foreign keys in the first place, you get a cyclical reference exception, which at least is understandable given the circumstances.

Also, it seems reasonable to me that if I specify a primaryjoin, you could infer the direction of the relationship from the object you're referencing in the mapper() call, and set impl properly just based on whether the key referenced in the primary join not belonging to the object specified in the mapper was unique/primary or not. Is there some situation where that logic is incorrect?

Thanks for the heads up on the backref object, btw.

Happy bughunting :)

[Mike Bayer]

Replying to guest:

Also, it seems reasonable to me that if I specify a primaryjoin, you could infer the direction of the relationship from the object you're referencing in the mapper() call,

this is correct, the direction of the relationship is inferred from the primaryjoin as well as that part of the primaryjoin which is a foreign key.

[Mike Bayer]

OK, here is the essence of your test:

from sqlalchemy import *
from sqlalchemy.orm import *

m = MetaData()
t1 = Table('t1',
    m,
    Column('id', Integer, primary_key=True),
    Column('t2_id', Integer)
)

t2 = Table('t2',
    m,
    Column('id', Integer, ForeignKey('t1.t2_id'), primary_key=True)
)

class A(object):pass
class B(object):pass

mapper(A, t1)
mapper(B, t2, properties={
    "t1s": relationship(A)

    # equivalent:
    #"t1s": relationship(A, primaryjoin=t2.c.id==t1.c.t2_id)
})

compile_mappers()
assert B.t1s.property.uselist

The above table setup is relationally invalid. SQLite is letting you do it since it doesn't actually support foreign keys by default. Postgresql will not allow such a table setup, nor will MySQL if you use InnoDB.

From Wikipedia: http://en.wikipedia.org/wiki/Foreign_key

In the context of relational databases, a foreign key is a referential constraint between two tables.1 The foreign key identifies a column or a set of columns in one (referencing) table that refers to set of columns in another (referenced) table. The columns in the referencing table must be the primary key or other candidate key in the referenced table. The values in one row of the referencing columns must occur in a single row in the referenced table.

The third and fourth sentences both say the same thing - the referenced column must be unique. So not valid for foreign key to reference a non-unique column in "t1". You can of course add uselist=True manually for this non standard setup and SQLA will probably be able to work with it.

It would be nice if SQLAlchemy could emit a warning for this scenario, however its not possible in all cases, since we don't require that, for example, a UniqueConstraint() object exists for any Table that actually has such a constraint on the database side. I.e. there's no single place we actually "know" that the targeted columns are "unique" in a database sense. So I'd like to issue "worksforme" on this one.

[Former user]

Okay, sounds good. Thanks for the assistance Mike.