Re: Custom SQL query with LIKE statement
> Then using distinct() might do the trick: > >entries = entries.distinct() I try this too in the past, but it doesn't work, because I want to filter out duplicate data in column note. In the other columns might be different values, so in query result isn't 2 or more absolutely identical rows. Am I right, that distinct filter out only identical rows in result? > Or, if you stick with using raw SQL, it's likely better to do > >SELECT DISTINCT note >FROM journals_journal >WHERE length(note) > 0 and note like 'whatever%' >ORDER BY note > > as this tells the DB exactly what your intentions are, and it can > optimize accordingly. Thank you, my SQL knowledge isn't very strong. I compare your and my (GROUP BY) version with EXPLAIN ANALYZE statement in DB, and your variant is faster. I rewrite my code. Thank you again. Regards Michal --~--~-~--~~~---~--~~ 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?hl=en -~--~~~~--~~--~--~---
Re: Custom SQL query with LIKE statement
>> I'm not sure why you're GROUPing BY "note" as you don't have any >> aggregate functions in play in your example code. > > I need grouping, because in the table could be several rows with the > same note and I want only "unique" note results. Then using distinct() might do the trick: entries = entries.distinct() Or, if you stick with using raw SQL, it's likely better to do SELECT DISTINCT note FROM journals_journal WHERE length(note) > 0 and note like 'whatever%' ORDER BY note as this tells the DB exactly what your intentions are, and it can optimize accordingly. Unfortunately, for all these cases, Length() is a non-standard/non-portable function and is sometimes len() but othertimes length() depending on your back-end. Sigh. :( If one needed to workaround that and could assume that you had some sort of alpha-numeric data in the field, it could be reduced to a single test of WHERE note ILIKE '%[a-z0-9]%' or ...filter(note__icontains='[a-z0-9]') Just a few more ideas, -tim --~--~-~--~~~---~--~~ 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?hl=en -~--~~~~--~~--~--~---
Re: Custom SQL query with LIKE statement
Tim Chase wrote: > I'm not sure why you're GROUPing BY "note" as you don't have any > aggregate functions in play in your example code. I need grouping, because in the table could be several rows with the same note and I want only "unique" note results. Regards Michal --~--~-~--~~~---~--~~ 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?hl=en -~--~~~~--~~--~--~---
Re: Custom SQL query with LIKE statement
> This should be writable in a slightly more Djangoic (I suppose if > Python code is Pythonic, Django code is Djangoic? Djanonic? > Djangonical? Djangoish?) fashion: > my vote would be to user "superfly," as in: This should be writable in a slightly more superfly fashion >entries = Journal.objects.filter(note__startswith = q) >entries = entries.extra(where='length(note) > 0') > I've spent some time recently rewriting some of my custom managers to use the extra method, and I highly recommend it. I know it may not be possible in all circumstances, but for things like sorting and using generic views it made my life a lot easier. I do suggest spending time playing with it in the shell to get the hang of it. So, nothing of real substance to impart here, just to encourage you to check out the extras() method. --~--~-~--~~~---~--~~ 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?hl=en -~--~~~~--~~--~--~---
Re: Custom SQL query with LIKE statement
> I need to write some custom SQL in Django:
>
>from django.db import connection
>cursor = connection.cursor()
>cursor.execute("SELECT note FROM journals_journal WHERE LENGTH(note)
> > 0 AND note LIKE %s GROUP BY note ORDER BY note;", [q+'%'])
>
> where q is string, for example 'foo'.
This should be writable in a slightly more Djangoic (I suppose if
Python code is Pythonic, Django code is Djangoic? Djanonic?
Djangonical? Djangoish?) fashion:
entries = Journal.objects.filter(note__startswith = q)
entries = entries.extra(where='length(note) > 0')
(you might have to tweak that "length(note) > 0" bit, as Django
mungs field-names a bit in the query).
I'm not sure why you're GROUPing BY "note" as you don't have any
aggregate functions in play in your example code.
http://www.djangoproject.com/documentation/0.95/db-api/#startswith
and the "where" section of
http://www.djangoproject.com/documentation/0.95/db-api/#extra-select-none-where-none-params-none-tables-none
> I have problems with it so I print out connection.queries and I was
> suprised, because foo% wasn't surrounded by ' or " :
>
>SELECT note FROM journals_journal WHERE LENGTH(note) > 0 AND note
> LIKE foo% GROUP BY note ORDER BY note;'
>
> Is this normal? Isn't there possibility for SQL inject?
As Malcom noted, it's not quite what was sent to the DB.
However, it /would/ be nice for debugging purposes to have the
*exact* query sent to the DB. However, this would have to be
supported on a per-backend basis. :(
-tim
--~--~-~--~~~---~--~~
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?hl=en
-~--~~~~--~~--~--~---
Re: Custom SQL query with LIKE statement
>> Is this normal? Isn't there possibility for SQL inject? > > No, because of the reason described in this email: > > http://groups.google.com/group/django-users/msg/0f3f9d729413ee32 Thak you for quick answer Malcolm. Regards Michal --~--~-~--~~~---~--~~ 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?hl=en -~--~~~~--~~--~--~---
Re: Custom SQL query with LIKE statement
On Thu, 2007-06-21 at 13:47 +0200, Michal wrote:
> Hello,
> I need to write some custom SQL in Django:
>
>from django.db import connection
>cursor = connection.cursor()
>cursor.execute("SELECT note FROM journals_journal WHERE LENGTH(note)
> > 0 AND note LIKE %s GROUP BY note ORDER BY note;", [q+'%'])
>
> where q is string, for example 'foo'.
>
> I have problems with it so I print out connection.queries and I was
> suprised, because foo% wasn't surrounded by ' or " :
>
>SELECT note FROM journals_journal WHERE LENGTH(note) > 0 AND note
> LIKE foo% GROUP BY note ORDER BY note;'
>
> Is this normal? Isn't there possibility for SQL inject?
No, because of the reason described in this email:
http://groups.google.com/group/django-users/msg/0f3f9d729413ee32
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?hl=en
-~--~~~~--~~--~--~---
Custom SQL query with LIKE statement
Hello,
I need to write some custom SQL in Django:
from django.db import connection
cursor = connection.cursor()
cursor.execute("SELECT note FROM journals_journal WHERE LENGTH(note)
> 0 AND note LIKE %s GROUP BY note ORDER BY note;", [q+'%'])
where q is string, for example 'foo'.
I have problems with it so I print out connection.queries and I was
suprised, because foo% wasn't surrounded by ' or " :
SELECT note FROM journals_journal WHERE LENGTH(note) > 0 AND note
LIKE foo% GROUP BY note ORDER BY note;'
Is this normal? Isn't there possibility for SQL inject?
Regards
Michal
PS: I am using PostgreSQL 7.4.12 and psycopg-1.1.21
--~--~-~--~~~---~--~~
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?hl=en
-~--~~~~--~~--~--~---
