[Tutor] Searching for email id in MySQL giving wrong results

[Alan Gauld]

Hi Ziyad,

Don't beat yourself up over this, you were trying to be helpful
and that's a good thing. The original syntax looked weird to me too,
but mainly because the variable was inside a pair of parens, but
because I didn't know MySql I didn't say anything at the time.

But your answer seemed reasonable enough.

>>         entry.execute("""SELECT * FROM contact WHERE email_id = %s""", 
>> (s_email,))
>>
>> The execute method will handle the string substitution.  This is better
>> than doing it yourself, because execute will deal with any quoting
>> issues for feeding the data to the database.

Personally I never use this feature of execute because I then can't print
out the SQL statement sent to the database if anything unexpected happens.
I'd rather take responsibility for awkward escape strings than have a string
I can't debug being sent to the database, but I'm aware that others take
the opposite view and have more confidence in Python's libraries than I do!

> Okay, here's a quote from the definition of "execute()":
>        def execute(self, query, args=None):
>                """Execute a query.
>
>                query -- string, query to execute on server
>                args -- optional sequence or mapping, parameters to use 
> with query.

Actually this might still be the issue since the OP used a single variable 
not a
sequence but I suspect Python is smart enough to deal with that.

> So, my answer is correct.  And it seems there are other solutions (using
> sequences and "%s" (just like Lloyd Kvam said) or "%(key1)s" in case of
> passing a mapping sequence (which, I think, is just a dictionary)).

You would have been correct if he had been building the query outside
of execute().

> Why it didn't work for him in the first place?  I really don't know.

Me neither! :-)

>        I *NEVER* used "MySQLdb".  I just wanted to help.  The sentence:
>                """%s""", (email_id)

Maybe he just needs a comma after email_id to force the second arg
to be a tuple - who knows, I certainly don't.

>        more than a day, I suggested (what seemed to me) the right
>        answer.
>
>        That's a wrong thing to do since I don't know anything about
>        "MySQLdb".  So, I apologise to all of you.

No apology is necessary, you were trying to help. Without your
wrong answer Lloyd may not have posted the correction and we
might not have had a discussion about the options available in
cursor.execute()

Alan G
Author of the learn to program web tutor
http://www.freenetpages.co.uk/hp/alan.gauld