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!