Executing raw SQL with python3/Django 1.7

Question

I am working with simple_history because I need historical data of my models. I have a model called po_staff which has a "historical" model called pool_historicalpo_staff. Now I want to get a queryset based on pool_historicalpo_staff with the last record for each employee (I tried to make it as simple as possible, because the real query is much more complex). I am working currently on SQLLite

The historical model contains the following data:

ID staff_nr valid      staff_firstname staff_lastname
1  111      01/01/2014 Firstname1      Lastname1
2  3        01/01/2014 Firstname2      Lastname2
2  3        01/04/2014 Firstname2      Lastname2_new #Employee has changed his Lastname

This code works fine (ordered alpabetical):

qs = po_staff.history.raw ('SELECT * FROM pool_historicalpo_staff '
     'WHERE clientID_id is %s and companyID_id is %s '
     'GROUP BY id '
     'ORDER BY staff_name, staff_surname ',
     [client_id, company_id])

I have to use "id" for grouping, because staff_nr can be changed from the user (but it's still the same employee).

PROBLEM 1: The user can choose the order of the result between alpabetical and staff_id

To make this possible, I added a 3th argument to the query:

if order == 1:
    order_by = "staff_name, staff_surname"
else:
    order_by = "staff_nr"
qs = po_staff.history.raw ('SELECT * FROM pool_historicalpo_staff '
     'WHERE clientID_id is %s and companyID_id is %s '
     'GROUP BY id '
     'ORDER BY %s ',
     [client_id, company_id, order_by])

The query works but instead of an alphabetical order I get it ordered by "id".

PROBLEM 2: The query has to be executed for other models too. Just the model and the order_by will be different. I don't want to write for every model the nearly same query, therefore I tried to make it more flexibel:

model      = ....  # instance of current model
order_by   = ....  # gets the choosen order for current model, p.e. "staff_name, staff_surname"
model_name = ....  # gets the model name, p.e. "pool_historicalpo_staff"

qs = model.history.raw ('SELECT * FROM %s '
     'WHERE clientID_id is %s and companyID_id is %s '
     'GROUP BY id '
     'ORDER BY %s ',
     [model_name, client_id, company_id, order_by])

I get an error executing this query:

Request Method:     GET
Request URL:    http://127.0.0.1:8000/framework/21/?view=kanban
Django Version:     1.7
Exception Type:     OperationalError
Exception Value:    near "?": syntax error
Exception Location:     c:\python34\lib\site-packages\django\db\backends\sqlite3\base.py in     
                        execute, line 485
Python Executable:  c:\python34\python.EXE
Python Version:     3.4.0

I hope I explained everything clearly. I am new to django y thats my first raw SQL

Thanks a lot for you help!

Community · Accepted Answer · 2017-05-23 12:27:17Z

1

This seems similar to Django: MySQL syntax error when passing parameters to raw SQL query

Short answer: you can only pass parameters to the query. To support substituting parts of the SQL syntax like table names, you will need to build the query in two passes, only passing the actual parameters to the query in the second pass.

Hope this helps.

edited May 23, 2017 at 12:27

CommunityBot

11 silver badge

answered Jan 11, 2015 at 13:29

bimsapi

5,0652 gold badges22 silver badges27 bronze badges

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

3 Comments

sascha2014 Over a year ago

thanks bimsapi. But how to I do that (sorry, I am a new)? Do you have a short example? Thanks a lot!!

bimsapi Over a year ago

There is an example in the provided link, as well. Basically something like sql = 'select * from {} where foo = %s'.format(table_name), followed by model.history.raw(sql, params). First pass uses Python format to insert the table name (which uses the {} placeholder - see docs.python.org/2/library/string.html#string-formatting), second pass is actually running the query (which uses the %s placeholder).

sascha2014 Over a year ago

Thanks, I hoped to avoid string formatting of queries but if it's the only way....

Collectives™ on Stack Overflow

Executing raw SQL with python3/Django 1.7

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related