MySQLdb LIKE '%%%s%%' problem
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!!
--
http://mail.python.org/mailman/listinfo/python-list
Re: MySQLdb LIKE '%%%s%%' problem
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
--
http://mail.python.org/mailman/listinfo/python-list
Re: MySQLdb LIKE '%%%s%%' problem
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 <__pete...@web.de> 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
--
http://mail.python.org/mailman/listinfo/python-list
Re: MySQLdb LIKE '%%%s%%' problem
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 <__pete...@web.de> 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
--
http://mail.python.org/mailman/listinfo/python-list
