Solved - Python: automate input to MySQL query

[D'Arcy J.M. Cain]

On Tue, 22 Sep 2009 22:36:15 -0700
Dennis Lee Bieber <wlfraed at ix.netcom.com> wrote:
> 	I wasn't really referring to the table used as a qualifier (in front
> of the "."). But in your sample statement, every /field/ name seemed to
> contain the table name too...
> 
> 	traveler.travelerFirstName
> 
> rather than being just
> 
> 	traveler.FirstName

I do that all the time.  Many of my tables have an ID, an active flag
and other fields such as first name like this example.  Putting the
table name into the field name simplifies joins.  Consider the
following (untested) code.

from pg import DB # PyGreSQL
db = DB() # uses my default PostgeSQL database
res = db.query("""
    SELECT * FROM employee, manager
    WHERE manager.id = employee.id""").dictresult()
print res[0]['id'] # is this the employee's ID or the manager's?
print res[0]['firstname'] # same question

Of course you can get around this by specifying every field and using
"AS" to change the names to manager_id and employee_firstname, etc. but
if you are going to do that anyway, why not just do it once in the
database instead of littering the code?

In my databases I make sure that every field name is unique across all
tables unless the field actually refers the the same item (i.e. joining
field) so that I never have an accident.  My manager table would have a
manager_id and the employee table would have an employee_id.  The
employee table would also have a manager_id but it is not a different
field.  It's the same field as in the manager table.

-- 
D'Arcy J.M. Cain <darcy at druid.net>         |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.