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
--
http://mail.python.org/mailman/listinfo/python-list
