Python : Inserting multiple values into a table from excel
Question: I have to read data from Excel and insert it into Table...

For this I am using Python 2.7, pymssql and xlrd modules...

My sql connection is working fine and I am also able to read data from Excel
properly.

My table structure :

    
    
    CREATE TABLE MONTHLY_BUDGET
    (
        SEQUENCE            INT IDENTITY,
        TRANSACTION_DATE    VARCHAR(100),
        TRANSACTION_REMARKS VARCHAR(1000),
        WITHDRAWL_AMOUNT    VARCHAR(100),
        DEPOSIT_AMOUNT      VARCHAR(100),
        BALANCE_AMOUNT      VARCHAR(100)
    )
    

My excel values are like this :

    
    
    02/01/2015  To RD Ac no 147825000874    7,000.00    -   36,575.74
    

I am having problem while inserting multiple values in the table... I am not
sure how to do this...

    
    
    import xlrd
    import pymssql
    
    file_location = 'C:/Users/praveen/Downloads/OpTransactionHistory03-01-2015.xls'
    
    #Connecting SQL Server
    conn = pymssql.connect (host='host',user='user',password='pwd',database='Practice')
    cur = conn.cursor()
    
    # Open Workbook
    workbook = xlrd.open_workbook(file_location)
    
    # Open Worksheet
    sheet = workbook.sheet_by_index(0)
    
    for rows in range(13,sheet.nrows):
        for cols in range(sheet.ncols):
            cur.execute(
            " INSERT INTO MONTHLY_BUDGET VALUES (%s, %s, %s, %s, %s)", <--- Not sure!!!
            [(sheet.cell_value(rows,cols))])
    
    conn.commit()
    

Error : ValueError: 'params' arg () can be only a tuple or a dictionary.

The docs are here : <http://pymssql.org/en/stable/pymssql_examples.html>


Answer: The exception you are getting says that the "'params' arg() can be only a
tuple or a dictionary" but you're passing in a list. Also, your parameter list
appears to be a single tuple instead of a list with 4 values. Try changing

    
    
    cur.execute(
        " INSERT INTO MONTHLY_BUDGET VALUES (?, ?, ?, ?, ?)", <--- Not sure!!!
        [(sheet.cell_value(rows,cols))])
    

to

    
    
    cur.execute(
        " INSERT INTO MONTHLY_BUDGET VALUES (?, ?, ?, ?, ?)", <--- Not sure!!!
        (sheet.cell_value(rows,cols)))
    

... or maybe

    
    
    cur.execute(
        " INSERT INTO MONTHLY_BUDGET VALUES (?, ?, ?, ?, ?)", <--- Not sure!!!
        ((sheet.cell_value(rows,cols))))
    

NB: untested. I've always changed how the bind variables in your SQL are being
called.

