Python: Keeping Track of Database Columns

One of the things I like in working with a new programming language is observing the phases of using the language, and watching the changes in use while gaining mastery. In my case, the language is Python, and most of the software I’m writing these days is for a data exchange system, which, when completed will act as a conduit between a commercial water meter status and daily read system and an in-house tax collection/billing system.

Python is touted as both a scripting and object oriented language. In other words, you can use the language for small informal tasks, or you can engineer a formal project using it. However, until you start learning and using the language, there are differences to which a programmer must adapt, especially if your training has been in conventional computer languages, like C/C++, Java, or older languages like PL/I and Pascal.

One of these that stuck out immediately were database reads and writes. My project requires the use of two Python database modules, Informixdb — http://informixdb.sourceforge.net/ — and MySQLDB — http://mysql-python.sourceforge.net/ .

When a row of data is returned from a select statement, or data is updated or inserted, the python list data structure is used. Most of my database rows are more than ten columns wide. The two most important tables, that define today’s water meter account data and the current and historical table housing all customer data that has been or will be sent to the meter read system are of identical schema for comparison purposes.

However, it is difficult to write meaningful code when column position references are numbers, like in_data[4], in_data[5], and in_data[6], instead of more descriptive references like in_data[CustID], in_data[PremisesID], and in_data[AcctID].

As good as the database module documentation is, the people who write it assume a level of confidence in Python programming and assume you will have your own way of accessing this data consistently and reliably, so your program works now and can easily be maintained a few weeks, months, and years from now.

So, how do you do that? I chose an intermediate approach using classes.

class csSynch():

def __init__(self):

self.Action = ‘U’

self.MeterID = 0

self.EndpointID = 0

.

.

.

s = csSynch()

print(in_data[s.Action])

Eventually, I will probably be moved to hand over lists to this class, and let it extract its own data with class functions like

def action(self):

return self.Action

but for now, I was able to straighten out my selects and inserts in a big hurry by going back to a tried and true of computer programming and using names instead of magic numbers.

How others handle this is something I am hoping to discover.

Advertisements

Leave a comment

Filed under General

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s