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

Discussion in 'Python' started by len, Dec 12, 2007.

  1. len

    len Guest

    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()
     
    len, Dec 12, 2007
    #1
    1. Advertising

  2. On Wed, 12 Dec 2007 11:52:44 -0800 (PST), len <>
    declaimed the following in comp.lang.python:

    > 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

    HTTP://wlfraed.home.netcom.com/
    (Bestiaria Support Staff: )
    HTTP://www.bestiaria.com/
     
    Dennis Lee Bieber, Dec 13, 2007
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Alfonso Morra
    Replies:
    11
    Views:
    740
    Emmanuel Delahaye
    Sep 24, 2005
  2. Replies:
    5
    Views:
    572
    Thomas J. Gritzan
    Oct 6, 2006
  3. tuples within tuples

    , Oct 26, 2007, in forum: Python
    Replies:
    12
    Views:
    601
    Dennis Lee Bieber
    Oct 27, 2007
  4. xera121
    Replies:
    8
    Views:
    750
    lolmc
    Sep 30, 2009
  5. Jon Reyes
    Replies:
    18
    Views:
    259
    Mitya Sirenef
    Feb 19, 2013
Loading...

Share This Page