Re: viewing generated SQL without running the query

[larry.mart...@gmail.com]

On Feb 21, 7:32 pm, diafygi  wrote:
> There's a previous thread about this[1], but it was closed back in
> 2006 without resolution. So I'd like to check back in and see if there
> is a way to get a complete query string without executing the query.
>
> At first, I thought I could just use the QuerySet.query.__str__(), but
> that does not put quotes around dates, so MySQL returns a warning when
> trying to execute it. I filed a bug report[2], but apparently
> QuerySet.query.__str__() isn't supposed to return a valid query
> string.
>
> So is there a way to get the compiled query string without executing
> the query (including when debug=False)?
>
> Thanks!
> Daniel
>
> [1] -http://groups.google.com/group/django-users/browse_thread/thread/37a6...
> [2] -https://code.djangoproject.com/ticket/17741

What I usually do is set a breakpoint with pdb at the appropriate
place ('import pdb; pdb.set_trace()') then run the devel server, and
then you hit the breakpoint do 'print queryset.query'

There's also this:

https://docs.djangoproject.com/en/dev/faq/models/#how-can-i-see-the-raw-sql-queries-django-is-running

But I've never tried it.

-larry

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to 
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: viewing generated SQL without running the query

[Kelly Nicholes]

Can't you get a queryset, like

print MyObject.objects.all().query
?

On Feb 21, 7:32 pm, diafygi  wrote:
> There's a previous thread about this[1], but it was closed back in
> 2006 without resolution. So I'd like to check back in and see if there
> is a way to get a complete query string without executing the query.
>
> At first, I thought I could just use the QuerySet.query.__str__(), but
> that does not put quotes around dates, so MySQL returns a warning when
> trying to execute it. I filed a bug report[2], but apparently
> QuerySet.query.__str__() isn't supposed to return a valid query
> string.
>
> So is there a way to get the compiled query string without executing
> the query (including when debug=False)?
>
> Thanks!
> Daniel
>
> [1] -http://groups.google.com/group/django-users/browse_thread/thread/37a6...
> [2] -https://code.djangoproject.com/ticket/17741

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to 
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: [re-open] viewing generated SQL without running the query

[Russell Keith-Magee]

On 22/02/2012, at 10:32 AM, diafygi wrote:

> There's a previous thread about this[1], but it was closed back in
> 2006 without resolution. So I'd like to check back in and see if there
> is a way to get a complete query string without executing the query.
> 
> At first, I thought I could just use the QuerySet.query.__str__(), but
> that does not put quotes around dates, so MySQL returns a warning when
> trying to execute it. I filed a bug report[2], but apparently
> QuerySet.query.__str__() isn't supposed to return a valid query
> string.
> 
> So is there a way to get the compiled query string without executing
> the query (including when debug=False)?
> 


Technically, yes; practically, no.

The problem is that the SQL argument quoting isn't something that is under 
Django's control. The PyDB interface specifies (with good reason) that you pass 
SQL to the database using parameterized SQL, with a separate tuple of 
arguments. The PyDB backend then handles the process of escaping, quoting, or 
whatever else is required to combine the SQL and arguments to make sure that 
the final SQL is valid.

Unfortunately, what the PyDB interface *doesn't* provide is a way to extract 
the actual SQL that was (or will be) executed.

So - in order for Django to provide a 100% guaranteed correct output for 
Queryset.query.__str()__, we would need to re-implement the quoting and 
escaping feature of the PyDB backend -- and we'd need to do so for every 
backend (because quoting and escaping requirements are slightly different for 
every backend). This would be complex to implement, would be error prone, and 
would lead to even more confusing errors whenever the quoting/escaping failed 
to implement exactly what the PyDB backend implements.

The alternative -- which is what Django has opted to do -- is to provide SQL 
output that is indicative of what was (or would be) executed, and accept that 
sometimes that SQL won't run verbatim. Incorrect quoting is more than 
sufficient for eyeballing whether a query is correct; if you actually need to 
execute the SQL it's not *that* hard to manually correct the quoting.

Yes, it's annoying. Yes, in an ideal world, SQL output would be guaranteed 
valid. However, in this case, pragmatism takes precedence.

Yours,
Russ Magee %-)


-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to 
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

[re-open] viewing generated SQL without running the query

[diafygi]

There's a previous thread about this[1], but it was closed back in
2006 without resolution. So I'd like to check back in and see if there
is a way to get a complete query string without executing the query.

At first, I thought I could just use the QuerySet.query.__str__(), but
that does not put quotes around dates, so MySQL returns a warning when
trying to execute it. I filed a bug report[2], but apparently
QuerySet.query.__str__() isn't supposed to return a valid query
string.

So is there a way to get the compiled query string without executing
the query (including when debug=False)?

Thanks!
Daniel

[1] - 
http://groups.google.com/group/django-users/browse_thread/thread/37a6222006d0633b/
[2] - https://code.djangoproject.com/ticket/17741

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com.
To unsubscribe from this group, send email to 
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/django-users?hl=en.

Re: viewing generated SQL without running the query

[Gary Wilson]

Malcolm Tredinnick wrote:
> On Tue, 2006-08-29 at 03:53 +, Gary Wilson wrote:
> > I see that there is a _get_sql_clause() method, but is there a function
> > that will return the constructed query string?
>
> Deja vu. I tried to implement something like this a little while back.
> The thing that is hard about getting it to work is that we rely on the
> database backend to do some of the quoting and function substitution for
> us. As far as I know, there's no generic way to pull that information
> out of each backend. I wasn't really keen in duplicating the logic from
> the queryset stuff, however this may be easier with the query.py rewrite
> I'm doing (I just thought of that point now, so I really mean "may" --
> need to look into it).

Ah, I guess I didn't notice the using of the db backend to do some work
for us.  That does make things a bit harder.


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users
-~--~~~~--~~--~--~---

Re: viewing generated SQL without running the query

[Malcolm Tredinnick]

On Tue, 2006-08-29 at 03:53 +, Gary Wilson wrote:
> I see that there is a _get_sql_clause() method, but is there a function
> that will return the constructed query string?

Deja vu. I tried to implement something like this a little while back.
The thing that is hard about getting it to work is that we rely on the
database backend to do some of the quoting and function substitution for
us. As far as I know, there's no generic way to pull that information
out of each backend. I wasn't really keen in duplicating the logic from
the queryset stuff, however this may be easier with the query.py rewrite
I'm doing (I just thought of that point now, so I really mean "may" --
need to look into it).

Regards,
Malcolm


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users
-~--~~~~--~~--~--~---

Re: viewing generated SQL without running the query

[DavidA]

Gary Wilson wrote:
> I see that there is a _get_sql_clause() method, but is there a function
> that will return the constructed query string?

You can just do the same construction that's done in
django/db/models/query.py:

>>> from danet.blog.models import Post, Tag
>>> qs = Tag.objects.filter(title__icontains='a', description__icontains='n')
>>> cols, sql, args = qs._get_sql_clause()
>>> "SELECT %s %s" % (', '.join(cols), sql % tuple(args))
'SELECT `blog_tag`.`slug`, `blog_tag`.`title`, `blog_tag`.`description`
 FROM `b
log_tag` WHERE (`blog_tag`.`description` LIKE %n% AND
`blog_tag`.`title` LIKE %a
%) ORDER BY `blog_tag`.`title` ASC'
>>>

There's some special handling for DISTINCT if you look at
django.db.models.query.iterator(), but the snippet above is close
enough. After a while you get used to just looking at the list from
_get_sql_clause() directly and can just add the "SELECT" and replace
the args in your head.
-Dave


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users
-~--~~~~--~~--~--~---

viewing generated SQL without running the query

[Gary Wilson]

I see that there is a _get_sql_clause() method, but is there a function
that will return the constructed query string?


--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/django-users
-~--~~~~--~~--~--~---