Issue 1:
Migrated issue, originally created by Michael Bayer (@zzzeek)

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class A(Base):
__tablename__ = 'a'
id1 = Column(Integer, primary_key=True)
id2 = Column(Integer, primary_key=True)

class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
a_id1 = Column(Integer, ForeignKey('a.id1'))

a = relationship(
"A", uselist=False,
primaryjoin=and_(
a_id1 == A.id1,
A.id2 == bindparam("my_bindparam", callable_=lambda: 2)))

configure_mappers()

assert not B.a.property.strategy.use_get

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

s = Session(e)
s.add(A(id1=1, id2=1))
s.add(A(id1=1, id2=2))
s.add(B(a_id1=1))
s.commit()

b1 = s.query(B).first()
print b1.a

first, use_get is improperly detected. We would need to compare for something like the "callable" as well (we don't seem to be able to compare for the "key", not sure why):

diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index e277b28..1d19707 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -1142,7 +1142,8 @@ class BindParameter(ColumnElement):

return isinstance(other, BindParameter) \
and self.type._compare_type_affinity(other.type) \
- and self.value == other.value
+ and self.value == other.value \
+ and self.callable == other.callable

def __getstate__(self):
"""execute a deferred value for serialization purposes."""

next, the visitation in _memoized_attr__simple_lazy_clause is picking up on this bind as one of their own. ideally we'd use annotations to get around this but then we need to get rid of that deep_deannotate, this seems overall more risky:

diff --git a/lib/sqlalchemy/orm/relationships.py b/lib/sqlalchemy/orm/relationships.py
index e8a2992..e16c52d 100644
--- a/lib/sqlalchemy/orm/relationships.py
+++ b/lib/sqlalchemy/orm/relationships.py
@@ -2827,7 +2827,8 @@ class JoinCondition(object):
):
if col not in binds:
binds[col] = sql.bindparam(
- None, None, type_=col.type, unique=True)
+ None, None, type_=col.type, unique=True).\
+ _annotate({"lazyloader": True})
return binds[col]
return None

@@ -2845,9 +2846,6 @@ class JoinCondition(object):

bind_to_col = dict((binds[col].key, col) for col in binds)

- # this is probably not necessary
- lazywhere = _deep_deannotate(lazywhere)
-
return lazywhere, bind_to_col, equated_columns

diff --git a/lib/sqlalchemy/orm/strategies.py b/lib/sqlalchemy/orm/strategies.py
index 8260732..f5bf041 100644
--- a/lib/sqlalchemy/orm/strategies.py
+++ b/lib/sqlalchemy/orm/strategies.py
@@ -473,6 +473,9 @@ class LazyLoader(AbstractRelationshipLoader, util.MemoizedSlots):
params = []

def visit_bindparam(bindparam):
+ if not bindparam._annotations.get("lazyloader"):
+ return
+
bindparam.unique = False
if bindparam._identifying_key in bind_to_col:
params.append((

Issue 2:
Describe the bug

Hello,

I posted this to stack overflow to see if anyone had a solution but no one seems to have a solution. Essentially, I want to make a SQLAlchemy class match a Pydanic class including its relationships then post it to the database. However, if there are unique constraints in the "many" table I get consistently get a violation of the unique constraints.

To Reproduce

The stack overflow post contains all of the SQLAlchemy code necessary to reproduce the issue. Below is how I constructed the tables in Microsoft SQL Server using SSM:

CREATE TABLE dbo.organization(
org_id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
org_name NVARCHAR(255)
)

CREATE TABLE dbo.alt_org_name(
alt_org_name_id UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
org_id UNIQUEIDENTIFIER FOREIGN KEY REFERENCES dbo.organization(org_id),
alt_org_name NVARCHAR(255),
partner NVARCHAR(255),
UNIQUE(org_id, alt_org_name, partner)
)

Error

IntegrityError Traceback (most recent call last)
~\anaconda3\envs\or\lib\site-packages\sqlalchemy\engine\base.py in _execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
1899 if not evt_handled:
-> 1900 self.dialect.do_execute(
1901 cursor, statement, parameters, context

~\anaconda3\envs\or\lib\site-packages\sqlalchemy\engine\default.py in do_execute(self, cursor, statement, parameters, context)
735 def do_execute(self, cursor, statement, parameters, context=None):
--> 736 cursor.execute(statement, parameters)
737

IntegrityError: ('23000', "[23000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Violation of UNIQUE KEY constraint 'UQ__alt_org___89413C3E1298F58D'. Cannot insert duplicate key in object 'dbo.alt_org_name'. The duplicate key value is (ab6ca7c5-b969-4e6a-97fa-eba4a9b1e7f6, MSFT, New York Stock Exchange). (2627) (SQLExecDirectW); [23000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The statement has been terminated. (3621)")

The above exception was the direct cause of the following exception:

IntegrityError Traceback (most recent call last)
C:\Users\ERIKOS~1\AppData\Local\Temp/ipykernel_18652/2240032650.py in <module>
4 o1.alt_names.append(AltOrgName(alt_org_name="MSFT", partner="New York Stock Exchange"))
5 o1.alt_names.append(AltOrgName(alt_org_name="Micro-Soft", partner=None))
----> 6 db.commit()

~\anaconda3\envs\or\lib\site-packages\sqlalchemy\orm\session.py in commit(self)
1449 raise sa_exc.InvalidRequestError("No transaction is begun.")
1450
-> 1451 self._transaction.commit(_to_root=self.future)
1452
1453 def prepare(self):

~\anaconda3\envs\or\lib\site-packages\sqlalchemy\orm\session.py in commit(self, _to_root)
827 self._assert_active(prepared_ok=True)
828 if self._state is not PREPARED:
--> 829 self._prepare_impl()
830
831 if self._parent is None or self.nested:

~\anaconda3\envs\or\lib\site-packages\sqlalchemy\orm\session.py in _prepare_impl(self)
806 if self.session._is_clean():
807 break
--> 808 self.session.flush()
809 else:
810 raise exc.FlushError(

~\anaconda3\envs\or\lib\site-packages\sqlalchemy\orm\session.py in flush(self, objects)
3384 try:
3385 self._flushing = True
-> 3386 self._flush(objects)
3387 finally:
3388 self._flushing = False

~\anaconda3\envs\or\lib\site-packages\sqlalchemy\orm\session.py in _flush(self, objects)
3524 except:
3525 with util.safe_reraise():
-> 3526 transaction.rollback(_capture_exception=True)
3527
3528 def bulk_save_objects(

~\anaconda3\envs\or\lib\site-packages\sqlalchemy\util\langhelpers.py in __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,

~\anaconda3\envs\or\lib\site-packages\sqlalchemy\util\compat.py in raise_(***failed resolving arguments***)
206
207 try:
--> 208 raise exception
209 finally:
210 # credit to

~\anaconda3\envs\or\lib\site-packages\sqlalchemy\orm\session.py in _flush(self, objects)
3484 self._warn_on_events = True
3485 try:
-> 3486 flush_context.execute()
3487 finally:
3488 self._warn_on_events = False

~\anaconda3\envs\or\lib\site-packages\sqlalchemy\orm\unitofwork.py in execute(self)
454 else:
455 for rec in topological.sort(self.dependencies, postsort_actions):
--> 456 rec.execute(self)
457
458 def finalize_flush_changes(self):

~\anaconda3\envs\or\lib\site-packages\sqlalchemy\orm\unitofwork.py in execute(self, uow)
628 @util.preload_module("sqlalchemy.orm.persistence")
629 def execute(self, uow):
--> 630 util.preloaded.orm_persistence.save_obj(
631 self.mapper,
632 uow.states_for_mapper_hierarchy(self.mapper, False, False),

~\anaconda3\envs\or\lib\site-packages\sqlalchemy\orm\persistence.py in save_obj(base_mapper, states, uowtransaction, single)
243 )
244
--> 245 _emit_insert_statements(
246 base_mapper,
247 uowtransaction,

~\anaconda3\envs\or\lib\site-packages\sqlalchemy\orm\persistence.py in _emit_insert_statements(base_mapper, uowtransaction, mapper, table, insert, bookkeeping)
1236 )
1237 else:
-> 1238 result = connection._execute_20(
1239 statement,
1240 params,

~\anaconda3\envs\or\lib\site-packages\sqlalchemy\engine\base.py in _execute_20(self, statement, parameters, execution_options)
1703 )
1704 else:
-> 1705 return meth(self, args_10style, kwargs_10style, execution_options)
1706
1707 def exec_driver_sql(

~\anaconda3\envs\or\lib\site-packages\sqlalchemy\sql\elements.py in _execute_on_connection(self, connection, multiparams, params, execution_options, _force)
331 ):
332 if _force or self.supports_execution:
--> 333 return connection._execute_clauseelement(
334 self, multiparams, params, execution_options
335 )

~\anaconda3\envs\or\lib\site-packages\sqlalchemy\engine\base.py in _execute_clauseelement(self, elem, multiparams, params, execution_options)
1570 linting=self.dialect.compiler_linting | compiler.WARN_LINTING,
1571 )
-> 1572 ret = self._execute_context(
1573 dialect,
1574 dialect.execution_ctx_cls._init_compiled,

~\anaconda3\envs\or\lib\site-packages\sqlalchemy\engine\base.py in _execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
1941
1942 except BaseException as e:
-> 1943 self._handle_dbapi_exception(
1944 e, statement, parameters, cursor, context
1945 )

~\anaconda3\envs\or\lib\site-packages\sqlalchemy\engine\base.py in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
2122 util.raise_(newraise, with_traceback=exc_info[2], from_=e)
2123 elif should_wrap:
-> 2124 util.raise_(
2125 sqlalchemy_exception, with_traceback=exc_info[2], from_=e
2126 )

~\anaconda3\envs\or\lib\site-packages\sqlalchemy\util\compat.py in raise_(***failed resolving arguments***)
206
207 try:
--> 208 raise exception
209 finally:
210 # credit to

~\anaconda3\envs\or\lib\site-packages\sqlalchemy\engine\base.py in _execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
1898 break
1899 if not evt_handled:
-> 1900 self.dialect.do_execute(
1901 cursor, statement, parameters, context
1902 )

~\anaconda3\envs\or\lib\site-packages\sqlalchemy\engine\default.py in do_execute(self, cursor, statement, parameters, context)
734
735 def do_execute(self, cursor, statement, parameters, context=None):
--> 736 cursor.execute(statement, parameters)
737
738 def do_execute_no_params(self, cursor, statement, context=None):

IntegrityError: (pyodbc.IntegrityError) ('23000', "[23000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Violation of UNIQUE KEY constraint 'UQ__alt_org___89413C3E1298F58D'. Cannot insert duplicate key in object 'dbo.alt_org_name'. The duplicate key value is (ab6ca7c5-b969-4e6a-97fa-eba4a9b1e7f6, MSFT, New York Stock Exchange). (2627) (SQLExecDirectW); [23000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The statement has been terminated. (3621)")
[SQL: INSERT INTO dbo.alt_org_name (alt_org_name_id, org_id, alt_org_name, partner) VALUES (?, ?, ?, ?)]
[parameters: ('9f6fbfeb-aece-4dc5-a4a4-789426ed4e60', 'ab6ca7c5-b969-4e6a-97fa-eba4a9b1e7f6', 'MSFT', 'New York Stock Exchange')]
(Background on this error at: https://sqlalche.me/e/14/gkpj)

Versions

OS: Windows

Python: 3.9

SQLAlchemy: 1.4.41

Database: SQL Server

DBAPI (eg: psycopg, cx_oracle, mysqlclient):

Additional context

No response