How can I insert parameters in raw SQL in Django Python

Question

I have this code

myquery =   '''SELECT * from users 
               where id = 10 and
               city = 20 and 
               state = 30'''

I want to replace those with three variables like

var_id = bla
var_city = bla
var_state = bla

Answer 1

You can also use dictionaries and variables in your queries as so:

my_dict = {
   'id': 10,
   'city': 20,
   'state': 30
} 

mymodel.objects.raw('''SELECT * from users 
                       where id = %(id)s and
                       city = %(city)s and 
                       state = %(state)s ''', my_dict)

You can read more up on it here: https://docs.djangoproject.com/en/1.10/topics/db/sql/#passing-parameters-into-raw

Answer 2

Use the params argument to raw():

var_id = 10
var_city = 20
var_state = 30

mymodel.objects.raw('''SELECT * from users 
                       where id = %s and
                       city = %s and 
                       state = %s ''', [var_id, var_city, var_state])

params is a list of parameters. You'll use %s placeholders in the query string (regardless of your database engine); they'll be replaced with parameters from the params list.

---

Important note from Django docs:

Warning Do not use string formatting on raw queries!

It's tempting to write the above query as:

>>> query = 'SELECT * FROM myapp_person WHERE last_name = %s' % lname
>>> Person.objects.raw(query)

Don't.

Using the params list completely protects you from SQL injection attacks, a common exploit where attackers inject arbitrary SQL into your database. If you use string interpolation, sooner or later you'll fall victim to SQL injection. As long as you remember to always use the params list you'll be protected.