我发现了一些其他的问题,它们与我得到的结果有着相似的错误,但是还不能根据答案来解决这个问题。我试图在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已经在数据库中了。。。下一步我应该采取什么步骤?