Issue 1:
Describe the bug

Using a variant of referred_column in the fk naming_convention placeholder (see https://docs.sqlalchemy.org/en/14/core/constraints.html#constraint-naming-conventions), causes an exception, when trying to initialise the ORM.

(without using referred_column, this works fine)

To Reproduce

import sqlalchemy as sa
from sqlalchemy.orm import declarative_base

Base = declarative_base(
metadata=MetaData(naming_convention={
"fk": "fk_%(table_name)s_%(column_0_N_name)s_%(referred_table_name)s_%(referred_column_0_N_name)s"
}
))

class Model1(Base):
__tablename__ = 'model1'
pk = sa.Column('pk', sa.Integer(), primary_key=True)
model2_pk = sa.Column(sa.Integer(), sa.ForeignKey('model2.pk'))

class Model2(Base):
__tablename__ = 'model2'
pk = sa.Column('pk', sa.Integer(), primary_key=True)

causes

---------------------------------------------------------------------------
NoReferencedTableError Traceback (most recent call last)
Input In [100], in <module>
2 from sqlalchemy.orm import declarative_base
4 Base = declarative_base(
5 metadata=MetaData(naming_convention={
6 "fk": "fk_%(table_name)s_%(column_0_N_name)s_%(referred_table_name)s_%(referred_column_0_N_name)s"
7 }
8 ))
---> 10 class Model1(Base):
11 __tablename__ = 'model1'
12 pk = sa.Column('pk', sa.Integer(), primary_key=True)

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/orm/decl_api.py:72, in DeclarativeMeta.__init__(cls, classname, bases, dict_, **kw)
69 cls._sa_registry = reg
71 if not cls.__dict__.get("__abstract__", False):
---> 72 _as_declarative(reg, cls, dict_)
73 type.__init__(cls, classname, bases, dict_)

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/orm/decl_base.py:126, in _as_declarative(registry, cls, dict_)
121 def _as_declarative(registry, cls, dict_):
122
123 # declarative scans the class for attributes. no table or mapper
124 # args passed separately.
--> 126 return _MapperConfig.setup_mapping(registry, cls, dict_, None, {})

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/orm/decl_base.py:177, in _MapperConfig.setup_mapping(cls, registry, cls_, dict_, table, mapper_kw)
174 else:
175 cfg_cls = _ClassScanMapperConfig
--> 177 return cfg_cls(registry, cls_, dict_, table, mapper_kw)

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/orm/decl_base.py:322, in _ClassScanMapperConfig.__init__(self, registry, cls_, dict_, table, mapper_kw)
318 self._extract_mappable_attributes()
320 self._extract_declared_columns()
--> 322 self._setup_table(table)
324 self._setup_inheritance(mapper_kw)
326 self._early_mapping(mapper_kw)

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/orm/decl_base.py:811, in _ClassScanMapperConfig._setup_table(self, table)
806 if autoload:
807 table_kw["autoload"] = True
809 table = self.set_cls_attribute(
810 "__table__",
--> 811 table_cls(
812 tablename,
813 self._metadata_for_cls(manager),
814 *(tuple(declared_columns) + tuple(args)),
815 **table_kw
816 ),
817 )
818 else:
819 if table is None:

File <string>:2, in __new__(cls, *args, **kw)

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/util/deprecations.py:309, in deprecated_params.<locals>.decorate.<locals>.warned(fn, *args, **kwargs)
302 if m in kwargs:
303 _warn_with_version(
304 messages[m],
305 versions[m],
306 version_warnings[m],
307 stacklevel=3,
308 )
--> 309 return fn(*args, **kwargs)

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/sql/schema.py:606, in Table.__new__(cls, *args, **kw)
604 return table
605 except Exception:
--> 606 with util.safe_reraise():
607 metadata._remove_table(name, schema)

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py:70, in safe_reraise.__exit__(self, type_, value, traceback)
68 self._exc_info = None # remove potential circular references
69 if not self.warn_only:
---> 70 compat.raise_(
71 exc_value,
72 with_traceback=exc_tb,
73 )
74 else:
75 if not compat.py3k and self._exc_info and self._exc_info[1]:
76 # emulate Py3K's behavior of telling us when an exception
77 # occurs in an exception handler.

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/util/compat.py:207, in raise_(***failed resolving arguments***)
204 exception.__cause__ = replace_context
206 try:
--> 207 raise exception
208 finally:
209 # credit to
210 # https://cosmicpercolator.com/2016/01/13/exception-leaks-in-python-2-and-3/
211 # as the __traceback__ object creates a cycle
212 del exception, replace_context, from_, with_traceback

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/sql/schema.py:602, in Table.__new__(cls, *args, **kw)
600 metadata._add_table(name, schema, table)
601 try:
--> 602 table._init(name, metadata, *args, **kw)
603 table.dispatch.after_parent_attach(table, metadata)
604 return table

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/sql/schema.py:688, in Table._init(self, name, metadata, *args, **kwargs)
677 self._autoload(
678 metadata,
679 autoload_with,
(...)
682 resolve_fks=resolve_fks,
683 )
685 # initialize all the column, etc. objects. done after reflection to
686 # allow user-overrides
--> 688 self._init_items(
689 *args,
690 allow_replacements=extend_existing or keep_existing or autoload
691 )

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/sql/schema.py:135, in SchemaItem._init_items(self, *args, **kw)
127 util.raise_(
128 exc.ArgumentError(
129 "'SchemaItem' object, such as a 'Column' or a "
(...)
132 replace_context=err,
133 )
134 else:
--> 135 spwd(self, **kw)

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/sql/base.py:1047, in SchemaEventTarget._set_parent_with_dispatch(self, parent, **kw)
1045 self.dispatch.before_parent_attach(self, parent)
1046 self._set_parent(parent, **kw)
-> 1047 self.dispatch.after_parent_attach(self, parent)

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/event/attr.py:343, in _CompoundListener.__call__(self, *args, **kw)
341 fn(*args, **kw)
342 for fn in self.listeners:
--> 343 fn(*args, **kw)

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/sql/schema.py:2478, in ForeignKey._set_table(self, column, table)
2465 self.constraint = ForeignKeyConstraint(
2466 [],
2467 [],
(...)
2475 **self._unvalidated_dialect_kw
2476 )
2477 self.constraint._append_element(column, self)
-> 2478 self.constraint._set_parent_with_dispatch(table)
2479 table.foreign_keys.add(self)
2480 # set up remote ".column" attribute, or a note to pick it
2481 # up when the other Table/Column shows up

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/sql/base.py:1047, in SchemaEventTarget._set_parent_with_dispatch(self, parent, **kw)
1045 self.dispatch.before_parent_attach(self, parent)
1046 self._set_parent(parent, **kw)
-> 1047 self.dispatch.after_parent_attach(self, parent)

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/event/attr.py:256, in _EmptyListener.__call__(self, *args, **kw)
253 """Execute this event."""
255 for fn in self.parent_listeners:
--> 256 fn(*args, **kw)

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/sql/naming.py:208, in _constraint_name(const, table)
205 if isinstance(const.name, conv) or const.name is _NONE_NAME:
206 return
--> 208 newname = _constraint_name_for_table(const, table)
209 if newname:
210 const.name = newname

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/sql/naming.py:166, in _constraint_name_for_table(const, table)
155 return const.name
156 elif (
157 convention is not None
158 and not isinstance(const.name, conv)
(...)
163 )
164 ):
165 return conv(
--> 166 convention
167 % ConventionDict(const, table, metadata.naming_convention)
168 )
169 elif convention is _NONE_NAME:
170 return None

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/sql/naming.py:117, in ConventionDict.__getitem__(self, key)
115 attr = "_key_" + key.replace("0" + multiples, "X")
116 try:
--> 117 tokens.append(getattr(self, attr)(idx))
118 except AttributeError:
119 raise KeyError(key)

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/sql/naming.py:95, in ConventionDict._key_referred_column_X_name(self, idx)
91 fk = self.const.elements[idx]
92 # note that before [ticket:3989], this method was returning
93 # the specification for the :class:`.ForeignKey` itself, which normally
94 # would be using the ``.key`` of the column, not the name.
---> 95 return fk.column.name

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py:1113, in memoized_property.__get__(self, obj, cls)
1111 if obj is None:
1112 return self
-> 1113 obj.__dict__[self.__name__] = result = self.fget(obj)
1114 return result

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/sql/schema.py:2410, in ForeignKey.column(self)
2407 parenttable, tablekey, colname = self._resolve_col_tokens()
2409 if tablekey not in parenttable.metadata:
-> 2410 raise exc.NoReferencedTableError(
2411 "Foreign key associated with column '%s' could not find "
2412 "table '%s' with which to generate a "
2413 "foreign key to target column '%s'"
2414 % (self.parent, tablekey, colname),
2415 tablekey,
2416 )
2417 elif parenttable.key not in parenttable.metadata:
2418 raise exc.InvalidRequestError(
2419 "Table %s is no longer associated with its "
2420 "parent MetaData" % parenttable
2421 )

NoReferencedTableError: Foreign key associated with column 'model1.model2_pk' could not find table 'model2' with which to generate a foreign key to target column 'pk'

or, ordering the models the other way:

class Model2(Base):
__tablename__ = 'model2'
pk = sa.Column('pk', sa.Integer(), primary_key=True)

class Model1(Base):
__tablename__ = 'model1'
pk = sa.Column('pk', sa.Integer(), primary_key=True)
model2_pk = sa.Column(sa.Integer(), sa.ForeignKey('model2.pk')

causes

---------------------------------------------------------------------------
NoReferencedColumnError Traceback (most recent call last)
Input In [98], in <module>
10 __tablename__ = 'model2'
11 pk = sa.Column('pk', sa.Integer(), primary_key=True)
---> 12 class Model1(Base):
13 __tablename__ = 'model1'
14 pk = sa.Column('pk', sa.Integer(), primary_key=True)

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/orm/decl_api.py:72, in DeclarativeMeta.__init__(cls, classname, bases, dict_, **kw)
69 cls._sa_registry = reg
71 if not cls.__dict__.get("__abstract__", False):
---> 72 _as_declarative(reg, cls, dict_)
73 type.__init__(cls, classname, bases, dict_)

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/orm/decl_base.py:126, in _as_declarative(registry, cls, dict_)
121 def _as_declarative(registry, cls, dict_):
122
123 # declarative scans the class for attributes. no table or mapper
124 # args passed separately.
--> 126 return _MapperConfig.setup_mapping(registry, cls, dict_, None, {})

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/orm/decl_base.py:177, in _MapperConfig.setup_mapping(cls, registry, cls_, dict_, table, mapper_kw)
174 else:
175 cfg_cls = _ClassScanMapperConfig
--> 177 return cfg_cls(registry, cls_, dict_, table, mapper_kw)

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/orm/decl_base.py:322, in _ClassScanMapperConfig.__init__(self, registry, cls_, dict_, table, mapper_kw)
318 self._extract_mappable_attributes()
320 self._extract_declared_columns()
--> 322 self._setup_table(table)
324 self._setup_inheritance(mapper_kw)
326 self._early_mapping(mapper_kw)

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/orm/decl_base.py:811, in _ClassScanMapperConfig._setup_table(self, table)
806 if autoload:
807 table_kw["autoload"] = True
809 table = self.set_cls_attribute(
810 "__table__",
--> 811 table_cls(
812 tablename,
813 self._metadata_for_cls(manager),
814 *(tuple(declared_columns) + tuple(args)),
815 **table_kw
816 ),
817 )
818 else:
819 if table is None:

File <string>:2, in __new__(cls, *args, **kw)

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/util/deprecations.py:309, in deprecated_params.<locals>.decorate.<locals>.warned(fn, *args, **kwargs)
302 if m in kwargs:
303 _warn_with_version(
304 messages[m],
305 versions[m],
306 version_warnings[m],
307 stacklevel=3,
308 )
--> 309 return fn(*args, **kwargs)

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/sql/schema.py:606, in Table.__new__(cls, *args, **kw)
604 return table
605 except Exception:
--> 606 with util.safe_reraise():
607 metadata._remove_table(name, schema)

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py:70, in safe_reraise.__exit__(self, type_, value, traceback)
68 self._exc_info = None # remove potential circular references
69 if not self.warn_only:
---> 70 compat.raise_(
71 exc_value,
72 with_traceback=exc_tb,
73 )
74 else:
75 if not compat.py3k and self._exc_info and self._exc_info[1]:
76 # emulate Py3K's behavior of telling us when an exception
77 # occurs in an exception handler.

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/util/compat.py:207, in raise_(***failed resolving arguments***)
204 exception.__cause__ = replace_context
206 try:
--> 207 raise exception
208 finally:
209 # credit to
210 # https://cosmicpercolator.com/2016/01/13/exception-leaks-in-python-2-and-3/
211 # as the __traceback__ object creates a cycle
212 del exception, replace_context, from_, with_traceback

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/sql/schema.py:602, in Table.__new__(cls, *args, **kw)
600 metadata._add_table(name, schema, table)
601 try:
--> 602 table._init(name, metadata, *args, **kw)
603 table.dispatch.after_parent_attach(table, metadata)
604 return table

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/sql/schema.py:688, in Table._init(self, name, metadata, *args, **kwargs)
677 self._autoload(
678 metadata,
679 autoload_with,
(...)
682 resolve_fks=resolve_fks,
683 )
685 # initialize all the column, etc. objects. done after reflection to
686 # allow user-overrides
--> 688 self._init_items(
689 *args,
690 allow_replacements=extend_existing or keep_existing or autoload
691 )

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/sql/schema.py:135, in SchemaItem._init_items(self, *args, **kw)
127 util.raise_(
128 exc.ArgumentError(
129 "'SchemaItem' object, such as a 'Column' or a "
(...)
132 replace_context=err,
133 )
134 else:
--> 135 spwd(self, **kw)

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/sql/base.py:1047, in SchemaEventTarget._set_parent_with_dispatch(self, parent, **kw)
1045 self.dispatch.before_parent_attach(self, parent)
1046 self._set_parent(parent, **kw)
-> 1047 self.dispatch.after_parent_attach(self, parent)

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/event/attr.py:343, in _CompoundListener.__call__(self, *args, **kw)
341 fn(*args, **kw)
342 for fn in self.listeners:
--> 343 fn(*args, **kw)

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/sql/schema.py:2478, in ForeignKey._set_table(self, column, table)
2465 self.constraint = ForeignKeyConstraint(
2466 [],
2467 [],
(...)
2475 **self._unvalidated_dialect_kw
2476 )
2477 self.constraint._append_element(column, self)
-> 2478 self.constraint._set_parent_with_dispatch(table)
2479 table.foreign_keys.add(self)
2480 # set up remote ".column" attribute, or a note to pick it
2481 # up when the other Table/Column shows up

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/sql/base.py:1047, in SchemaEventTarget._set_parent_with_dispatch(self, parent, **kw)
1045 self.dispatch.before_parent_attach(self, parent)
1046 self._set_parent(parent, **kw)
-> 1047 self.dispatch.after_parent_attach(self, parent)

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/event/attr.py:256, in _EmptyListener.__call__(self, *args, **kw)
253 """Execute this event."""
255 for fn in self.parent_listeners:
--> 256 fn(*args, **kw)

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/sql/naming.py:208, in _constraint_name(const, table)
205 if isinstance(const.name, conv) or const.name is _NONE_NAME:
206 return
--> 208 newname = _constraint_name_for_table(const, table)
209 if newname:
210 const.name = newname

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/sql/naming.py:166, in _constraint_name_for_table(const, table)
155 return const.name
156 elif (
157 convention is not None
158 and not isinstance(const.name, conv)
(...)
163 )
164 ):
165 return conv(
--> 166 convention
167 % ConventionDict(const, table, metadata.naming_convention)
168 )
169 elif convention is _NONE_NAME:
170 return None

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/sql/naming.py:117, in ConventionDict.__getitem__(self, key)
115 attr = "_key_" + key.replace("0" + multiples, "X")
116 try:
--> 117 tokens.append(getattr(self, attr)(idx))
118 except AttributeError:
119 raise KeyError(key)

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/sql/naming.py:95, in ConventionDict._key_referred_column_X_name(self, idx)
91 fk = self.const.elements[idx]
92 # note that before [ticket:3989], this method was returning
93 # the specification for the :class:`.ForeignKey` itself, which normally
94 # would be using the ``.key`` of the column, not the name.
---> 95 return fk.column.name

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py:1113, in memoized_property.__get__(self, obj, cls)
1111 if obj is None:
1112 return self
-> 1113 obj.__dict__[self.__name__] = result = self.fget(obj)
1114 return result

File ~/.local/pipx/venvs/jupyterlab/lib/python3.10/site-packages/sqlalchemy/sql/schema.py:2423, in ForeignKey.column(self)
2418 raise exc.InvalidRequestError(
2419 "Table %s is no longer associated with its "
2420 "parent MetaData" % parenttable
2421 )
2422 else:
-> 2423 raise exc.NoReferencedColumnError(
2424 "Could not initialize target column for "
2425 "ForeignKey '%s' on table '%s': "
2426 "table '%s' has no column named '%s'"
2427 % (self._colspec, parenttable.name, tablekey, colname),
2428 tablekey,
2429 colname,
2430 )
2431 elif hasattr(self._colspec, "__clause_element__"):
2432 _column = self._colspec.__clause_element__()

NoReferencedColumnError: Could not initialize target column for ForeignKey 'model2.pk' on table 'model1': table 'model2' has no column named 'pk'

Error

See above

Versions

OS: Mac OS

Python: tested 3.7 and 3.10

SQLAlchemy: 1.4.31

Database: Any

DBAPI (eg: psycopg, cx_oracle, mysqlclient): -

Additional context

No response

Issue 2:
I've read #3989 but this seems unrelated.

When defining a ForeignKey on a SQLAlchemy model, if the referenced column is given as a string ({table_name}.{column_name}) and the Base.metadata has been setup with a naming convention that uses any of thereferred_column_0[[_]N]_name tokens for the foreign key convention, then an sqlalchemy.exc.NoReferencedColumnError is raised when the model class is being declared/mapped. Declaring the ForeignKey by passing a Column object works, though.

This sample code triggers the bug:

import sqlalchemy as sa
from sqlalchemy import MetaData
from sqlalchemy.ext.declarative import declarative_base

metadata = MetaData(naming_convention={
"fk": "fk__%(referred_table_name)s__%(referred_column_0_name)s",
})
Base = declarative_base(metadata=metadata)

class Parent(Base):
__tablename__ = 'parent'
id = sa.Column(sa.Integer, autoincrement=True, primary_key=True)
parent_id = sa.Column(sa.Integer, sa.ForeignKey('parent.id'))

If I change the last line to

parent_id = sa.Column(sa.Integer, sa.ForeignKey(id))

it works as expected. But referencing the column directly is not always possible. Although, in those cases, declaring the ForeignKeyConstraint separately via __table_args__ works like a charm.

Error

Traceback (most recent call last):
File "/home/thomas/dev/python/sqlalchemy_test/naming.py", line 17, in <module>
class Child(Base):
File "/home/thomas/dev/python/sqlalchemy_test/.env/lib/python3.8/site-packages/sqlalchemy/ext/declarative/api.py", line 75, in __init__
_as_declarative(cls, classname, cls.__dict__)
File "/home/thomas/dev/python/sqlalchemy_test/.env/lib/python3.8/site-packages/sqlalchemy/ext/declarative/base.py", line 131, in _as_declarative
_MapperConfig.setup_mapping(cls, classname, dict_)
File "/home/thomas/dev/python/sqlalchemy_test/.env/lib/python3.8/site-packages/sqlalchemy/ext/declarative/base.py", line 160, in setup_mapping
cfg_cls(cls_, classname, dict_)
File "/home/thomas/dev/python/sqlalchemy_test/.env/lib/python3.8/site-packages/sqlalchemy/ext/declarative/base.py", line 190, in __init__
self._setup_table()
File "/home/thomas/dev/python/sqlalchemy_test/.env/lib/python3.8/site-packages/sqlalchemy/ext/declarative/base.py", line 534, in _setup_table
cls.__table__ = table = table_cls(
File "<string>", line 2, in __new__
File "/home/thomas/dev/python/sqlalchemy_test/.env/lib/python3.8/site-packages/sqlalchemy/util/deprecations.py", line 128, in warned
return fn(*args, **kwargs)
File "/home/thomas/dev/python/sqlalchemy_test/.env/lib/python3.8/site-packages/sqlalchemy/sql/schema.py", line 507, in __new__
metadata._remove_table(name, schema)
File "/home/thomas/dev/python/sqlalchemy_test/.env/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 68, in __exit__
compat.raise_(
File "/home/thomas/dev/python/sqlalchemy_test/.env/lib/python3.8/site-packages/sqlalchemy/util/compat.py", line 178, in raise_
raise exception
File "/home/thomas/dev/python/sqlalchemy_test/.env/lib/python3.8/site-packages/sqlalchemy/sql/schema.py", line 502, in __new__
table._init(name, metadata, *args, **kw)
File "/home/thomas/dev/python/sqlalchemy_test/.env/lib/python3.8/site-packages/sqlalchemy/sql/schema.py", line 601, in _init
self._init_items(*args)
File "/home/thomas/dev/python/sqlalchemy_test/.env/lib/python3.8/site-packages/sqlalchemy/sql/schema.py", line 118, in _init_items
spwd(self)
File "/home/thomas/dev/python/sqlalchemy_test/.env/lib/python3.8/site-packages/sqlalchemy/sql/base.py", line 460, in _set_parent_with_dispatch
self.dispatch.after_parent_attach(self, parent)
File "/home/thomas/dev/python/sqlalchemy_test/.env/lib/python3.8/site-packages/sqlalchemy/event/attr.py", line 322, in __call__
fn(*args, **kw)
File "/home/thomas/dev/python/sqlalchemy_test/.env/lib/python3.8/site-packages/sqlalchemy/sql/schema.py", line 2111, in _set_table
self.constraint._set_parent_with_dispatch(table)
File "/home/thomas/dev/python/sqlalchemy_test/.env/lib/python3.8/site-packages/sqlalchemy/sql/base.py", line 460, in _set_parent_with_dispatch
self.dispatch.after_parent_attach(self, parent)
File "/home/thomas/dev/python/sqlalchemy_test/.env/lib/python3.8/site-packages/sqlalchemy/event/attr.py", line 261, in __call__
fn(*args, **kw)
File "/home/thomas/dev/python/sqlalchemy_test/.env/lib/python3.8/site-packages/sqlalchemy/sql/naming.py", line 180, in _constraint_name
newname = _constraint_name_for_table(const, table)
File "/home/thomas/dev/python/sqlalchemy_test/.env/lib/python3.8/site-packages/sqlalchemy/sql/naming.py", line 157, in _constraint_name_for_table
convention
File "/home/thomas/dev/python/sqlalchemy_test/.env/lib/python3.8/site-packages/sqlalchemy/sql/naming.py", line 108, in __getitem__
tokens.append(getattr(self, attr)(idx))
File "/home/thomas/dev/python/sqlalchemy_test/.env/lib/python3.8/site-packages/sqlalchemy/sql/naming.py", line 86, in _key_referred_column_X_name
return fk.column.name
File "/home/thomas/dev/python/sqlalchemy_test/.env/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py", line 883, in __get__
obj.__dict__[self.__name__] = result = self.fget(obj)
File "/home/thomas/dev/python/sqlalchemy_test/.env/lib/python3.8/site-packages/sqlalchemy/sql/schema.py", line 2057, in column
raise exc.NoReferencedColumnError(
sqlalchemy.exc.NoReferencedColumnError: Could not initialize target column for ForeignKey 'parent.id' on table 'child': table 'parent' has no column named 'id'

After some investigating, I realized the the naming convention was applied before the ForeignKey had been associated to its referenced Column so, as a test, I made this rather crude modification to delay the _set_parent_with_dispatch after the Column had been associated. It works both on 1.3.16 and master, but my knowledge of SQLAlchemy's internals is not good, and the fix seems to delay a huge chunk of logic (a lot more than just applying the naming convention). I'm posting it here in case it can help in finding an actual fix.

diff --git a/lib/sqlalchemy/sql/schema.py b/lib/sqlalchemy/sql/schema.py
index 689eda11d..4245a7f45 100644
--- a/lib/sqlalchemy/sql/schema.py
+++ b/lib/sqlalchemy/sql/schema.py
@@ -2088,6 +2088,7 @@ class ForeignKey(DialectKWArgs, SchemaItem):
# standalone ForeignKey - create ForeignKeyConstraint
# on the hosting Table when attached to the Table.
assert isinstance(table, Table)
+ created = False
if self.constraint is None:
self.constraint = ForeignKeyConstraint(
[],
@@ -2102,7 +2103,8 @@ class ForeignKey(DialectKWArgs, SchemaItem):
**self._unvalidated_dialect_kw
)
self.constraint._append_element(column, self)
- self.constraint._set_parent_with_dispatch(table)
+ created = True
+
table.foreign_keys.add(self)
# set up remote ".column" attribute, or a note to pick it
# up when the other Table/Column shows up
@@ -2124,6 +2126,8 @@ class ForeignKey(DialectKWArgs, SchemaItem):
_column = self._colspec
self._set_target_column(_column)

+ if created:
+ self.constraint._set_parent_with_dispatch(table)

class DefaultGenerator(SchemaItem):
"""Base class for column *default* values."""

Otherwise, a least-side-effect-possible solution would be to detect the missing column inside of sqlalchemy.sql.naming.ConventionDict._key_referred_column_X_name, and raise a clearer error. Something like this (pseudo-code, just to illustrate):

def _key_referred_column_X_name(self, idx):
fk = self.const.elements[idx]
try:
return fk.column.name
except NoReferencedColumnError as err:
raise NamingConventionError(
'Token column_0_name cannot be used with ForeignKey(str)'
) from err

Versions

OS: Manjaro Linux

Python: 3.8.2

SQLAlchemy: 1.3.16 + master (both for the bug and the ugly fix)

Database: None

DBAPI: None