Django: raw SQL queries with a dynamic number of variables

Question

Is it possible to construct raw SQL queries in Django so that they accept a dynamic number of arguments? So for example say that I have the following url structure in my app:

/books/category/history/
/books/category/history/1800s/

For the first query, I'm looking for all books with the keyword 'history', and for the second, I'm looking for all books with the keyword 'history' AND the keyword '1800s'.

I currently have two separate queries for each of these:

keyword1 = 'history'
SELECT appname_book.name AS name FROM appname_book WHERE keyword=%s,[keyword1]

keyword1 = 'history'
keyword2 = '1800s'
SELECT appname_book.name AS name FROM appname_book WHERE keyword=%s AND keyword=%s,[keyword1, keyword2]

Anyone know of a cleaner and more efficient way to do this?

I'm using Django 1.3 and MySQL.

Thanks.

Arthur Neves · Accepted Answer · 2011-11-30 01:59:39Z

2

Why dont you use Django QuerySet, like this:

Book.objects.all().filter(keyword__in=['history','1800s']).values('name')

Another possible solution using RAW SQL, coud be:

keywords = []
SQL = 'SELECT appname_book.name AS name FROM appname_book WHERE 1=1 '
SQL += ' '.join(['AND keyword=%s' for _ in params])

edited Nov 30, 2011 at 1:59

answered Nov 30, 2011 at 1:44

Arthur Neves

12.2k8 gold badges64 silver badges74 bronze badges

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

1 Comment

rolling stone Over a year ago

thanks, that would be ideal, but for a lot of other reasons I have to write my own SQL.

Luiz Felipe Lima · Accepted Answer · 2021-02-23 18:31:50Z

0

Sure, you could do something like this to dynamically generate a raw SQL query

sql = 'SELECT id FROM table WHERE 1 = 1'
params = []

if 'description' in args.keys():
    sql += ' AND description LIKE %s'
    params.append('%'+args['description']+'%')
if 'is_active' in args.keys():
    sql += ' AND is_active LIKE %s'
    params.append(args['is_active'])

... you can put as many "ifs" you want to construct the query

with connections['default'].cursor() as cursor:
    cursor.execute(sql, params)

This way would still be completely safe against SQL Injections vulnerability

answered Feb 23, 2021 at 18:31

Luiz Felipe Lima

712 bronze badges

Collectives™ on Stack Overflow

Django: raw SQL queries with a dynamic number of variables

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related