ForeignKey schema detection from MetaData problem

[Iurie Gheorghies]

"""Python 2.7.5+ SQLAlchemy 9.4"""

A regression fix was made: https://github.com/zzzeek/sqlalchemy/pull/67

but the script fails with exception

The script attached output:

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'user.account_id' could not find table 'account' with which to generate a foreign key to target column 'id'

[Mike Bayer]

foreign key defs using strings require the fully qualified schema.tablename in order to locate their referenced col...unless you use the .schema argument on the MetaData() collection overall. Sorry!

  account_id = Column(Integer, ForeignKey('test.account.id'))

[Iurie Gheorghies]

Not a trivial task. )) Planed for future releases?

[Mike Bayer]

what should the system do if you have two tables both named "account" with different schema names?

[Mike Bayer]

as well as an "account" table that has no schema name, it uses the "default" for the connection. how to refer to that? "<none>".table.colname ? seems awkward?

[Iurie Gheorghies]

Any ideas how to overcome this. Don't want to use one Base class for one schema at the same time don't want to hardcode schema in foreign keys.

[Mike Bayer]

there's a lot of ways, it depends on what you're trying to do. like use a mixin:

class UsesTest(object):
    __table_args__ = {'schema': 'test'}

    @staticmethod
    def ForeignKey(name):
        return ForeignKey("test.%s" % name)

class User(UsesTest, Base):

    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    account_id = Column(Integer, UsesTest.ForeignKey("account.id"))

class Account(UsesTest, Base):

    __tablename__ = 'account'
    id = Column(Integer, primary_key=True)


    user = relationship(User)

or if you just have certain schemas based on something, just use plain functions, like def schema1_fk(), depends on who has the knowledge where.

[Iurie Gheorghies]

Good tips. But with table_args might get in problems if Child overwrites it.

[Mike Bayer]

then pull it out of the table args!

class UsesTest(object):
    __table_args__ = {'schema': 'test'}

    @classmethod
    def ForeignKey(cls, name):
        return ForeignKey("%s.%s" % (cls.__table_args__['schema'], name))

[Iurie Gheorghies]

I mean there are times when I need to use it for unique constrains or other things s0 I silently overwrite it.

class UsesTest(object):
    __table_args__ = {'schema': 'test'}

    @staticmethod
    def ForeignKey(name):
        return ForeignKey("test.%s" % name)

class A(UsesTest, Base):

    __tablename__ = 'user'

    id = Column(Integer, primary_key=True)
    field = Column(Integer)
    field2 = Column(Integer)


    __table_args__ = (UniqueConstraint('field', 'field2', name='unique_field_field2'))

Sorry for taking your time with Python lessons. )

[Mike Bayer]

well that can happen regardless, a subclass can override anything. if its valid for them to subclass it you can make the foreignkey method smarter, or have it raise an exception....