我的个人技术笔记

导入数据类型对应关系

13 min read

下面分别说明 Python 导入 Excel 数据时的类型对应，以及从 MySQL 查询数据时的类型对应。

一、Python 导入 Excel 数据的类型对应关系

Excel 中的数据类型并不像数据库那样严格，它通过“单元格格式”来表达数值、文本、日期、布尔等。Python 读取 Excel 时，使用的库不同，映射规则也会不同，常见的有 pandas.read_excel()（背后引擎可以是 openpyxl 或 xlrd）和直接使用 openpyxl。

1. 用 pandas.read_excel() 读取时的类型映射

pandas 会推断整列的数据类型，返回的 DataFrame 的 dtype 是列的通用类型。单个值的实际 Python 类型依赖于此列 dtype。

Excel 中的内容/格式pandas 列类型 (dtype)单元格内的 Python 类型备注
常规数字、数值int64（无缺失/空）
float64（有空值或含小数）		int
float		若包含空值，列会提升为 float64，空值显示 NaN
文本 / 字符串object
文本型数字视作常规数字、数值
空字符串和空白都被读为NaN		str
int或float

float		混合类型列通常也是 object
日期（如 2024-01-01）若 parse_dates=True 或自动识别(当excel中的日期类型为文本时不被识别，为日期时可以自动识别) → datetime64[ns]
若包含空值，空值类型为NaT		pd.Timestamp



NaTType,		本质是 numpy.datetime64 的封装，可当作 datetime 使用
日期+时间（如 2024-01-01 14:30）datetime64[ns]pd.Timestamp同上
纯时间（如 14:30:00）objectdatetime.time整列不是时间类型，但是内部是
布尔值 TRUE/FALSEbool（无缺失）
float64（有缺失）		bool
float（缺失值为float类型)		含有空单元格时可能退化为 float64，值为 float
空白单元格同列数字列 → float64 下的 NaN
同列文本列 → object 下的 NaN (实为 float)		float空值统一为缺失值
空字符串也为缺失值
公式计算结果的值，按值类型对应上表与计算结果类型一致需要 Excel 计算过的值（读取时使用 data_only=True 的引擎参数）
错误值 (#DIV/0! 等)objectstr (如 '#DIV/0!')pandas 默认将错误当作文本

日期类型读取

日期和时间能不能读取为日期和时间类型，主要还是看excel中数据有没有被标为日期时间，如果被标为文本，则该列以及列中的数据都不会为日期时间类型

混合类型列

pandas 会将整个列当作 object 类型，每个单元格保留其原始 Python 类型（如同时包含 int 和 str）。当单元格格式能跟数据格式保持一致，或者常规（无特定格式），pandas可以识别每个单元格本来的格式。如果整列被设置为文本，那么在混合列中就不能识别除文本外的其他格式

2. 用 openpyxl 直接读取单元格 .value 时的类型映射

这种方式可以获取每个单元格的精确 Python 类型。

Excel 单元格类型（数字格式）openpyxl 返回的 Python 类型说明
整数数字int如 Excel 中的 100
小数 / 浮点数字float如 3.14
日期格式（如 yyyy-mm-dd）datetime.datetimeExcel 序列号转换为 datetime（日期部分有效，时间默认 00:00:00）
日期+时间格式datetime.datetime完整的日期时间
纯时间格式（如 hh:mm:ss）datetime.timeExcel 内部为小数，openpyxl 转为 time
时长格式（如 [hh]:mm:ss）datetime.timedelta适用于超过 24 小时的时间差
文本 / 字符串str
布尔值boolTrue 或 False
空单元格None
错误 (#N/A, #VALUE! 等)str（如 '#N/A'）需注意，不是异常而是字符串
公式（工作簿未计算）公式字符串（如 '=A1+B1'）若 data_only=True 且缓存了结果，则返回计算结果值

小结与注意点

  • Excel 没有独立的“日期”或“时间”类型，它们本质是数字（序列号），通过数字格式显示。openpyxl 能根据格式智能转换为 datetime/time/timedelta，而 pandas 默认只自动处理常见的日期时间列，纯时间列可能需要手动 pd.to_timedelta 或自定义转换。
  • 公式：如果 Excel 文件未保存计算结果，openpyxl 只能读到公式字符串，pandas 可能无法读取到期望的值。

二、查询 MySQL 数据时 Python 数据类型的对应关系

同样取决于你使用的 Python 库。最常见的是 DB-API 驱动（如 pymysql、mysql-connector-python） 和 pandas.read_sql()，下面以 pymysql 为例展示标准 DB-API 的映射，再补充 pandas 的差异。

1. pymysql（及大多数 MySQL Python 驱动）的类型映射

默认情况下，游标返回的行中的字段已经转换为对应的 Python 对象。

MySQL 数据类型Python 类型备注
TINYINT, SMALLINT, MEDIUMINT, INT, INTEGER, BIGINTintPython 3 的 int 无上限，能容纳 BIGINT
FLOAT, DOUBLEfloat
DECIMAL, NUMERICdecimal.Decimal保留精度，适合金融计算
BITbytesBIT(1) 返回 b'\x00' 或 b'\x01'
BOOL, BOOLEANint (0 或 1)实际上是 TINYINT(1)，默认返回 int；可通过 conv 参数转为 bool
CHAR, VARCHAR, TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXTstr
ENUM, SETstr返回枚举/集合的字符串值
BINARY, VARBINARY, TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOBbytes二进制数据
DATEdatetime.date
DATETIME, TIMESTAMPdatetime.datetime
TIMEdatetime.timedeltaMySQL 的 TIME 范围较大（-838:59:59 ~ 838:59:59），timedelta 可完整表示
YEARint返回年份整数（如 2024）
JSONstrMySQL 返回 JSON 文本，需手动 json.loads() 解析
GEOMETRYbytes返回 WKB（Well-Known Binary）格式字节串
NULLNone

一些可调整的地方

  • 布尔值：pymysql 默认将 TINYINT(1) 转为 int。若想直接得到 bool，可在连接时设置 conv=pymysql.converters.conversions 或添加自定义转换器。
  • TIME 作为字符串：某些驱动或设置下，TIME 会返回 str（如 '14:30:00'），可检查驱动文档或连接参数（如 pymysql 的 converter_class）。

2. 使用 pandas.read_sql() 时的类型映射

pandas.read_sql() 底层利用 SQLAlchemy 或 DB-API 游标，并将结果构造成 DataFrame。它会再次调整类型以适合数据分析。

MySQL 类型pandas 列类型 (dtype)说明
整数列 (INT, BIGINT 等)int64（有空值则变为 float64）pandas 要求整数列不能有 NaN，有缺失值会提升为 float
FLOAT, DOUBLEfloat64
DECIMALfloat64 或 object往往被转为 float，可能丢失精度；也可通过 SQLAlchemy 保留为 Decimal（列类型为 object）
CHAR, VARCHAR, TEXT 等object (元素为 str)
DATEobject (元素为 datetime.date) 或 datetime64[ns]若连接时加了 parse_dates 参数，则转为 datetime64
DATETIME, TIMESTAMPdatetime64[ns]
TIMEobject (元素为 timedelta 或 str)取决于驱动返回的类型，pandas 不会自动转为时间类型
BOOLEAN / BOOLint64 或 bool若驱动返回 0/1，则是 int；若返回 True/False 则为 bool
ENUM, SETobject (字符串)
JSONobject (字符串)需手动解析 JSON
空值 NULLNaN 或 None取决于列类型，数值列 NaN，对象列可能是 None 或 NaN

3. 字节类型转换

从数据库读取 BIT 类型数据时，Python 里得到的往往不是直观的 0/1 或 True/False，常见的是 bytes 或字符串。下面根据不同驱动说明如何还原成正常类型。

1. BIT 数据在 Python 驱动中的常见形态

数据库驱动读取 BIT(1) 的返回类型示例值
pymysqlbytesb'\x00' 或 b'\x01'
mysql-connector-pythonbytearray 或 bytesbytearray(b'\x01')
SQLAlchemy (底层用pymysql)通常自动转为 int1
某些ODBC/JDBC桥接字符串 '0' / '1''1'

如果是 BIT(n>1)，通常返回对应的字节串，里面存储的是二进制表示，比如 b'\x03' 代表 3。

2. 转换方法

情形一：返回 bytes / bytearray

使用 int.from_bytes() 转为整数，再按需转布尔值。

def bit_to_int(raw):
    """将 bytes/bytearray 转为整数"""
    if raw is None:
        return None
    return int.from_bytes(raw, byteorder='big', signed=False)
 
# 使用示例
val = b'\x01'
int_val = bit_to_int(val)   # 1
bool_val = bool(int_val)    # True

如果确定只有 0 和 1，也可以更直接：

bool_val = (val != b'\x00')
情形二：返回字符串 '0' / '1'

直接 int() 或比较即可。

int_val = int(val)          # 1
bool_val = val == '1'       # True
情形三：可能混入十六进制字符串 "0x01"

先用 int(..., 16) 转换。

if isinstance(val, str) and val.startswith('0x'):
    int_val = int(val, 16)

3. 封装一个通用转换函数

def parse_db_bit(value):
    """将数据库读取的 BIT 值转为 Python 整数"""
    if value is None:
        return None
    if isinstance(value, (bytes, bytearray)):
        return int.from_bytes(value, 'big')
    if isinstance(value, str):
        # 处理类似 '0x01' 的十六进制字符串
        if value.startswith('0x') or value.startswith('0X'):
            return int(value, 16)
        return int(value)   # 普通数字字符串
    if isinstance(value, int):
        return value
    raise TypeError(f"Unexpected BIT type: {type(value)}")

4. 从源头改进（连接参数设置）

如果不想每次手动转换，可以在创建连接时让驱动自动转换：

pymysql

设置 conv 参数将 BIT 映射为自定义转换器：

import pymysql
from pymysql.constants import FIELD_TYPE
 
def bit_converter(value):
    if value is None:
        return None
    return int.from_bytes(value, 'big')
 
conversions = pymysql.converters.conversions.copy()
conversions[FIELD_TYPE.BIT] = bit_converter
 
conn = pymysql.connect(
    host='...',
    user='...',
    password='...',
    db='...',
    charset='utf8mb4',
    conv=conversions
)

这样所有 BIT 字段查询出来就直接是整数。

mysql-connector-python

该驱动有时会返回 bytearray，可以在游标上设置 binary=True 或使用 raw 模式？ 更简单的方法还是后处理，或者使用 converter_class 自定义。但通用性不如后处理。

SQLAlchemy

如果你用的是 ORM，通常模型字段定义为 Boolean 或 Integer 即可自动映射，无需额外处理。

5. 注意 NULL 值

数据库的 BIT 字段允许 NULL，Python 里会得到 None，转换时请保留 None。

建议：如果项目里多处都要查 BIT 字段，最好在数据库连接层统一做转换（比如修改 pymysql 的 converter），一劳永逸；如果只是少量查询，用现成的转换函数更简单。

小结与实用建议

  • 从 MySQL 拿数据做精确计算：使用 pymysql 直接获取 Decimal 而不要用 pandas 将其转为 float。
  • 处理时间：pymysql 的 TIME → timedelta 很方便；而 pandas 读取时通常需要额外的 pd.to_timedelta 进行转换。
  • 布尔值：如果数据库使用 TINYINT(1) 表示布尔，记得根据需要转换 int → bool。
  • JSON 字段：从 MySQL 查出来默认是字符串，一般需要 json.loads() 解析成字典或列表。

总的来说，Python 与 Excel 的类型映射高度依赖读取库及其参数，Python 与 MySQL 的映射则由驱动和是否使用 pandas 决定，但大体遵循上述规则。搞清楚这些对应关系，在数据清洗和格式转换时就能避免很多隐式错误。

Graph View