parsing flat file to mssql (odbc)

[M.-A. Lemburg]

.d.hos wrote:
> 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.

Strange error message you have there...

Have you tried using mxODBC at this ? I'd bet you get much better
results.

-- 
Marc-Andre Lemburg
eGenix.com

Professional Python Software directly from the Source  (#1, Jul 08 2003)
 >>> Python/Zope Products & Consulting ...         http://www.egenix.com/
 >>> mxODBC, mxDateTime, mxTextTools ...        http://python.egenix.com/
________________________________________________________________________
2003-07-01: Released mxODBC.Zope.DA for FreeBSD             1.0.6 beta 1