rules for locating schema qualified table are inconsistent in FK vs. declarative

[Alistair Watson]

The following code fails to print the relationships when I set the schema argument of Metadata and when inspecting a table with a many to many relationship. If the schema argument is removed or the tables have a different relationship such as one to many then the relationship attribute works. I am using the latest version of SQLAlchemy (1.1.9) and Python 3.4.5. I also tried it using an old version of SQLAlchemy (0.9.10) and the problem exists there too.

from sqlalchemy import MetaData, inspect, Column, ForeignKey, Integer
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

metadata = MetaData(schema='test_schema')
Base = declarative_base(metadata=metadata)

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    children = relationship("Child",back_populates="parent",secondary="secondary")

class Secondary(Base):
    __tablename__ = 'secondary'
    fk_parent = Column(Integer, ForeignKey('parent.id'), primary_key=True)
    fk_child = Column(Integer, ForeignKey('child.id'), primary_key=True)

class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent = relationship("Parent",back_populates="children",secondary="secondary")

insp = inspect(Child)

for a in insp.relationships:
    print(a)

and the stack trace

Traceback (most recent call last):
  File "/home/aw/env3/lib/python3.4/site-packages/sqlalchemy/ext/declarative/clsregistry.py", line 281, in __call__
    x = eval(self.arg, globals(), self._dict)
  File "<string>", line 1, in <module>
NameError: name 'secondary' is not defined

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "schema_test.py", line 25, in <module>
    for a in insp.relationships:
  File "/home/aw/env3/lib/python3.4/site-packages/sqlalchemy/util/langhelpers.py", line 764, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "/home/aw/env3/lib/python3.4/site-packages/sqlalchemy/orm/mapper.py", line 2265, in relationships
    return self._filter_properties(properties.RelationshipProperty)
  File "/home/aw/env3/lib/python3.4/site-packages/sqlalchemy/orm/mapper.py", line 2282, in _filter_properties
    configure_mappers()
  File "/home/aw/env3/lib/python3.4/site-packages/sqlalchemy/orm/mapper.py", line 2866, in configure_mappers
    mapper._post_configure_properties()
  File "/home/aw/env3/lib/python3.4/site-packages/sqlalchemy/orm/mapper.py", line 1765, in _post_configure_properties
    prop.init()
  File "/home/aw/env3/lib/python3.4/site-packages/sqlalchemy/orm/interfaces.py", line 184, in init
    self.do_init()
  File "/home/aw/env3/lib/python3.4/site-packages/sqlalchemy/orm/relationships.py", line 1652, in do_init
    self._process_dependent_arguments()
  File "/home/aw/env3/lib/python3.4/site-packages/sqlalchemy/orm/relationships.py", line 1677, in _process_dependent_arguments
    setattr(self, attr, attr_value())
  File "/home/aw/env3/lib/python3.4/site-packages/sqlalchemy/ext/declarative/clsregistry.py", line 292, in __call__
    (self.prop.parent, self.arg, n.args[0], self.cls)
sqlalchemy.exc.InvalidRequestError: When initializing mapper Mapper|Child|child, expression 'secondary' failed to locate a name ("name 'secondary' is not defined"). If this is a class name, consider adding this relationship() to the <class '__main__.Child'> class after both dependent classes have been defined.

[Mike Bayer]

Hi there, and thanks for posting a complete test case with accurate details.

The "schema" argument in MetaData defines the default schema that will be applied to a new Table object that does not otherwise specify a schema. It does not prevent a Table from being added that does not specify a schema; such a Table can be added using the symbol sqlalchemy.schema.BLANK_SCHEMA.

So we've established that your MetaData is capable of containing a table only identified by "secondary" and another identified by "test_schema.secondary". You're looking to target the latter. Therefore, to eliminate ambiguity, when referring to tables by name in a MetaData object, they must always be schema qualified if they in fact refer to a non-blank schema. That is:

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    children = relationship(
        "Child", back_populates="parent",
        secondary="test_schema.secondary")


class Secondary(Base):
    __tablename__ = 'secondary'
    fk_parent = Column(Integer, ForeignKey('parent.id'), primary_key=True)
    fk_child = Column(Integer, ForeignKey('child.id'), primary_key=True)


class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent = relationship(
        "Parent",
        back_populates="children",
        secondary="test_schema.secondary")

To make it work the way you expect, the "schema" argument on MetaData would need to take effect within the lookup of table names in the .tables dictionary as well, which means new rules and such would need to be set up to accommodate for BLANK_SCHEMA and such. I'm not sure what effects this might have long term and the purpose of "MetaData.schema" in the first place didn't intend to go this far.

If you're looking for indirection so that you don't have to memorize "test_schema", you can move from strings to using objects instead:

class Parent(Base):
    __tablename__ = 'parent'
    id = Column(Integer, primary_key=True)
    children = relationship(
        "Child", back_populates="parent",
        secondary=lambda: Secondary.__table__)


class Secondary(Base):
    __tablename__ = 'secondary'
    fk_parent = Column(Integer, ForeignKey('parent.id'), primary_key=True)
    fk_child = Column(Integer, ForeignKey('child.id'), primary_key=True)


class Child(Base):
    __tablename__ = 'child'
    id = Column(Integer, primary_key=True)
    parent = relationship(
        "Parent",
        back_populates="children",
        secondary=lambda: Secondary.__table__)

[Mike Bayer]

• add a note to MetaData.schema indicating that the Table will be cataloged in the .tables collection with its fully qualified name. Fixes #3978

Change-Id: I65fa063918efc22658e93c39a0680cb83553dec8

→ <<cset 339e2c13b0fc>>

[Mike Bayer]

• add a note to MetaData.schema indicating that the Table will be cataloged in the .tables collection with its fully qualified name. Fixes #3978

Change-Id: I65fa063918efc22658e93c39a0680cb83553dec8 (cherry picked from commit 339e2c13b0fc8e95a47d00c0f8fc5afc4b6dff9a)

→ <<cset abaa6da7a04f>>

[Mike Bayer]

• add a note to MetaData.schema indicating that the Table will be cataloged in the .tables collection with its fully qualified name. Fixes #3978

Change-Id: I65fa063918efc22658e93c39a0680cb83553dec8 (cherry picked from commit 339e2c13b0fc8e95a47d00c0f8fc5afc4b6dff9a)

→ <<cset 6d8d6c334b0e>>

[Alistair Watson]

That's great, thanks for the tip. I guess what got me was the fact the foreign key strings don't need to be fully qualified with the schema. Unfortunately the code I've got already uses strings for the entire config and the classes are built from there, but it's not a problem now I understand where I went wrong. Thanks for your help :)

[Mike Bayer]

I guess what got me was the fact the foreign key strings don't need to be fully qualified with the schema

oh. hm. they don't, do they :) see I totally forgot about that. That's a rule in ForeignKey itself that is looking at MetaData.schema. And.....it looks like it would in fact break if we had "BLANK_SCHEMA.secondary" in there as well. This is why adding little things here and there over time is so scary - that's actually kind of a bug. but again, one I can't change since yeah people are going to be relying on that.

if we wanted to duplicate what ForeignKey does here we'd put it in the declarative string lookup logic. It could be done. but it seems like there's a real problem here that the addition of MetaData.schema, and then later BLANK_SCHEMA to suit someone's particular use case, wasn't considered in total.

[Mike Bayer]

bigger issue

[Mike Bayer]

There is no way to create a foreign key to a BLANK_SCHEMA column name. Similarly, the lookup in MetaData.tables is inconsistent vs. ForeignKey string lookup, inconsistent vs. declarative.

These should be made completely consistent. The rule can be made as follows:

1. the MetaData.tables collection stays as a dictionary with a single, fully qualified key. I'd rather not make this dictionary something "magic", as particularly it needs to iterate accurately.

2. MetaData gets a new accessor resolve_table(<string>). Rules are:

   a. if MetaData has no "schema", this is a direct key into the .tables dictionary.

   b. string has no schema name. table returned is <metadata_schema_name>.table. if <metadata_schema_name>.table doesn't exist, raise an error. The error can include a note if there is a BLANK_SCHEMA.<tablename> present, but doesn't assume that's what the caller wants.

c. string takes on special form "BLANK_SCHEMA.<tablename>". then we look up on "blank" schema. This also works in case "a.". not sure I like the actual symbol name there, need to think about it.

Declarative needs to use resolve_table(). ForeignKey unfortunately needs to determine its exact schemaname.tablename.colname before the remote table potentially exists.

from sqlalchemy import *

m = MetaData(schema="foo")

a1 = Table(
    'a', m,
    Column('id', Integer, primary_key=True)
)

a2 = Table(
    'a', m,
    Column('id', Integer, primary_key=True),
    schema=BLANK_SCHEMA
)

b = Table(
    'b', m,
    Column('id', Integer, primary_key=True),
    Column('aid', ForeignKey('a.id'))
)

assert list(b.c.aid.foreign_keys)[0].column is a1.c.id

Table(
    'c', m,
    Column('id', Integer, primary_key=True),
    Column('aid', ForeignKey('foo.a.id'))
)

Table(
    'd', m,
    Column('id', Integer, primary_key=True),
    Column('aid', ForeignKey(a2.c.id))
)