Solved - Python: automate input to MySQL query

[Threader Slash]

---------- Forwarded message ----------
> From: Philip Semanchuk <philip at semanchuk.com>
> To: "Python-list (General)" <python-list at python.org>
> Date: Mon, 21 Sep 2009 08:49:27 -0400
> Subject: Re: Python: automate input to MySQL query
>
> On Sep 21, 2009, at 5:18 AM, Threader Slash wrote:
>
>  Hi Everybody...
>>
>> I have a query that works as follows:
>>
>> Code:
>>
>> db.query("""SELECT traveler.travelerFirstName,vaccine.vaccineName from
>> (traveler INNER JOIN takenvaccine ON traveler.travelerID =
>> takenvaccine.travelerID)
>>       INNER JOIN vaccine ON takenvaccine.vaccineID=vaccine.vaccineID
>>       INNER JOIN requiredvaccine ON
>> vaccine.vaccineID=requiredvaccine.requiredvaccineID
>>       INNER JOIN city ON requiredvaccine.cityID = city.cityID
>> WHERE traveler.travelerFirstName = 'John'""")
>>
>> The output gives me all vaccines taken by a given employee. To allow the
>> user to choose different names when running the system, I am trying to use
>> a
>> variable, named *person*:
>>
>> Code:
>>
>> person = "John"
>>
>> db.query("""SELECT traveler.travelerFirstName,vaccine.vaccineName from
>> (traveler INNER JOIN takenvaccine ON traveler.travelerID =
>> takenvaccine.travelerID)
>>       INNER JOIN vaccine ON takenvaccine.vaccineID=vaccine.vaccineID
>>       INNER JOIN requiredvaccine ON
>> vaccine.vaccineID=requiredvaccine.requiredvaccineID
>>       INNER JOIN city ON requiredvaccine.cityID = city.cityID
>> WHERE traveler.travelerFirstName = 'person'""")
>>
>> Then I run the query inside my python program. The first version without
>> variable works fine. But the second, using variable, doesn't give me any
>> output. What I am missing here about the python variable sintaxe to make
>> the
>> MySQL work with variable ... Any suggestion?
>>
>
> In your second query you've got "person" hardcoded as a string. You're
> looking for a traveler with the actual first name of person.
>
> Change this line:
>
>> WHERE traveler.travelerFirstName = 'person'""")
>>
>
> to this:
>
>> WHERE traveler.travelerFirstName = %s""")
>>
>
> and pass person as a list of params to db.query(). Something like this
> should work:
>
> sql = """SELECT blah blah blah WHERE traveler.travelerFirstName = %s"""
> db.query(sql, [person])
>
> See the Python DB API documentation for specifics. This might look like the
> same thing as string interpolation, but it isn't.
>
> Good luck
> Philip
>

Hi Philip,

Thanks for comments and suggestions.

Now it works! Here is the solution:

 Code:


self.db = MySQLdb.connect(hostname,username,passwd,dbname)
self.cursor=self.db.cursor();

name="John"
runQuery="SELECT traveler.travelerFirstName,vaccine.vaccineName from
traveler INNER JOIN takenvaccine ON traveler.travelerID =
takenvaccine.travelerID INNER JOIN vaccine ON
takenvaccine.vaccineID=vaccine.vaccineID INNER JOIN requiredvaccine ON
vaccine.vaccineID=requiredvaccine.requiredvaccineID INNER JOIN site ON
requiredvaccine.siteID = site.siteID WHERE traveler.travelerFirstName
= %s"

self.cursor.execute(runQuery,(name,))

print "tell vaccines taken for a chosen traveler\n"

for row in self.cursor.fetchall():
     print row


Note: you need to declare the whole query in the same line to it take effect
in the variable "runQuery".
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-list/attachments/20090922/13024d80/attachment.html>