Newbie using python to update sql table
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
Advice on this code
If this is the wrong place to post this, please advise better place.
Otherwise, I have created the following python program and it works.
Running on XP. I think I am now at that stage of learning python where
I'm not quit a newbie and I am not really knowlegable. I know just
enough to be dangerous and can really screw things up.
Any suggestion on improving would be greatly appreciated. However my
real question is I would like to run this program under a GUI interface
and have the GUI have a start button to start this process running and
the messages in the program placed in a multiline text field which when
the stop button is pressed the text field would be copied to a logfile
and the program exited. Below this program is the skeleton of the
python gui program using wxPython (the gui program also works). I have
a metal block on merging the two programs. I think part of the problem
is the first is not really using object except for the database access
and the gui is all object. Second, I'am getting all wrapped up with
variable, object, etc scope issues.
** FIRST PROGRAM **
"""This program is an attempt to modularize the lmsface program.
I also will try to document the logic of the program"""
import os
import glob
import time
import re
import shutil
from win32com.client import Dispatch
from ADOConstants import *
def cvtfiles():
""" cvtfiles is the driving routine for converting and/or changing
the V1sta quote flat file being sent by QuotePro to Unique. The
function gets a list of the files in the FTP directory path and
opens each file one at a time and copies the data (with any
necessary changes or additions to the output directory where
V1sta's
interface program
picks it up and converts it into the V1sta's
SQL quote files. Individual functions are called to process
different segments of the flat file record."""
global novehflag
novehflag = False
global ofile
list1 = glob.glob('*.dat')
for f1 in list1:
if f1.lower().startswith("unq"):
if f1.lower().rfind("void") < 0:
print f1 + " is being processed now."
input1 = open(cfdir + f1, 'r')
output = open(ctdir + f1, 'w+')
ifile = input1.readline()
output.write(wrkpol(ifile))
output.write(wrkdrv(ifile,1406,6))
output.write(wrkveh(ifile,1784,6))
if novehflag == True:
input1.close()
output.close()
shutil.copy2(cfdir + f1,cfdir + 'voided\\' + f1)
os.remove(cfdir + f1)
os.remove(ctdir + f1)
novehflag = False
else:
output.write(wrkmisc(ifile,2582))
output.write(wrkviol(ifile,2774,16))
output.write(wrkaccid(ifile,3270,16))
output.write(wrkmisc2(ifile,3638))
output.write(wrkcov(ifile,3666,6))
output.write(wrklp(ifile,4314,7))
output.write(wrkai(ifile,4909,6))
output.write(wrkmisc3(ifile,5707))
output.close()
input1.close()
shutil.copy2(cfdir + f1,cfdir + 'processed\\' + f1)
os.remove(cfdir + f1)
print f1 + " has been processed."
else:
shutil.copy2(cfdir + f1,cfdir + 'voided\\' + f1)
os.remove(cfdir + f1)
print f1 + " is a VOIDED Quote from QuotePro."
else:
pass
def wrkpol(ifile):
""" wrkpol functions converts the policy information segment.
Currently the only changes made to the policy segment is to
change Current-Carrier-Type to 0 if it contains a 0 or 1 or
change Current-Carrier-Type to 1 if it contains a 2"""
polwrk = ''
polwrk = polwrk + ifile[0:577]
polwrk = polwrk + ' '
polwrk = polwrk + ifile[588:653]
if ifile[653:654] in ['0','1']:
polwrk = polwrk + '0'
else:
polwrk = polwrk + '1'
polwrk = polwrk + ifile[654:974]
maxcnt = 6
cnt = 0
strstart = 974
while cnt < maxcnt:
if ifile[strstart + 41:strstart + 52] == ' ':
polwrk = polwrk + ifile[strstart:strstart + 72]
else:
polwrk = polwrk + ifile[strstart:strstart + 41]
polwrk = polwrk + ' '
polwrk = polwrk + ifile[strstart + 52:strstart + 72]
strstart += 72
cnt += 1
return polwrk
def wrkdrv(ifile,strstart,maxcnt):
""" wrkdrv function at this point just moves the data as is.
The driver segment is an occurs 6"""
cnt = 0
drvwrk = ''
while cnt < maxcnt:
if ifile[strstart + 23:strstart + 31] <> '':
drvwrk = drvwrk + ifile[strstart:strstart + 63]
else:
drvwrk = drvwrk + ifile[strstart:st
Re: Python adodb
I have used the following code in ADO:
# 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=servername;" +\
"uid=loginid;" +\
"pwd=password;" +\
"database=databasename"
oConn.Open()
if oConn.State == adStateOpen:
print "Database connection SUCCEEDED"
else:
print "Database connection FAILED"
Hope this helps
--
http://mail.python.org/mailman/listinfo/python-list
Looking for a Python mentor
Hello Was wandering if there is any place where some one could go to get mentoring on python coding. I have started coding in python but I am the only one in the shop using it. So there is no one around to look over my code give suggestions on improvement, in style, logic, easier ways of doing things etc. I am not really looking for hand holding as much mentoring. I have purchased about every python book out and have a list a mile long on my internet bookmarks. I believe I have a good grasp of the fundamentals procedurally and I am fighting my way through OOP. I am more at the stage of trying to pull things together into programs that perform real world tasks. At one point I thought I saw some place on one of the web site that did this kind of thing but I can't find it now. Any help or suggestions welcomed Len Sumnler -- http://mail.python.org/mailman/listinfo/python-list
Re: Python adodb
You also might want to take a look at; http://www.mayukhbose.com/python/ado/index.php I found it very helpful LenS -- http://mail.python.org/mailman/listinfo/python-list
Problem Pythoncard tutorial
Under the "Getting Started with Pythoncard" there is a short little example of changing the starter1.py. I have made the 2 changes and when I run the program I get the following error: Traceback error ... result = dialog.alertDialog(self, 'It works!', 'Showing Off') NameError: name 'self' is not defined whats up Len Sumnler -- http://mail.python.org/mailman/listinfo/python-list
? Pythoncard
I have created a small gui stub as follows using Pythoncard: c:\myhome lmsgui.py lmsgui.rsrc.py When I try to run I get the following error: (Errno2) no such file or directory lmsqui.rsrc.py I understand why I am getting the error the question is how do I fix this so it looks in the proper directories. Len Sumnler -- http://mail.python.org/mailman/listinfo/python-list
Re: ? Pythoncard
Never mind I figured out my on stupid mistake Thanks -- http://mail.python.org/mailman/listinfo/python-list
wxGlade will not run on my machine
I have installed wxGlade on a MS XP machine. It executed on completion of the install. However, when I try to double click on the wxGlade icon to start nothing happens. I searched the NG and found that this had been report before but no solution was posted. I am running the following versions; Python 2.4.1 wxPython 2.6.1.0 Would like to give wxGlade a try. Len Sumnler -- http://mail.python.org/mailman/listinfo/python-list
Icon on wx.MDIParentFrame
I would like to put an Icon on the frame but cannot figure out how to do it any help. Running on XP machine. It appears that wxPython suffers from the same problem as most software packages. I find that the reference manual is only of any real help once you have a pretty good understanding of how the software works in the first place. Len Sumnler -- http://mail.python.org/mailman/listinfo/python-list
Re: Icon on wx.MDIParentFrame
Thanks I will keep an eye out for the book. I just downloaded wxGlade and created a small file and looked at how it does it. Again thanks for the response -- http://mail.python.org/mailman/listinfo/python-list
? MDI depreciated
Hate to ask this dum question (since I've been hiding under a rock). But if the MDI UI model is/was depreciated. What is the new UI model. Would love some links that explain in gerneral and specific terms. Len Sumnler -- http://mail.python.org/mailman/listinfo/python-list
Newbie learning OOP
Trying to learn OOP concepts and decided to use Python for this purpose. I have coded the following CLASS and it seems to work fine. Any comments on the code or suggestions would be appreciated. The class let you take a person's name and split it up into first last and middle. The class defaults to the assumption that the name will be passed in as a string in first last and middle order however you can set the format attribute to take last first and middle order. You can then get a string back in and opposite order. class names: def __init__(self, format = "F"): self.format = format def namesplit(self, name): if self.format == "F": self.namelist = name.split() self.first = self.namelist[0] self.init = self.namelist[1] self.last = self.namelist[2] else: self.namelist = name.split() self.first = self.namelist[1] self.init = self.namelist[2] self.last = self.namelist[0] return self.first, self.init, self.last def fjoin(self): self.namestring = self.first + ' ' + self.init + ' ' + self.last def ljoin(self): self.namestring = self.last + ' ' + self.first + ' ' + self.init Any comments appreciated. Len Sumnler -- http://mail.python.org/mailman/listinfo/python-list
Re: Newbie learning OOP
Thanks for the reply. I will be looking over you code. I'm trying to learn OOP. It just hasn't sunk in yet;-\ -- http://mail.python.org/mailman/listinfo/python-list
Re: Newbie learning OOP
You are correct, the code so far is just a simple problem to learn OOP. Oh by the way I don't work for Dept. of Homeland Security just remember "drop those fingernail clipers and step away from those shoes:-) -- http://mail.python.org/mailman/listinfo/python-list
Newbie learning OOP 2nd ?
I have coded this little program which is a small little tip calculator
program. I am in the process of trying to convert this program to use
OOP. Would appreciate others more experienced in OOP code in how they
might do it.
Would be happy to forward all profits from the sale of the program;-))
#Program name: tipcalc05.py
#This program calculates the dollar amount of a tip on a resturant bill
import datetime
import string
def calc_full_bill():
'''This function is used to get information regarding the bill and
calculate the tax, tip, and total amount of the bill'''
global billamt
global tippct
global taxpct
global totalbill
billamt = raw_input("Please enter the bill amount: ")
tippct = raw_input("Please enter the percent tip: ")
location = getanswers("Are you in Chicago, ")
if location == True:
taxpct = 7.25
else:
taxpct = 6.75
tipamt = calcpct(billamt, tippct)
taxamt = calcpct(billamt, taxpct)
totalbill = float(billamt) + taxamt + tipamt
print "Tip = %.2f Tax = %.2f Total = %.2f" % (tipamt, taxamt,
totalbill)
def group_bill_process():
grpbill = open("grpbill.txt", 'a+')
group = raw_input("Please give the name of the group? ")
for line in grpbill:
print string.split(line, sep=',')
who_paid = raw_input("Who paid the bill? ")
grpdate = datetime.datetime.now()
group_detail = group + ',' + who_paid + ',' + str(totalbill) + ','
+ str(grpdate) + '\n'
grpbill.write(group_detail)
def calc_bill_by_guest():
'''This function allows you to get multiple amounts for any number
of individuals and calculate each individuals share of the tip
tax and bill amount'''
guest = []
netbill = float(billamt)
while True:
gf_name = raw_input("Enter girlfriends name: ")
gf_amt = raw_input("Enter girlfriends amount: ")
guest.append((gf_name, gf_amt))
netbill = netbill - float(gf_amt)
print "Amount remaining %.2f" % netbill
anymore = getanswers("Anymore girlfriends, ")
if anymore == False:
break
#these print lines are here just to show the tuples within a list
print guest
print
for (g, a) in (guest):
gf_tax = calcpct(a, taxpct)
gf_tip = calcpct(a, tippct)
gf_total = float(a) + gf_tax + gf_tip
print "%s Tip = %.2f Tax = %.2f Total = %.2f" % (g, gf_tip,
gf_tax, gf_total)
print
def getanswers(question):
'''This function allows you to print some question looking for
and answer of the form Yes or No'''
answer = raw_input(question + "'Yes' or 'No': ")
print
if answer in ["Yes", "yes", "Y", "y", "OK", "ok", "Ok", "yeh",
"Yeh"]:
return(True)
else:
return(False)
def calcpct(bill, pct):
'''This function calculates the amount base off of some
given percentage'''
amt = float(bill) * float(pct) / 100
return(amt)
while True:
calc_full_bill()
group_bill = getanswers("Is this a group bill? ")
if group_bill == True:
group_bill_process()
else:
split_the_bill = getanswers("Do you want to equally spilt the
Bill, ")
if split_the_bill == True:
no_of_dinners = raw_input("Split between how many people?
")
print "Each person owes %.2f" % (float(totalbill) /
float(no_of_dinners))
else:
calc_bill_by_guest()
runagain = getanswers("Do you want to run again, ")
if runagain == False:
break
PS If there is somewhere else I should be posting this kind of thing
let me know otherwise thanks everyone for your help.
Len Sumnler
--
http://mail.python.org/mailman/listinfo/python-list
Re: Newbie learning OOP 2nd ?
Thank you for your suggestion and especially your time. I will study your code:-) Len Sumnler -- http://mail.python.org/mailman/listinfo/python-list
Newbie Python & XML
I have a situation at work. Will be receiving XML file which contains quote information for car insurance. I need to translate this file into a flat comma delimited file which will be imported into a software package. Each XML file I receive will contain information on one quote only. I have documentation on layout of flat file and examples of XML file (lot of fields but only container tags and field tags no DTD's,look easy enough). I am just starting to learn python and have never had to work with XML files before. Working in MS Windows environment. I have Python 2.4 with win32 extensions. 1. What else do I need 2. Any examples of program doing the same. 3. Have read some stuff that indicates I may need ? schema of XML layout The rating company will ftp a quote realtime to a dir on my system. the python program will then have to poll the dir for new files and automaticly translate and initiate import. Any help appreciated Len Sumnler -- http://mail.python.org/mailman/listinfo/python-list
