Best way to implement SQL select query

[Rob Goldsmith]

I tried posting this earlier, but it doesn't seem to have appeared yet - so apologies if this comes up twice.

I am writing a web2py application which accepts a UK postcode then provides information about nearby postcodes. I have tried two methods, both of which work fine when the number of nearby postcodes is relatively few (less than a hundred).

Method 1 uses the contains method as follows

query &= db.t_properties.f_postcode.contains(result)

where result is the list of nearby postcodes

Method 2 does the query first and then uses exclude to eliminate the unwanted rows as follows

rows.exclude(lambda row: row.f_postcode not in result)

However, when the list of postcodes is larger (>about 100), both methods are failing. Method 1 appears to fail because of inherent limitations of sqlite (https://sqlite.org/limits.html - SQLITE_LIMIT_VARIABLE_NUMBER - albeit that refers to a limit of 999 rather than 99). Method 2 fails with the message:

<type 'exceptions.RuntimeError'> maximum recursion depth exceeded in cmp

Now, I'm pretty sure that I can solve it by moving to MySQL or another db but I'm pretty sure that I'm not doing the query in the optimal way. Can anyone give any advice as to the correct / most efficient way to accomplish this?

thanks 

Rob.

[Niphlod]

what field type is f_postcode ?
what type is the result variable ?

[Joe Barnhart]

You probably want "belongs" instead of "contains".  It's a common mistake (which I make regularly myself).  The "contains" function in web2py is to test if strings are contained in a field, i.e. db.mytable.myfield.contains("joe") is like db.mytable.myfield.like("%joe%").  

"Belongs" is used to test for membership in a list, such as db.mytable.myfield.belongs([95050, 95051, 95052])

Hope this helps.

-- Joe

[Rob Goldsmith]

Thanks Joe - changing the query to belongs did the trick although the query is still very slow when the list is long - but I'm not sure if that's primarily because I'm using sqlite at this stage?

Niphlod - f_postcode is of type text and result is a list of strings (e.g. ['W1 2TR', 'SW1 5AB' ...] 

[Niphlod]

you can use "contains" for 'text' fields if you want it turned to a "LIKE '%whatever%' query, or as a "find me where 'whatever' is" in a 'list:string' field.
at this point a bit of code wouldn't hurt :D