Python: automate input to MySQL query

[Philip Semanchuk]

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