Re: Problem with raw query and using in

[huw_at1]

Hi,

I think I had something similar and followed this thread:

http://stackoverflow.com/questions/4574609/executing-select-where-in-using-mysqldb

I think this is what Dennis has already said so apologies if it isn't 
helpful.

Kindest regards

On Saturday, 16 November 2013 12:40:52 UTC, Thorsten wrote:
>
> Hello, 
>
> wondering if I am doing something wrong or it is a bug, using django 
> 1.5.5, but also tried with 1.6 resulting in the same problem. 
>
> When I do the following: 
>
> realms=[1] 
> data = AuctionData.objects.filter(itemid__exact=itemid,realm__in=realms) 
> data2 = AuctionData.objects.raw('SELECT * FROM auctiondata_auctiondata 
> WHERE itemid_id=%s AND realm_id in %s ',[itemid,realms]) 
>
> The first query works as expected, but the 2nd one fails, because the 
> query ends up as: 
>
> SELECT * FROM auctiondata_auctiondata WHERE itemid_id='43552' AND 
> realm_id in ('1',) 
>
> The last comma shouldnt be there, if I use more than 1 value with the 
> realms variable it works and the last comma isn't there. 
>
> Greetings, 
> Thorsten 
>
>

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/79829d6d-6f82-4de9-823d-8cbfb1cade7e%40googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

Re: Problem with raw query and using in

[Thorsten Sanders]

Thanks, I tried that, it makes the query right, but when I access the 
result in a for loop it gives now:


'Cursor' object has no attribute '_last_executed'

Think for now I just stay with using 2 variables, that works fine for 
the moment.



Am 17.11.2013 00:38, schrieb Dennis Lee Bieber:

On Sat, 16 Nov 2013 22:39:09 +0100, Thorsten Sanders
 declaimed the following:


I am using mysql and when I write it like (1) then I get int is not
iterable on the first one, but the raw works, if I do it like (1,) the
first one works, but the raw one gets again the comma at the end, tried
several ways always one of both not working, for me it looks kinda there
is some magic happening and with only 1 value the comma is not removed,
but is removed with several values.

For now I just use 2 variables one for the raw queries and one for the
other, not the best solution, but works for now.


Am 16.11.2013 16:01, schrieb Javier Guerra Giraldez:

On Sat, Nov 16, 2013 at 7:40 AM, Thorsten Sanders
 wrote:

realms=[1]
data = AuctionData.objects.filter(itemid__exact=itemid,realm__in=realms)
data2 = AuctionData.objects.raw('SELECT * FROM auctiondata_auctiondata WHERE
itemid_id=%s AND realm_id in %s ',[itemid,realms])

not sure if it's related. but most SQL adaptors use a tuple for the
IN(...) parameters, not a list.

check http://initd.org/psycopg/docs/usage.html#tuples-adaptation


I suspect the main problem is that you need to set up the realms
parameter differently -- along with the query string.

MySQL, and most other SQL systems, as I recall, expect to see

... needle in (first, second, ..., last)

MySQLdb sanitizes parameters by converting them to a string
representation, escaping any special characters, and wrapping it is '
marks.

In the raw query, unless Django has an SQL parser, the above is
generating

select * from auctiondata_auctiondata where itemid_id = 'something' and
realm_id in '[1]'

when what you need to generate is

select * from auctiondata_auctiondata where itemid_id = 'something' and
realm_id in ('1')

(MySQLdb doesn't know what datatype to put in at each %s -- it has first
converted all inputs into safe strings; that's why it uses %s for the
placeholder).

If using a list of values, you'll have to create an SQL format with a
%s for EACH value, not the list

SQL = " ...%%s and stuff in (%s)" % ", ".join("%s" for x in realms)
(if realms is [1, 3, 99] this creates   
SQL ="... %s stuff in (%s, %s, %s")

You then need to flatten the parameters

parms=[itemid].extend(realms)



--
You received this message because you are subscribed to the Google Groups "Django 
users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/5288A08E.4090605%40gmx.net.
For more options, visit https://groups.google.com/groups/opt_out.

Re: Problem with raw query and using in

[Javier Guerra Giraldez]

On Sat, Nov 16, 2013 at 7:40 AM, Thorsten Sanders
 wrote:
> realms=[1]
> data = AuctionData.objects.filter(itemid__exact=itemid,realm__in=realms)
> data2 = AuctionData.objects.raw('SELECT * FROM auctiondata_auctiondata WHERE
> itemid_id=%s AND realm_id in %s ',[itemid,realms])


not sure if it's related. but most SQL adaptors use a tuple for the
IN(...) parameters, not a list.

check http://initd.org/psycopg/docs/usage.html#tuples-adaptation

-- 
Javier

-- 
You received this message because you are subscribed to the Google Groups 
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/django-users/CAFkDaoTSsD%2B8S0rdvOnHVCBxxF6UzRCK9jzscTW4Ff677Yt_dw%40mail.gmail.com.
For more options, visit https://groups.google.com/groups/opt_out.