MySQLdb compare lower

[Cameron Simpson]

On 11Dec2012 22:01, Anatoli Hristov <tolidtm at gmail.com> wrote:
| Excuse me for the noob question, but is there a way to compare a field
| in mysql as lower() somehow?
| 
| I have a situation where I compare the SKU in my DB and there are some
| SKU that are with lowercase and some with uppercase, how can I solve
| this in your opinion ?
| 
| def Update_SQL(price, sku):
| 
|     db = MySQLdb.connect("localhost","getit","opencart",
| use_unicode=True, charset="utf8")
|     cursor = db.cursor()
|     sql = "UPDATE product SET price=%s WHERE sku=%s"
|     cursor.execute(sql, (price, sku))
|     db.commit()
|     db.close()

Let the db do the work. Untested example:

  sql = "UPDATE product SET price=%s WHERE LOWER(sku)=LOWER(%s)"

See:

  http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

However I would point out that this form of the SQL requires a scan of
the whole db table per update. The plain test against "sku" instead of
"LOWER(sku)" lets the db use an index on "sku" to access the relevant
row directly; MUCH more efficient (if you've got an index on "sku",
of course).

You can only run with the first, efficient, form if the sku values in
the db are normalised. For example, all upper case or all lower case.

Supposing that your sku has been normalised to all lower case (i.e. the
data entry phase converts skus to lowercase when inserting data into the
db), then you can write:

  sql = "UPDATE product SET price=%s WHERE sku=LOWER(%s)"

which can use the index on "sku" - efficient. Here's you're normalising
the test value (the %s part) to match the db content, which should
alreay be lowercase.

_If_ you know SKUs can always be normalised to lower case (or upper
case, your call provided it is consistent), you can normalise the values
in the db if they've been put in unnormalised. And then get on with your
life as above.

Cheers,
-- 
Cameron Simpson <cs at zip.com.au>

Hal, open the file
Hal, open the damn file, Hal
open the, please Hal
- Haiku Error Messages http://www.salonmagazine.com/21st/chal/1998/02/10chal2.html