SQLalchemy not find table for creating foreign key

Question

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'

Matthew Moisen · Accepted Answer · 2017-06-21 22:00:29Z

80

The solution is to replace the strings with actual columns:

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

answered Jun 21, 2017 at 22:00

Matthew Moisen

17.7k30 gold badges142 silver badges240 bronze badges

13

@Icyh Late to the party, but it works because you must import the Table object, which means it is actually created. I'm willing to bet that in the original code the tbl_person had not been created and so the string lookup fails, because the MetaData did not contain it.
– Ilja Everilä
Commented Sep 28, 2018 at 7:05
Thanks, this still worked for me. Not sure why because parts of the code still work with ' ' but works. :)
– El Dude
Commented Aug 23, 2023 at 21:14

Add a comment |

Community · Accepted Answer · 2017-05-23 12:06:47Z

20

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.

edited May 23, 2017 at 12:06

CommunityBot

11 silver badge

answered Mar 26, 2015 at 15:03

Hamed

86910 silver badges26 bronze badges

Add a comment |

Jakobovski · Accepted Answer · 2019-02-24 15:19:55Z

20

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

answered Feb 24, 2019 at 15:19

Jakobovski

3,3711 gold badge32 silver badges41 bronze badges

Add a comment |

swimmer · Accepted Answer · 2022-01-22 16:29:40Z

Although the top voted answer solves the issue, replacing strings with objects forces you to define tables in a specific order (which can be non-trivial for very large databases). From the SQLAlchemy docs:

The advantage to using a string is that the in-python linkage between [different tables] is resolved only when first needed, so that table objects can be easily spread across multiple modules and defined in any order.

You can continue to use strings by passing the schema to the ForeignKey. For example, instead of doing:

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

One can do:

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

It worked here. Specially because I was not using the default schema perhaps. Thanks man — Caleb Santos, Commented Jan 24 at 2:23

imgrass · Accepted Answer · 2021-07-07 05:13:08Z

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!

This answer is underrated. It shows the real problem here and describes how to properly handle it. — Qback, Commented Feb 19 at 10:54

Alexey · Accepted Answer · 2024-04-03 16:59:16Z

In my case in ForeignKey I have not used schema name, just table name and column: project.id

class Property(Base):
    __tablename__ = "property"
    __table_args__= {'schema':'scraper'}
    id = mapped_column(Integer,primary_key=True, autoincrement=False)
    project_id = mapped_column(Integer, ForeignKey("project.id"))

I got an error:

Foreign key associated with column 'property.project_id' could not find table 'project' with which to generate a foreign key to target column 'id'

Adding schema fixes the issue:

class Property(Base):
  __tablename__ = "property"
  __table_args__= {'schema':'scraper'}
  id = mapped_column(Integer,primary_key=True, autoincrement=False)
  project_id = mapped_column(Integer, ForeignKey("scraper.project.id"))

Collectives™ on Stack Overflow

SQLalchemy not find table for creating foreign key

6 Answers 6

Conclusion

Description

Root cause

Not the answer you're looking for? Browse other questions tagged
python
postgresql
python-2.7
sqlalchemy
or ask your own question.

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Conclusion

Description

Root cause

Not the answer you're looking for? Browse other questions tagged pythonpostgresqlpython-2.7sqlalchemy or ask your own question.

Linked

Related

Not the answer you're looking for? Browse other questions tagged
python
postgresql
python-2.7
sqlalchemy
or ask your own question.