Solved - Python: automate input to MySQL query

[Threader Slash]

Hi Dennis,
You're right. Putting """ allows indentation. Great!

About putting the name of the table: x.travelerID, etc.. I think it keep the
query more documented, specially when you are dealing with 5 tables in a
same query, like this query.

Cheers.. Threader.

---------- Forwarded message ----------
From: Dennis Lee Bieber <wlfraed at ix.netcom.com>
To: python-list at python.org
Date: Tue, 22 Sep 2009 10:43:52 -0700
Subject: Re: Solved - Python: automate input to MySQL query
On Tue, 22 Sep 2009 15:58:18 +1000, Threader Slash
<threaderslash at gmail.com> declaimed the following in
gmane.comp.python.general:

>
> 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"
>
       <snip>
>
> Note: you need to declare the whole query in the same line to it take
effect
> in the variable "runQuery".

       No, you don't... IF you wrap it in triple-quotes...

runQuery="""select t.travelerFirstName, v.vaccineName
                                       from traveler as t
                               inner join takenvaccine as tv
                                       on t.travelerID = tv.travelerID
                               inner join vaccine as v
                                       on tv.vaccineID = v.vaccineID
                               inner join requiredvaccine as rv
                                       on v.vaccineID = rv.requiredvaccineID
                               inner join site as s
                                       on rv.siteID = s.siteID
                       where t.travelerFirstName = %s"""

Should be perfectly acceptable to Python and the database query
processor.

       I'm not too impressed with the database naming scheme -- if the
TABLE is named "x", why prefix all the fields in the table with the same
"x"
       requiredvaccine.requiredvaccineID
is rather redundant -- same with all the others. The only place where it
might be justified is when it is a foreign key linkage.

       vaccine.ID              ID of the vaccine record

       requiredvaccine.ID              ID of the rv record itself, not of
the
vaccine
       requiredvaccine.vaccineID               foreign key, ID /in/ vaccine
that
this record refers to.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/python-list/attachments/20090923/b72fd962/attachment-0001.html>