Solved - Python: automate input to MySQL query

D'Arcy J.M. Cain darcy at druid.net
Wed Sep 23 22:21:22 CEST 2009


On Wed, 23 Sep 2009 11:37:07 -0700
Dennis Lee Bieber <wlfraed at ix.netcom.com> wrote:
> On Wed, 23 Sep 2009 10:49:51 -0400, "D'Arcy J.M. Cain" <darcy at druid.net>
> declaimed the following in gmane.comp.python.general:
> > 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
> [...]
> 	Since I would never recommend using "*" in production code (if
> someone changes the database schema, all your assumptions about returned
> fields changes anyway), then yes -- in my usage I WOULD itemize the

Look again.  I am using a database module that returns dicts so I never
have to worry about the order of returned fields.

> fields to specify what order each is to be returned, and maybe even
> specify an "as" name to avoid duplicate column names (but in program

I just like nice, readable code.  Specifying every field and adding
"AS" statements just makes it harder to read - and write.

> code, even that is not needed, as the columns are returned in the order
> specified so code /knows/ that the first column is <name> from /this/
> table and the other column with <name> is from /that/ table).

Unless you get a dictionary return.

In any case, I have a strong philosophical objection to using the same
name to refer to two different things regardless of any operational
issues.  The manager.firstname and employee.firstname are not the same
thing and should have different names to reflect that.

A similar issue comes up in the classic situation of the inventory table
price field and the sale item price field.  They sound like the same
thing but they are not.  One is the current price in the catalogue and
the other is the price it was sold for at a particular time.  They need
different names for that reason.

-- 
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.



More information about the Python-list mailing list