Re: MySQLdb compare lower

Discussion in 'Python' started by Cameron Simpson, Dec 11, 2012.

  1. On 11Dec2012 22:01, Anatoli Hristov <> 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 <>

    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
    Cameron Simpson, Dec 11, 2012
    #1
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Anatoli Hristov

    MySQLdb compare lower

    Anatoli Hristov, Dec 11, 2012, in forum: Python
    Replies:
    2
    Views:
    120
    Anatoli Hristov
    Dec 11, 2012
  2. Dennis Lee Bieber

    Re: MySQLdb compare lower

    Dennis Lee Bieber, Dec 12, 2012, in forum: Python
    Replies:
    0
    Views:
    134
    Dennis Lee Bieber
    Dec 12, 2012
  3. Cameron Simpson

    Re: MySQLdb compare lower

    Cameron Simpson, Dec 13, 2012, in forum: Python
    Replies:
    0
    Views:
    137
    Cameron Simpson
    Dec 13, 2012
  4. Dennis Lee Bieber

    Re: MySQLdb compare lower

    Dennis Lee Bieber, Dec 13, 2012, in forum: Python
    Replies:
    0
    Views:
    131
    Dennis Lee Bieber
    Dec 13, 2012
  5. Cameron Simpson

    Re: MySQLdb compare lower

    Cameron Simpson, Dec 14, 2012, in forum: Python
    Replies:
    0
    Views:
    121
    Cameron Simpson
    Dec 14, 2012
Loading...

Share This Page