Which import should I use for raw sql ?

[HuzaifahLutaaya]

Which of these, should I use for Raw SQL ?

https://github.com/uriyyo/fastapi-pagination/tree/main/fastapi_pagination

Suppose I have this query ;

GET_FILTERED_FOOD_QUERY = """
SELECT DISTINCT food_name
FROM clients
WHERE tenant_id = :tenant_id
AND (
    :search::text IS NULL 
    OR internal_name ILIKE '%' || :search || '%'
)
ORDER BY internal_name
"""

[uriyyo]

Hi @HuzaifahLutaaya,

You can use sqalchemy extension for that:

from fastapi_pagination.ext.sqlalchemy import paginate

GET_FILTERED_FOOD_QUERY = """
SELECT DISTINCT food_name
FROM clients
WHERE tenant_id = :tenant_id
AND (
    :search::text IS NULL 
    OR internal_name ILIKE '%' || :search || '%'
)
ORDER BY internal_name
"""


@app.get("/items")
def read_items(db: Session = Depends(get_db)) -> Page[Any]:
    return paginate(db, text(GET_FILTERED_FOOD_QUERY).bindparams(tenant_id=1, search=None))

[HuzaifahLutaaya]

Hi @uriyyo

Thanks for your answer, but now am getting the error AttributeError: 'Database' object has no attribute 'scalar'

Full error log;

pi-1               |       | Traceback (most recent call last):
api-1               |       |   File "/opt/pysetup/.venv/lib/python3.11/site-packages/anyio/streams/memory.py", line 105, in receive
api-1               |       |     return self.receive_nowait()
api-1               |       |   File "/opt/pysetup/.venv/lib/python3.11/site-packages/anyio/streams/memory.py", line 100, in receive_nowait
api-1               |       |     raise WouldBlock
api-1               |       | anyio.WouldBlock
api-1               |       |
api-1               |       | During handling of the above exception, another exception occurred:
api-1               |       |
api-1               |       | Traceback (most recent call last):
api-1               |       |   File "/opt/pysetup/.venv/lib/python3.11/site-packages/anyio/streams/memory.py", line 118, in receive
api-1               |       |     return receiver.item
api-1               |       | AttributeError: 'MemoryObjectItemReceiver' object has no attribute 'item'
api-1               |       |
api-1               |       | During handling of the above exception, another exception occurred:
api-1               |       |
api-1               |       | Traceback (most recent call last):
api-1               |       |   File "/opt/pysetup/.venv/lib/python3.11/site-packages/starlette/middleware/base.py", line 78, in call_next
api-1               |       |     message = await recv_stream.receive()
api-1               |       |   File "/opt/pysetup/.venv/lib/python3.11/site-packages/anyio/streams/memory.py", line 120, in receive
api-1               |       |     raise EndOfStream
api-1               |       | anyio.EndOfStream
api-1               |       |
api-1               |       | During handling of the above exception, another exception occurred:
api-1               |       |
api-1               |       | Traceback (most recent call last):
api-1               |       |   File "/opt/pysetup/.venv/lib/python3.11/site-packages/starlette/middleware/base.py", line 108, in __call__
api-1               |       |     response = await self.dispatch_func(request, call_next)
api-1               |       |   File "/app/api/__init__.py", line 110, in add_default_cache_control_header
api-1               |       |     response = await call_next(request)
api-1               |       |   File "/opt/pysetup/.venv/lib/python3.11/site-packages/starlette/middleware/base.py", line 84, in call_next
api-1               |       |     raise app_exc
api-1               |       |   File "/opt/pysetup/.venv/lib/python3.11/site-packages/starlette/middleware/base.py", line 70, in coro
api-1               |       |     await self.app(scope, receive_or_disconnect, send_no_error)
api-1               |       |   File "/opt/pysetup/.venv/lib/python3.11/site-packages/starlette/middleware/cors.py", line 83, in __call__
api-1               |       |     await self.app(scope, receive, send)
api-1               |       |   File "/opt/pysetup/.venv/lib/python3.11/site-packages/sentry_sdk/integrations/starlette.py", line 257, in _sentry_exceptionmiddleware_call
api-1               |       |     await old_call(self, scope, receive, send)
api-1               |       |   File "/opt/pysetup/.venv/lib/python3.11/site-packages/sentry_sdk/integrations/starlette.py", line 158, in _create_span_call
api-1               |       |     return await old_call(app, scope, new_receive, new_send, **kwargs)
api-1               |       |   File "/opt/pysetup/.venv/lib/python3.11/site-packages/starlette/middleware/exceptions.py", line 79, in __call__
api-1               |       |     raise exc
api-1               |       |   File "/opt/pysetup/.venv/lib/python3.11/site-packages/starlette/middleware/exceptions.py", line 68, in __call__
api-1               |       |     await self.app(scope, receive, sender)
api-1               |       |   File "/opt/pysetup/.venv/lib/python3.11/site-packages/sentry_sdk/integrations/starlette.py", line 158, in _create_span_call
api-1               |       |     return await old_call(app, scope, new_receive, new_send, **kwargs)
api-1               |       |   File "/opt/pysetup/.venv/lib/python3.11/site-packages/fastapi/middleware/asyncexitstack.py", line 20, in __call__
api-1               |       |     raise e
api-1               |       |   File "/opt/pysetup/.venv/lib/python3.11/site-packages/fastapi/middleware/asyncexitstack.py", line 17, in __call__
api-1               |       |     await self.app(scope, receive, send)
api-1               |       |   File "/opt/pysetup/.venv/lib/python3.11/site-packages/starlette/routing.py", line 718, in __call__
api-1               |       |     await route.handle(scope, receive, send)
api-1               |       |   File "/opt/pysetup/.venv/lib/python3.11/site-packages/starlette/routing.py", line 276, in handle
api-1               |       |     await self.app(scope, receive, send)
api-1               |       |   File "/opt/pysetup/.venv/lib/python3.11/site-packages/starlette/routing.py", line 66, in app
api-1               |       |     response = await func(request)
api-1               |       |   File "/app/api/activity_logging.py", line 22, in custom_route_handler
api-1               |       |     response: Response = await original_route_handler(request)
api-1               |       |   File "/opt/pysetup/.venv/lib/python3.11/site-packages/sentry_sdk/integrations/fastapi.py", line 137, in _sentry_app
api-1               |       |     return await old_app(*args, **kwargs)
api-1               |       |   File "/opt/pysetup/.venv/lib/python3.11/site-packages/fastapi/routing.py", line 241, in app
api-1               |       |     raw_response = await run_endpoint_function(
api-1               |       |   File "/opt/pysetup/.venv/lib/python3.11/site-packages/fastapi/routing.py", line 167, in run_endpoint_function
api-1               |       |     return await dependant.call(**values)
api-1               |       |   File "/app/api/users/router.py", line 446, in get_user_filters
api-1               |       |     user_filters = await self.service.get_filter_user_attributes(
api-1               |       |   File "/app/api/users/service.py", line 793, in get_filter_user_attributes
api-1               |       |     paginated_results = await self.repository.get_filtered_courses(tenant_id, params, search)  # type: ignore
api-1               |       |   File "/app/api/users/repository.py", line 115, in get_filtered_courses
api-1               |       |     return paginate(
api-1               |       |   File "/opt/pysetup/.venv/lib/python3.11/site-packages/fastapi_pagination/ext/sqlalchemy.py", line 282, in paginate
api-1               |       |     return exec_pagination(
api-1               |       |   File "/opt/pysetup/.venv/lib/python3.11/site-packages/fastapi_pagination/ext/sqlalchemy.py", line 152, in exec_pagination
api-1               |       |     total = conn.scalar(count_query)
api-1               |       | AttributeError: 'Database' object has no attribute 'scalar'

[uriyyo]

@HuzaifahLutaaya Are you using databases library?

[HuzaifahLutaaya]

I ended up changing my query to Sqlalchemy. @uriyyo