Re: Solved - Python: automate input to MySQL query
On Wed, Sep 23, 2009 at 4:21 PM, D'Arcy J.M. Cain da...@druid.net wrote: 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. They are in separate tables, that should be enough to reflect the fact that they're not the same thing. 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. I am not sure if you're talking about two different tables (inventory and sale?). If you meant a single table; I would hope you don't have two columns, with the same name, in a single table. If you were talking about two different tables, see my above comment. -- http://mail.python.org/mailman/listinfo/python-list
Re: Solved - Python: automate input to MySQL query
Hi, ... code, even that is not needed, as the columns are returned in the order specified so code /knows/ that the first column isname from /this/ table and the other column withname 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. This seems to be a shortcome in your datamodel, in fact I'd see a firstname of an employee as the same type of information as the firstname of a manager. So what you really would have is a table with all the people and another which declares who is manager of who. recommended of course is to name different data differently and also to keep the name of the foreign keys the same on every table so you can use: SELECT a,b,c FROM tablea JOIN tableb USING (ab_id) since this avoids duplicate columns in the result. 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. Yes and you should name them different in your query when they appear in the same result set. Regards Tino smime.p7s Description: S/MIME Cryptographic Signature -- http://mail.python.org/mailman/listinfo/python-list
Re: Solved - Python: automate input to MySQL query
On Thu, 24 Sep 2009 08:49:02 -0400 Joe Riopel goo...@gmail.com wrote: On Wed, Sep 23, 2009 at 4:21 PM, D'Arcy J.M. Cain da...@druid.net wrote: 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. They are in separate tables, that should be enough to reflect the fact that they're not the same thing. But manager_id (to join the employee to their manager) is in both tables and it does refer to the same thing. 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. I am not sure if you're talking about two different tables (inventory and sale?). If you meant a single table; I would hope you don't have two columns, with the same name, in a single table. If you were talking about two different tables, see my above comment. I'm talking about two different tables. Consider an inventory table that includes the current price of an item. The invoice line item table references the inventory table for details of the item. A classic mistake is to use the price from the inventory table to determine the total price (item.quantity * invent.price). The problem is that the price may change tomorrow but the price it was sold for yesterday does not. Hence, invent.price and item.price are not the same thing and I would name one of them differently. -- D'Arcy J.M. Cain da...@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. -- http://mail.python.org/mailman/listinfo/python-list
Re: Solved - Python: automate input to MySQL query
On Thu, 24 Sep 2009 15:03:26 +0200 Tino Wildenhain t...@wildenhain.de wrote: 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. This seems to be a shortcome in your datamodel, in fact I'd see a firstname of an employee as the same type of information as the firstname of a manager. So what you really would have is a table with all the people and another which declares who is manager of who. Sure, I was trying to show a hypothetical situation. In my real databases I don't do that and in fact I point the manager_id field to another entry in the same table. Obviously in that case I am forced to make it a different name even though it references the same column as employee_id. recommended of course is to name different data differently and also to keep the name of the foreign keys the same on every table so you can use: SELECT a,b,c FROM tablea JOIN tableb USING (ab_id) since this avoids duplicate columns in the result. Exactly. 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. Yes and you should name them different in your query when they appear in the same result set. I want to name them differently even if they aren't in the same result set because they are different things that just happen to have the same type - money. I wouldn't name every int column int and I wouldn't name every price column price. -- D'Arcy J.M. Cain da...@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. -- http://mail.python.org/mailman/listinfo/python-list
Re: Solved - Python: automate input to MySQL query
On Tue, 22 Sep 2009 22:36:15 -0700 Dennis Lee Bieber wlfr...@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 da...@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. -- http://mail.python.org/mailman/listinfo/python-list
Re: Solved - Python: automate input to MySQL query
On Wed, Sep 23, 2009 at 10:49 AM, D'Arcy J.M. Cain da...@druid.net wrote: 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 case, I sometimes generate entity classes from automatically from the database. So, I am not typing them, but the field names on some of my classes will be longer than they need to be. Also, I only have to use the AS when I am doing joins, which I don't do as often as selects, inserts, and/or updates. So now I would have to type the long redundant column name for those 3 types of queries, instead of saving me from typing AS in the joins. -- http://mail.python.org/mailman/listinfo/python-list
Re: Solved - Python: automate input to MySQL query
On Wed, 23 Sep 2009 11:37:07 -0700 Dennis Lee Bieber wlfr...@ix.netcom.com wrote: On Wed, 23 Sep 2009 10:49:51 -0400, D'Arcy J.M. Cain da...@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 da...@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. -- http://mail.python.org/mailman/listinfo/python-list
