How to create a dynamic SQL query
<p>I have a large sqlite table of 33k records (movies) . I want to give the user the ability to search in that table via filters . Each movie record has 10 fields : Actors, Directors, Runtime, Genre, Rating and few more . The only filters available for the user to use are people (Include Actors and Directors), Genre, Rating and Runtime . </p>

<p>The problem is that the user can use two filters or more, hence we don't really know which filter will be used . Filters values are passed via an HTTP req to the server which process it and create an SQL query based on filters to execute on the db .</p>

<p>What I can't understand is how can I create an SQL query if I don't know which filters will be used ? Because only used filters will be sent to the server .</p>

<p>Basically I want to find a way to create an SQL query based on sent filters by each users . </p>

<p>If you're given a list of filters, you can just apply <code>Query.filter_by()</code> or <code>Query.filter()</code> over and over:</p>

<pre><code>filters = [
    ('rating', 3),
    ('runtime', 90),
    ('genre', 'documentary')
]

query = session.query(Movie)

for column, value in filters:
    # Make sure `column` and `value` are legal values!

    query = query.filter_by(**{column: value})
</code></pre>

<p>Since the query is evaluated only at the end, you're essentially doing:</p>

<pre><code>query = query.filter_by(...).filter_by(...) ...
</code></pre>

<p>In the last line, <code>filter_by(**{column: value})</code> is notation for <code>filter_by(value_of_column_variable=value)</code>.</p>

python sql sqlalchemy flask-sqlalchemy