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
-~----------~----~----~----~------~----~------~--~---
