Hello Python Group
I am new to python and I am trying to write a file conversion program
using Python Ver 2.5 on
XP. Following are the specifications of this program;
I need to convert an auto policy file which is in MySQL and consists
of the following tables
Policy - Policy_Sid
pDriver - Driver_Sid, Policy_Sid
pCar - Car_Sid, Policy_Sid
pCoverage - Coverage_Sid, Car_Sid, Policy_Sid
pDiscount - Discount_Sid, Coverage_Sid, Car_Sid,
Policy_Sid
I have created a dictionary for each table ex.
poldict{keys:values....}, drvdict{keys:values,...} etc
For each auto policy a single record ASCII file of length 6534 bytes
is to be created. I have created
a simple csv file containing a fieldname, startpos, length. I bring
this file into the program and
convert the csv file to two dictionaries one is
csvdictval{fieldname:values,....} and
csvdictlen{fieldname:length, ....}.
Now to move values from the MySQL tables to a single string I end up
with a bunch of code that looks
like this:
drcdict['quote-number'] =
str(pol['PolicyNoBase']).ljust(int(drcdlen['quote-number']))
drcdict['quote-trans-type'] = '0'
drcdict['last-name-of-customer'] =
pol['Last'].ljust(int(drcdlen['last-name-of-customer']))
drcdict['first-name-of-customer'] =
pol['First'].ljust(int(drcdlen['first-name-of-customer']))
Now I have a 100 or so of these type of lines to code and it seems
like an enormous amount of
typing and I admit I'm pretty lazy. I should state that my
programming background is in things
like Cobol, Assembler, VB, Topspeed, etc (yey I'm that old). In
those languages I had file
definition sections in the code which described the file layouts.
Therefore my code for the
above would look like
quote-number = PolicyNoBase or
move PolicyNoBase to quote-number etc
It is not the dynamic typing that is causing me problem it is more
the proper way to denote file
structures using dictionaries, lists, tuples, strings.
Please understand I have the majority of the code for this program
working so I am not looking for
coding help as much as real world advice, examples etc, I just feel
there is a better/easier way
then what I am doing now.
I am providing a listing of the code below which may be more
informative but I don't really expect
any one to go through it.
Len Sumnler
""" This program takes as input PMS Policy files and creates a DRC
csv file per policy.
The program takes a start date and end date as program arguments to
filter the PMS
policies"""
import sys
import os
import time
import ceODBC
import datetime
import csv
drcdict = {}
drckeys = []
drcvals = []
drclens = []
olddrc = csv.reader(open('QuoteProFields.csv', 'rb'))
for oname, ostart, olength, ovalue, f5, f6, f7, f8, f9, f10, f11 in
olddrc:
nname = oname.lower()
nvalue = ' ' * int(olength)
drckeys.append(nname)
drcvals.append(nvalue)
drclens.append(olength)
copyofdrcvals = drcvals
drcdict = dict(zip(drckeys,drcvals))
drcdlen = dict(zip(drckeys,drclens))
# Get start and end date arguments
#lStart = raw_input('Please enter start effective date')
#lEnd = raw_input('Please enter end effective date')
lStart = time.strftime("%Y-%m-%d",time.strptime(sys.argv[1],"%m/%d/
%Y"))
lEnd = time.strftime("%Y-%m-%d",time.strptime(sys.argv[2],"%m/%d/%Y"))
# Connect to TPS files through ODBC
dbconn = ceODBC.Connection("DSN=Unique", autocommit=True)
dbcursor = dbconn.cursor()
# Get Policy records using filter
policysql = "select * from policy where effdate between ? and ?"
dbcursor.execute(policysql, (lStart, lEnd))
polfld = [i[0] for i in dbcursor.description]
# Fetch Policy record
polhdr = dbcursor.fetchall()
for polrec in polhdr:
pol = dict(zip(polfld,polrec))
drcfile = open('drc'+str(pol['PolicyNoBase'])+'.txt', 'w')
drcvals = copyofdrcvals
drcrec = ''
drcdict['quote-number'] =
str(pol['PolicyNoBase']).ljust(int(drcdlen['quote-number']))
drcdict['quote-trans-type'] = '0'
drcdict['last-name-of-customer'] =
pol['Last'].ljust(int(drcdlen['last-name-of-customer']))
drcdict['first-name-of-customer'] =
pol['First'].ljust(int(drcdlen['first-name-of-customer']))
drvcursor = dbconn.cursor()
driversql = "select * from pdriver where Policy_Sid = ?"
drvcursor.execute(driversql, (pol['Policy_Sid'],))
drvfld = [i[0] for i in drvcursor.description]
pdriver = drvcursor.fetchall()
for drvrec in pdriver:
drv = dict(zip(drvfld,drvrec))
wno = drv['Driver_Sid']
if drv['Driver_Sid'] == 1:
wno = ''
else:
wno = str(drv['Driver_Sid'])
drcdict['driv-first'+wno] =
drv['First'].ljust(int(drcdlen['driv-first']))
drcdict['driv-last'+wno] = drv['Last'].ljust(int(drcdlen['driv-
last']))
if drv['Init'] == None:
drcdict['drv-middle'+wno] = ' '
else:
drcdict['driv-middle'+wno] =
str(drv['Init']).ljust(int(drcdlen['driv-middle']))
drcdict['birth-date-of-driv'+wno] = drv['DOB'].strftime("%Y%m
%d")
carcursor = dbconn.cursor()
carsql = "select * from pvehicle where Policy_Sid = ?"
carcursor.execute(carsql, (pol['Policy_Sid'],))
carfld = [i[0] for i in carcursor.description]
pvehicle = carcursor.fetchall()
for carrec in pvehicle:
car = dict(zip(carfld,carrec))
covcursor = dbconn.cursor()
coveragesql = "select * from pcoverage where Policy_Sid = ?"
covcursor.execute(coveragesql, (pol['Policy_Sid'],))
covfld = [i[0] for i in covcursor.description]
pcoverage = covcursor.fetchall()
for covrec in pcoverage:
cov = dict(zip(covfld,covrec))
disccursor = dbconn.cursor()
discsql = "select * from pdiscschg where Policy_Sid = ? and
Coverage_Sid = ?"
disccursor.execute(discsql, (pol['Policy_Sid'],
cov['Coverage_Sid']))
discfld = [i[0] for i in disccursor.description]
pdiscount = disccursor.fetchall()
for discrec in pdiscount:
disc = dict(zip(discfld,discrec))
for keys in drckeys:
drcrec = drcrec + str(drcdict[keys])
drcfile.write(drcrec)
drcfile.close()
--
http://mail.python.org/mailman/listinfo/python-list
