How to retrieve the real SQL from the Django logger?

Question

I am trying to analyse the SQL performance of our Django (1.3) web application. I have added a custom log handler which attaches to django.db.backends and set DEBUG = True, this allows me to see all the database queries that are being executed.

However the SQL is not valid SQL! The actual query is select * from app_model where name = %s with some parameters passed in (e.g. "admin"), however the logging message doesn't quote the params, so the sql is select * from app_model where name = admin, which is wrong. This also happens using django.db.connection.queries. AFAIK the django debug toolbar has a complex custom cursor to handle this.

Update For those suggesting the Django debug toolbar: I am aware of that tool, it is great. However it does not do what I need. I want to run a sample interaction of our application, and aggregate the SQL that's used. DjDT is great for showing and shallow learning. But not great for aggregating and summarazing the interaction of dozens of pages.

Is there any easy way to get the real, legit, SQL that is run?

score 2 · Answer 1 · answered Jan 28 '13 at 17:02

2

Check out django-debug-toolbar. Open a page, and a sidebar will be displayed with all SQL queries plus other information.

answered Jan 28 '13 at 17:02

msc

3,780
1
22
27

I have added an update as to why DjDT doesn't work for this problem. – Amandasaurus Jan 28 '13 at 18:03

cms_mgr · Answer 2 · 2013-01-28T17:36:34.650

0

Every QuerySet object has a 'query' attribute. One way to do what you want (I accept perhaps not an ideal one) is to chain the lookups each view is producing into a kind of scripted user-story, using Django's test client. For each lookup your user story contains just append the query to a file-like object that you write at the end, for example (using a list instead for brevity):

l = []
o = Object.objects.all()
l.append(o.query)

edited Jan 28 '13 at 17:36

answered Jan 28 '13 at 17:02

cms_mgr

1,977
2
17
31

I've updated question with why DjDT is not good for this goal. – Amandasaurus Jan 28 '13 at 17:09
To ensure I understand the update correctly, is what you mean by 'sample interaction' that you intend to crawl several of your own pages n times and see all the SQL queries the traversal generates? – cms_mgr Jan 28 '13 at 17:14
Yes, I plan to crawl several of the pages of our site, do common interactions, post forms, etc. I want to see the total SQL for the whole of this 'sample workflow'. Not just for one page. – Amandasaurus Jan 28 '13 at 17:23

score 0 · Answer 3 · answered Jan 28 '13 at 17:18

select * from app_model where name = %s is a prepared statement. I would recommend you to log the statement and the parameters separately. In order to get a wellformed query you need to do something like "select * from app_model where name = %s" % quote_string("user") or more general query % map(quote_string, params).

Please note that quote_string is DB specific and the DB 2.0 API does not define a quote_string method. So you need to write one yourself. For logging purposes I'd recommend keeping the queries and parameters separate as it allows for far better profiling as you can easily group the queries without taking the actual values into account.

Yes, but *how* I do log that preparated statement? Django doesn't give me the original prepared query. — Amandasaurus, Jan 28 '13 at 17:22
Whopsies. I was pretty sure it was logged separately. You are right, it is not there. That is awkward. — bikeshedder, Jan 28 '13 at 17:25

score 0 · Answer 4 · answered Jan 28 '13 at 17:27

0

The Django Docs state that this incorrect quoting only happens for SQLite.

https://docs.djangoproject.com/en/dev/ref/databases/#sqlite-connection-queries

Have you tried another Database Engine?

answered Jan 28 '13 at 17:27

bikeshedder

7,337
1
23
29

How to retrieve the real SQL from the Django logger?

4 Answers4