Issues

Issue #3070 closed

ForeignKey schema detection from MetaData problem

Iurie Gheorghies
created an issue

"""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'

Comments (10)

  1. Mike Bayer repo owner

    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'))
    
  2. Mike Bayer repo owner

    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?

  3. Mike Bayer repo owner

    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.

  4. Mike Bayer repo owner

    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))
    
  5. Iurie Gheorghies reporter

    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. )

  6. Mike Bayer repo owner

    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....

  7. Log in to comment