Problem SQL ADO
Using Python on WinXP going against MS SQL 2000 server. Connection is fine and I have executed several queries successfully. The following SQL statement however gives me an error and I have tried it several different ways: "SELECT VA_MK_YEAR,VA_MK_DESCRIP,VO_VIN_NO,VO_MODEL,VO_BODY,VO_DESCRIPTION" + \ "FROM D014800 LEFT OUTER JOIN D014900 ON (VA_MK_NUMBER_VER = VO_MAKE_NO) AND (VA_MK_YEAR = VO_YEAR)" + \ "WHERE (((VA_MK_YEAR)=?) AND ((VA_MK_DESCRIP)=?) AND ((VO_MODEL)=?))" I have tried it with a "INNER JOIN", "JOIN", "LEFT JOIN" etc and get the following error each time with a syntax error just after the word INNER, JOIN, or LEFT sample message below: com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft OLE DB Provider for SQL Server', "Incorrect syntax near the keyword 'LEFT'.", None, 0, -2147217900), None) OR com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft OLE DB Provider for SQL Server', "Incorrect syntax near the keyword 'INNER'.", None, 0, -2147217900), None) any help appreciated -- http://mail.python.org/mailman/listinfo/python-list
Re: Problem SQL ADO
Thanks You are correct. That was the problem. -- http://mail.python.org/mailman/listinfo/python-list
Sybase Python WinXP
Before posting I did search through the newsgroup and saw several references to people requesting the binaries to the Sybase module. However I did not see in these requests answers as to where they might be found. Could someone please point me to them (if they exist). I do not have a C compiler and I am a newbie to boot. I only have need to read the Sybase tables and will not be updating or appending to these tables. Len Sumnler -- http://mail.python.org/mailman/listinfo/python-list
Re: Sybase Python WinXP
Thanks I will do that. Len Sumnler -- http://mail.python.org/mailman/listinfo/python-list
Newbie regular expression ?
I have the following statement and it works fine;
list1 = glob.glob('*.dat')
however I now have an additional requirement the the string must begin
with
any form of "UNQ,Unq,unq,..."
as an example if I had the following four files in the directory:
unq123abc.dat
xy4223.dat
myfile.dat
UNQxyc123489-24.dat
only unq123abc.dat and UNQxyc123489-24.dat would be selected
I have read through the documentation and I am now so
confussedd!!
Len Sumnler
--
http://mail.python.org/mailman/listinfo/python-list
Re: Newbie regular expression ?
Thanks everyone for your help.
I took the option of f1.lower().startswith("unq").
Len Sumnler
--
http://mail.python.org/mailman/listinfo/python-list
Newbie ? MS Sql update of record
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
semi-Newbie question
Hi all I have a file that I receive from another party which is basicly a csv file containing the following type of information; Tagname Scope Value "first_name","POL01","John" "last_name","POL01","Doe" "birthday","POL01","04/03/61" etc I need to convert this file info into my file format used in my application. I have been given a file from the other company that gives me all of the tagname that could be used and their scope. I want to build a table which would have all of the various tagnames, scope, and put a fieldname equivalent in the fieldname in my file structure in my application. Then read through the vendors csv file index into my table file and get the field name of where to move the data into my data structure. Here is my question? basicly I need to get the data referenced by fieldname variable in my table and then use that data as a variable name in a python assignment statement. Thus changing the actual python code at each iteration through the lines in the csv file. for i in tagfile find tagname in tagtable *** the following line of code would change through each iteration *** myfirst = value *** next iteration mylast = value *** next iteration mybirth = value etc I hope this make sense. I remember seeing something like this somewhere. Any help appreciated. Len Sumnler Unique Insurance -- http://mail.python.org/mailman/listinfo/python-list
Re: semi-Newbie question
I appoligize I don't think I have done a very good job of explaining my
problem.
I work in the an insurance company that sells non standard auto. We
have an in house policy management system that uses MySQL for the data
storage.
An individual policy consists of a policy header and 1 to n drivers, 1
to n vehicles and 1 to n coverage records per vehicle.
A third party company will sell a policy and place it with our company.
They collect all of the information on their system and then send this
information to us as a CSV file (one file per policy). **This CSV file
is like an XML file and is formated as followes;
1st field is a tagname
2nd field is a scope
3rd field is a value
see example
"totalpolicypremium","pol0","1584"
"quotenumber","pol0","5"
"address1","pol0","123 Testing Street"
"address2","pol0",""
"apartmentnumber","pol0",""
"cellphone","pol0","( )-"
...
"annualmiles","car1","0"
"antilock","car1","A"
"antitheft","car1","1"
"bodytype","car1","4D"
"buybackpip","car1","N"
"carphone","car1","N"
"city","car1","ALEXANDRIA"
"coaccdeathlimit","car1","0"
...
"annualmiles","car2","0"
"antilock","car2","N"
"antitheft","car2","1"
"bodytype","car2","4D"
"buybackpip","car2","N"
"carphone","car2","N"
"city","car2","ALEXANDRIA"
...
"address1","drv1","123 Testing Street"
"address2","drv1",""
"agerated","drv1","0"
"banklienjudgstat","drv1","N"
"cellphone","drv1","( )-"
"city","drv1","Testing City"
"cluestatus","drv1","N"
etc
The third party company sent me a file that contained all of their
valid tagnames and scope which I then took and create a crossreference
file with three fields:
xreffile
tagname (key)
scopy
SQL_fieldname
The program I am writing is nothing more than a conversion program to
take the value out of the CSV file and map it into the appropriate
field in my SQL files. Rather than creating some huge if than else
(there are over 1000 tagnames) I created the xreffile.
Now when I read a record from the tagfile I use the data in the tagname
field to lookup the tagname in my xreffile. The data in the
SQL_fieldname is the fieldname in my SQL files I want to place the data
from the tagfile in the tagfile.value field into this field in my SQL
files;
data referenced by(xreffile.SQL_fieldname) = tagfile.value
what I see as the problem is I want to use what is the data reference
by xreffile.SQL.fieldname and now make it part of the python code as a
reference variable in an assignement code statement.
I hope this helps
Len Sumnler
Unique Insurance
[EMAIL PROTECTED] wrote:
> Do you absolutely need to use variables? A dictionary would serve if
> each case has a unique identifier.
>
> client_info_dict = {}
>
> for i in tagfile:
> tagname,scope,value = i.replace('"','').split(',') # split fields,
> strip redundant characters
> client_info_dict.setdefault(scope,{}) # set up an empty nested
> dict for the unique ID
> client_info_dict[scope][tagname] = value # set the tagname's value
>
> Then client info can be retrieved from the dictionary using unique IDs
> and stereotyped tags (with .get() if some tags are not always present).
> I won't make any claims about the efficiency of this approach, but it
> works for me.
>
> len wrote:
> > Hi all
> >
> > I have a file that I receive from another party which is basicly a csv
> > file containing the following type of information;
> >
> > Tagname Scope Value
> > "first_name","POL01","John"
> > "last_name","POL01","Doe"
> > "birthday","POL01","04/03/61"
> > etc
> >
> > I need to convert this file info into my file format used in my
> > application.
> >
> > I have been given a file from the other company that gives me all of
> > the tagname that could be used and their scope. I want to build a
> > table which would have all of the various tagnames, scope, and put a
> > fieldname equivalent in the fieldname in my file structure in my
> > application. Then read through the vendors csv file index into my
> > table file and get the field name of where to move the data into my
> > data structure.
> >
> > Here is my question? basicly I need to get the data referenced by
> > fieldname variable in my table and then use that data as a variable
> > name in a python assignment statement. Thus changing the actual python
> > code at each iteration through the lines in the csv file.
> >
> > for i in tagfile
> > find tagname in tagtable
> >
> > *** the following line of code would change through each iteration ***
> > myfirst = value
> >
> > *** next iteration
> > mylast = value
> >
> > *** next iteration
> > mybirth = value
> >
> > etc
> >
> > I hope this make sense. I remember seeing something like this
> > somewhere.
> >
> > Any help appreciated.
> >
> > Len Sumnler
> > Unique Insurance
--
http://mail.python.org/mailman/listinfo/python-list
Re: semi-Newbie question
Thank all for your reply. I will try again to state the problem. I have three files. 1. Tagfile - This file contains data in a CSV format each record in the file contains three fields 'Tagname', 'Scope', and 'Value' and exampe of this data file follows; "totalpolicypremium","pol0","1584" "quotenumber","pol0","5" "address1","pol0","123 Testing Street" "address2","pol0","" "apartmentnumber","pol0","" "cellphone","pol0","( )-" ... "annualmiles","car1","0" "antilock","car1","A" "antitheft","car1","1" "bodytype","car1","4D" "buybackpip","car1","N" "carphone","car1","N" "city","car1","ALEXANDRIA" "coaccdeathlimit","car1","0" ... "annualmiles","car2","0" "antilock","car2","N" "antitheft","car2","1" "bodytype","car2","4D" "buybackpip","car2","N" "carphone","car2","N" "city","car2","ALEXANDRIA" ... "address1","drv1","123 Testing Street" "address2","drv1","" "agerated","drv1","0" "banklienjudgstat","drv1","N" "cellphone","drv1","( )-" "city","drv1","Testing City" "cluestatus","drv1","N" etc I have already written the code that can pass through this file using the CSV module and extract the data as I need it from this file, no problem here. 2. TagToSQL - This is a file which I created which also contains 3 fields as follows 'theirTagname', 'theirScope', and 'mySQLfieldname' and acts as a crossreference file between the Tagfile and my SQL file example of TagToSQL this file has a primary index on theirTagname; "address1","POL1","bnd.addr1" "address2","POL1","bnd.addr2" "appartmentnumber","POL1","bnd.apptno" etc 3. Binder - This is the primary Policy header file and is in MySQL this file contains information such as; bnd.policyno bnd.last bnd.first bnd.addr1 bnd.addr2 bnd.city bnd.state etc Now most of my coding experience is in compiled languages such as cobol, c, assembler etc I have all of the file access code completed as far as reading through the CSV file and indexing into my TagToSQL file and writing to my SQL files the only problem I have is how to create what I believe to be the couple of lines of code which would allow me to move the date in 'Value' from the Tagfile into my SQL file using the data in the TagToSQL field mySQLfieldname. I have done some more reading and I think the code I need is as follows; mycode = "TagToSQL['mySQLfieldname'] = Tagfile['Value']" exec mycode This is very new to me because I don't believe this can be done in a compiled language or at least not as easily as in an interpeted language like Python. I hope this clarifies the problem Len Sumnler Unique Insurance Cameron Laird wrote: > In article <[EMAIL PROTECTED]>, > len <[EMAIL PROTECTED]> wrote: > >I appoligize I don't think I have done a very good job of explaining my > >problem. > . > . > . > >The program I am writing is nothing more than a conversion program to > >take the value out of the CSV file and map it into the appropriate > >field in my SQL files. Rather than creating some huge if than else > >(there are over 1000 tagnames) I created the xreffile. > > > >Now when I read a record from the tagfile I use the data in the tagname > >field to lookup the tagname in my xreffile. The data in the > >SQL_fieldname is the fieldname in my SQL files I want to place the data > >from the tagfile in the tagfile.value field into this field in my SQL > >files; > > > >data referenced by(xreffile.SQL_fieldname) = tagfile.value > > > >what I see as the problem is I want to use what is the data reference > >by xreffile.SQL.fieldname and now make it part of the python code as a > >reference variable in an assignement code statement. > . > . > . > 1. Take Daniel Wong's advice, elsewhere in this thread, > and use the Python CSV module. > 2. "what I see as the problem is I want ...": what you > want *is* rather a problem, because it's a troublesome > way to achieve what I understand to be your larger > aims. It was certainly good that you didn't create > "some huge if than else". > > Once you have an SQL_fieldname, and a tagfile.value, > what do you want to do? Are you stuffing data into > an SQL table? Continuing on with Python computations? > In almost any case, it sounds as though you will profit > greatly from study of Python's dictionaries http://www.developer.com/lang/other/article.php/630721 > > http://www.diveintopython.org/getting_to_know_python/dictionaries.html >. -- http://mail.python.org/mailman/listinfo/python-list
Re: semi-Newbie question
Sample and test code shows you are correct.
tpsFile - is really the SQL file I will be inserting new policy records
into
tagFile - is a CVS file containing all of the information for a new
policy in an XMLish fashion (one record per filed of the policy) I will
receive from a third party
tagIdxFile - is just a file that where the data from the tagFile should
be mapped into the tpsFile
CODE
tpsFile = {'tpsFirstName' : 'Kate', 'tpsLastName' : 'Sumner',
'tpsPhone': '532-1234'}
tagFile = {'tagname' : 'tagFirst', 'tagScope' : 'POL0', 'tagValue' :
'Rose'}
tagIdxFile = {'idxtagname' : 'tagFirst', 'idxtpsname' : 'tpsFirstName'}
print tpsFile['tpsFirstName']
tpsFile[tagIdxFile['idxtpsname']] = tagFile['tagValue']
print tpsFile['tpsFirstName']
RESULTS
>>>
Kate
Rose
>>>
Just a small note: As trivial as this may seem this task was not
possible in the compiled language I work in due to the fact that there
was no way for me to get the data referenced by
tagIdxFile['idxtpsname'] and then use it as a field label on the left
side of the assignment statement because in the compiled language the
left side must be a label and NOT and expression.
Just strengthens my commitment to learn Python.
I would like to thank every one for their help, advice and patients.
Len Sumnler
Cameron Laird wrote:
> In article <[EMAIL PROTECTED]>,
> len <[EMAIL PROTECTED]> wrote:
> .
> .
> .
> >I have done some more reading and I think the code I need is as
> >follows;
> >
> >mycode = "TagToSQL['mySQLfieldname'] = Tagfile['Value']"
> >exec mycode
> >
> >This is very new to me because I don't believe this can be done in a
> >compiled language or at least not as easily as in an interpeted
> >language like Python.
> >
> >I hope this clarifies the problem
> .
> .
> .
> I don't understand how
>
> TagToSQL[mySQLfieldname] = Tagfile[Value]
>
> fails to meet requirements that
>
> mycode = "TagToSQL['mySQLfieldname'] = Tagfile['Value']"
> exec mycode
>
> satisfies.
>
> This thread confuses me. Maybe you already have all the answers
> you seek. If not, I recommend that you simplify--perhaps work
> through a single example datum in detail. In the meantime, I
> applaud your judgment that you can achieve what you're after with
> table lookups and such rather than the thousand-way if-else at
> which you hinted at least once.
--
http://mail.python.org/mailman/listinfo/python-list
Newbie Python SQL
Hi all Could someone recommend a tutoral, book, white paper, etc. I am trying to write a python program which takes a CSV file and through SQL insert update my SQL files. The SQL files contain a parent file with multiply child and grandchildren plus various files for doing validation. I am using the mxODBC module for access to the SQL files. I have, through the IDLE connected done some simple queries and some testing of insert. I believe I am past the initial stage on the SQL stuff but just can't get to the next level. I have picked up several python book, but all take a very introductory approach. Any recommendation would be appreciated. Len Sumnler -- http://mail.python.org/mailman/listinfo/python-list
Newbie needs Help
Hi all
I am writing a python program that inserts records into a database on
XP using mxODBC.
I need to write a section of code which will create the following SQL
command as an example;
INSERT INTO statecode (state, name) VALUES ('IL', 'Illinois')
This statement will be built up using the following code;
import mx.ODBC
import mx.ODBC.Windows
def insertFromDict(table, dict):
"""Take dictionary object dict and produce sql for
inserting it into the named table"""
sql = 'INSERT INTO ' + table
sql += ' ('
sql += ', '.join(dict)
sql += ') VALUES ('
sql += ', '.join(map(dictValuePad, dict)) # ??? this code does
NOT format correctly
sql += ')'
return sql
def dictValuePad(key):# ??? this code
does Not format correctly
return "'" + str(key) + "'"
db = mx.ODBC.Windows.DriverConnect('dsn=UICPS Test')
c = db.cursor()
insert_dict = {'state':'IL', 'name':'Illinois'}
sql = insertFromDict("statecode", insert_dict)
print sql
c.execute(sql)
I copied this code off of ASP and I sure it worked for his particular
circumstance but I need to format up the VALUE clause just a bit
different.
I will be working from a dictionary which will be continualy update in
another part of the program and this code is working.
Len Sumnler
--
http://mail.python.org/mailman/listinfo/python-list
Newbie SQL ? in python.
I have tried both the pyodbc and mxODBC and with help from the ng been
able to do what I want using either. My needs are pretty basic some
simple selects and inserts.
The current problem I have hit is the database I am inserting into have
a special ODBC driver that using the files natively has an
autoincrement feature. However, through the ODBC driver the
autoincrement does not work. (The explanation I got was the creators
did not anticapate a great need for insert.) Anyway, I figured not a
problem I will just do a select on the table ordered by the ID field in
descending order and fetch the first record and do the autoincrementing
within the python program. The code , using pyodbc is as follows.
c.execute("select state_sid from statecode order by state_sid DESC")
sid = c.fetchone()
newsid = sid.state_sid + 1
This code works fine and I get what I want. My concern is that this
technique used on large files may cause problem. I really just want to
get what is the last record in the database to get the last ID used.
Is there a better way. I realize this may be more of an SQL question
but I figured I would try here first.
Len Sumnler
--
http://mail.python.org/mailman/listinfo/python-list
Help don't know what problem is Newbie
Have the following code:
import os
import csv
import re
import mx.ODBC
import mx.ODBC.Windows
currdir = os.getcwd()
db = mx.ODBC.Windows.DriverConnect('dsn=UICPS Test')
c = db.cursor()
sid = 315
matchstr = re.compile(r'\(*\)*-*,*')
reader = csv.reader(open(currdir + r'\\IBI Brokers List 8-21-06.csv'))
for rec in reader:
if rec[0] != '' or rec[1] != '':
if rec[0] == 'Name:':
orec = ''
orec = orec + rec[12] + ',' + rec[2]
elif rec[1] == 'Address1':
orec = orec + ',' + rec[4]
elif rec[1] == 'Address2':
orec = orec + ',' + rec[4]
elif rec[1] == 'City, State, ZIP':
csz = matchstr.sub('', rec[4]).split()
if len(csz) == 0:
c = ''
s = ''
z = ''
elif len(csz) == 3:
c = csz[0]
s = csz[1]
z = csz[2]
elif len(csz) == 4:
c = csz[0] + ' ' + csz[1]
s = csz[2]
z = csz[3]
orec = orec + ',' + c + ',' + s + ',' + z + ',' +
matchstr.sub('', rec[13])
elif rec[1] == 'Tax ID':
orec = orec + ',' + rec[4].replace('-', '') + ',' +
matchstr.sub('', rec[12])
sid += 1
orec = str(sid) + ',' + orec
print orec
c.execute("insert into Producer \
(Producer_Sid, Producerno, Company, Street, Suitepo, City,
State, Zip, \
Phone, Taxid, Fax) \
values (" + orec + ")")
Below is a listing of the orec which I printed along with the python
error:
[DEBUG ON]
>>>
316,001,#001 Insurance Brokers of Indiana,400 Camby Court,P O Box
190,Greenwood,IN,46142,3178882593 ,351539405,3178857011
Traceback (most recent call last):
File "C:\UICPS\IBIagent.py", line 44, in -toplevel-
c.execute("insert into Producer \
AttributeError: 'str' object has no attribute 'execute'
Thanks
Len Sumnler
--
http://mail.python.org/mailman/listinfo/python-list
Re: Help don't know what problem is Newbie
Sorry for the stupid question. I was fixated on the SQL.
Thanks
Len Sumnler
Peter Otten wrote:
> len wrote:
>
>
> > Have the following code:
>
> Short variable names increase the likelihood of name clashes:
>
> > c = db.cursor()
>
> > c = ''
>
> > c = csz[0]
>
> > c = csz[0] + ' ' + csz[1]
> > c.execute("insert into Producer \
> > (Producer_Sid, Producerno, Company, Street, Suitepo, City,
> > State, Zip, \
> > Phone, Taxid, Fax) \
> > values (" + orec + ")")
>
> Peter
--
http://mail.python.org/mailman/listinfo/python-list
newbie file/DB processing
I am in the process of learning python. I have bought Learning Python by Mark Lutz, printed a copy of Dive into Python and various other books and looked at several tutorials. I have started a stupid little project in python and things are proceeding well. I am an old time cobol programmer from the IBM 360/370 eria and this ingrained idea of file processing using file definition (FD's) I believe is causing me problems because I think python requires a different way of looking at datafiles and I haven't really gotten my brain around it yet. I would like to create a small sequential file, processing at first to store a group id, name, amount, and date which I can add to delete from and update. Could someone point me to some code that would show me how this is done in python. Eventually, I intend to expand my little program to process this file as a flat comma delimited file, move it to some type of indexed file and finally to some RDBMS system. My little program started out at about 9 lines of code and is now at about 100 with 5 or six functions which I will eventually change to classes (I need to learn OOP to but one step at a time). So any recommendations of code, online tutorial, or book that might address this file processing/database would be appreciated Thanks Len -- http://mail.python.org/mailman/listinfo/python-list
Re: newbie file/DB processing
Thanks for the reply. I just read your response and will be taking your suggestion immediatly Len Sumnler -- http://mail.python.org/mailman/listinfo/python-list
Re: newbie file/DB processing
Thanks for the reply I think you might be right. I have been playing around with Linux at home. What I may have to do in switch my mindset from IBM/Microsoft to a more Unix way of thinking. Also thanks for the code samples. Len Sumnler -- http://mail.python.org/mailman/listinfo/python-list
Re: newbie file/DB processing
Thanks for the reply Everyone seems to be saying the same thing which is jump into some RDBM. Len Sumnler -- http://mail.python.org/mailman/listinfo/python-list
Newbie ? file structures in Dict, List, Tuples etc How
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
How to find wxPython method documentation??
Hi I am going through the "wxPython in Action" book by Noel Rappin and Robin Dunn. I have been typing in the example programs as I go and play with modifing the code. Thought I should start trying to find my way around the documentation found on the wxPython web site. The problem I have been having is I can't find the methods or more specifically 'SetBackgroundColour' or 'AddSimpleTool' for example. How does one find the methods that are available in the classes. I tried looking at the wxWidgets web site and still had the same problem. Could someone give me a little direction in this? Thanks Len -- http://mail.python.org/mailman/listinfo/python-list
Little direction please Python MySQL
Hi all; I am looking for a little direction in moving from novice python MySQL to real world processing. I can connect to MySQL databases and have performed most of the various select, create, update, insert, etc given the examples in the various books and internet tutorials not to many problems. Here is my problem. I need to start doing this in the really world at my company converting some older cobol system and data to python programs and MySQL. I have gotten past packed decimal fields and various other little tidbits. My problem is the data files aren't little three of four field files but Customer File with 98 fields etc. I understand building dictionaries and building with zip and I have even seen a reference to using __setattr__ in an empty class but I'm having a hard time moving past the little code snippts to real code. As you can image the data coming from old cobol files fieldname are generally very large and tons of code that might start looking like; order['ordhdr_sales_amount'] += order['ordlin_sales_qty'] * order ['ordlin_sales_price'] could start becoming quite cumbersum. I'm sure that there is someway to make all of this less verbose using classes and such but I need some direction. Could someone recommend either books, website, or free package whose code I could look at to move from the student type programs to gee I work in the real world now programs. Thanks Len -- http://mail.python.org/mailman/listinfo/python-list
Re: Little direction please Python MySQL
On Nov 13, 7:32 pm, Ethan Furman <[EMAIL PROTECTED]> wrote: > len wrote: > > Hi all; > > [snip] > > > Here is my problem. I need to start doing this in the really world at > > my company converting some older cobol system and data to python > > programs and MySQL. I have gotten past packed decimal fields and > > various other little tidbits. My problem is the data files aren't > > little three of four field files but Customer File with 98 fields > > etc. I understand building dictionaries and building with zip and I > > have even seen a reference to using __setattr__ in an empty class but > > I'm having a hard time moving past the little code snippts to real > > code. > > [snip] > > > Thanks Len > > I've never had the (mis?)fortune to work with COBOL -- what are the > files like? Fixed format, or something like a dBase III style? I > presume also that you only need access to them in COBOL format long > enough to transfer them into MySQL -- true? > > ~ethan~ Files are fixed format no field delimiters, fields are position and length records are terminated by newline. In cobol the read statement which read a record from the file automaticly mapped the date to the fieldnames in the cobol file definition. In python you as the programmer have to do the mapping of data to fieldnames whether this is using list and numeric indexing (list[n]), dictionaries file['fieldname'] = value or attribute (self.fieldname = value through some class). Now in my case I literally have a couple of hundred files and each file may have 20 or 30 fieldnames and in several cases 100 to 150 fields (customer file alone has 98). So as you can imagine standardize the mapping is a big deal to me. Now all of the sample code you find (understandably) usually shows SQL code and python code manipulating 3 or 4 fields at the most and one 1 or 2 tables at a time. In the real world I have programs that will need to work on 5, 10, and 15 files at a time and 100's of fields. Basicly it is the difference between writing your jave, C++, or python program to complete your programming language assignment for your college class and then graduating and getting a job and being told to write the companies new CRM or ERP system. You can find plenty of beginning tutorial and code snippets or esotiric code using stuff for landing the lunar lander but where is the middle ground. That is the stuff I'm looking for. Please understand this is not a rant against SQL or python or their communities but at my own progress in these to become a competent programmer and I'm sure as every programmer in the world has experienced, it just never occurs fast enough. Len -- http://mail.python.org/mailman/listinfo/python-list
Re: Little direction please Python MySQL
On Nov 14, 4:19 am, Bruno Desthuilliers wrote: > len a écrit : > > > > > > > Hi all; > > > I am looking for a little direction in moving from novice python MySQL > > to real world processing. > > > I can connect to MySQL databases and have performed most of the > > various select, create, update, insert, etc given the examples in the > > various books and internet tutorials not to many problems. > > > Here is my problem. I need to start doing this in the really world at > > my company converting some older cobol system and data to python > > programs and MySQL. I have gotten past packed decimal fields and > > various other little tidbits. My problem is the data files aren't > > little three of four field files but Customer File with 98 fields > > etc. I understand building dictionaries and building with zip and I > > have even seen a reference to using __setattr__ in an empty class but > > I'm having a hard time moving past the little code snippts to real > > code. > > > As you can image the data coming from old cobol files fieldname are > > generally very large and tons of code that might start looking like; > > > order['ordhdr_sales_amount'] += order['ordlin_sales_qty'] * order > > ['ordlin_sales_price'] > > > could start becoming quite cumbersum. > > > I'm sure that there is someway to make all of this less verbose using > > classes and such but I need some direction. > > You may want to have a look at SQLAlchemy. It will require some > additional learning, but chances are you'll waste less time than trying > to roll your own half-backed ORM-like system. > > My 2 cents... I haved looked at SQLAlchemy and will start using it. I do feel that I need to hand code a few things just to get and understanding of whats involved. I find that this usually making the package a little easier to use and generally makes me a better user of the package or at least it has in the past. Len -- http://mail.python.org/mailman/listinfo/python-list
How to eliminate quotes around string field written to a file.
hi
Have this code in my program;
filename = 'custfile'
codeline = filename + ' = [\n'
output.write(codeline)
record written to file look like this
"custfile" = [
Yet in another part of the program I have code:
def fmtline(fieldline):
code = '(' + fieldline[1].replace('-', '_').replace('.',
'').lower() + \
', ' + fieldline[3] + '),\n'
return code
...
output.write(fmtline(wrkline))
fieldline is a list of which the first item in the list is a fieldname
record written to file looks like this, no quotes
(customer_no, X(6).),
(customer_name, X(30).),
(customer_street_1, X(30).),
(customer_street_2, X(30).),
(customer_city, X(15).),
Why quotes in the first case and not the second and how do I get read
of the quotes
in the first case.
Thanks
Len Sumnle
--
http://mail.python.org/mailman/listinfo/python-list
Re: Little direction please Python MySQL
On Nov 15, 4:41 pm, Dennis Lee Bieber <[EMAIL PROTECTED]> wrote:
> On Sat, 15 Nov 2008 11:41:17 -0800, Ethan Furman <[EMAIL PROTECTED]>
> declaimed the following in comp.lang.python:
>
>
>
> > len wrote:
>
>
> > > Files are fixed format no field delimiters, fields are position and
> > > length
> > > records are terminated by newline. In cobol the read statement which
> > > read
> > > a record from the file automaticly mapped the date to the fieldnames
> > > in
> > > the cobol file definition.
>
> Sounds like standard COBOL record definitions. Next factor would be
> if they are text format (human readable) or COBOL binary format (and if
> so, are they using comp-1 integers or COBOL standard packed decimal?)...
> Given the mention of new-line termination, probably not binary (though
> technically, COBOL's fixed width files probably don't even require a
> new-line).
>
> In either event, use of the struct module to break the input record
> into a cluster of Python strings is probably useful, and may be more
> efficient than a series of string slicing operations.
>
> Also, if the conversion is from file direct to database, it is
> likely safe to leave most of the fields in text format; since MySQLdb
> passes everything as delimited strings in the INSERT statement -- which
> convert from "123.5" to float("123.5") -> 123.5 only to have the
> cursor.execute() convert it back to "123.5"
>
> Exception: might want to convert date/time fields into Python
> date/time objects and let MySQLdb handle conversion to/from MySQL
> datetime formats.
>
>
>
> > Are the cobol file definitions available in a file that can be parsed,
> > or are they buried in the source code?
>
> Hmmm, ever seen COBOL source?
>
> Nothing is buried in COBOL -- the data section should have nicely
> laid out record representations... (it's been some time, so this is
> pseudo-COBOL)
>
> 01 MYRECORD
> 03 NAME PIC A(50)
> 03 DATE
> 05 MONTH PIC 99
> 05 DAY PIC 99
> 05 YEAR PIC
> 03 AGE PIC 999
> 03 ADDRESS
> 05 STREET PIC X(50)
> 05 CITY PIC A(50)
> 05 STATE PIC A(50)
> 05 ZIP PIC 9-
>
> > What type of data is in the files? Integer, float, character, date, etc.
>
> If new-line terminated, likely all is human readable text -- see my
> above comment re: numeric conversions and MySQL
>
> > Once you have the data out, will you need access these same cobol files
> > in the future? (i.e. more data is being added to them that you will
> > need to migrate)
>
> That is what I considered key also...
>
> Best would be a one-time conversion -- once the new applications
> have been checked out -- meaning the converter may be used multiple
> times during development and testing of the new applications (to refresh
> the development database with production data), but that in the end the
> files become defunct and the new input process directly loads to the
> production database.
>
> No indication of what type of processes the existing COBOL
> application is performing, but I can easily visualize a pre-database
> processing style, using sorted input files, with parallel readings
>
> read EMPLOYEE (with salary rate)
> read TIMECARD (with hours)
>
> while EMPLOYEE.ID < TIMECARD.ID
> write EXCEPTION No timecard for EMPLOYEE
> read EMPLOYEE
> while TIMECARD.ID < EMPLOYEE.ID
> write EXCEPTION No employee for TIMECARD
> read TIMECARD
>
> compute and write paycheck
>
> repeat until EOF on both EMPLOYEE and TIMECARD
>
> {side note: apologies for piggy-backing -- the original poster is using
> an address that my filters are set to kill; as most of the spam on this
> group has the same domain}
> --
> Wulfraed Dennis Lee Bieber KD6MOG
> [EMAIL PROTECTED] [EMAIL PROTECTED]
> HTTP://wlfraed.home.netcom.com/
> (Bestiaria Support Staff: [EMAIL PROTECTED])
> HTTP://www.bestiaria.com/
If anyone is interested I have just posted on the group under the
title
'Newbie code review of parsing program Please'
Len
--
http://mail.python.org/mailman/listinfo/python-list
Newbie code review of parsing program Please
I have created the following program to read a text file which happens
to be a cobol filed definition. The program then outputs to a file
what is essentially a file which is a list definition which I can
later
copy and past into a python program. I will eventually expand the
program
to also output an SQL script to create a SQL file in MySQL
The program still need a little work, it does not handle the following
items
yet;
1. It does not handle OCCURS yet.
2. It does not handle REDEFINE yet.
3. GROUP structures will need work.
4. Does not create SQL script yet.
It is my anticipation that any files created out of this program may
need
manual tweeking but I have a large number of cobol file definitions
which
I may need to work with and this seemed like a better solution than
hand
typing each list definition and SQL create file script by hand.
What I would like is if some kind soul could review my code and give
me
some suggestions on how I might improve it. I think the use of
regular
expression might cut the code down or at least simplify the parsing
but
I'm just starting to read those chapters in the book;)
*** SAMPLE INPUT FILE ***
000100 FD SALESMEN-FILE
000200 LABEL RECORDS ARE STANDARD
000300 VALUE OF FILENAME IS "SALESMEN".
000400
000500 01 SALESMEN-RECORD.
000600 05 SALESMEN-NOPIC 9(3).
000700 05 SALESMEN-NAME PIC X(30).
000800 05 SALESMEN-TERRITORY PIC X(30).
000900 05 SALESMEN-QUOTA PIC S9(7) COMP.
001000 05 SALESMEN-1ST-BONUS PIC S9(5)V99 COMP.
001100 05 SALESMEN-2ND-BONUS PIC S9(5)V99 COMP.
001200 05 SALESMEN-3RD-BONUS PIC S9(5)V99 COMP.
001300 05 SALESMEN-4TH-BONUS PIC S9(5)V99 COMP.
*** PROGRAM CODE ***
#!/usr/bin/python
import sys
f_path = '/home/lenyel/Bruske/MCBA/Internet/'
f_name = sys.argv[1]
fd = open(f_path + f_name, 'r')
def fmtline(fieldline):
size = ''
type = ''
dec = ''
codeline = []
if fieldline.count('COMP.') > 0:
left = fieldline[3].find('(') + 1
right = fieldline[3].find(')')
num = fieldline[3][left:right].lstrip()
if fieldline[3].count('V'):
left = fieldline[3].find('V') + 1
dec = int(len(fieldline[3][left:]))
size = ((int(num) + int(dec)) / 2) + 1
else:
size = (int(num) / 2) + 1
dec = 0
type = 'Pdec'
elif fieldline[3][0] in ('X', '9'):
dec = 0
left = fieldline[3].find('(') + 1
right = fieldline[3].find(')')
size = int(fieldline[3][left:right].lstrip('0'))
if fieldline[3][0] == 'X':
type = 'Xstr'
else:
type = 'Xint'
else:
dec = 0
left = fieldline[3].find('(') + 1
right = fieldline[3].find(')')
size = int(fieldline[3][left:right].lstrip('0'))
if fieldline[3][0] == 'X':
type = 'Xint'
codeline.append(fieldline[1].replace('-', '_').replace('.',
'').lower())
codeline.append(size)
codeline.append(type)
codeline.append(dec)
return codeline
wrkfd = []
rec_len = 0
for line in fd:
if line[6] == '*': # drop comment lines
continue
newline = line.split()
if len(newline) == 1: # drop blank line
continue
newline = newline[1:]
if 'FILENAME' in newline:
filename = newline[-1].replace('"','').lower()
filename = filename.replace('.','')
output = open('/home/lenyel/Bruske/MCBA/Internet/'+filename
+'.fd', 'w')
code = filename + ' = [\n'
output.write(code)
elif newline[0].isdigit() and 'PIC' in newline:
wrkfd.append(fmtline(newline))
rec_len += wrkfd[-1][1]
fd.close()
fmtfd = []
for wrkline in wrkfd[:-1]:
fmtline = str(tuple(wrkline)) + ',\n'
output.write(fmtline)
fmtline = tuple(wrkfd[-1])
fmtline = str(fmtline) + '\n'
output.write(fmtline)
lastline = ']\n'
output.write(lastline)
lenrec = filename + '_len = ' + str(rec_len)
output.write(lenrec)
output.close()
*** RESULTING OUTPUT ***
salesmen = [
('salesmen_no', 3, 'Xint', 0),
('salesmen_name', 30, 'Xstr', 0),
('salesmen_territory', 30, 'Xstr', 0),
('salesmen_quota', 4, 'Pdec', 0),
('salesmen_1st_bonus', 4, 'Pdec', 2),
('salesmen_2nd_bonus', 4, 'Pdec', 2),
('salesmen_3rd_bonus', 4, 'Pdec', 2),
('salesmen_4th_bonus', 4, 'Pdec', 2)
]
salesmen_len = 83
If you find this code useful please feel free to use any or all of it
at your own risk.
Thanks
Len S
--
http://mail.python.org/mailman/listinfo/python-list
Re: Newbie code review of parsing program Please
On Nov 16, 12:40 pm, "Mark Tolonen" <[EMAIL PROTECTED]> wrote:
> "len" <[EMAIL PROTECTED]> wrote in message
>
> news:[EMAIL PROTECTED]
>
>
>
>
>
> >I have created the following program to read a text file which happens
> > to be a cobol filed definition. The program then outputs to a file
> > what is essentially a file which is a list definition which I can
> > later
> > copy and past into a python program. I will eventually expand the
> > program
> > to also output an SQL script to create a SQL file in MySQL
>
> > The program still need a little work, it does not handle the following
> > items
> > yet;
>
> > 1. It does not handle OCCURS yet.
> > 2. It does not handle REDEFINE yet.
> > 3. GROUP structures will need work.
> > 4. Does not create SQL script yet.
>
> > It is my anticipation that any files created out of this program may
> > need
> > manual tweeking but I have a large number of cobol file definitions
> > which
> > I may need to work with and this seemed like a better solution than
> > hand
> > typing each list definition and SQL create file script by hand.
>
> > What I would like is if some kind soul could review my code and give
> > me
> > some suggestions on how I might improve it. I think the use of
> > regular
> > expression might cut the code down or at least simplify the parsing
> > but
> > I'm just starting to read those chapters in the book;)
>
> > *** SAMPLE INPUT FILE ***
>
> > 000100 FD SALESMEN-FILE
> > 000200 LABEL RECORDS ARE STANDARD
> > 000300 VALUE OF FILENAME IS "SALESMEN".
> > 000400
> > 000500 01 SALESMEN-RECORD.
> > 000600 05 SALESMEN-NO PIC 9(3).
> > 000700 05 SALESMEN-NAME PIC X(30).
> > 000800 05 SALESMEN-TERRITORY PIC X(30).
> > 000900 05 SALESMEN-QUOTA PIC S9(7) COMP.
> > 001000 05 SALESMEN-1ST-BONUS PIC S9(5)V99 COMP.
> > 001100 05 SALESMEN-2ND-BONUS PIC S9(5)V99 COMP.
> > 001200 05 SALESMEN-3RD-BONUS PIC S9(5)V99 COMP.
> > 001300 05 SALESMEN-4TH-BONUS PIC S9(5)V99 COMP.
>
> > *** PROGRAM CODE ***
>
> > #!/usr/bin/python
>
> > import sys
>
> > f_path = '/home/lenyel/Bruske/MCBA/Internet/'
> > f_name = sys.argv[1]
>
> > fd = open(f_path + f_name, 'r')
>
> > def fmtline(fieldline):
> > size = ''
> > type = ''
> > dec = ''
> > codeline = []
> > if fieldline.count('COMP.') > 0:
> > left = fieldline[3].find('(') + 1
> > right = fieldline[3].find(')')
> > num = fieldline[3][left:right].lstrip()
> > if fieldline[3].count('V'):
> > left = fieldline[3].find('V') + 1
> > dec = int(len(fieldline[3][left:]))
> > size = ((int(num) + int(dec)) / 2) + 1
> > else:
> > size = (int(num) / 2) + 1
> > dec = 0
> > type = 'Pdec'
> > elif fieldline[3][0] in ('X', '9'):
> > dec = 0
> > left = fieldline[3].find('(') + 1
> > right = fieldline[3].find(')')
> > size = int(fieldline[3][left:right].lstrip('0'))
> > if fieldline[3][0] == 'X':
> > type = 'Xstr'
> > else:
> > type = 'Xint'
> > else:
> > dec = 0
> > left = fieldline[3].find('(') + 1
> > right = fieldline[3].find(')')
> > size = int(fieldline[3][left:right].lstrip('0'))
> > if fieldline[3][0] == 'X':
> > type = 'Xint'
> > codeline.append(fieldline[1].replace('-', '_').replace('.',
> > '').lower())
> > codeline.append(size)
> > codeline.append(type)
> > codeline.append(dec)
> > return codeline
>
> > wrkfd = []
> > rec_len = 0
>
> > for line in fd:
> > if line[6] == '*': # drop comment lines
> > continue
> > newline = line.split()
> > if len(newline) == 1: # drop blank line
> > continue
> > newline = newline[1:]
> > if 'FILENAME' in newline:
> > filename = newline[-1].replace('"','').lower()
> >
Re: Little direction please Python MySQL
On Nov 17, 3:24 am, Lawrence D'Oliveiro <[EMAIL PROTECTED] central.gen.new_zealand> wrote: > len wrote: > > Files are fixed format no field delimiters, fields are position and > > length records are terminated by newline. > > Assuming no COMPUTATIONAL fields, it should be easy enough to split each line > up into fixed-length pieces, e.g. assuming a simple example > > 01 Sample-Record. > 02 Field-1 pic XXX. > 02 Field-2 pic . > 02 Field-3 pic X. > > then a Python sequence that read one line's worth of fields might be > > line = infile.read() > (field_1, field_2, field_3) = (line[0:3], line[3:7], line[7:12]) -- http://mail.python.org/mailman/listinfo/python-list
Re: Little direction please Python MySQL
On Nov 17, 3:24 am, Lawrence D'Oliveiro <[EMAIL PROTECTED] central.gen.new_zealand> wrote: > len wrote: > > Files are fixed format no field delimiters, fields are position and > > length records are terminated by newline. > > Assuming no COMPUTATIONAL fields, it should be easy enough to split each line > up into fixed-length pieces, e.g. assuming a simple example > > 01 Sample-Record. > 02 Field-1 pic XXX. > 02 Field-2 pic . > 02 Field-3 pic X. > > then a Python sequence that read one line's worth of fields might be > > line = infile.read() > (field_1, field_2, field_3) = (line[0:3], line[3:7], line[7:12]) Thank you for the suggestion. I have done what you have suggested and if your interested you can take a look at the code in my post titled; 'Newbie code review of parsing program Please' Just shows great minds run in the same gutter;) Len -- http://mail.python.org/mailman/listinfo/python-list
Re: Little direction please Python MySQL
On Nov 17, 5:52 am, Tim Chase <[EMAIL PROTECTED]> wrote:
> >> Files are fixed format no field delimiters, fields are position and
> >> length records are terminated by newline.
>
> > Assuming no COMPUTATIONAL fields, it should be easy enough to split each
> > line up into fixed-length pieces, e.g. assuming a simple example
>
> > 01 Sample-Record.
> > 02 Field-1 pic XXX.
> > 02 Field-2 pic .
> > 02 Field-3 pic X.
>
> > then a Python sequence that read one line's worth of fields might be
>
> > line = infile.read()
> > (field_1, field_2, field_3) = (line[0:3], line[3:7], line[7:12])
>
> A recent posting on the list offered an elegant solution to this
> with a function something like
>
> def splitter(s, *lens):
> offset = 0
> pieces = []
> for length in lens:
> pieces.append(s[offset:offset+length])
> offset += length
> return pieces
>
> which could then be used to simplify that to
>
> (f1, f1, f3) = splitter(line, 3, 4, 5)
>
> It may not be quite so significant with just 3 items, but the OP
> mentioned having a large number of items in each record. One
> could even use something like a mapping for this. Something like:
>
> field_lens = [
> ("field1", 3),
> ("field2", 4),
> ("field3", 5),
> # stacks more
> ]
> fields = dict(zip(
> (name for name, _ in field_lens),
> splitter(line, *[length for _, length in field_lens])
> ))
> something = "Whatever %s you want" % fields["field2"]
>
> If you like this method, you can even make a more targeted
> splitter() function and add some function mappings like
>
> field_lens = [ # somewhat similar to your Cobol masks
> ("field1", 3, str),
> ("field2", 4, int),
> ("field3", 5, float),
> ("field4", 17, lambda s: s.strip().upper() ),
> # stacks more
> ]
>
> def splitter(s, field_lens):
> pieces = {}
> offset = 0
> for name, length, fn in field_lens:
> pieces[name] = fn(s[offset:offset+length])
> offset += length
> return pieces
>
> bits = splitter(line), field_lens)
> # do addition of int+float rather than string concat
> print bits["field2"] + bits["field3"]
>
> -tkc
Thank for your reply. I will have to study your code and
see if I can inprove mine.
Len
--
http://mail.python.org/mailman/listinfo/python-list
Re: Newbie code review of parsing program Please
On Nov 16, 9:57 pm, Lawrence D'Oliveiro <[EMAIL PROTECTED]
central.gen.new_zealand> wrote:
> len wrote:
> > if fieldline.count('COMP.') > 0:
>
> I take it you're only handling a particular subset of COBOL constructs: thus,
> "COMP" is never "COMPUTATIONAL" or "USAGE IS COMPUTATIONAL", and it always
> occurs just before the full-stop (can't remember enough COBOL syntax to be
> sure if anything else can go afterwards).
>
> > elif newline[0].isdigit() and 'PIC' in newline:
>
> Similarly, "PIC" is never "PICTURE" or "PICTURE IS".
>
> Aargh, I think I have to stop. I'm remembering more than I ever wanted to
> about COBOL. Must ... rip ... brain ... out ...
Most of the cobol code originally comes from packages and is
relatively consistant.
Thanks
Len
--
http://mail.python.org/mailman/listinfo/python-list
Re: Newbie code review of parsing program Please
Thanks Paul
I will be going over your code today. I started looking at Pyparsing
last night
and it just got to late and my brain started to fog over. I would
really like
to thank you for taking the time to provide me with the code sample
I'm sure it
will really help. Again thank you very much.
Len
On Nov 17, 8:01 am, Paul McGuire <[EMAIL PROTECTED]> wrote:
> On Nov 16, 12:53 pm, len <[EMAIL PROTECTED]> wrote:
>
> > On Nov 16, 12:40 pm, "Mark Tolonen" <[EMAIL PROTECTED]> wrote:
>
> > > You might want to check out the pyparsing library.
>
> > > -Mark
>
> > Thanks Mark I will check in out right now.
>
> > Len
>
> Len -
>
> Here is a rough pyparsing starter for your problem:
>
> from pyparsing import *
>
> COMP = Optional("USAGE IS") + oneOf("COMP COMPUTATIONAL")
> PIC = oneOf("PIC PICTURE") + Optional("IS")
> PERIOD,LPAREN,RPAREN = map(Suppress,".()")
>
> ident = Word(alphanums.upper()+"_-")
> integer = Word(nums).setParseAction(lambda t:int(t[0]))
> lineNum = Suppress(Optional(LineEnd()) + LineStart() + Word(nums))
>
> rep = LPAREN + integer + RPAREN
> repchars = "X" + rep
> repchars.setParseAction(lambda tokens: ['X']*tokens[1])
> strdecl = Combine(OneOrMore(repchars | "X"))
>
> SIGN = Optional("S")
> repdigits = "9" + rep
> repdigits.setParseAction(lambda tokens: ['9']*tokens[1])
> intdecl = SIGN("sign") + Combine(OneOrMore(repdigits | "9"))
> ("intpart")
> realdecl = SIGN("sign") + Combine(OneOrMore(repdigits | "9"))
> ("intpart") + "V" + \
> Combine(OneOrMore("9" + rep | "9"))("realpart")
>
> type = Group((strdecl | realdecl | intdecl) +
> Optional(COMP("COMP")))
>
> fieldDecl = lineNum + "05" + ident("name") + \
> PIC + type("type") + PERIOD
> structDecl = lineNum + "01" + ident("name") + PERIOD + \
> OneOrMore(Group(fieldDecl))("fields")
>
> It prints out:
>
> SALESMEN-RECORD
> SALESMEN-NO ['999']
> SALESMEN-NAME ['XX']
> SALESMEN-TERRITORY ['XX']
> SALESMEN-QUOTA ['S', '999', 'COMP']
> SALESMEN-1ST-BONUS ['S', '9', 'V', '99', 'COMP']
> SALESMEN-2ND-BONUS ['S', '9', 'V', '99', 'COMP']
> SALESMEN-3RD-BONUS ['S', '9', 'V', '99', 'COMP']
> SALESMEN-4TH-BONUS ['S', '99999', 'V', '99', 'COMP']
>
> I too have some dim, dark, memories of COBOL. I seem to recall having
> to infer from the number of digits in an integer or real what size the
> number would be. I don't have that logic implemented, but here is an
> extension to the above program, which shows you where you could put
> this kind of type inference logic (insert this code before the call to
> searchString):
>
> class TypeDefn(object):
> @staticmethod
> def intType(tokens):
> self = TypeDefn()
> self.str = "int(%d)" % (len(tokens.intpart),)
> self.isSigned = bool(tokens.sign)
> return self
> @staticmethod
> def realType(tokens):
> self = TypeDefn()
> self.str = "real(%d.%d)" % (len(tokens.intpart),len
> (tokens.realpart))
> self.isSigned = bool(tokens.sign)
> return self
> @staticmethod
> def charType(tokens):
> self = TypeDefn()
> self.str = "char(%d)" % len(tokens)
> self.isSigned = False
> self.isComp = False
> return self
> def __repr__(self):
> return ("+-" if self.isSigned else "") + self.str
> intdecl.setParseAction(TypeDefn.intType)
> realdecl.setParseAction(TypeDefn.realType)
> strdecl.setParseAction(TypeDefn.charType)
>
> This prints:
>
> SALESMEN-RECORD
> SALESMEN-NO [int(3)]
> SALESMEN-NAME [char(1)]
> SALESMEN-TERRITORY [char(1)]
> SALESMEN-QUOTA [+-int(7), 'COMP']
> SALESMEN-1ST-BONUS [+-real(5.2), 'COMP']
> SALESMEN-2ND-BONUS [+-real(5.2), 'COMP']
> SALESMEN-3RD-BONUS [+-real(5.2), 'COMP']
> SALESMEN-4TH-BONUS [+-real(5.2), 'COMP']
>
> You can post more questions about pyparsing on the Discussion tab of
> the pyparsing wiki home page.
>
> Best of luck!
> -- Paul
--
http://mail.python.org/mailman/listinfo/python-list
Newbie problem inserting into MySQL
Hi All
I have started a little pet project to learn python and MySQL. The
project involves figuring out all the combinations for a 5 number
lottery and storing the data in a MySQL file.
The file looks like this;
+--+-+--+-+-
++
| Field| Type| Null | Key | Default |
Extra |
+--+-+--+-+-
++
| lottryid | int(11) | NO | PRI | NULL|
auto_increment |
| lottryno | char(10)| YES | | NULL
||
| no1 | tinyint(3) unsigned | NO | | NULL
||
| no2 | tinyint(3) unsigned | NO | | NULL
||
| no3 | tinyint(3) unsigned | NO | | NULL
||
| no4 | tinyint(3) unsigned | NO | | NULL
||
| no5 | tinyint(3) unsigned | NO | | NULL
||
| nosum| tinyint(3) unsigned | NO | | NULL
||
| nohits | int(10) unsigned| YES | | NULL
||
+--+-+--+-+-
++
The code looks as follows;
#!/usr/lib/env python
import MySQLdb
import datetime
db = MySQLdb.Connection(host="localhost", user="lenyel",
passwd="lsumnler", \
db="lottery")
cursor = db.cursor()
cursor.execute('delete from littlelottery')
listofrec = []
tupcnt = 0
print "first tuple created"
for a in xrange(1,36):
for b in xrange(2,37):
for c in xrange(3,38):
for d in xrange(4,39):
for e in xrange(5,40):
tupcnt += 1
thekey = ('%02i%02i%02i%02i%02i' % (a,b,c,d,e))
mysum = a + b + c + d + e
rectuple = tupcnt, thekey, a, b, c, d, e, mysum, 0
listofrec.append(rectuple)
if tupcnt % 1 == 0:
print "beginnign of mysql write"
print datetime.datetime.now().time()
cursor.executemany('''insert into
littlelottery
values (?,?,?,?,?,?,?,?,?)''', listofrec)
db.close()
print "end of mysql write"
print datetime.datetime.now().time()
os._exit()
print "insert into mysql completed"
i get the following error on insert;
raise errorclass, errorvalue
TypeError: not all arguments converted during string formatting
Script terminated.
Do I have to covert all of the fields in the tuple records to string
or what?
Len Sumnler
--
http://mail.python.org/mailman/listinfo/python-list
Chris Miles? TGBooleanFormWidget?
trying to install zoner. Needs <http://www.psychofx.com/TGBooleanFormWidget/>http://www.psychofx.com/TGBooleanFormWidget/ but that's giving "502 Bad Gateway" can't find TGBooleanFormWidget anywhere else. Suggestions? Thanks Len -- https://mail.python.org/mailman/listinfo/python-list
Re: Python advanced course (preferably in NA)
http://www.dabeaz.com/pythonmaster.html -- Original Message -- From: Emile van Sebille Date: Thu, 03 Nov 2011 14:25:03 -0700 >On 11/3/2011 11:13 AM Behnam said... >> Anybody is aware of any advanced course in Python preferably in north >> america? >> >> I've been partly coding in Python for couple of years now and have >> used PyQt. What I'd like to learn more is a kind of advance OOP in >> python. Any idea? > >This list works well for that. Try answering all OOP related questions >as completely as possible and provide example code. Those that know the >difference will promptly point out the improved and generally preferred >approaches. When no one corrects you, you're done. > >Only-slightly-tongue-in-cheek-ly y'rs, > >Emile > >-- >http://mail.python.org/mailman/listinfo/python-list > -- http://mail.python.org/mailman/listinfo/python-list
Re: Pulling data from a .asps site
On Tue, 27 Nov 2007 20:37:19 +0200, <[EMAIL PROTECTED]> wrote: > > There's a government website which shows public data for banks. We'd > like to pull the data down programmatically but the data is "hidden" > behind .aspx... > > Is there anyway in Python to hook in directly to a browser (firefox or > IE) to do the following... > > 1) Fill the search criteria > 2) Press the "Search" button > 3) Press another button (the CSV button) on the resulting page > 4) Then grab the data out of the notepad file that pops up > > If this is a wild good chase, let me know... (or if there's a better > way besides Python... I may have to explore writing a firefox plug-in > or something)... Well, Python supports moving the mouse pointer so it's should be quite simple to write such a program. -- http://mail.python.org/mailman/listinfo/python-list
Re: read lines
> Hi, I have a text file like this;
>
> 1 -33.453579
> 2 -148.487125
>
>
> So I want to write a program in python that reads each line and
> detects which numbers of the second column are the maximum and the
> minimum.
>
> I tried with;
>
> import os, sys,re,string
>
> # first parameter is the name of the data file
> name1 = sys.argv[1]
> infile1 = open(name1,"r")
>
> # 1. get minimum and maximum
>
> minimum=0
> maximum=0
>
>
> print " minimum = ",minimum
> print " maximum = ",maximum
>
>
> while 1:
> line = infile1.readline()
> ll = re.split("\s+",string.strip(line))
>print ll[0],ll[1]
>a=ll[0]
>b=ll[1]
>print a,b
>if(b minimum=b
> print " minimum= ",minimum
>if(b>maximum):
> maximum=b
> print " maximum= ",maximum
>
>print minimum, maximum
>
>
> But it does not work and I get errors like;
>
> Traceback (most recent call last):
> File "translate_to_intervals.py", line 20, in
> print ll[0],ll[1]
> IndexError: list index out of range
Your regex is not working correctly I guess, I don't even know why you are
using a regex, something like this would work just fine:
import sys
nums = [float(line.split(' -')[1]) for line in open(sys.argv[1])]
print 'min=', min(nums), 'max=', max(nums)
--
http://mail.python.org/mailman/listinfo/python-list
Re: read lines
> Your regex is not working correctly I guess, I don't even know why you
> are using a regex, something like this would work just fine:
>
> import sys
> nums = [float(line.split(' -')[1]) for line in open(sys.argv[1])]
> print 'min=', min(nums), 'max=', max(nums)
Sorry, that should be line.split() - didn't realise those were negative
numbers.
--
http://mail.python.org/mailman/listinfo/python-list
Re: highscores list
> I'm writing a game that uses two functions to check and see if a file
> called highScoresList.txt exists in the main dir of the game program.
> If it doesn, it creates one. That part is working fine. The problem is
> arising when it goes to read in the high scores from the file when I
> play again.
>
> This is the error msg python is giving me
>
> Traceback (most recent call last):
> File "", line 1, in
>main()
> File "I:\PYTHON\PROJECT #3\PROJECT3.PYW", line 330, in main
>if(hasHighScore(wins) == True):
> File "I:\PYTHON\PROJECT #3\PROJECT3.PYW", line 175, in hasHighScore
>scores[i],names[i] = string.split(line,"\t")
> ValueError: need more than 1 value to unpack
>
> for line in infile.readlines():
> scores[i],names[i] = string.split(line,"\t")
The error message is straightforward, you are trying to unpack a 1 value
tuple to 2 values
The reason it's a one value tuple is that your first line is this:
> outfile.write (" High Score Name \n")
Running split('\t') on this will return a tuple of length one (since there
is no tab in that line)
Your code is also really unpythonic, take a look at this rewrite to see
what you could have done better:
def getHighScoreList():
scores = []
try:
highscore_file = list(open("highScoresList.txt"))
for line in highscore_file[2:]:
score, name = line.split('\t\t\t')
scores.append((int(score), name.rstrip()))
except IOError: # no highscore file yet
pass
return scores
def hasHighScore(score):
scores = [s for (s, n) in getHighScoreList()]
if scores:
return score > min(scores)
return True
def setHighScores(newscore, newname):
max_scores = 3
scores = getHighScoreList()
for i, (score, name) in enumerate(scores):
if newscore > score:
scores.insert(i, (newscore, newname))
break
else:
scores.append((newscore, newname))
outfile = open("highScoresList.txt","w")
outfile.write (" High Score Name \n")
outfile.write ("-\n")
for i in range(max_scores):
try:
score, name = scores[i]
except IndexError:
score, name = 0, ''
outfile.write("\t%s\t\t\t%s\n" % (score, name))
outfile.close()
if hasHighScore(wins):
setHighScores(wins, getName(wins))
--
http://mail.python.org/mailman/listinfo/python-list
Re: searching a value of a dict (each value is a list)
> I have a dictionary with million keys. Each value in the
> dictionary has a list with up to thousand integers.
> Follow is a simple example with 5 keys.
>
> dict = {1: [1, 2, 3, 4, 5],
>2: [10, 11, 12],
>90: [100, 101, 102, 103, 104, 105],
>91: [20, 21, 22],
>99: [15, 16, 17, 18, 19]}
>
> I want to find out the key value which has a specific
> integer in the list of its value. For example, if I search
> 104 in the list, 90 must be returned.
>
> How can I do this with Python? Ideas?
def find_key(dict, num):
for k in dict:
if num in dict[k]:
return k
--
http://mail.python.org/mailman/listinfo/python-list
