468,139 Members | 1,419 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,139 developers. It's quick & easy.

parsing flat file to mssql (odbc)

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_D ate_11,Add_Start_Date_12,
Drop_Start_Date_13,Add_End_Date_14,Faculty_15,Shor t_Title_16,Prerequisite_17,
Required_18,Coreq_Noncourses_19,Course_20,Cred_Typ e_21,SEC_CRS_DESC_22,
Long_Title_23,Depts_24,Fee_25,Meeting_Days_26,Prin ted_Comments_27,Subject_28,
Supplies_29,Transfer_Status_30,Course_Cost_31,Stat us_32,Capacity_33,
COURSE_SECTIONS_34,Corequisite_Sections_35,Section _36,Min_Cred_37,
Instr_Methods_38,SEC_FACULTY_FIRST_NAME_39,SEC_FAC ULTY_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
Jul 18 '05 #1
1 2476
>
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

Mark,
thanks for your suggestion, it seems to be working w/ mxODBC.
Jul 18 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by mj | last post: by
2 posts views Thread by Josh Close | last post: by
3 posts views Thread by francisl | last post: by
14 posts views Thread by Kukurydz | last post: by
2 posts views Thread by Neural | last post: by
4 posts views Thread by kenneth.osenbroch | last post: by
reply views Thread by Gosth in the shell | last post: by
27 posts views Thread by didacticone | last post: by
1 post views Thread by gcdp | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.