SQLalchemy not find table for creating foreign key

I have a problem with SQL Alchemy, while trying to create a database, i get:

"sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'estate_agent.person_id' could not find table 'person' with which to generate a foreign key to target column 'id'"

Meta datas:

db = create_engine('postgresql+psycopg2:...//')
meta = MetaData()
meta.bind = db

Person table:

tbl_person = Table(
   'person', meta,
   Column('id', Integer, Sequence('seq_person_id'), primary_key=True),
   Column('name', String(100), unique=True, nullable = False),
   Column('password', String(40), nullable = False),
   Column('person_type_id', Integer, ForeignKey("person_type.id"), nullable = False),
   Column('register_date', DateTime, default = datetime.now),
   Column('pendencies', String(200)),
   Column('active', Boolean, default = True),
   schema = 'public')

Bug Table:

tbl_estate_agent = Table(
   'estate_agent', meta,
   Column('person_id', Integer, ForeignKey("person.id"), primary_key = True),
   Column('prize_range_id', Integer, ForeignKey("prize_range.id"), nullable = False),
   schema = 'public')

Normal table (creating normally the fk)

tbl_person_agent = Table(
   'person_agent', meta,
   Column('person_id', Integer, ForeignKey("person.id"), primary_key = True),
   Column('prize_range_id', Integer, ForeignKey("prize_range.id"), nullable = False),
   schema = 'public')

Creation Call:

meta.create_all(checkfirst=True)

Complete error log:

Traceback (most recent call last):
   File "database_client.py", line 159, in <module>
    meta.create_all(checkfirst=True)
   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/schema.py", line 3404, in create_all
    tables=tables)
   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1616, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/engine/base.py", line 1245, in _run_visitor
    **kwargs).traverse_single(element)
   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/visitors.py", line 120, in traverse_single
    return meth(obj, **kw)
   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/ddl.py", line 699, in visit_metadata
    collection = [t for t in sort_tables(tables)
   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/ddl.py", line 862, in sort_tables
    {'foreign_key': visit_foreign_key})
   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/visitors.py", line 256, in traverse
    return traverse_using(iterate(obj, opts), obj, visitors)
   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/visitors.py", line 247, in traverse_using
    meth(target)
   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/ddl.py", line 853, in visit_foreign_key
    parent_table = fkey.column.table   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/util/langhelpers.py", line 725, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
   File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/sql/schema.py", line 1720, in column tablekey)
sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'estate_agent.person_id' could not find table 'person' with which to generate a foreign key to target column 'id'

The solution is to replace the strings with actual columns:

Column('person_id', Integer, ForeignKey(tbl_person.c.id), primary_key=True)

By adding the following line to my parent table solved my problem. In case of Declarative:

children = relationship("Child")

Otherwise: SQLAlchemy - Classic Mapper

Also try to have a look in here (SO) too, might help.


In case of Declarative, I solved this problem by simply importing the class that was 'could not be found'.


Conclusion

This exception was caused because that there is no record of parent table in MetaData instance, and this table needs to be retrieved in the DB. Invoke the function "reflect" of class MetaData to obtain all existed tables on the DB. It should be used like this

def upgrade(migrate_engine):

    meta.bind = migrate_engine

    meta.reflect() # <------ Obtain all tables here.

    aggregate_metadata.create()
    aggregate_hosts.create()

Description

A table is in a different file from another table that has a foreign key associate with it. In this case, sqlalchemy will fail to find the corresponding table when create tables, as shown below:

sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'aggregate_metadata.aggregate_id' could not find table 'aggregates' with which to generate a foreign key to target column 'id'

For example:

# File 002_Add_aggregates_table.py
# ========================================
...

meta = MetaData()

aggregates = Table('aggregates', meta,
    ...
    Column('id', Integer, primary_key=True, nullable=False),
    mysql_engine='InnoDB',
    mysql_charset='utf8'
)

def upgrade(migrate_engine):
    meta.bind = migrate_engine
    aggregates.create()
# File 003_Add_aggregate_metadata_hosts.py
# ========================================
...

meta = MetaData()

aggregate_metadata = Table('aggregate_metadata', meta,
    ...
    Column('aggregate_id', Integer, ForeignKey('aggregates.id'), # <------ ForeignKey
           nullable=False),
    mysql_engine='InnoDB',
    mysql_charset='utf8'
    )

def upgrade(migrate_engine):

    meta.bind = migrate_engine
    aggregate_metadata.create()

Root cause

Let's locate at the point that throw the exception

  File "/opt/xxx/.local/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py", line 1113, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "/opt/xxx/.local/lib/python3.6/site-packages/sqlalchemy/sql/schema.py", line 2394, in column
    tablekey,
sqlalchemy.exc.NoReferencedTableError: Foreign key associated with column 'aggregate_metadata.aggregate_id' could not find table 'aggregates' with which to generate a foreign key to target column 'id'

We could find the corresponding code and debug it:

# File: sqlalchemy/sql/schema.py
2358     def column(self):
            ...
2371
2372        if isinstance(self._colspec, util.string_types):
2373
2374           parenttable, tablekey, colname = self._resolve_col_tokens()

# =========> BeginDebug
2375           raise Exception(
2376               'imgrass->\n'
2377               '  - parenttable: %s\n'
2378               '  - parenttable.metadata: %s\n'
2379               '  - tablekey: %s\n'
2380               '  - colname: %s' % (
2381                   parenttable,
2382                   parenttable.metadata,
2383                   tablekey,
2384                   colname
2385                   )
2386               )
# =========> EndDebug

2387
2388           if tablekey not in parenttable.metadata:
2389              raise exc.NoReferencedTableError(
2390                 "Foreign key associated with column '%s' could not find "
2391                 "table '%s' with which to generate a "
2392                 "foreign key to target column '%s'"
2393                 % (self.parent, tablekey, colname),
2394                 tablekey,
2395              )

Then we could get below exceptions:

Exception: imgrass->
  - parenttable: aggregate_metadata
  - parenttable.metadata: MetaData(bind=Engine(mysql+pymysql://imgrass:***@172.17.0.1/demo))
  - tablekey: aggregates
  - colname: id

So, the parenttable.metadata is an instance of class MetaData, and the tablekey is a table name. We could reasonably guess that the table aggregates should be included in the instance of class MetaData. Considering that the definition of this table is in another file, and the MetaData instance has the connection way of DB(bind=xxx), there should be a function in class MetaData to obtain all tables in the DB.

In MetaData, we could find this function

# File: sqlalchemy/sql/schema.py

class MetaData(SchemaItem):
    ...

    def reflect(...):
        r"""Load all available table definitions from the database.
        ...

From its description, we could guess its function, let's apply it in my script:

# File 003_Add_aggregate_metadata_hosts.py
# ========================================
...

def upgrade(migrate_engine):

    meta.bind = migrate_engine

# ==================> BeginUpdate
    meta.reflect()
# ==================> EndUpdate

    aggregate_metadata.create()
    aggregate_hosts.create()

It's okay!