Doesn't seem to work with SQLAlchemy from_statement

[kiranzo]

Long story short - I'm getting this when trying to paginate my query
sqlalchemy.exc.NoSuchColumnError: Could not locate column in row for column 'count(*)'

It's a simple query that only has WHERE and ORDER BY, and the query doesn't seem to be paginated, it only throws an error.
Please tell me - does this library support from_statement, or should I do it manually for this particular method?

P.S. The query in question uses a function from Epam Bingo data cartridge, so I'm not sure which library causes this.
UPD: Bingo support says it's likely on pagination part.

[kiranzo]

It seems like this is an issue with SqlAlchemy and stored procedures.
sqlalchemy/sqlalchemy#3103
sqlalchemy/sqlalchemy#2972
In my specific case, the problems start when it tries to do 'SELECT COUNT(*) WHERE [call to stored procedure]'.
I think I'll just use cursor to get totals and page items and then call fastapi_pagination.api.create_page

[kiranzo]

Actually, disregard that.
'sqlalchemy.exc.NoSuchColumnError: Could not locate column in row for column 'count(*)'' IS caused by inability to paginate queries created by from_statement as I found out when I moved on to paginate other methods.

[uriyyo]

@kiranzo Could you please provide code example so I can reproduce this issue?

[kiranzo]

@uriyyo Okay, let's try this

  from sqlalchemy.ext.declarative import declarative_base
  from sqlalchemy.sql import text
  
  
  Base = declarative_base()
  
  class Publication(Base):
      __tablename__ = "publication"
  
      id = Column(Integer, primary_key=True, index=True)
      year = Column(Integer)
  
  
  .......
  
  paginate(session.query(Publication).from_statement(text('select * from publication where year=2015')), params)

Really, any query created with from_statement wouldn't work, because sqlalchemy throws that error when trying to do query.count()

[aledt]

I think there's a few things to unpack here.

First of all, from_statement cannot be used with any additional criteria, so any SQLAlchemy query using from_statement can't be paginated.

See https://docs.sqlalchemy.org/en/20/tutorial/data_select.html#selecting-orm-entities-from-unions

Secondly, on a side note, the Query API has been deprecated and you want to move to using the Select functions instead.

For the query quoted above, you want to pass the constructed Select object into the paginate function (although you'll want to use that from the fastapi_pagination.ext.sqlalchemy_future (or sqlalchemy) module depending on what version of fastapi_pagination.

You'll likely end up with something like:

from fastapi_pagination.ext.sqlalchemy_future import paginate
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import select
  
  
Base = declarative_base()
  
class Publication(Base):
    __tablename__ = "publication"
  
    id = Column(Integer, primary_key=True, index=True)
    year = Column(Integer)

paginate(session, select(Publication).where(Publication.year == 2015))