Repository: djrobstep/sqlakeyset (python)

Issue:
Breaks on sqlalchemy & flask-sqlalchemy on a simple model
See example code (python 3.11, sqlalchemy 2.0.20, flask-sqlalchemy 3.0.5, sqlakeyset 2.0.1691149549):

```python
import time

import gevent
import sqlalchemy as sa
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlakeyset import get_page, select_page

app = Flask(__name__)
# configure the SQLite database, relative to the app instance folder
app.config["SQLALCHEMY_DATABASE_URI"] = "postgresql+psycopg://postgres:1@127.0.0.1:5432/postgres"
# initialize the app with the extension
db = SQLAlchemy(app)

from sqlalchemy import Integer
from sqlalchemy.orm import Mapped, mapped_column


class User2(db.Model):
    __tablename__ = "user2"
    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)


with app.app_context():
    db.create_all()
    for _i in range(50):
        user = User2()
        db.session.add(user)
    db.session.commit()

    # works
    query_works = User2.query.order_by(User2.id.desc())
    page_works = get_page(query_works, per_page=3)
    print([type(page_works), page_works.paging.next])

    # breaks
    query_breaks = sa.select(User2).order_by(User2.id)
    page_breaks = select_page(db.session.connection(), query_breaks, per_page=3)
    print([type(page_breaks), page_breaks.paging.next])

```
And exception:
```python
Traceback (most recent call last):
  File "<frozen runpy>", line 198, in _run_module_as_main
  File "<frozen runpy>", line 88, in _run_code
  File "/home/guru/Desktop/myproject/.venv/lib/python3.11/site-packages/flask/__main__.py", line 3, in <module>
    main()
  File "/home/guru/Desktop/myproject/.venv/lib/python3.11/site-packages/flask/cli.py", line 1064, in main
    cli.main()
  File "/home/guru/Desktop/myproject/.venv/lib/python3.11/site-packages/click/core.py", line 1078, in main
    rv = self.invoke(ctx)
         ^^^^^^^^^^^^^^^^
  File "/home/guru/Desktop/myproject/.venv/lib/python3.11/site-packages/click/core.py", line 1688, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
                           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/guru/Desktop/myproject/.venv/lib/python3.11/site-packages/click/core.py", line 1434, in invoke
    return ctx.invoke(self.callback, **ctx.params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/guru/Desktop/myproject/.venv/lib/python3.11/site-packages/click/core.py", line 783, in invoke
    return __callback(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/guru/Desktop/myproject/.venv/lib/python3.11/site-packages/click/decorators.py", line 92, in new_func
    return ctx.invoke(f, obj, *args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/guru/Desktop/myproject/.venv/lib/python3.11/site-packages/click/core.py", line 783, in invoke
    return __callback(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/guru/Desktop/myproject/.venv/lib/python3.11/site-packages/flask/cli.py", line 912, in run_command
    raise e from None
  File "/home/guru/Desktop/myproject/.venv/lib/python3.11/site-packages/flask/cli.py", line 898, in run_command
    app = info.load_app()
          ^^^^^^^^^^^^^^^
  File "/home/guru/Desktop/myproject/.venv/lib/python3.11/site-packages/flask/cli.py", line 309, in load_app
    app = locate_app(import_name, name)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/guru/Desktop/myproject/.venv/lib/python3.11/site-packages/flask/cli.py", line 219, in locate_app
    __import__(module_name)
  File "/home/guru/Desktop/myproject/myflask.py", line 39, in <module>
    page_breaks = select_page(db.session.connection(), query_breaks, per_page=3)
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/guru/Desktop/myproject/.venv/lib/python3.11/site-packages/sqlakeyset/paging.py", line 411, in select_page
    return core_get_page(s, selectable, per_page, place, backwards)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/guru/Desktop/myproject/.venv/lib/python3.11/site-packages/sqlakeyset/paging.py", line 297, in core_get_page
    page = core_page_from_rows(
           ^^^^^^^^^^^^^^^^^^^^
  File "/home/guru/Desktop/myproject/.venv/lib/python3.11/site-packages/sqlakeyset/paging.py", line 328, in core_page_from_rows
    key_rows = [tuple(col.get_from_row(row) for col in mapped_ocols) for row in rows]
               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/guru/Desktop/myproject/.venv/lib/python3.11/site-packages/sqlakeyset/paging.py", line 328, in <listcomp>
    key_rows = [tuple(col.get_from_row(row) for col in mapped_ocols) for row in rows]
                ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/guru/Desktop/myproject/.venv/lib/python3.11/site-packages/sqlakeyset/paging.py", line 328, in <genexpr>
    key_rows = [tuple(col.get_from_row(row) for col in mapped_ocols) for row in rows]
                      ^^^^^^^^^^^^^^^^^^^^^
  File "/home/guru/Desktop/myproject/.venv/lib/python3.11/site-packages/sqlakeyset/columns.py", line 323, in get_from_row
    return getattr(row[self.index], self.attr)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
AttributeError: 'int' object has no attribute 'id'

Process finished with exit code 1

```

Pull request:
Fix errors when using sqla2-style ORM query with a `Connection`
Fixes #94

Patch:
diff --git a/sqlakeyset/columns.py b/sqlakeyset/columns.py
index 4295ce0..4d1d1ac 100644
--- a/sqlakeyset/columns.py
+++ b/sqlakeyset/columns.py
@@ -60,13 +60,14 @@ class OC:
     :class:`sqlalchemy.sql.expression.ColumnElement` appearing in the ORDER BY
     clause of a query we are paging."""
 
-    def __init__(self, x):
+    def __init__(self, x, enable_warnings=True):
         if isinstance(x, str):
             x = column(x)
         if _get_order_direction(x) is None:
             x = asc(x)
         self.uo = x
-        _warn_if_nullable(self.comparable_value)
+        if enable_warnings:
+            _warn_if_nullable(self.comparable_value)
         self.full_name = str(self.element)
         try:
             table_name, name = self.full_name.split(".", 1)
@@ -409,7 +410,7 @@ def derive_order_key(ocol, desc, index):
 
     # is an attribute with label
     try:
-        if ocol.quoted_full_name == OC(expr).full_name:
+        if ocol.quoted_full_name == OC(expr, enable_warnings=False).full_name:
             return DirectColumn(ocol, index)
     except sqlalchemy.exc.ArgumentError:
         pass
diff --git a/sqlakeyset/paging.py b/sqlakeyset/paging.py
index c1ed1d3..e82c738 100644
--- a/sqlakeyset/paging.py
+++ b/sqlakeyset/paging.py
@@ -31,6 +31,7 @@ from .sqla import (
     core_coerce_row,
     core_result_type,
     get_bind,
+    get_session,
     group_by_clauses,
     orm_coerce_row,
     orm_query_keys,
@@ -260,7 +261,7 @@ def orm_get_page(
 
 
 def core_get_page(
-    s: Union[Session, Connection],
+    s: Session,
     selectable: Select[_TP],
     per_page: int,
     place: Optional[Keyset],
@@ -406,7 +407,8 @@ def select_page(
     """
     place, backwards = process_args(after, before, page)
 
-    return core_get_page(s, selectable, per_page, place, backwards)
+    session = get_session(s)
+    return core_get_page(session, selectable, per_page, place, backwards)
 
 
 def get_page(
diff --git a/sqlakeyset/results.py b/sqlakeyset/results.py
index 03c187f..f897df4 100644
--- a/sqlakeyset/results.py
+++ b/sqlakeyset/results.py
@@ -101,7 +101,9 @@ def unserialize_bookmark(bookmark: str) -> Marker:
 _Row = TypeVar("_Row", bound=Sequence, covariant=True)
 
 
-class Page(list, Sequence[_Row]):  # Can't subclass List[_Row] directly because _Row is covariant
+class Page(
+    list, Sequence[_Row]
+):  # Can't subclass List[_Row] directly because _Row is covariant
     """A :class:`list` of result rows with access to paging information and
     some convenience methods."""
 
diff --git a/sqlakeyset/sqla.py b/sqlakeyset/sqla.py
index 0a76b12..bd3781b 100644
--- a/sqlakeyset/sqla.py
+++ b/sqlakeyset/sqla.py
@@ -5,6 +5,7 @@ import sqlalchemy
 from packaging import version
 from sqlalchemy.engine import Connection, Engine
 from sqlalchemy.orm import Session
+from sqlalchemy.orm.scoping import ScopedSession
 
 
 SQLA_VERSION = version.parse(sqlalchemy.__version__)
@@ -14,6 +15,7 @@ try:
     from sqlalchemy.ext.asyncio import AsyncConnection, AsyncEngine, AsyncSession
 except ImportError:
     if not TYPE_CHECKING:
+
         class AsyncConnection:
             pass
 
@@ -37,6 +39,17 @@ def get_bind(
         raise ValueError(f"{s} is not a (sync/async) Engine, Connection or Session.")
 
 
+def get_session(s: Union[Engine, Connection, Session, ScopedSession]) -> Session:
+    if isinstance(s, Session):
+        return s
+    elif isinstance(s, ScopedSession):
+        return s()
+    elif isinstance(s, (Engine, Connection)):
+        return Session(bind=s)
+    else:
+        raise ValueError(f"{s} is not an Engine, Connection or Session.")
+
+
 if SQLA_VERSION < version.parse("1.4.0b1"):
     from .sqla13 import (
         core_coerce_row,


Test patch:
diff --git a/tests/conftest.py b/tests/conftest.py
index 3742108..2d5699d 100644
--- a/tests/conftest.py
+++ b/tests/conftest.py
@@ -20,7 +20,6 @@ from sqlakeyset import custom_bookmark_type
 from sqlalchemy_utils import ArrowType
 
 SQLA2 = SQLA_VERSION >= version.parse("1.4")
-
 if not SQLA2:
     # This block needs to be before the sqla2 block for type hints to work correctly??
     # Thus the backwards if-else.
@@ -38,6 +37,12 @@ else:
 class Base(declarative_base()):
     __abstract__ = True
 
+    def _as_dict(self):
+        return {k: getattr(self, k) for k, v in self.__mapper__.columns.items()}
+
+    def __eq__(self, other):
+        return type(self) == type(other) and self._as_dict() == other._as_dict()
+
     def __repr__(self):
         try:
             name = type(self).__name__
diff --git a/tests/test_paging.py b/tests/test_paging.py
index 721224f..0883ab9 100644
--- a/tests/test_paging.py
+++ b/tests/test_paging.py
@@ -17,7 +17,7 @@ from packaging import version
 
 import pytest
 import sqlalchemy
-from sqlalchemy.orm import sessionmaker, aliased, Bundle
+from sqlalchemy.orm import Session, sessionmaker, aliased, Bundle
 from sqlalchemy import (
     desc,
     func,
@@ -96,7 +96,10 @@ def check_paging_orm(q):
 def check_paging_core(selectable, s):
     item_counts = range(1, 12)
 
-    result = s.execute(selectable)
+    if isinstance(s, Session):
+        result = s.execute(selectable)
+    else:
+        result = Session(bind=s).execute(selectable)
     unpaged = result.fetchall()
 
     for backwards in [False, True]:
diff --git a/tests/test_paging_20_style.py b/tests/test_paging_20_style.py
index 9bb7570..9c16350 100644
--- a/tests/test_paging_20_style.py
+++ b/tests/test_paging_20_style.py
@@ -173,3 +173,9 @@ def test_new_orm_recursive_cte(pg_only_dburl):
         )
 
         check_paging_core(q, s)
+
+
+def test_new_orm_query_using_connection(dburl):
+    with S(dburl, echo=ECHO) as s:
+        q = select(Book).order_by(Book.id, Book.name)
+        check_paging_core(q, s.connection())