Executing raw SQL with python3/Django 1.7

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!

Solution

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.