MySQLdb LIKE '%%%s%%' problem

[gumbah]

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

 

[John Machin]

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]

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

 

[Peter Otten]

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]

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

 

[gumbah]

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!

 

[Steve Holden]

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

 

[John Machin]

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]

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]

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

 

[Marco Mariani]

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.

 

[Steve Holden]

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