MySQLdb LIKE '%%%s%%' problem

Discussion in 'Python' started by gumbah, Jan 14, 2009.

  1. gumbah

    gumbah Guest

    I have this really strange problem. I hope someone can help:

    I am trying to update a database like so:

    UPDATE `tablename` set fieldx='test' WHERE flfieldx = null and fieldy
    like '%certainvalue%'

    My Python code looks like this:

    fillsql = "UPDATE `tablename` set fieldx='test' WHERE flfieldx = null
    and fieldy like '%%%s%%' " % certainvalue
    print fillsql
    cursor.execute(fillsql)

    #also tried:
    #cursor.execute("UPDATE `tablename` set fieldx='test' WHERE flfieldx =
    null and fieldy like %s ", "%%%s%%" % certainvalue)

    But it doesn't work... But when i copy and past the SQL (printed by
    "print fillsql" line) and execute that in phpMyAdmin, it does work!!!

    Can anyone tell me what i am doing wrong??

    Thanks in advance!!
     
    gumbah, Jan 14, 2009
    #1
    1. Advertising

  2. gumbah

    John Machin Guest

    On Jan 14, 7:31 pm, gumbah <> wrote:
    > I have this really strange problem. I hope someone can help:
    >
    > I am trying to update a database like so:
    >
    > UPDATE `tablename` set fieldx='test' WHERE flfieldx = null and fieldy
    > like '%certainvalue%'
    >
    > My Python code looks like this:
    >
    > fillsql = "UPDATE `tablename` set fieldx='test' WHERE flfieldx = null
    > and fieldy like '%%%s%%' " % certainvalue


    call this trial 1

    > print fillsql
    > cursor.execute(fillsql)
    >
    > #also tried:
    > #cursor.execute("UPDATE `tablename` set fieldx='test' WHERE flfieldx =
    > null and fieldy like %s ", "%%%s%%" % certainvalue)


    call this trial 2

    >
    > But it doesn't work... But when i copy and past the SQL (printed by
    > "print fillsql" line) and execute that in phpMyAdmin, it does work!!!


    You don't say what "doesn't work" means ... did you get exceptions, or
    just silently not updating?

    >
    > Can anyone tell me what i am doing wrong??


    Well the "trial 1" method is guaranteed not to work if certainvalue
    contains an apostrophe e.g. "O'Reilly". In any case, you should never
    build your own SQL statement like that; use the "trial 2" method -- it
    will do whatever is necessary in the way of reformatting or escaping
    your input.

    I know near nothing about mySQLdb, but here are some comments based on
    general SQL experience:
    (1) `tablename` isn't SQL syntax that I've seen before; perhaps it
    works in phpMyAdmin but not in cursor.execute()
    (2) similarly = NULL ... I'd expect IS NULL
    (3) It is updating but your script and your phpMyAdmin session are
    pointing at different instances of the database
    (4) For trial 2 method, shouldn't the 2nd arg of cursor.execute()
    should be a sequence e.g. ("%%%s%%" % certainvalue, ) ?

    HTH
    John
     
    John Machin, Jan 14, 2009
    #2
    1. Advertising

  3. gumbah

    gumbah Guest

    Hi John,

    thanks a lot for your quick reply!

    I tried all of your suggestions but none of them work... I have a clue
    on why it is failing: MySQLdb seems to quote the % characters or
    something...

    Even when i do:
    cursor.execute("UPDATE tablename set fieldx='test' WHERE flfieldx is
    null and fieldy like '%therealvalue%' "

    or:
    cursor.execute("UPDATE tablename set fieldx='test' WHERE flfieldx is
    null and fieldy like '%%therealvalue%%' " # escaping the %

    it is not updating the database...

    Maybe I am completely overlooking something, but I am pretty lost
    here... Googling this it seems as if no one is using "LIKE '%value%'"
    type queries with Python & mySQL...

    Anyone any other thoughts?

    regards,
    Joost


    On 14 jan, 10:14, John Machin <> wrote:
    > On Jan 14, 7:31 pm, gumbah <> wrote:
    >
    > > I have this really strange problem. I hope someone can help:

    >
    > > I am trying to update a database like so:

    >
    > > UPDATE `tablename` set fieldx='test' WHERE flfieldx = null and fieldy
    > > like '%certainvalue%'

    >
    > > My Python code looks like this:

    >
    > > fillsql = "UPDATE `tablename` set fieldx='test' WHERE flfieldx = null
    > > and fieldy like '%%%s%%' " % certainvalue

    >
    > call this trial 1
    >
    > > print fillsql
    > > cursor.execute(fillsql)

    >
    > > #also tried:
    > > #cursor.execute("UPDATE `tablename` set fieldx='test' WHERE flfieldx =
    > > null and fieldy like %s ", "%%%s%%" % certainvalue)

    >
    > call this trial 2
    >
    >
    >
    > > But it doesn't work... But when i copy and past the SQL (printed by
    > > "print fillsql" line) and execute that in phpMyAdmin, it does work!!!

    >
    > You don't say what "doesn't work" means ... did you get exceptions, or
    > just silently not updating?
    >
    >
    >
    > > Can anyone tell me what i am doing wrong??

    >
    > Well the "trial 1" method is guaranteed not to work if certainvalue
    > contains an apostrophe e.g. "O'Reilly". In any case, you should never
    > build your own SQL statement like that; use the "trial 2" method -- it
    > will do whatever is necessary in the way of reformatting or escaping
    > your input.
    >
    > I know near nothing about mySQLdb, but here are some comments based on
    > general SQL experience:
    > (1) `tablename` isn't SQL syntax that I've seen before; perhaps it
    > works in phpMyAdmin but not in cursor.execute()
    > (2) similarly = NULL ... I'd expect IS NULL
    > (3) It is updating but your script and your phpMyAdmin session are
    > pointing at different instances of the database
    > (4) For trial 2 method, shouldn't the 2nd arg of cursor.execute()
    > should be a sequence e.g. ("%%%s%%" % certainvalue, ) ?
    >
    > HTH
    > John
     
    gumbah, Jan 14, 2009
    #3
  4. gumbah

    Peter Otten Guest

    gumbah wrote:

    > I tried all of your suggestions but none of them work... I have a clue
    > on why it is failing: MySQLdb seems to quote the % characters or
    > something...
    >
    > Even when i do:
    > cursor.execute("UPDATE tablename set fieldx='test' WHERE flfieldx is
    > null and fieldy like '%therealvalue%' "


    > it is not updating the database...
    >
    > Maybe I am completely overlooking something, but I am pretty lost
    > here... Googling this it seems as if no one is using "LIKE '%value%'"
    > type queries with Python & mySQL...
    >
    > Anyone any other thoughts?


    Did you call the connection object's commit() method?

    Peter
     
    Peter Otten, Jan 14, 2009
    #4
  5. gumbah

    gumbah Guest

    Yep, also tried that. Weird thing is that I get no errors, it's just
    silently not updating...

    On 14 jan, 11:06, Peter Otten <> wrote:
    > gumbah wrote:
    > > I tried all of your suggestions but none of them work... I have a clue
    > > on why it is failing: MySQLdb seems to quote the % characters or
    > > something...

    >
    > > Even when i do:
    > > cursor.execute("UPDATE tablename set fieldx='test' WHERE flfieldx is
    > > null and fieldy like '%therealvalue%' "
    > > it is not updating the database...

    >
    > > Maybe I am completely overlooking something, but I am pretty lost
    > > here... Googling this it seems as if no one is using "LIKE '%value%'"
    > > type queries with Python & mySQL...

    >
    > > Anyone any other thoughts?

    >
    > Did you call the connection object's commit() method?
    >
    > Peter
     
    gumbah, Jan 14, 2009
    #5
  6. gumbah

    gumbah Guest

    Aahh the conn.commit() DID the trick!!

    I tried that before, but then it failed at another point. I got it
    working now! Thanks a lot Peter and John!!

    cheers!

    On 14 jan, 11:14, gumbah <> wrote:
    > Yep, also tried that. Weird thing is that I get no errors, it's just
    > silently not updating...
    >
    > On 14 jan, 11:06, Peter Otten <> wrote:
    >
    > > gumbah wrote:
    > > > I tried all of your suggestions but none of them work... I have a clue
    > > > on why it is failing: MySQLdb seems to quote the % characters or
    > > > something...

    >
    > > > Even when i do:
    > > > cursor.execute("UPDATE tablename set fieldx='test' WHERE flfieldx is
    > > > null and fieldy like '%therealvalue%' "
    > > > it is not updating the database...

    >
    > > > Maybe I am completely overlooking something, but I am pretty lost
    > > > here... Googling this it seems as if no one is using "LIKE '%value%'"
    > > > type queries with Python & mySQL...

    >
    > > > Anyone any other thoughts?

    >
    > > Did you call the connection object's commit() method?

    >
    > > Peter
     
    gumbah, Jan 14, 2009
    #6
  7. gumbah

    Steve Holden Guest

    gumbah wrote:
    > I have this really strange problem. I hope someone can help:
    >
    > I am trying to update a database like so:
    >
    > UPDATE `tablename` set fieldx='test' WHERE flfieldx = null and fieldy
    > like '%certainvalue%'
    >
    > My Python code looks like this:
    >
    > fillsql = "UPDATE `tablename` set fieldx='test' WHERE flfieldx = null
    > and fieldy like '%%%s%%' " % certainvalue
    > print fillsql
    > cursor.execute(fillsql)
    >
    > #also tried:
    > #cursor.execute("UPDATE `tablename` set fieldx='test' WHERE flfieldx =
    > null and fieldy like %s ", "%%%s%%" % certainvalue)
    >
    > But it doesn't work... But when i copy and past the SQL (printed by
    > "print fillsql" line) and execute that in phpMyAdmin, it does work!!!
    >
    > Can anyone tell me what i am doing wrong??
    >

    1. You can't test for equality with NULL.

    2. You don't have quote marks around the LIKE argument.

    cursor.execute("UPDATE `tablename` set fieldx='test' WHERE flfieldx IS
    NULL and fieldy like '%s' ", "%%%s%%" % certainvalue)

    *might* work.

    3. I can't be certain my experience with PostgreSQL extends to MySQl,
    but I have done experiments which prove to my satisfaction that it isn't
    possible to parameterize LIKE arguments. So the only way to do it
    appears to be to build the query yourself. This means that you will need
    to make sure the string is made "safe", typically by replacing each
    occurrence of the string "'" with "''" to retain the syntactic integrity
    of the SQL statement. So finally, try

    cursor.execute("""UPDATE tablename set fieldx='test'
    WHERE flfieldx IS NULL
    AND fieldy LIKE '%%%s%%'""" %
    certainvalue.replace("'", "''"))

    regards
    Steve
    --
    Steve Holden +1 571 484 6266 +1 800 494 3119
    Holden Web LLC http://www.holdenweb.com/
     
    Steve Holden, Jan 14, 2009
    #7
  8. gumbah

    John Machin Guest

    On Jan 14, 8:55 pm, gumbah <> wrote:
    > Hi John,
    >
    > thanks a lot for your quick reply!


    Please don't top-post.
    Please answer the question """You don't say what "doesn't work"
    means ... did you get exceptions, or just silently not updating?"""

    >
    > I tried all of your suggestions but none of them work... I have a clue
    > on why it is failing: MySQLdb seems to quote the % characters or
    > something...
    >
    > Even when i do:
    > cursor.execute("UPDATE tablename set fieldx='test' WHERE flfieldx is
    > null and fieldy like '%therealvalue%' "


    Please stop stuffing around with the 'trial 1' method.

    What SQL data type is fieldy?
    What Python datatype is therealvalue coming from?
    Do this:
    print type(realvalue), repr(realvalue)
    Are you sure that the datatypes are compatible? Is there perhaps a
    Unicode encoding problem?

    Try this:
    cursor.execute("select fieldy from tablename")
    # dig out a sample result
    print type(sample_result), repr(sample_result)
    print therealvalue in sample_result

    I suggest that you avoid UPDATE and the supersensitive fieldy none of
    whose values you can reveal, and try the code on a column with simple
    noncontroversial data e.g. if you have an address table with the U.S.
    state code in it, we can try a simple query to find all addresses that
    are in a state that contains some letter:
    state_letter = "A"
    sql = "select address_id, state from address where state like %s"
    cursor.execute(sql, ("%" + state_letter + "%", ))
    That would be my best shot at getting it to work. It is based on this:
    http://mail.python.org/pipermail/python-list/2003-August/218382.html

    If that works, try applying it to a query on fieldy and then to your
    original problem.

    Cheers,
    John
     
    John Machin, Jan 14, 2009
    #8
  9. gumbah

    John Machin Guest

    On Jan 14, 9:22 pm, gumbah <> wrote:
    > Aahh the conn.commit() DID the trick!!
    >
    > I tried that before, but then it failed at another point. I got it
    > working now! Thanks a lot Peter and John!!


    For the benefit of future searchers, can you please tell us which
    varieties of cursor.execute() it works on -- copy/paste the code that
    was run, please, rather than some possibly vague/ambiguous
    narrative :)
    and do please try it using a parameterised method ("trial 2"), because
    that's much more prefereable to build-your-own SQL ("trial 1") if both
    work.

    Cheers,
    John
     
    John Machin, Jan 14, 2009
    #9
  10. gumbah

    John Machin Guest

    On Jan 14, 9:42 pm, Steve Holden <> wrote:

    > 3. I can't be certain my experience with PostgreSQL extends to MySQl,
    > but I have done experiments which prove to my satisfaction that it isn't
    > possible to parameterize LIKE arguments. So the only way to do it
    > appears to be to build the query yourself. This means that you will need
    > to make sure the string is made "safe", typically by replacing each
    > occurrence of the string "'" with "''" to retain the syntactic integrity
    > of the SQL statement. So finally, try
    >
    > cursor.execute("""UPDATE tablename set fieldx='test'
    >                   WHERE flfieldx IS NULL
    >                   AND fieldy LIKE '%%%s%%'""" %
    >                   certainvalue.replace("'", "''"))


    It appears possible to parameterise LIKE arguments in sqlite3:
    8<--- code
    import sqlite3
    tests = [
    ["select * from foo", None],
    ["select * from foo where text like '%o%'", None],
    ["select * from foo where text like ?", "o"],
    ["select * from foo where text like ?", "a"],
    ]
    conn = sqlite3.connect("c:/junk/sql_like/foodb")
    curs = conn.cursor()
    for testno, test in enumerate(tests):
    sql, parm = test
    print "\n=== Test %d ===" % (testno + 1)
    print "sql =", sql
    print "parm =", parm
    if parm is None:
    curs.execute(sql)
    else:
    arg2 = "%" + parm + "%"
    curs.execute(sql, (arg2, ))
    results = curs.fetchall()
    print "results:", results
    8<--- output

    === Test 1 ===
    sql = select * from foo
    parm = None
    results: [(u'alpha',), (u'bravo',), (u'charlie',), (u'delta',),
    (u'echo',), (u'foxtrot',)]

    === Test 2 ===
    sql = select * from foo where text like '%o%'
    parm = None
    results: [(u'bravo',), (u'echo',), (u'foxtrot',)]

    === Test 3 ===
    sql = select * from foo where text like ?
    parm = o
    results: [(u'bravo',), (u'echo',), (u'foxtrot',)]

    === Test 4 ===
    sql = select * from foo where text like ?
    parm = a
    results: [(u'alpha',), (u'bravo',), (u'charlie',), (u'delta',)]

    Cheers,
    John
     
    John Machin, Jan 14, 2009
    #10
  11. Steve Holden wrote:

    > 3. I can't be certain my experience with PostgreSQL extends to MySQl,
    > but I have done experiments which prove to my satisfaction that it isn't
    > possible to parameterize LIKE arguments. So the only way to do it
    > appears to be to build the query yourself.


    Or using Postgres through SQLAlchemy.

    Actually, the only thing SQLAlchemy cannot do (yet) is withstand a
    zombie army.
     
    Marco Mariani, Jan 14, 2009
    #11
  12. gumbah

    Steve Holden Guest

    John Machin wrote:
    > On Jan 14, 9:42 pm, Steve Holden <> wrote:
    >
    >> 3. I can't be certain my experience with PostgreSQL extends to MySQl,
    >> but I have done experiments which prove to my satisfaction that it isn't
    >> possible to parameterize LIKE arguments. So the only way to do it
    >> appears to be to build the query yourself. This means that you will need
    >> to make sure the string is made "safe", typically by replacing each
    >> occurrence of the string "'" with "''" to retain the syntactic integrity
    >> of the SQL statement. So finally, try
    >>
    >> cursor.execute("""UPDATE tablename set fieldx='test'
    >> WHERE flfieldx IS NULL
    >> AND fieldy LIKE '%%%s%%'""" %
    >> certainvalue.replace("'", "''"))

    >
    > It appears possible to parameterise LIKE arguments in sqlite3:
    > 8<--- code
    > import sqlite3
    > tests = [
    > ["select * from foo", None],
    > ["select * from foo where text like '%o%'", None],
    > ["select * from foo where text like ?", "o"],
    > ["select * from foo where text like ?", "a"],
    > ]
    > conn = sqlite3.connect("c:/junk/sql_like/foodb")
    > curs = conn.cursor()
    > for testno, test in enumerate(tests):
    > sql, parm = test
    > print "\n=== Test %d ===" % (testno + 1)
    > print "sql =", sql
    > print "parm =", parm
    > if parm is None:
    > curs.execute(sql)
    > else:
    > arg2 = "%" + parm + "%"
    > curs.execute(sql, (arg2, ))
    > results = curs.fetchall()
    > print "results:", results
    > 8<--- output
    >
    > === Test 1 ===
    > sql = select * from foo
    > parm = None
    > results: [(u'alpha',), (u'bravo',), (u'charlie',), (u'delta',),
    > (u'echo',), (u'foxtrot',)]
    >
    > === Test 2 ===
    > sql = select * from foo where text like '%o%'
    > parm = None
    > results: [(u'bravo',), (u'echo',), (u'foxtrot',)]
    >
    > === Test 3 ===
    > sql = select * from foo where text like ?
    > parm = o
    > results: [(u'bravo',), (u'echo',), (u'foxtrot',)]
    >
    > === Test 4 ===
    > sql = select * from foo where text like ?
    > parm = a
    > results: [(u'alpha',), (u'bravo',), (u'charlie',), (u'delta',)]
    >

    Thanks. So this is probably a driver, or a platform, restriction.

    regards
    Steve
    --
    Steve Holden +1 571 484 6266 +1 800 494 3119
    Holden Web LLC http://www.holdenweb.com/
     
    Steve Holden, Jan 14, 2009
    #12
    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. Dave Harrison

    using like and % in MySQLdb

    Dave Harrison, Aug 7, 2003, in forum: Python
    Replies:
    0
    Views:
    318
    Dave Harrison
    Aug 7, 2003
  2. Dave Harrison

    using like and % in MySQLdb

    Dave Harrison, Aug 7, 2003, in forum: Python
    Replies:
    5
    Views:
    435
    Chris
    Aug 7, 2003
  3. Chris Withers
    Replies:
    0
    Views:
    277
    Chris Withers
    Apr 3, 2004
  4. Guest
    Replies:
    0
    Views:
    285
    Guest
    Sep 2, 2004
  5. Patrick Kowalzick
    Replies:
    5
    Views:
    477
    Patrick Kowalzick
    Mar 14, 2006
Loading...

Share This Page