Python MySQL: interactive query input

threaderslash · 09-21-2009, 04:29 AM

Hello 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 sintax to make the MySQL work with variable ... Any suggestion?

All comments or suggestions are highly appreciated!

AnanthaP · 09-21-2009, 10:08 PM

Since your code for john and person are identical, it probably looks for a person called 'person'.

Some variation of \{$person) is called for (I dont know python).

Please post the solution.

End

forrestt · 09-21-2009, 10:26 PM

Don't really know python either, but this link should give you the format you need:

http://atomboy.isa-geek.com/plone/Me...-python-module

HTH

Forrest

threaderslash · 09-22-2009, 12:41 AM

Thanks for all 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

The term "self." is a object oriented syntax used in Python to denote a global variable that internally belongs to that class in use and is visible to all methods inside that object.

Note: you need to declare the whole query in the same line to it take effect in the variable "runQuery".

threaderslash · 09-22-2009, 06:36 PM

That is it! Looks better now...

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 city ON requiredvaccine.cityID = city.cityID
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

To keep indentation in the query, just use triple """ when entering it to the variable/string runQuery.