Menu
I have list of ID from external postgresql database.
A = [1,2,3,4,5,6,7,98,0]
I would to do query to database using SQLAlchemy, but I would to sort data in postgresql by A list.
I have read a lot of documentation but cannot find any suggestions how to do that.
So, in final I would to have:
results = session.query(user).limit(20).offset(10).order_by(A)
Cheers
UPDATE:
I found solution, it's not so good as I expected, but works well. Anyway if you know better solution, just let me know!
ids = ','.join([str(i) for i in A])
results = session.query(user).filter(user.id.in_(A)).\
limit(20).offset(10).\
order_by(" position(CONCAT(',',users.id::text,',') in ',{0},'.format(ids)")
414
The statement ends by calling subquery() , which tells SQLAlchemy that our intention for this query is to use it inside a bigger query instead of on its own.
As the documentation says, all() returns the result of the query as a list.
Flask-SQLAlchemy¶Flask-SQLAlchemy is an extension for Flask that adds support for SQLAlchemy to your application. It simplifies using SQLAlchemy with Flask by setting up common objects and patterns for using those objects, such as a session tied to each web request, models, and engines.
Metadata contains definitions of tables and associated objects such as index, view, triggers, etc. Hence an object of MetaData class from SQLAlchemy Metadata is a collection of Table objects and their associated schema constructs.
If you do not necessarily need to do this in SQL, you could simply sort the returned list of objects directly in python.
Example (using python's sorted function)
results = session.query(user).filter(user.id.in_(A)).all()
results = sorted(results, key=lambda o: A.index(o.id))
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
