使用Python从Excel文件导入数据到SQL Server

Question

使用Python从Excel文件导入数据到SQL Server

10

我发现了一些类似于我遇到错误的问题，但是根据那些答案我还无法解决这个问题。我的目标是使用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)
<ipython-input-24-c525ebf0af73> in <module>()
 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已经在数据库中了...接下来我应该采取什么步骤？

- bernando_vialli

5

请使用 ? 代替 %s 作为占位符。 - Scratch'N'Purr

谢谢，这样就解决了那个错误信息，但又出现了一个新的：ProgrammingError: ('42S02'，"[42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'orders'. (208) (SQLExecDirectW)")。我该怎么办？ - bernando_vialli

我刚意识到“Orders”作为无效对象列出，其实是我在SQL中想要创建的表的名称。那为什么它是无效的？应该将它重命名为什么？ - bernando_vialli

“ORDER”是SQL关键字（如“ORDER BY”），但我通常不会预期“orders”会成为问题。无论如何，请尝试在您的SQL命令文本中使用“[orders]”。 - Gord Thompson

所以我在我的代码中添加了一个额外的查询：query1 = """CREATE TABLE orders (Lease_Number varchar(255), Start_Date varchar(255), Report_Status varchar(255), Status_Date varchar(255), Current_Status varchar(255), Sales_Rep varchar(255), Customer_Name varchar(255),Total_Finance varchar(255), Rate_Class varchar(255) ,Supplier_Name varchar(255) ,DecisionStatus varchar(255))"""，并且还添加了cursor.execute(query1)，这样是否可以解决我的问题？但是现在我又遇到了一个新的错误： - bernando_vialli

显示剩余15条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Scratch'N'Purr · Accepted Answer

根据我们在聊天中的对话，以下是一些要点：

执行 CREATE TABLE 查询后，务必立即提交再运行任何后续的 INSERT 查询。
对于表已经存在于数据库中的情况，使用错误捕获。您问道如果您想要将更多数据导入表中，脚本是否仍会运行。答案是否定的，因为 Python 会在 cursor.execute(query1) 处抛出异常。
如果您想要验证插入操作是否成功，可以进行简单的记录计数检查。

编辑昨天，在我让 @mkheifetz 测试我的代码时，他发现了一个小错误，验证检查会返回 False，原因是数据库已经有现有记录，因此当只与当前导入的数据进行比较时，验证将失败。因此，为解决这个 bug，我又修改了代码。

以下是我如何修改您的代码：

import pandas as pd
import numpy as np
import seaborn as sns
import scipy.stats as stats
import matplotlib.pyplot as plt

import pandas.io.sql
import pyodbc

import xlrd
server = 'XXXXX'
db = 'XXXXXdb'

# create Connection and Cursor objects
conn = pyodbc.connect('DRIVER={SQL Server};SERVER=' + server + ';DATABASE=' + db + ';Trusted_Connection=yes')
cursor = conn.cursor()

# read data
data = pd.read_excel('Flash Daily Apps through 070918.xls')

# rename columns
data = data.rename(columns={'Lease Number': 'Lease_Number',
                            'Start Date': 'Start_Date',
                            'Report Status': 'Report_Status',
                            'Status Date': 'Status_Date',
                            'Current Status': 'Current_Status',
                            'Sales Rep': 'Sales_Rep',
                            'Customer Name': 'Customer_Name',
                            'Total Financed': 'Total_Financed',
                            'Rate Class': 'Rate_Class',
                            'Supplier Name': 'Supplier_Name'})

# export
data.to_excel('Daily Flash.xlsx', index=False)

# Open the workbook and define the worksheet
book = xlrd.open_workbook("Daily Flash.xlsx")
sheet = book.sheet_by_name("Sheet1")

query1 = """
CREATE TABLE [LEAF].[ZZZ] (
    Lease_Number varchar(255),
    Start_Date varchar(255),
    Report_Status varchar(255),
    Status_Date varchar(255),
    Current_Status varchar(255),
    Sales_Rep varchar(255),
    Customer_Name varchar(255),
    Total_Finance varchar(255),
    Rate_Class varchar(255),
    Supplier_Name varchar(255),
    DecisionStatus varchar(255)
)"""

query = """
INSERT INTO [LEAF].[ZZZ] (
    Lease_Number,
    Start_Date,
    Report_Status,
    Status_Date,
    Current_Status,
    Sales_Rep,
    Customer_Name,
    Total_Finance,
    Rate_Class,
    Supplier_Name,
    DecisionStatus
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"""

# execute create table
try:
    cursor.execute(query1)
    conn.commit()
except pyodbc.ProgrammingError:
    pass

# grab existing row count in the database for validation later
cursor.execute("SELECT count(*) FROM LEAF.ZZZ")
before_import = cursor.fetchone()

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

    # Assign values from each row
    values = (Lease_Number, Start_Date, Report_Status, Status_Date, Current_Status,
              Sales_Rep, Customer_Name, Total_Financed, Rate_Class, Supplier_Name,
              DecisionStatus)

    # Execute sql Query
    cursor.execute(query, values)

# Commit the transaction
conn.commit()

# If you want to check if all rows are imported
cursor.execute("SELECT count(*) FROM LEAF.ZZZ")
result = cursor.fetchone()

print((result[0] - before_import[0]) == len(data.index))  # should be True

# Close the database connection
conn.close()