python怎么导入数据文件_使用python将excel文件中的数据导入SQL

我发现了一些其他的问题，它们与我得到的结果有着相似的错误，但是还不能根据答案来解决这个问题。我试图在python的帮助下将excel文件导入SQL Server。这是我写的代码：import pandas as pd

import numpy as np

import pandas.io.sql

import pyodbc

import xlrd

server = "won't disclose private info"

db = 'private info'

conn = pyodbc.connect('DRIVER={SQL Server};SERVER=' + Server + ';DATABASE=' +

db + ';Trusted_Connection=yes')

cursor = conn.cursor()

book = xlrd.open_workbook("Daily Flash.xlsx")

sheet = book.sheet_by_name("Sheet1")

query1 = """CREATE TABLE [LEAF].[MK] ([LEAF][Lease_Number] varchar(255),

[LEAF][Start_Date] varchar(255), [LEAF][Report_Status] varchar(255), [LEAF]

[Status_Date] varchar(255), [LEAF][Current_Status] varchar(255), [LEAF]

[Sales_Rep] varchar(255), [LEAF][Customer_Name] varchar(255),[LEAF]

[Total_Finance] varchar(255),

[LEAF][Rate_Class] varchar(255) ,[LEAF][Supplier_Name] varchar(255) ,[LEAF]

[DecisionStatus] varchar(255))"""

query = """INSERT INTO [LEAF].[MK] (Lease_Number, Start_Date, Report_Status,

Status_Date, Current_Status, Sales_Rep, Customer_Name,Total_Finance,

Rate_Class,Supplier_Name,DecisionStatus) VALUES (%s, %s, %s, %s, %s, %s, %s,

%s, %s, %s, %s)"""

for r in range(1, sheet.nrows):

Lease_Number = sheet.cell(r,0).value

Start_Date = sheet.cell(r,1).value

Report_Status = sheet.cell(r,2).value

Status_Date = sheet.cell(r,3).value

Current_Status= sheet.cell(r,4).value

Sales_Rep = sheet.cell(r,5).value

Customer_Name = sheet.cell(r,6).value

Total_Financed= sheet.cell(r,7).value

Rate_Class = sheet.cell(r,8).value

Supplier_Name = sheet.cell(r,9).value

DecisionStatus= sheet.cell(r,10).value

values = (Lease_Number, Start_Date, Report_Status, Status_Date,

Current_Status, Sales_Rep, Customer_Name, Total_Financed, Rate_Class,

Supplier_Name, DecisionStatus)

cursor.execute(query1)

cursor.execute(query, values)

database.commit()

database.close()

database.commit()

我得到的错误消息是：ProgrammingError Traceback (most recent call last)

in ()

16

17 # Execute sql Query

---> 18 cursor.execute(query, values)

19

20 # Commit the transaction

ProgrammingError: ('The SQL contains 0 parameter markers, but 11 parameters

were supplied', 'HY000')

有人能给我解释一下这个问题吗？我该怎么解决？谢谢您！

更新：

根据下面的评论，我已经得到了这个错误信息。我修改了我的查询，也是因为我试图向其中插入值的表不是以前创建的，所以我更新了代码，试图创建它。

但是，现在我收到错误消息：ProgrammingError: ('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL

Server]The specified schema name "dbo" either does not exist or you do not

have permission to use it. (2760) (SQLExecDirectW)')

我试着通过编写CREATE[HELLO][MK]而不是仅仅创建MK来稍微改变它，但这告诉我MK已经在数据库中了。。。下一步我应该采取什么步骤？