从Pandas导入至SQLAlchemy声明式类时类型不匹配，如何转换？

问题背景 #

代码实际可正常运行，但Pyright检测到类型不匹配。将CSV文件导入Pandas DataFrame后，实例化SQLAlchemy的Company类时执行以下代码：

company = Company(name=df['name'][index],
                  url=df['url'][index],
                  stype=df['stype'][index],
                  base_selector=df['base_selector'][index],
                  name_selector=df['name_selector'][index],
                  price_selector=df['price_selector'][index],
                  eos_type=df['eos_type'][index],
                  eos_element=df['eos_element'][index])

收到Pyright错误提示：

Argument of type "Series | Any | ndarray[Any, Unknown] | Unknown | NDArray[Unknown] | DataFrame" cannot be assigned to parameter "stype" of type "SQLCoreOperations[str]"

原因是SQLAlchemy ORM类的属性类型为InstrumentedAttribute，而Pyright无法确定df['col'][index]返回的是字符串标量，反而识别为Series、ndarray等复合类型。

Company类定义如下：

from sqlalchemy import String, mapped_column
from sqlalchemy.orm import Mapped, Base, relationship

class Company(Base):
    __tablename__ = "companies"
    name: Mapped[str] = mapped_column(String(100), primary_key=True, init=True, unique=True)
    url: Mapped[str] = mapped_column(String(100))
    stype: Mapped[str] = mapped_column(String(10))
    base_selector: Mapped[str] = mapped_column(String(100))
    name_selector: Mapped[str] = mapped_column(String(100))
    price_selector: Mapped[str] = mapped_column(String(100))
    eos_type: Mapped[str] = mapped_column(String(50))
    eos_element: Mapped[str] = mapped_column(String(100))
    prices: Mapped[list["Item"]] = relationship("Item", back_populates="company", init=False)

解决方案 #

以下三种方法均可解决Pyright的类型检测问题：

1. 显式转换为字符串类型 #

对每个DataFrame取值结果调用str()，明确告诉Pyright这是字符串类型：

company = Company(
    name=str(df['name'][index]),
    url=str(df['url'][index]),
    stype=str(df['stype'][index]),
    base_selector=str(df['base_selector'][index]),
    name_selector=str(df['name_selector'][index]),
    price_selector=str(df['price_selector'][index]),
    eos_type=str(df['eos_type'][index]),
    eos_element=str(df['eos_element'][index])
)

2. 使用.at或.loc获取标量值 #

Pandas的.at[index, col]和.loc[index, col]会明确返回单个标量值，Pyright对这两个方法的类型推断更准确：

company = Company(
    name=df.at[index, 'name'],
    url=df.at[index, 'url'],
    stype=df.at[index, 'stype'],
    base_selector=df.at[index, 'base_selector'],
    name_selector=df.at[index, 'name_selector'],
    price_selector=df.at[index, 'price_selector'],
    eos_type=df.at[index, 'eos_type'],
    eos_element=df.at[index, 'eos_element']
)

3. 导入CSV时指定全局字符串类型 #

使用pd.read_csv的dtype参数，强制所有列都为字符串类型，从源头上消除类型歧义：

import pandas as pd

# 导入CSV时指定所有列类型为str
df = pd.read_csv('companies.csv', dtype=str)

# 后续实例化时直接取值即可
company = Company(
    name=df['name'][index],
    url=df['url'][index],
    stype=df['stype'][index],
    base_selector=df['base_selector'][index],
    name_selector=df['name_selector'][index],
    price_selector=df['price_selector'][index],
    eos_type=df['eos_type'][index],
    eos_element=df['eos_element'][index]
)

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