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 <sjmac...@lexicon.net> wrote:
> 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
