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(fiel ds)
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_SECTION S_0,Term_1,Syno nym_2,Section_N ame_3,Location_ 4,Bldg_5,Room_6 ,
Days_7,Start_Ti me_8,End_Time_9 ,Start_Date_10, End_Date_11,Add _Start_Date_12,
Drop_Start_Date _13,Add_End_Dat e_14,Faculty_15 ,Short_Title_16 ,Prerequisite_1 7,
Required_18,Cor eq_Noncourses_1 9,Course_20,Cre d_Type_21,SEC_C RS_DESC_22,
Long_Title_23,D epts_24,Fee_25, Meeting_Days_26 ,Printed_Commen ts_27,Subject_2 8,
Supplies_29,Tra nsfer_Status_30 ,Course_Cost_31 ,Status_32,Capa city_33,
COURSE_SECTIONS _34,Corequisite _Sections_35,Se ction_36,Min_Cr ed_37,
Instr_Methods_3 8,SEC_FACULTY_F IRST_NAME_39,SE C_FACULTY_LAST_ NAME_40,
Refund_41,CoReq _Name_42,Drop_E nd_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
AssessmentSyste m 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\pa rse_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
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(fiel ds)
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_SECTION S_0,Term_1,Syno nym_2,Section_N ame_3,Location_ 4,Bldg_5,Room_6 ,
Days_7,Start_Ti me_8,End_Time_9 ,Start_Date_10, End_Date_11,Add _Start_Date_12,
Drop_Start_Date _13,Add_End_Dat e_14,Faculty_15 ,Short_Title_16 ,Prerequisite_1 7,
Required_18,Cor eq_Noncourses_1 9,Course_20,Cre d_Type_21,SEC_C RS_DESC_22,
Long_Title_23,D epts_24,Fee_25, Meeting_Days_26 ,Printed_Commen ts_27,Subject_2 8,
Supplies_29,Tra nsfer_Status_30 ,Course_Cost_31 ,Status_32,Capa city_33,
COURSE_SECTIONS _34,Corequisite _Sections_35,Se ction_36,Min_Cr ed_37,
Instr_Methods_3 8,SEC_FACULTY_F IRST_NAME_39,SE C_FACULTY_LAST_ NAME_40,
Refund_41,CoReq _Name_42,Drop_E nd_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
AssessmentSyste m 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\pa rse_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
Comment