Thanks for your quick reply. I have been searching for a long time
for good documentation on using IN and paramaters with no luck. When
it worked with a list I assumed that was correct. Could you clarify
an example of transforming a list of items into something mysqldb
would accept then executing it? I think you are saying that if I have
2 items in the list I need to manually specify:
zips = ['77777', '55544']
sql = """
SELECT id
FROM Event
WHERE zip_code IN %s, %s"""
cursor.execute(sql, (zips[0], zips[1]))
and do you need to specify the parens around the params? I tried
passing in a string like '("33333", "33344")' and it didn't error, but
it didn't return any results either.
On Jul 8, 8:00 pm, Malcolm Tredinnick <[EMAIL PROTECTED]>
wrote:
> On Tue, 2008-07-08 at 16:56 -0700, AJ wrote:
> > I've run into an issue using a manager for a model to run some custom
> > sql.
> > A dumbed down version of the code looks something like this:
> > ------------------------------------------------------------------------
> > cursor = connection.cursor()
> > # Get list of zipcodes
> > zips = [zip.zipcode for zip in
> > Zip_Code.objects.filter(lat__range=(min_lat, max_lat),
> > lon__range=(min_lon, max_lon))]
>
> > sql = """
> > SELECT id
> > FROM Event
> > WHERE zip_code IN %s"""
> > cursor.execute(sql, (zips,))
>
> > ------------------------------------------------------------------------
> > The Zipcode field is a VARCHAR(10) in the database, so the zipcodes in
> > the list are unicode strings. If I run the query this way, I get 0
> > results. Although If I copy the sql it generates and run it I get
> > several results.
>
> > The interesting this is if I cast the zipcode to an integer then the
> > query works. I also tried casting each zipcode to a regular string
> > but it also returns 0 results. So it looks like in order for the IN
> > function to work for the MySQLdb backend it must be a list of
> > integers. This doesn't work so well for zipcodes that begin with 0's
> > like Burlington VT which is "05401".
>
> > Is there some trick I am missing, or is this a genuine bug in the
> > python MySQLdb module?
>
> You're making the assumption that you can pass a list or a tuple as a
> parameter to the query and that the DB wrapper will magically turn it
> into the right thing. This isn't correct (it happens to work by accident
> in some cases, like integers where str(some_list) is valid SQL).
>
> You need to be passing in N parameters with something like
>
> ', '.join(["%s"] * len(params))
>
> used to construct the placeholders.
>
> 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
-~----------~----~----~----~------~----~------~--~---
