Getting SQL from QuerySet

[Info Cascade]

Hi --

I am getting intermittent errors on certain database queries on our
production server.
I am having a hard time duplicating them (most of the time they run
okay, only sometimes on the production server am I receiving exception
messages),
so I want to log the SQL query itself and see what it is, and why it
might be failing.
Is there some way of seeing what SQL statement will be executed by a
QuerySet before actually executing it?
I've also set up logging on the database side, so I can look there as well.
Any other ideas for how I might debug this? See anything wrong with the
queries?
I do need to know the total number of articles returned, and .count() is
the recommended way of doing that. In most cases I'm slicing the
QuerySet after getting the count.

Any help would be very much appreciated!

~Liam

Traceback (most recent call last):

File "/usr/lib/python2.4/site-packages/django/core/handlers/base.py", line 86, in get_response
response = callback(request, *callback_args, **callback_kwargs)

File "/var/www/vhosts/site.com/django/obsidian/obs/views.py", line 219, in index
radius=radius, order_by='distance')

File "/var/www/vhosts/site.com/django/obsidian/obs/data_access.py", line 551, in search
art_result_count = art_list.count()

File "/usr/lib/python2.4/site-packages/django/db/models/query.py", line 296, in count
return self.query.get_count()

File "/usr/lib/python2.4/site-packages/django/db/models/sql/query.py", line 237, in get_count
data = obj.execute_sql(SINGLE)

File "/usr/lib/python2.4/site-packages/django/db/models/sql/query.py", line 1734, in execute_sql
cursor.execute(sql, params)

InternalError: current transaction is aborted, commands ignored until end of transaction block

The code looks like this:

if term:

if pnt:

art_list = Article.objects.filter(\

status='PUBLISHED')\

.extra(where=["title_tsv @@ plainto_tsquery(%s)"], params=[term])\

.distance(pnt)\
.order_by(*order_by)

else:

art_list = Article.objects.extra (\

where=['title_tsv @@ plainto_tsquery(%s) AND status=%s'],params=[term,'PUBLISHED'])\

.order_by(*order_by)

elif search_origin:

try:

art_list = Article.objects.filter(status='PUBLISHED',\

point__distance_lte=(pnt,D(km=radius))).distance(pnt)\

.order_by(*order_by)

except Exception, e:

raise

else:

raise

The exception comes when I execute the query by getting the count

art_result_count = art_list.count()
if rows:
art_list = art_list[start:start+rows]

[flo...@gmail.com]

I think that

>>> print my_query_set.query.as_sql()

should do the trick.

Thanks,
Eric Florenzano

On Dec 30, 12:11 pm, Info Cascade <informationcasc...@gmail.com>
wrote:

[Russell Keith-Magee]

On Wed, Dec 31, 2008 at 5:11 AM, Info Cascade
<informati...@gmail.com> wrote:
>
> Hi --
>
> I am getting intermittent errors on certain database queries on our
> production server.
> I am having a hard time duplicating them (most of the time they run
> okay, only sometimes on the production server am I receiving exception
> messages),
> so I want to log the SQL query itself and see what it is, and why it
> might be failing.
> Is there some way of seeing what SQL statement will be executed by a
> QuerySet before actually executing it?

Yes. If you have the query:

>>> Author.objects.filter(...)

then the following:

>>> print Author.objects.filter(...).query.as_sql()

will print the SQL that results from the query.

Another approach is to inspect the query log. If debug is enabled in
your settings, then:

>>> from django.db import connection
>>> connection.queries

will print a list of all queries that have been passed to the database.

Yours,
Russ Magee %-)

[Matías Costa]

On Tue, Dec 30, 2008 at 9:11 PM, Info Cascade
<informati...@gmail.com> wrote:
>
> Hi --
>
> I am getting intermittent errors on certain database queries on our
> production server.
> I am having a hard time duplicating them (most of the time they run
> okay, only sometimes on the production server am I receiving exception
> messages),
> so I want to log the SQL query itself and see what it is, and why it
> might be failing.

The Django Debug Toolbar does that and much more

http://rob.cogit8.org/blog/2008/Sep/19/introducing-django-debug-toolbar/