CODEWITHSUNDEEP
mysqldjangomariadb
Cheknov
Cheknov

Reputation: 2072

How to perform a parametrized raw query in Django?

I'm reading the official Django documentation, but I can't find an answer to my question.

Right now I have this query implemented, working with a custom MariaDB connector for Django:

results = []
cursor  = get_cursor()
try:
    sql="SELECT a.*, COALESCE( NULLIF(a.aa, '.'), NULLIF(a.gen, '.') ) AS variant, b.*, c.* FROM `db-dummy`.sp_gen_data c JOIN `db-dummy`.gen_info a ON a.record_id = c.gen_id JOIN `db-dummy`.sp_data b ON b.record_id = c.sp_id WHERE a.gene_name LIKE concat(?, '%') AND a.report_notation LIKE concat('%', ?, '%') AND b.sp_id LIKE concat(?, '%');"
    data = (gen, var, sp)
    cursor.execute(sql, data)
except mariadb.Error as e:
    print(f"Error: {e}")
columns = [column[0] for column in cursor.description]
results = []
for row in cursor.fetchall():
    results.append(dict(zip(columns, row)))     
return results

It works, but now I need to adapt this query to the django's default MySQL backend.

What I've tried:

results = []
cursor  = get_cursor()
sql="SELECT a.*, COALESCE( NULLIF(a.aa, '.'), NULLIF(a.gen, '.') ) AS variant, b.*, c.* FROM `db-dummy`.sp_gen_data c JOIN `db-dummy`.gen_info a ON a.record_id = c.gen_id JOIN `db-dummy`.sp_data b ON b.record_id = c.sp_id WHERE a.gene_name LIKE %s AND a.report_notation LIKE %s AND b.sp_id LIKE %s;"
data = ('{}%'.format(gen), '{}%'.format(var), '{}%'.format(sp))
cursor.execute(sql, data)

columns = [column[0] for column in cursor.description]
results = []
for row in cursor.fetchall():
    results.append(dict(zip(columns, row)))
return results

So basically what I had to change to make it work was LIKE concat(?, '%') for LIKE %s.

The problem is, for the a.report_notation LIKE concat('%', ?, '%') part, I do not know how to convert it to something that Django can interpret.

Any ideas?

Upvotes: 0

Views: 185

Answers (1)

Iain Shelvington
Iain Shelvington

Reputation: 32244

Your first query should be fine just adjusted to match the format that Django expects.

First, replace ? with %s to pass parameters to the query

Second, replace % with %% as a single percent is an "escape" character and you need to escape the escape char

DOCS

Here's your original query truncated to show an example of how it could work

sql="... WHERE a.gene_name LIKE concat(%s, '%%') AND a.report_notation LIKE concat('%%', %s, '%%') AND b.sp_id LIKE concat(%s, '%%');"
data = (gen, var, sp)
cursor.execute(sql, data)

Upvotes: 1

Related Questions