On Jan 14, 7:31 pm, gumbah <joost.ruy...@gmail.com> 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
--
http://mail.python.org/mailman/listinfo/python-list
