python - pymssql : Inserting mutliple values into a table

Question

我正在尝试将多个值插入到一个表中，但我不确定语法..我正在从 excel 文件中读取这些值...

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)"
        %(sheet.cell_value(rows,cols))) <---Getting 5 values from here as expected,but not sure how to map these values to the table values..

conn.commit()

问题:

我一次插入 5 个值，这些值正确地来自 (sheet.cell_value(rows,cols)))，如下所示:

31/12/2014  147801503777:Int.Pd:30-06-2014 to 30-12-2014    -   860.00  20,458.74

但在这种情况下，参数编号不匹配..所以我得到一个错误:

TypeError: not enough arguments for format string

表结构:

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)
)

Answer 1

我假设调用 sheet.cell_value(rows,cols) 只返回一个值。

所以也许收集这些值会有所帮助:

for rows in range(13,sheet.nrows):
    arr = []
    for cols in range(5):
        arr.append(sheet.cell_value(rows,cols))
    cur.execute(
        "INSERT INTO MONTHLY_BUDGET VALUES (%s, %s, %s, %s, %s)",
        tuple(arr)
    )