SQLAlchemy: limit in the same string as where

Question

We're trying to enable a SQL query front-end to our Web application, which is WSGI and uses Python, with SQLAlchemy (core, not ORM) to query a PostgreSQL database. We have several data layer functions set up to assist in query construction, and we are now trying to set something up that allows this type of query:

select id from <table_name> where ... limit ...

In the front end, we have a text box which lets the user type in the where clause and the limit clause, so that the data can be queried flexibly and dynamically from the front end, that is, we want to enable ad hoc querying. So, the only thing that we now firsthand is:

select id from <table_name>

And the user will type in, for example:

where date > <some_date>
where location is not null limit 10 order by location desc

using the same back end function. The select, column and table should be managed by the data layer (i.e. it knows what they are, and the user should not need to know that). However, I'm not aware of any way to get SQLAlchemy to automatically parse both the where clause and the limit clause automatically. What we have right now is a function which can return the table name and the name of the id column, and then use that to create a text query, which is passed to SQLAlchemy, as the input to a text() call.

Is there any way I can do this with SQLAlchemy, or some other library? Or is there a better pattern of which I should be aware, which does not involve parsing the SQL while still allowing this functionality from the front-end?

Thanks a lot! All suggestions will be greatly appreciated.

Answer 1

I'm not sure I follow, but the general SQL-Alchemy usage is like:

 results = db.session.query(User).filter(User.name == "Bob").order_by(User.age.desc()).limit(10)

That will query the User table to return the top ten oldest members named "Bob"