Issue 1:
Migrated issue, originally created by chrismacklin (@chrismacklin)

When using the association object pattern with a table with columns besides the two bridging foreign keys, the docs only gently state that association_proxy is needed. I think this needs to be called out much more loudly and clearly in the docs, as using the secondary kwarg with an association table with extra mapped columns can cause silent corruption of the result set. As it stands, there's just one sentence in the docs that mentions in an almost offhand fashion that if there are extra columns, you need to use association_proxy:

http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#association-object

"The association object pattern is a variant on many-to-many: it’s used when your association table contains additional columns beyond those which are foreign keys to the left and right tables."

This makes it sounds like it provides something like a nice convenience, rather than being strictly required.

It would be even better if the secondary kwarg could perform some validation and refuse to map an association table that doesn't meet the requirement that the only columns on the table should be the FKs participating in the association.

In my case, I had an association table with an integer primary key ID and a pair of FKs forming the association (I'll call them foo_id and bar_id), associating zero-to-many instances of Bar with a single Foo, allowing duplicate association of the same Bar multiple times to a single Foo. Suppose I have a single instance of Foo (my_foo), with two rows in the association table to a single instance of Bar (my_bar).

If my_foo.bars is declared with association_proxy, this holds:

assert my_foo.bars == [my_bar, my_bar]

If my_foo.bars is declared using relationship with secondary, I end up with the corrupt result set:

assert my_foo.bars == [my_bar]

No warning or error is emitted when creating this mapping.

This seems like a pretty big footgun to me, and I think the docs need to be MUCH more clear on this.

This was tested on Postgres 9.3

I can produce a minimal example of this issue if it is needed.

Issue 2:
Describe the bug

Hello,

By following the tutorial, when I execute

https://docs.sqlalchemy.org/en/14/tutorial/data_select.html#:~:text=row%20%3D%20session.execute(select(User)).first(),

I got the following error:

---------------------------------------------------------------------------
InvalidRequestError Traceback (most recent call last)
(see details below)
InvalidRequestError: Multiple classes found for path "Address" in the registry of this declarative base. Please use a fully module-qualified path.

and I found that the reason is in

https://docs.sqlalchemy.org/en/14/tutorial/metadata.html#combining-core-table-declarations-with-orm-declarative

was duplicate with the code in

https://docs.sqlalchemy.org/en/14/tutorial/metadata.html#:~:text=self.fullname!r%7D)%22%0A%0A%3E%3E%3E-,class%20Address(Base)%3A,-...%20%20%20%20%20__tablename__%20%3D%20%27address%27%0A...%0A...%20%20%20%20%20id

how do I fix this? should the tutorial be modified too? (e.g. add a warning)

To Reproduce

# %%
from sqlalchemy.orm import registry

mapper_registry = registry()
Base = mapper_registry.generate_base()

from sqlalchemy import select
from sqlalchemy import Table, Column, Integer, String
from sqlalchemy import ForeignKey

from sqlalchemy.orm import relationship
from sqlalchemy.orm import Session

from sqlalchemy import create_engine

engine = create_engine("sqlite+pysqlite:///:memory:", echo=True, future=True)

class User(Base):
__tablename__ = "user_account"
id = Column(Integer, primary_key=True)
name = Column(String(30))
fullname = Column(String)
addresses = relationship("Address", back_populates="user")

def __repr__(self):
return (
f"User(id={self.id!r},"
f" name={self.name!r},"
f" fullname={self.fullname!r})"
)

class Address(Base):
__tablename__ = "address"
id = Column(Integer, primary_key=True)
email_address = Column(String, nullable=False)
user_id = Column(Integer, ForeignKey("user_account.id"))
user = relationship("User", back_populates="addresses")

def __repr__(self):
return f"Address(id={self.id!r}," f" email_address=" f"{self.email_address!r})"

# %%
mapper_registry.metadata.create_all(engine)

# %%
from sqlalchemy import MetaData

metadata_obj = MetaData()

user_table = Table(
"user_account",
metadata_obj,
Column("id", Integer, primary_key=True),
Column("name", String(30)),
Column("fullname", String),
)

address_table = Table(
"address",
metadata_obj,
Column("id", Integer, primary_key=True),
Column("user_id", ForeignKey("user_account.id"), nullable=False),
Column("email_address", String, nullable=False),
)
metadata_obj.create_all(engine)

# %%
from sqlalchemy import insert
stmt = insert(user_table).\
values(name='spongebob',
fullname="Spongebob Squarepants")

# %%
from sqlalchemy import select, bindparam

scalar_subq = (
select(user_table.c.id)
.where(user_table.c.name == bindparam("username"))
.scalar_subquery()
)

with engine.connect() as conn:
result = conn.execute(
insert(address_table).values(user_id=scalar_subq),
[
{"username": "spongebob", "email_address": "spongebob@sqlalchemy.org"},
{"username": "sandy", "email_address": "sandy@sqlalchemy.org"},
{"username": "sandy", "email_address": "sandy@squirrelpower.org"},
],
)
conn.commit()

# %%
stmt = select(User).where(User.name == 'spongebob')
with Session(engine) as session:
for row in session.execute(stmt):
print(row)

# %%
class User(Base):
__table__ = user_table

addresses = relationship("Address", back_populates="user")

def __repr__(self):
return f"User({self.name!r}, {self.fullname!r})"

class Address(Base):
__table__ = address_table

user = relationship("User", back_populates="addresses")

def __repr__(self):
return f"Address({self.email_address!r})"

# %%
stmt = select(User).where(User.name == 'spongebob')
with Session(engine) as session:
for row in session.execute(stmt):
print(row)

Error

---------------------------------------------------------------------------
InvalidRequestError Traceback (most recent call last)
c:\Users\18463\Documents\GitHub\learn-orm\bug-report.ipynb Cell 9' in <cell line: 2>()
1 stmt = select(User).where(User.name == 'spongebob')
2 with Session(engine) as session:
----> 3 for row in session.execute(stmt):
4 print(row)

File C:\ProgramData\scoop\apps\python\3.10.4\lib\site-packages\sqlalchemy\orm\session.py:1692, in Session.execute(self, statement, params, execution_options, bind_arguments, _parent_execute_state, _add_event, **kw)
1690 else:
1691 conn = self._connection_for_bind(bind)
-> 1692 result = conn._execute_20(statement, params or {}, execution_options)
1694 if compile_state_cls:
1695 result = compile_state_cls.orm_setup_cursor_result(
1696 self,
1697 statement,
(...)
1701 result,
1702 )

File C:\ProgramData\scoop\apps\python\3.10.4\lib\site-packages\sqlalchemy\engine\base.py:1620, in Connection._execute_20(self, statement, parameters, execution_options)
1616 util.raise_(
1617 exc.ObjectNotExecutableError(statement), replace_context=err
1618 )
1619 else:
-> 1620 return meth(self, args_10style, kwargs_10style, execution_options)

File C:\ProgramData\scoop\apps\python\3.10.4\lib\site-packages\sqlalchemy\sql\elements.py:325, in ClauseElement._execute_on_connection(self, connection, multiparams, params, execution_options, _force)
321 def _execute_on_connection(
322 self, connection, multiparams, params, execution_options, _force=False
323 ):
324 if _force or self.supports_execution:
--> 325 return connection._execute_clauseelement(
326 self, multiparams, params, execution_options
327 )
328 else:
329 raise exc.ObjectNotExecutableError(self)

File C:\ProgramData\scoop\apps\python\3.10.4\lib\site-packages\sqlalchemy\engine\base.py:1479, in Connection._execute_clauseelement(self, elem, multiparams, params, execution_options)
1471 schema_translate_map = execution_options.get(
1472 "schema_translate_map", None
1473 )
1475 compiled_cache = execution_options.get(
1476 "compiled_cache", self.engine._compiled_cache
1477 )
-> 1479 compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
1480 dialect=dialect,
1481 compiled_cache=compiled_cache,
1482 column_keys=keys,
1483 for_executemany=for_executemany,
1484 schema_translate_map=schema_translate_map,
1485 linting=self.dialect.compiler_linting | compiler.WARN_LINTING,
1486 )
1487 ret = self._execute_context(
1488 dialect,
1489 dialect.execution_ctx_cls._init_compiled,
(...)
1497 cache_hit=cache_hit,
1498 )
1499 if has_events:

File C:\ProgramData\scoop\apps\python\3.10.4\lib\site-packages\sqlalchemy\sql\elements.py:523, in ClauseElement._compile_w_cache(self, dialect, compiled_cache, column_keys, for_executemany, schema_translate_map, **kw)
521 if compiled_sql is None:
522 cache_hit = dialect.CACHE_MISS
--> 523 compiled_sql = self._compiler(
524 dialect,
525 cache_key=elem_cache_key,
526 column_keys=column_keys,
527 for_executemany=for_executemany,
528 schema_translate_map=schema_translate_map,
529 **kw
530 )
531 compiled_cache[key] = compiled_sql
532 else:

File C:\ProgramData\scoop\apps\python\3.10.4\lib\site-packages\sqlalchemy\sql\elements.py:558, in ClauseElement._compiler(self, dialect, **kw)
554 def _compiler(self, dialect, **kw):
555 """Return a compiler appropriate for this ClauseElement, given a
556 Dialect."""
--> 558 return dialect.statement_compiler(dialect, self, **kw)

File C:\ProgramData\scoop\apps\python\3.10.4\lib\site-packages\sqlalchemy\sql\compiler.py:776, in SQLCompiler.__init__(self, dialect, statement, cache_key, column_keys, for_executemany, linting, **kwargs)
772 # a map which tracks "truncated" names based on
773 # dialect.label_length or dialect.max_identifier_length
774 self.truncated_names = {}
--> 776 Compiled.__init__(self, dialect, statement, **kwargs)
778 if self.isinsert or self.isupdate or self.isdelete:
779 if statement._returning:

File C:\ProgramData\scoop\apps\python\3.10.4\lib\site-packages\sqlalchemy\sql\compiler.py:451, in Compiled.__init__(self, dialect, statement, schema_translate_map, render_schema_translate, compile_kwargs)
449 if self.can_execute:
450 self.execution_options = statement._execution_options
--> 451 self.string = self.process(self.statement, **compile_kwargs)
453 if render_schema_translate:
454 self.string = self.preparer._render_schema_translates(
455 self.string, schema_translate_map
456 )

File C:\ProgramData\scoop\apps\python\3.10.4\lib\site-packages\sqlalchemy\sql\compiler.py:486, in Compiled.process(self, obj, **kwargs)
485 def process(self, obj, **kwargs):
--> 486 return obj._compiler_dispatch(self, **kwargs)

File C:\ProgramData\scoop\apps\python\3.10.4\lib\site-packages\sqlalchemy\sql\visitors.py:82, in _generate_compiler_dispatch.<locals>._compiler_dispatch(self, visitor, **kw)
79 return visitor.visit_unsupported_compilation(self, err, **kw)
81 else:
---> 82 return meth(self, **kw)

File C:\ProgramData\scoop\apps\python\3.10.4\lib\site-packages\sqlalchemy\sql\compiler.py:3261, in SQLCompiler.visit_select(self, select_stmt, asfrom, insert_into, fromhints, compound_index, select_wraps_for, lateral, from_linter, **kwargs)
3253 # initial setup of SELECT. the compile_state_factory may now
3254 # be creating a totally different SELECT from the one that was
3255 # passed in. for ORM use this will convert from an ORM-state
3256 # SELECT to a regular "Core" SELECT. other composed operations
3257 # such as computation of joins will be performed.
3259 kwargs["within_columns_clause"] = False
-> 3261 compile_state = select_stmt._compile_state_factory(
3262 select_stmt, self, **kwargs
3263 )
3264 select_stmt = compile_state.statement
3266 toplevel = not self.stack

File C:\ProgramData\scoop\apps\python\3.10.4\lib\site-packages\sqlalchemy\sql\base.py:510, in CompileState.create_for_statement(cls, statement, compiler, **kw)
508 return cls(statement, compiler, **kw)
509 else:
--> 510 return klass.create_for_statement(statement, compiler, **kw)

File C:\ProgramData\scoop\apps\python\3.10.4\lib\site-packages\sqlalchemy\orm\context.py:696, in ORMSelectCompileState.create_for_statement(cls, statement, compiler, **kw)
683 if select_statement._memoized_select_entities:
684 self._memoized_entities = {
685 memoized_entities: _QueryEntity.to_compile_state(
686 self,
(...)
693 )
694 }
--> 696 _QueryEntity.to_compile_state(
697 self,
698 select_statement._raw_columns,
699 self._entities,
700 is_current_entities=True,
701 )
703 self.current_path = select_statement._compile_options._current_path
705 self.eager_order_by = ()

File C:\ProgramData\scoop\apps\python\3.10.4\lib\site-packages\sqlalchemy\orm\context.py:2432, in _QueryEntity.to_compile_state(cls, compile_state, entities, entities_collection, is_current_entities)
2430 if entity.is_selectable:
2431 if "parententity" in entity._annotations:
-> 2432 _MapperEntity(
2433 compile_state,
2434 entity,
2435 entities_collection,
2436 is_current_entities,
2437 )
2438 else:
2439 _ColumnEntity._for_columns(
2440 compile_state,
2441 entity._select_iterable,
2442 entities_collection,
2443 idx,
2444 )

File C:\ProgramData\scoop\apps\python\3.10.4\lib\site-packages\sqlalchemy\orm\context.py:2496, in _MapperEntity.__init__(self, compile_state, entity, entities_collection, is_current_entities)
2493 compile_state._has_orm_entities = True
2495 entity = entity._annotations["parententity"]
-> 2496 entity._post_inspect
2497 ext_info = self.entity_zero = entity
2498 entity = ext_info.entity

File C:\ProgramData\scoop\apps\python\3.10.4\lib\site-packages\sqlalchemy\util\langhelpers.py:1180, in HasMemoized.memoized_attribute.__get__(self, obj, cls)
1178 if obj is None:
1179 return self
-> 1180 obj.__dict__[self.__name__] = result = self.fget(obj)
1181 obj._memoized_keys |= {self.__name__}
1182 return result

File C:\ProgramData\scoop\apps\python\3.10.4\lib\site-packages\sqlalchemy\orm\mapper.py:2123, in Mapper._post_inspect(self)
2112 @HasMemoized.memoized_attribute
2113 def _post_inspect(self):
2114 """This hook is invoked by attribute inspection.
2115
2116 E.g. when Query calls:
(...)
2121
2122 """
-> 2123 self._check_configure()

File C:\ProgramData\scoop\apps\python\3.10.4\lib\site-packages\sqlalchemy\orm\mapper.py:1900, in Mapper._check_configure(self)
1898 def _check_configure(self):
1899 if self.registry._new_mappers:
-> 1900 _configure_registries({self.registry}, cascade=True)

File C:\ProgramData\scoop\apps\python\3.10.4\lib\site-packages\sqlalchemy\orm\mapper.py:3418, in _configure_registries(registries, cascade)
3412 Mapper.dispatch._for_class(Mapper).before_configured()
3413 # initialize properties on all mappers
3414 # note that _mapper_registry is unordered, which
3415 # may randomly conceal/reveal issues related to
3416 # the order of mapper compilation
-> 3418 _do_configure_registries(registries, cascade)
3419 finally:
3420 _already_compiling = False

File C:\ProgramData\scoop\apps\python\3.10.4\lib\site-packages\sqlalchemy\orm\mapper.py:3457, in _do_configure_registries(registries, cascade)
3455 if not mapper.configured:
3456 try:
-> 3457 mapper._post_configure_properties()
3458 mapper._expire_memoizations()
3459 mapper.dispatch.mapper_configured(mapper, mapper.class_)

File C:\ProgramData\scoop\apps\python\3.10.4\lib\site-packages\sqlalchemy\orm\mapper.py:1917, in Mapper._post_configure_properties(self)
1914 self._log("initialize prop %s", key)
1916 if prop.parent is self and not prop._configure_started:
-> 1917 prop.init()
1919 if prop._configure_finished:
1920 prop.post_instrument_class(self)

File C:\ProgramData\scoop\apps\python\3.10.4\lib\site-packages\sqlalchemy\orm\interfaces.py:231, in MapperProperty.init(self)
224 """Called after all mappers are created to assemble
225 relationships between mappers and perform other post-mapper-creation
226 initialization steps.
227
228
229 """
230 self._configure_started = True
--> 231 self.do_init()
232 self._configure_finished = True

File C:\ProgramData\scoop\apps\python\3.10.4\lib\site-packages\sqlalchemy\orm\relationships.py:2150, in RelationshipProperty.do_init(self)
2148 def do_init(self):
2149 self._check_conflicts()
-> 2150 self._process_dependent_arguments()
2151 self._setup_registry_dependencies()
2152 self._setup_join_conditions()

File C:\ProgramData\scoop\apps\python\3.10.4\lib\site-packages\sqlalchemy\orm\relationships.py:2245, in RelationshipProperty._process_dependent_arguments(self)
2231 self._user_defined_foreign_keys = util.column_set(
2232 coercions.expect(
2233 roles.ColumnArgumentRole, x, argname="foreign_keys"
2234 )
2235 for x in util.to_column_set(self._user_defined_foreign_keys)
2236 )
2238 self.remote_side = util.column_set(
2239 coercions.expect(
2240 roles.ColumnArgumentRole, x, argname="remote_side"
2241 )
2242 for x in util.to_column_set(self.remote_side)
2243 )
-> 2245 self.target = self.entity.persist_selectable

File C:\ProgramData\scoop\apps\python\3.10.4\lib\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 C:\ProgramData\scoop\apps\python\3.10.4\lib\site-packages\sqlalchemy\orm\relationships.py:2112, in RelationshipProperty.entity(self)
2109 mapperlib = util.preloaded.orm_mapper
2111 if isinstance(self.argument, util.string_types):
-> 2112 argument = self._clsregistry_resolve_name(self.argument)()
2114 elif callable(self.argument) and not isinstance(
2115 self.argument, (type, mapperlib.Mapper)
2116 ):
2117 argument = self.argument()

File C:\ProgramData\scoop\apps\python\3.10.4\lib\site-packages\sqlalchemy\orm\clsregistry.py:393, in _class_resolver._resolve_name(self)
391 for token in name.split("."):
392 if rval is None:
--> 393 rval = d[token]
394 else:
395 rval = getattr(rval, token)

File C:\ProgramData\scoop\apps\python\3.10.4\lib\site-packages\sqlalchemy\util\_collections.py:746, in PopulateDict.__missing__(self, key)
745 def __missing__(self, key):
--> 746 self[key] = val = self.creator(key)
747 return val

File C:\ProgramData\scoop\apps\python\3.10.4\lib\site-packages\sqlalchemy\orm\clsregistry.py:352, in _class_resolver._access_cls(self, key)
349 return _GetTable(key, cls.metadata)
351 if key in decl_class_registry:
--> 352 return _determine_container(key, decl_class_registry[key])
354 if not self.favor_tables:
355 if key in metadata.tables:

File C:\ProgramData\scoop\apps\python\3.10.4\lib\site-packages\sqlalchemy\orm\clsregistry.py:313, in _determine_container(key, value)
311 def _determine_container(key, value):
312 if isinstance(value, _MultipleClassMarker):
--> 313 value = value.attempt_get([], key)
314 return _GetColumns(value)

File C:\ProgramData\scoop\apps\python\3.10.4\lib\site-packages\sqlalchemy\orm\clsregistry.py:144, in _MultipleClassMarker.attempt_get(self, path, key)
142 def attempt_get(self, path, key):
143 if len(self.contents) > 1:
--> 144 raise exc.InvalidRequestError(
145 'Multiple classes found for path "%s" '
146 "in the registry of this declarative "
147 "base. Please use a fully module-qualified path."
148 % (".".join(path + [key]))
149 )
150 else:
151 ref = list(self.contents)[0]

InvalidRequestError: Multiple classes found for path "Address" in the registry of this declarative base. Please use a fully module-qualified path.

Versions

OS: Windows 11

Python: 3.10.4

SQLAlchemy: 1.4.32

Database: sqlite3 (memory)

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

Additional context

No response