Newbie ? file structures in Dict, List, Tuples etc How

[len]

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()

 

[Dennis Lee Bieber]

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

Could you expand upon these? Show an actual CREATE TABLE perhaps?

I have created a dictionary for each table ex.
poldict{keys:values....}, drvdict{keys:values,...} etc

This step I don't understand... You have the data in MySQL, and you
are pulling it out on a per table basis, just to recreate the contents
in dictionaries? Why not just use (I think MySQLdb supports the type) a
Dictionary cursor, AND set up a multi-table SELECT that returns
everything you need for one policy document as a single record. Then
just loop over the returned records:

dctCursor.execute("select ....", parms)
for policy in dctCursor:
#do something with policy record, which hopefully
#is a dictionary now.

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

I'm guessing you have fixed length fields... Do you also have
boilerplate text (I'm trying to figure out what a "single record" of
6.5kbytes would represent -- since for a text file, records are
delimited by newlines, so if you have a number of newlines in the output
format you have a multiple record text file... Is this supposed to be
something like a fill-in-the-blanks form letter?)

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']))

Have you considered using string interpolation? If you already know
the field widths and they aren't scheduled to change often you could do
something like:

#build format string
fm = []
for (fname, fwidth) in widths:
fm.append("%%(%s)-%ss" % (fname, fwidth))
#%% outputs a single %, (%s) gets fname,
#- means left align, %s convert fwidth
#argument, s is a literal.. so result should be something like
# "%(firstname)-30s"
form = "".join(fm)
#converts the separate field formats into one single string
# "%(lastname)-30s%(firstname)-25s"

#now in the database loop something like
for policy in dctCursor:
fout = open(some-file-name, "w")
fout.write(form % policy)
fout.close()

--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/