parsing flat file to mssql (odbc)

Discussion in 'Python' started by .d.hos, Jul 8, 2003.

  1. .d.hos

    .d.hos Guest

    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
    .d.hos, Jul 8, 2003
    #1
    1. Advertising

  2. .d.hos

    .d.hos Guest

    >
    > 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.
    .d.hos, Jul 14, 2003
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. clintsy
    Replies:
    1
    Views:
    854
    Marco Schmidt
    Mar 5, 2004
  2. R. P.
    Replies:
    3
    Views:
    8,240
    Joe Kesselman
    Jun 22, 2006
  3. Hugh
    Replies:
    4
    Views:
    442
    CBFalconer
    Jun 3, 2005
  4. Wes Gamble
    Replies:
    1
    Views:
    159
    Gerardo Santana Gómez Garrido
    Apr 5, 2006
  5. yermej
    Replies:
    0
    Views:
    763
    yermej
    Jun 10, 2011
Loading...

Share This Page