Python处理数据写入SQL Server时float类型拆分报错求助

问题背景 #

用Python抓取生成报表，清理格式化后写入SQL Server，再推送至Tableau。执行代码时因FirstName列存在float类型对象报错：

'float' object has no attribute 'replace'

报错代码行：

df[['FirstName', 'Business_Title']] = df['FirstName'].astype(str).str.rsplit(' ', 1, expand=True)

已尝试两种写法均未解决：

• df[['FirstName', 'Business_Title']] = df['FirstName'].str.rsplit(' ', 1, expand=True)
• df[['FirstName', 'Business_Title']] = df['FirstName'].apply(lambda x: pd.Series(str(x).rsplit(' ', 1)) if pd.notna(x) and isinstance(x, str) else pd.Series([x, '']))

完整代码（已屏蔽敏感信息）：

import pandas as pd
import pyodbc

def read_and_split_name(file_path, server_name, database_name):
    try:
        # Read the Excel file into a pandas DataFrame, skip the first 4 rows, and use the 5th row as the header
        df = pd.read_excel(file_path, header=4)

        # Split the "Name" column into "First Name" and "Last Name" based on the comma
        df[['LastName', 'FirstName']] = df['Rendering'].astype(str).str.split(', ', 1, expand=True)

        # Further split the "First Name" column by spaces and add a new column "Business_Title"
        # Handle non-string values by converting them to strings
        df['FirstName'] = df['FirstName'].astype(str)
> df[['FirstName', 'Business_Title']] = df['FirstName'].astype(str).str.rsplit(' ', 1, expand=True)
> 
        # Use transform('nunique') to get the count of unique values for each group
        df['_Unique'] = df.groupby(['Loc Name', 'LastName', 'FirstName'])['Enc Dt'].transform('nunique')

        # Use transform('count') to get the total count for each group
        df['_ENC'] = df.groupby(['Loc Name', 'LastName'])['Enc Dt'].transform('count')

        # Drop rows where last name is 'Nurse' or 'Enabling'
        df = df[~df['LastName'].isin(['Nurse', 'Enabling'])]

        # Replace spaces and dashes with underscores in the "Loc Name" column
        df['Loc Name'] = df['Loc Name'].str.replace(' ', '_').str.replace('-', '_')

        # Calculate the new column "ENC_Per_"
        df['ENC_Per_'] = df['_ENC'] / df['_Unique']

        # Replace non-finite values (NA or inf) with a suitable replacement (e.g., 0)
        df['_Unique'] = df['_Unique'].fillna(0).astype(int)
        df['_ENC'] = df['_ENC'].fillna(0).astype(int)
        df['ENC_Per_'] = df['ENC_Per_'].fillna(0).astype(int)

        # Extract the month part of the date in MMM format
        df['MMM_Format'] = pd.to_datetime(df['Enc Dt'], errors='coerce', format='%m/%d/%Y').dt.strftime('%b')

        # Convert the month abbreviations to uppercase
        df['MMM_Format'] = df['MMM_Format'].str.upper()

        # Update column headers based on the formatted month
        df.rename(columns={
            '_ENC': f"{df['MMM_Format'][0]}_ENC",
            'ENC_Per_': f"ENC_Per_{df['MMM_Format'][0]}",
            '_Unique': f"{df['MMM_Format'][0]}_Unique"
        }, inplace=True)

        # Export the DataFrame to a Microsoft SQL Server table using pyodbc
        conn_str = f"DRIVER={{ODBC Driver 17 for SQL Server}};SERVER={server_name};DATABASE={database_name};Trusted_Connection=yes;"
        with pyodbc.connect(conn_str) as conn:
            cursor = conn.cursor()
            for index, row in df.iterrows():
                table_name = row['Loc Name'].replace(' ', '_').replace('-', '_')  # Use Loc Name as the table name
                update_query = f"""
                    UPDATE {table_name}
                    SET FirstName = '{row['FirstName']}',
                        LastName = '{row['LastName']}',
                        Business_Title = '{row['Business_Title']}',
                        {row['MMM_Format']}_Unique = {row[f"{row['MMM_Format']}_Unique"]},
                        {row['MMM_Format']}_ENC = {row[f"{row['MMM_Format']}_ENC"]}
                    WHERE FirstName = '{row['FirstName']}' AND LastName = '{row['LastName']}'
                """
                cursor.execute(update_query)

        print("Update to SQL Server successful.")

    except Exception as e:
        print(f"An error occurred: {e}")

# Specify the path to your .xls Excel file
file_path = "FILEPATH"

# Specify SQL Server connection details
server_name = "SERVERNAME"
database_name = "DATABASENAME"

# Call the function to read, split names, replace spaces and dashes, calculate new column, convert to integers, update column headers, and update SQL Server
read_and_split_name(file_path, server_name, database_name)

解决方法 #

方案1：先处理空值再统一转字符串 #

问题核心是FirstName列中的NaN会被识别为float类型，直接astype(str)会把NaN转为字符串'nan'，需先填充空值再处理：

# 替换原问题代码块：
# df['FirstName'] = df['FirstName'].astype(str)
# df[['FirstName', 'Business_Title']] = df['FirstName'].astype(str).str.rsplit(' ', 1, expand=True)

# 改成：
# 先填充空值为空白字符串，再转换为str类型
df['FirstName'] = df['FirstName'].fillna("").astype(str)
# 执行拆分，expand=True会自动生成两列，空值填充为NaN
df[['FirstName', 'Business_Title']] = df['FirstName'].str.rsplit(' ', 1, expand=True)
# 最后把拆分后的空值统一填充为空白字符串
df[['FirstName', 'Business_Title']] = df[['FirstName', 'Business_Title']].fillna("")

方案2：用自定义函数做健壮性处理 #

通过apply遍历每个元素，覆盖空值、浮点、纯字符串等所有场景：

# 替换原问题代码块：
# df['FirstName'] = df['FirstName'].astype(str)
# df[['FirstName', 'Business_Title']] = df['FirstName'].astype(str).str.rsplit(' ', 1, expand=True)

# 改成：
def split_name_title(value):
    # 处理空值或非字符串类型
    if pd.isna(value) or not isinstance(value, str):
        return pd.Series(["", ""])
    # 从右往左按空格拆分1次
    parts = value.rsplit(' ', 1)
    # 如果只有1个部分，说明没有头衔，返回原内容+空字符串
    if len(parts) == 1:
        return pd.Series([parts[0], ""])
    # 拆分成功返回两部分
    return pd.Series(parts)

df[['FirstName', 'Business_Title']] = df['FirstName'].apply(split_name_title)

额外优化：修复SQL注入风险 #

原代码中用字符串拼接生成SQL语句存在注入风险，改用参数化查询（表名需单独校验合法性）：

# 替换原SQL更新代码块：
# update_query = f"""
#     UPDATE {table_name}
#     SET FirstName = '{row['FirstName']}',
#         LastName = '{row['LastName']}',
#         Business_Title = '{row['Business_Title']}',
#         {row['MMM_Format']}_Unique = {row[f"{row['MMM_Format']}_Unique"]},
#         {row['MMM_Format']}_ENC = {row[f"{row['MMM_Format']}_ENC"]}
#     WHERE FirstName = '{row['FirstName']}' AND LastName = '{row['LastName']}'
# """
# cursor.execute(update_query)

# 改成：
import re

# 校验并清理表名，只保留合法字符
valid_table_name = re.sub(r'[^a-zA-Z0-9_]', '', row['Loc Name'].replace(' ', '_').replace('-', '_'))
# 参数化SQL，表名无法参数化，需提前校验
update_query = f"""
    UPDATE {valid_table_name}
    SET FirstName = ?,
        LastName = ?,
        Business_Title = ?,
        {row['MMM_Format']}_Unique = ?,
        {row['MMM_Format']}_ENC = ?
    WHERE FirstName = ? AND LastName = ?
"""
# 传入参数列表
cursor.execute(update_query,
               row['FirstName'],
               row['LastName'],
               row['Business_Title'],
               row[f"{row['MMM_Format']}_Unique"],
               row[f"{row['MMM_Format']}_ENC"],
               row['FirstName'],
               row['LastName'])

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