parsing flat file to mssql (odbc)

.d.hos dhostetler at sopris.net
Tue Jul 8 15:01:55 EDT 2003


ok, fairly new to python, relatively familiar w/ ms-sql. here's my
issue:

my .py script parses the contents of a (tab delim.) flat file, then
attempts to insert the information into the db. I've been fighting
this for a day or so, and i'm stuck on the db insertion...

basically the script uses .readlines() to capture the flat file
contents, and stick the *record* into a container list...

something to the effect of:
for l in lines:
    fields = string.split(l,'\t')
    dic.append(fields)

then, I just want to loop over that container list and do a simple
INSERT statement. I've tried using the .executemany() method to no
avail. .execute(sql, tuple) seems to be working better.

sql statement (lots of columns):
---------------------------------
INSERT INTO tbl_pyDev
(COURSE_SECTIONS_0,Term_1,Synonym_2,Section_Name_3,Location_4,Bldg_5,Room_6,
Days_7,Start_Time_8,End_Time_9,Start_Date_10,End_Date_11,Add_Start_Date_12,
Drop_Start_Date_13,Add_End_Date_14,Faculty_15,Short_Title_16,Prerequisite_17,
Required_18,Coreq_Noncourses_19,Course_20,Cred_Type_21,SEC_CRS_DESC_22,
Long_Title_23,Depts_24,Fee_25,Meeting_Days_26,Printed_Comments_27,Subject_28,
Supplies_29,Transfer_Status_30,Course_Cost_31,Status_32,Capacity_33,
COURSE_SECTIONS_34,Corequisite_Sections_35,Section_36,Min_Cred_37,
Instr_Methods_38,SEC_FACULTY_FIRST_NAME_39,SEC_FACULTY_LAST_NAME_40,
Refund_41,CoReq_Name_42,Drop_End_43)  values (?,?,?,?,?,?,?,?,?,?,?,?,
?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

ugly eh?
well, here's an example of what I'm trying to feed this beast:
(the first row of data is col. headers, and thy insert fine. This is
actually the chunk of data the script is choking on (dic[1]))
---------------------------------------------------------------
('2475', '03/FA', '25000', 'AAA-010-AS01', 'AS', 'AS', '116', 'TTH',
'05:00PM', '06:50PM', '09/09/03', '10/02/03', '09/09/03', '09/09/03',
'09/26/03', 'Rameil, Lesley J', 'Aca Achieve Pre-College', '', '', '',
'1139', 'UG', "Meets the requirements of the Comprehensive  Student
AssessmentSystem and the Secretary's Commission on Achieving Necessary
Sklls, as well as work and postsecondary enrollment skills.  Enabes
the student to review and improve in reading, writing, matheatics,
science, and social studies in preparation for the GED tst.",
'Academic Achievement in Pre-College', 'AAA', '', 'T', '', 'AAA', '',
'NT', '', 'A', '35', '2475', '', 'AS01', '1.00', 'LEC', 'Lesley',
'Rameil', '09/12/03', '', '09/26/03')

I'm executing in this sort of fashion:
--------------------------------------
for data in dic:
     cursor.execute(insertSQL, tuple(data))

Error:
---------------------------------------
Traceback (most recent call last):
  File "C:\Python22\parse_flat.py", line 101, in ?
    cursor.execute(insertSQL, tuple(data))
dbi.internal-error: [Microsoft][ODBC SQL Server Driver][SQL
Server]Location:	 record.cpp:2253
Expression:	 m_futureSize == 0 || rec.Size () == m_futureSize
SPID:		 58
Process ID:	 1136 in EXEC



so, if the column headers make into the db, it has to be the
formatting of the following data?!?! single quotes?!?! i'm fried on
this...if anyone has any input I would be very grateful.

thanks - d




More information about the Python-list mailing list