How to create a SQL query with optional parameters in Python Flask?

I prefer to use Python's format string function for this. It's splitting hairs, but format allows to set name in the string, so it's technically more explicit. However, I would suggest using **kwargs instead of *args, so you don't have to rely on magic.

UPADATE 2019

This was a terrible answer. You never, ever, EVER want to take user generated data and interpolate it directly into a SQL query. It is imperative that you always sanitize user input in order to protect against SQL injection. Python has defined a database API specification that any database package that is not an ORM like SQLAlchemy should implement. Long story short, you should be NEVER, EVER, EVER use str.format(), %, or "fstrings" to interpolate data into your SQL queries.

The database API specification provides a way to safely interpolate data into queries. Every python database interface should have a Cursor class that is returned from a Connection object. The Cursor class will implement a method named execute. This obviously will execute a query, but it will also have a second argument–usually called args. According to the specification:

Parameters may be provided as sequence or mapping and will be bound to variables in the operation. Variables are specified in a database-specific notation (see the module's paramstyle attribute for details).

By "sequence", it means that args can be a list or tuple, and by "mapping", it means that args can also be a dict. Depending on the package, the way to specify where your data should be interpolated may differ. There are six options for this. Which formatting the package you're using can be found in the paramstyle constant of the package. For instance, PyMySQL(and most implementations of the spec that I've used) uses format and pyformat. A simple example would be:

format

cursor.execute('SELECT * FROM t WHERE a = %s, b = %s;', (1, 'baz'))

pyformat

cursor.execute('SELECT * FROM t WHERE a = %(foo)s, b = %(bar)s;', {'foo': 1, 'bar': 'baz'})

Both of these would execute as:

SELECT * FROM t WHERE a = 1, b = 'baz';

You should make sure to explore the documentation of the database API package you're using. One extremely helpful thing I came across using psycopg2, a PostgresSQL package, was its extras module. For instance, a common problem when trying to insert data securely is encountered when inserting multiple rows at once. psycopg2 has a clever solution to this problem in its execute_values function. Using execute_values, this code:

execute_values(cursor, "INSERT INTO t (a, b) VALUES %s;", ((1, 'foo'), (2, 'baz')))

... is executed as:

"INSERT INTO t (a, b) VALUES (1, 'foo'), (2, 'baz');"