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

right now session.__binds uses mappers completely. This makes it impossible to feed in abstract classes and also because it links to base_mapper it's not possible to change the bind along a class hierarchy either (like a concrete hierarchy).

propose that __binds stores classes directly, and this can likely be a fallback in addition to what we have now. this is kind of a proof of concept

diff --git a/lib/sqlalchemy/orm/session.py b/lib/sqlalchemy/orm/session.py
index 89d9946..f63adfb 100644
--- a/lib/sqlalchemy/orm/session.py
+++ b/lib/sqlalchemy/orm/session.py
@@ -636,13 +636,20 @@ class Session(_SessionClassMethods):

if binds is not None:
for mapperortable, bind in binds.items():
- insp = inspect(mapperortable)
- if insp.is_selectable:
- self.bind_table(mapperortable, bind)
- elif insp.is_mapper:
- self.bind_mapper(mapperortable, bind)
+ try:
+ insp = inspect(mapperortable)
+ except exc.NoInspectionAvailable:
+ if isinstance(mapperortable, type):
+ self.bind_cls(mapperortable, bind)
+ else:
+ raise exc.ArgumentError("Not acceptable bind target: %s" % mapperortable)
else:
- assert False
+ if insp.is_selectable:
+ self.bind_table(mapperortable, bind)
+ elif insp.is_mapper:
+ self.bind_mapper(mapperortable, bind)
+ else:
+ assert False

if not self.autocommit:
@@ -1039,6 +1046,17 @@ class Session(_SessionClassMethods):
for t in mapper._all_tables:
self.__binds[t] = bind

+ def bind_class(self, cls, bind):
+ """Bind operations for an unmapped class to a Connectable.
+
+ The behavior here is that a subclass of cls which is mapped
+ will be linked to this bind.
+
+ .. versionadded:: 1.0
+
+ """
+ self.__binds[cls] = bind
+
def bind_table(self, table, bind):
"""Bind operations on a Table to a Connectable.

@@ -1124,6 +1142,12 @@ class Session(_SessionClassMethods):
return self.__binds[c_mapper.base_mapper]
elif c_mapper.mapped_table in self.__binds:
return self.__binds[c_mapper.mapped_table]
+ else:
+ for cls in c_mapper.class_.__mro__:
+ if cls in self.__binds:
+ bind = self.__binds[cls]
+ self.bind_mapper(c_mapper, bind)
+ return bind
if clause is not None:
for t in sql_util.find_tables(clause, include_crud=True):
if t in self.__binds:

the tests here also need a rework, with an emphasis on straight unit testing of get_bind(). test_session -> BindTest is really just integration tests.

Issue 2:
Describe the bug

I am passing a very simple SQL query to a server via Python's SQLAlchemy library. Here is my code:

def query4():
ed_notes = sa.Table("ED_NOTES_MASTER",metadata,autoload=True,autoload_with=engine)
note_query = sa.select([ed_notes.columns["PT_FIN"],
ed_notes.columns["RESULT_TITLE_TEXT"],
ed_notes.columns["RESULT"],
ed_notes.columns["RESULT_DT_TM"]]).where(ed_notes.columns["PT_FIN"].in_(unique_fins)).where(start_time<ed_notes.columns["RESULT_DT_TM"]).where(end_time>ed_notes.columns["RESULT_DT_TM"])
result = connection.execute(note_query)
resultset = result.all()
note_data_prelim = pd.DataFrame(resultset)
return note_data_prelim

the variable "unique_fins" is a list of over 50,000 unique identifiers that I am trying to specifically query. When this query is run, the following error results:

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('The SQL contains -11872 parameter markers, but 53664 parameters were supplied', 'HY000')

Any ideas on what is going on? The problem is definitely arising in the ed_notes.columns["PT_FIN"].in_(unique_fins) portion of the query.

Thanks in advance!

To Reproduce

unique_fins = low_triage_only["PT_FIN"].unique().tolist()

def query4():
ed_notes = sa.Table("ED_NOTES_MASTER",metadata,autoload=True,autoload_with=engine)
note_query = sa.select([ed_notes.columns["PT_FIN"],
ed_notes.columns["RESULT_TITLE_TEXT"],
ed_notes.columns["RESULT"],
ed_notes.columns["RESULT_DT_TM"]]).where(ed_notes.columns["PT_FIN"].in_(unique_fins)).where(start_time<ed_notes.columns["RESULT_DT_TM"]).where(end_time>ed_notes.columns["RESULT_DT_TM"])
result = connection.execute(note_query)
resultset = result.fetchall()
return resultset

Error

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('The SQL contains -11877 parameter markers, but 53659 parameters were supplied', 'HY000')

Versions

OS: Windows 10

Python: 3.8.12

SQLAlchemy: 1.4.27

Database: Microsoft SQL Server

DBAPI (eg: psycopg, cx_oracle, mysqlclient): Microsoft ODBC 17 Driver

Additional context

No response