Put a word with white space as a variable in a sql query using .format()

I would like to know how to put a word with white space as a variable in a sql query (I use postgresql)

data   = "something with white space"
choice = "DESC"
limit  = 10 

def rDB(sql_request):
    with connection.cursor() as cursor:
        cursor.execute(sql_request)
        row = cursor.fetchall()
    return row

queryWithFormat(data, choice, limit):
    return('''
           SELECT col1, SUM(col2) as nb
           FROM Table
           WHERE col1 = {d}  
           ORDER BY nb {c}
           LIMIT {l}
           '''.format(d=data, c=choice, l=limit)

rDB(queryWithFormat(data, choice, limit))

django.db.utils.ProgrammingError: ERROR: syntax error on or near 'with'
LINE 8: WHERE col1 = something with white ...
-------------------------------^

The best way is to not use Python's native string formatting at all! Instead, you should create a parameterised query.

It would be possible to make your code work, I think, by quoting the variable col1 = '{d}', but I am telling you this for information only and you should not do it. It is a security risk. For best practice, see this section of the docs.

Django aside (not my area of expertise...), it looks like you're using SQLAlchemy to execute the query. This library has its own syntax and methods for parameter binding, which you could implement as follows:

from sqlalchemy.sql import text

def query_with_format(data, choice, limit):
    query = text("""
           SELECT col1, SUM(col2) as nb
             FROM Table
            WHERE col1 = :d 
           ORDER BY nb :c
           LIMIT :l
    """)
    query.bindparams(d=data, c=choice, l=limit)
    return query

rDB(query_with_format(data, choice, limit))

I do not think you will have any trouble with variables containing whitespace with this approach, and it is far, far safer.

Back to Top