Build sqlalchemy query from string

Question

How can I convert a sql where clause string to a sqlalchemy query? I'm assuming I already know the table.

I'm building an Angular webapp which hits a Flask API for data. Flask is using sqlalchemy to query the db. jQuery-QueryBuilder fromSQL (http://querybuilder.js.org/plugins.html#import-export) exports filters as raw SQL which I want to pass back to the api, parse, and query.

For example:

where_str = "name LIKE '%BOB%' AND fruit != 'pineapple'"

would get converted to:

session.query(Table).filter(Table.name.like('%BOB%')).filter(Table.fruit != 'pineapple)

Tagging blaze because odo might be what I need.

Check this Thread: https://stackoverflow.com/questions/14845196/dynamically-constructing-filters-in-sqlalchemy — Eduardo
– Eduardo, Commented Mar 21, 2019 at 14:28

Mary Marchini · Accepted Answer · 2015-09-10 22:04:10Z

7

You can try to execute session.query(Table).filter(where_str). It worked for me on SQLAlchemy v0.5.8. You can also build the whole SQL statement string and use the Connection.execute() method to execute it. Either way, passing SQL statements as strings directly from the webpage to the application can be very dangerous.

edited Sep 10, 2015 at 22:04

answered Sep 10, 2015 at 21:41

Mary Marchini

7185 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

postelrich Over a year ago

Yea I know you can do execute but you need a full SQL statement. I only have the WHERE part of the statement.

Mary Marchini Over a year ago

Which version of SQLAlchemy are you using? Our application still runs a very old one (0.5.8), but Ive tried something like session.query(Table).filter(where_str) and it worked

postelrich Over a year ago

I'm using 1.0.6, and you're right. Though it will work, it gives warnings for using a string directly and suggests using docs.sqlalchemy.org/en/rel_1_0/core/… such as session.query(Table).filter(text(where_str)). To reiterate what Matheus said, its not very safe to let your api accept a SQL string but that's a separate topic.

boatcoder Over a year ago

This is super powerful once you understand what it means. Sometimes it is harder to write the filter portion in SqlAlchemy than it is to just write it out in SQL (especially when user input is NOT involved). Knowing this is possible is gonna save me a lot of time.

user749127 Over a year ago

In my case, I am building an API that returns a result set from SQLAlchemy, and I want the API client to be able to filter the result set with a string, so being able to just pass a string to "filter" or sanitize the string and pass that in would be nice.

Lee Altman · Accepted Answer · 2015-09-10 21:37:30Z

-1

You don't need to filter twice to add an AND you can do this:

session.query(Table).filter(Table.name.like('%BOB%'), Table.fruit != 'pineapple)

answered Sep 10, 2015 at 21:37

Lee Altman

375 bronze badges

Collectives™ on Stack Overflow

Build sqlalchemy query from string

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related