Accessing Database Rows In Python

My programming experience goes back a long time, but I’ve been using Python for about one year. I like Python, but being used to a database report generation language like Informix 4GL or mainstream programming languages like C, C++, or Java left me puzzled on how to access columns returned in a select row from Python.

In our AMR (automated meter reading for our water utility) project, the two main data transfers with the AMR vendor, who is also hosting our system, involves multi-column tables. Specifically, our meter reads schema is 11 columns wide; our configuration schema is 26 columns wide.

Python uses 0-based indexes to access elements of a list or tuple, and the two database “APIs” I’ve used — informixdb and mysqldb — both return values in a list. I imagine all or most database modules follow suit, given Python is a very list oriented language.

With a 26-column wide table, accessing data fields in a list becomes awkward using hard-coded numbers, and it is easy to make mistakes. There is an enum package for python http://pypi.python.org/pypi/enum/. I chose to implement my column positions as a class.

class csIdx():
 def __init__(self):
 self.action = 0
 self.EndpointID = 1
 self.DeviceType = 2
 self.ChannelNumber = 3
 self.Status = 4
 self.MeterID = 5
 self.AcctID = 6
 self.PremiseID = 7
 self.CustomerID = 8
 self.CustLNam = 9
 self.CustFNam = 10
 self.Company = 11
 self.PremType = 12
 self.Addr1 = 13
 self.Addr2 = 14
 self.City = 15
 self.State = 16
 self.Zip = 17
 self.lat = 18
 self.long = 19
 self.cmnts = 20
 self.mlog = 21
 self.decodetypeoverride = 22
 self.route = 23
 self.last_update = 24
 self.cs_set_token = 25

I access returned data from a select like this

c = csIdx()
...
sql_cmd = \
"SELECT count(*) " + \
"FROM customer_synch c " + \
"WHERE c.EndpointId = " + str(incoming_data[c.EndpointID]) + " " + \
"and last_update >= " + "'" + str(base_date) + "'" + " "

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