SQLAlchemy: constraint on the same line as where

Question

We are trying to incorporate the SQL query interface into our web application, which is WSGI, and uses Python, with SQLAlchemy (the kernel, not ORM) to query the PostgreSQL database. We have several data-level functions created to help build queries, and now we are trying to install something that allows this type of query:

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

In the interface, we have a text field that allows the user to enter a where clause and a limit clause so that data can be flexibly and dynamically requested from the front, that is, we want to include an hoc request declaration. So, the only thing we have in mind now is:

 select id from <table_name> 

And the user will enter, for example:

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

using the same end function. The selection, column and table must be controlled by the data level (i.e., He knows what they are, and the user does not need to know this). However, I do not know how to force SQLAlchemy to automatically parse both the where clause and the limit clause automatically. Now we have a function that can return the table name and column name id, and then use it to create a text query, which is passed to SQLAlchemy as an input to the text() call.

Is there a way to do this using SQLAlchemy or another library? Or is there a better model that I should know about that does not involve SQL parsing, but at the same time allowing this functionality to work with the interface?

Thank you so much! All suggestions will be greatly appreciated.

Answer 1

I'm not sure what I am following, but the general use of SQL-Alchemy is similar:

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

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