Django - Passing multiple parameters into raw queries with %s format

Question

In the project I am working on before we used a custom connector for MariaDB but now I have to use the default connector for MySQL in Django.

The previous MariDb connector used ? For parameterization but MySQL for Django uses %s instead.

With the MariaDB you could something like this:

sd_query = create_update_query(interpretation, record_id, tableName)
cursor.execute(sd_query, tuple(interpretation.values()) )

how to do it with a %s parameterization?

I couldn't find anything in the official documentation.

Okay... is there a specific error you are getting with what you have tried? — Iain Shelvington
– Iain Shelvington, Commented May 31, 2021 at 4:36

Abdul Aziz Barkat · Accepted Answer · 2021-05-31 10:18:01Z

2

You seem to be confused with the solution I had given you in your previous question, there I had used '{}%'.format(some_value) because you had a value and wanted to make a LIKE query with it (<SOME_VALUE>%), so I had performed that string formatting to make the string of the proper needed form. You don't need to do that here, you can simply pass the tuple, hence what you do here would be the same as what you did with your MariaDB cursor, with the only difference being you use %s instead of ? in the placeholders:

sd_query = create_update_query(interpretation, record_id, tableName)
sd_data = tuple(interpretation.values())
cursor.execute(sd_query, sd_data)

answered May 31, 2021 at 10:18

Abdul Aziz Barkat

22.3k9 gold badges107 silver badges95 bronze badges

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

Comments

Hq Shiblu · Accepted Answer · 2021-05-31 04:57:20Z

0

If you are asking about how to prepare the SQL then it's a Python specific question, not Django. You can do something like this-

update_query = "UPDATE `db-dummy`.s_data SET s_id = {}, h_loss = {} WHERE record_id = {}"    
update_query = update_query.format(s_id_value, h_loss_value, 5877)

Now, if you want to execute it in Django you need to import cursor from connection.

from django.db import connection
cursor = connection.cursor()

Then execute it with-

cursor.execute(update_query)

If your SQL statement returns something then you can use

records = cursor.fetchall()

Then you will be able to loop through the 'records' variable.

answered May 31, 2021 at 4:57

Hq Shiblu

915 bronze badges

1 Comment

Abdul Aziz Barkat Over a year ago

Repeat after me: Making queries using String formatting / concatenation is BAD. It makes you vulnerable to SQL Injection.

Ale Ortega · Accepted Answer · 2021-06-02 06:30:23Z

0

Normal python string formating is not enough for this task, someone could try to do a query injection, I mean, the posibility still there (even if you secured the query sintax). Django already takes care of this for you so you don´t have to think too much about it, check this part of Django Docs article.

answered Jun 2, 2021 at 6:30

Ale Ortega

557 bronze badges

Collectives™ on Stack Overflow

Django - Passing multiple parameters into raw queries with %s format

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related