Newbie ? MS Sql update of record

len lsumnler at gmail.com
Thu Oct 6 17:58:33 CEST 2005


I have created a python program that takes a flat file and changes some
of the data and create a new flat file with the changes.  Part of this
process requires that I try to find a particular model car in an MS Sql
table.  This part of the program is now working great.

It has come to my attention that some of the information in the flat
file could be used to update our information in the MS Sql table that I
currently run the query on.  Basicly I create a recordset of vehicles
from the MS Sql table based on vehicle year and make and once I have
this recordset I run it through logic which does a reqular expression
compare on the vehicle VIN no to the
VIN number in the table to get a match.  I would like to update this
record in the table with info. in the flat file.  I believe there
should be some way to update the fields in this record of the recordset
and then update the table.  I am not an sql expert and would appreciate
someone pointing me in the right direction.  Contained below is a
listing of my code;

# The following code creates a connection object,
# assigns the connection string, opens the
# connection object, and then verifies a good
# connection.

oConn = Dispatch('ADODB.Connection')

oConn.ConnectionString = "Provider=SQLOLEDB.1;" +\
                         "Data Source=uicesv05;" +\
                         "uid=aiis;" +\
                         "pwd=aiis;" +\
                         "database=auto_mo_001"

oConn.Open()
if oConn.State == adStateOpen:
    print "Database connection SUCCEEDED"
else:
    print "Database connection FAILED"

# The following code creates a command object,
# assigns the command to the connection object,
# sets the query, creates the parameters objects to
# be passed to the command object and requests the
# query to be prepared (compiled by the SQL system).

oCmd = Dispatch('ADODB.Command')
oCmd.ActiveConnection = oConn
oCmd.CommandType = adCmdText

oCmd.CommandText = """\
SELECT
    VA_MK_YEAR,VA_MK_DESCRIP,VO_VIN_NO,VO_MODEL,VO_BODY,
    VO_DESCRIPTION,VO_MODEL_ID
FROM D014800 INNER JOIN D014900
    ON VA_MK_NUMBER_VER = VO_MAKE_NO AND
        VA_MK_YEAR = VO_YEAR
WHERE VA_MK_YEAR = ? AND VA_MK_DESCRIP = ?
"""

vyear = ''
vmake = ''
oParmYear = oCmd.CreateParameter(vyear,adChar,adParamInput)
oParmYear.Size = 4
oParmMake = oCmd.CreateParameter(vmake,adChar,adParamInput)
oParmMake.Size = 10

oCmd.Parameters.Append(oParmYear)
oCmd.Parameters.Append(oParmMake)

oCmd.Prepared = True

...

def wrkveh(ifile,strstart,maxcnt):
    """ wrkveh function does an SQL record lookup to try an select
    the correct vehicle in the V1sta make and model files.  If the
    correct model is found I move V1sta's make model and body
    descriptions to the flat file.  Currently, I hard code a 1 for
    vehicle use.  The drive segment is an occurs 6"""
    cnt = 0
    vehwrk = ''
    while cnt < maxcnt:
        if ifile[strstart:strstart + 10] == '          ':
            vehwrk = vehwrk + ifile[strstart:strstart + 133]
        else:
            vmake = ifile[strstart:strstart + 10]
            vyear = ifile[strstart + 98:strstart + 102]
            vvin4_8 = ifile[strstart +53:strstart + 58]
            vmodel = ''
            vbody = ''
            oParmYear.Value = vyear
            oParmMake.Value = vmake
            (oRS, result) = oCmd.Execute()
            while not oRS.EOF:
                wvin =
oRS.Fields.Item("VO_VIN_NO").Value.replace('*','.')
                wvin.replace('*','.')
                wvin = wvin[0:5]
                r1 = re.compile(wvin)
                if r1.match(vvin4_8):
                    vmake = oRS.Fields.Item("VA_MK_DESCRIP").Value
                    vmodel = oRS.Fields.Item("VO_MODEL").Value
                    vbody = oRS.Fields.Item("VO_DESCRIPTION").Value
                    vmodelid = oRS.Fields.Item("VO_MODEL_ID").Value
                    print 'DRC model ' + vmake + ' ' + vyear + ' ' +
vmodel + \
                          ' ' + vmodelid
                    break
                else:
                    oRS.MoveNext()
            else:
                print 'DRC model NOT FOUND'
            vehwrk = vehwrk + vmake + vmodel + vbody
            vehwrk = vehwrk + ifile[strstart + 50:strstart + 107]
            vehwrk = vehwrk + '1'
            vehwrk = vehwrk + ifile[strstart + 108:strstart + 133]
        strstart += 133
        cnt += 1
        
    return vehwrk




More information about the Python-list mailing list