使用Pandas向SQL Server追加数据时触发pyodbc.ProgrammingError：nvarchar转float数据类型转换失败

Problem Description #

I'm running into an error when trying to append Excel data to an existing SQL Server table:

(pyodbc.ProgrammingError) ('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type nvarchar to float. (8114)')

Here's my program logic:

• First run: Use if_exists='replace' to import Excel data into SQL Server (this works perfectly)
• Subsequent runs in the same session: Use if_exists='append' to add new data (this triggers the conversion error)

I've already tried configuring data type mappings via SSMS Import Wizard, and manually dropping/rebuilding the table, but the issue persists. The error points to a data type mismatch in one of the columns, but I can't figure out why it only happens during append and not replace.

Sample Code #

from selenium import webdriver
from getpass import getpass
from selenium.webdriver.common.keys import Keys
from selenium.webdriver.common.by import By
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from selenium.webdriver.chrome.options import Options
import time
import sys
from datetime import datetime
import pandas as pd
import numpy as np
import sqlalchemy
from sqlalchemy import create_engine
import pyodbc
import os
import datetime

class Data_Initialize:
    def __init__(self, instance):
        SERVER = 'localhost\xxx'
        DATABASE = 'xxx'
        DRIVER = 'SQL SERVER'
        USERNAME = xxx
        PASSWORD = xxx
        DATABASE_CONNECTION = 'mssql+pyodbc://localhost\xxx/xxx?driver=SQL SERVER'
        engine = create_engine(DATABASE_CONNECTION)
        df = pd.read_csv(r'C:\Users\Matt\Downloads\MLS ROI Calc.csv')
        df = df.to_excel(r'C:\Users\Matt\Documents\xxx\MLS ROI Calc.xlsx', index=False)
        df = pd.read_excel(r'C:\Users\Matt\Documents\xxx\MLS ROI Calc.xlsx', sheet_name='Sheet1', engine='openpyxl')
        try:
            if instance == 1:
                df.to_sql(con=engine.connect(), name='Splitt_Housing_Data_Crawler2', index=False, if_exists='replace')
                print("Data successfully transferred for initial instance...")
            else:
                df.drop_duplicates(inplace=True)
                df.to_sql(con=engine.connect(), name='Splitt_Housing_Data_Crawler2', index=False, if_exists='append')
                print("Data successfully transferred for subsequent [" + str(instance) + "] instance...")
        except Exception as e:
            print("ERROR - Data transfer FAILED. - " + str(datetime.datetime.now()))
            print(e)
        os.remove(r'C:\Users\Matt\Downloads\MLS ROI Calc.csv')
        time.sleep(.75)
        os.remove(r'C:\Users\Matt\Documents\xxx\MLS ROI Calc.xlsx')

Question #

Has anyone faced a similar issue before? I'm looking for a solution to fix this data type conversion error when appending data to my SQL Server table.

内容的提问来源于stack exchange，提问作者Mattr42