pymssql._pymssql.ProgrammingError: (207, b“Invalid column name ‘nan‘...错误问题的解决

问题描述：

用pandas读取csv文件，并通过pymssql存储至sqlserver数据库报错
Traceback (most recent call last):
File “src\pymssql\_pymssql.pyx”, line 461, in pymssql._pymssql.Cursor.execute
File “src\pymssql\_mssql.pyx”, line 1113, in pymssql._mssql.MSSQLConnection.execute_query
File “src\pymssql\_mssql.pyx”, line 1144, in pymssql._mssql.MSSQLConnection.execute_query
File “src\pymssql\_mssql.pyx”, line 1277, in pymssql._mssql.MSSQLConnection.format_and_run_query
File “src\pymssql\_mssql.pyx”, line 1815, in pymssql._mssql.check_cancel_and_raise
File “src\pymssql\_mssql.pyx”, line 1861, in pymssql._mssql.raise_MSSQLDatabaseException
pymssql._mssql.MSSQLDatabaseException: (207, b"Invalid column name ‘nan’.DB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\n")

原因过招

pandas读取csv文件时，因存在空值，pandas转换成了nan值，导致数据导入报错

解决方案

将nan值转换为空值在导入

import pandas as pd
import pymssql

filePath = 'data.csv'
# 读取数据，并通过fillna()方法将nan值转换为空值
f = pd.read_csv(filePath).fillna('').values.tolist()

tuple_List = []
for i in f:
	tuple_List.append(tuple(i))
	
saveToSqlServer(tuple_list) # 存储至数据库

print("数据已存储至sqlserver")