Polars+ConnectorX读取SQLite表遇类型转换错误求助

使用Polars的read_database方法指定connectorx引擎读取SQLite数据库的table1表时抛出RuntimeError，错误信息为Conversion error from type Text at index: 0, trailing input，但读取库内其他表正常，请求排查是表结构问题还是解析错误。

代码示例 #

conn = "sqlite://database.db"
query = "SELECT * FROM table1"
df = pl.read_database(
                    query, 
                    conn,
                    engine='connectorx'
                )

报错栈 #

---------------------------------------------------------------------------
RuntimeError                              Traceback (most recent call last)
Cell In[7], line 1
----> 1 df = pl.read_database(
      2                     query, 
      3                     conn,
      4                     engine='connectorx'
                )

File /usr/local/lib/python3.10/site-packages/polars/io/database.py:95, in read_database(query, connection_uri, partition_on, partition_range, partition_num, protocol, engine)
     24 """
     25 Read a SQL query into a DataFrame.
     26 
   (...)
     92 
     93 """
     94 if engine == "connectorx":
---> 95     return _read_sql_connectorx(
     96         query,
     97         connection_uri,
     98         partition_on=partition_on,
     99         partition_range=partition_range,
    100         partition_num=partition_num,
    101         protocol=protocol,
    102     )
    103 elif engine == "adbc":
    104     if not isinstance(query, str):

File /usr/local/lib/python3.10/site-packages/polars/io/database.py:224, in _read_sql_connectorx(query, connection_uri, partition_on, partition_range, partition_num, protocol)
    219 except ImportError:
    220     raise ImportError(
    221         "connectorx is not installed. Please run `pip install connectorx>=0.3.1`."
    222     ) from None
---> 224 tbl = cx.read_sql(
    225     conn=connection_uri,
    226     query=query,
    227     return_type="arrow2",
    228     partition_on=partition_on,
    229     partition_range=partition_range,
    230     partition_num=partition_num,
    231     protocol=protocol,
    232 )
    234 return from_arrow(tbl)

File /usr/local/lib/python3.10/site-packages/connectorx/__init__.py:257, in read_sql(conn, query, return_type, protocol, partition_on, partition_range, partition_num, index_col)
    254 except ModuleNotFoundError:
    255     raise ValueError("You need to install pyarrow first")
---> 257 result = _read_sql(
    258     conn,
    259     "arrow" if return_type in {"arrow", "polars"} else "arrow2",
    260     queries=queries,
    261     protocol=protocol,
    262     partition_query=partition_query,
    263 )
    264 df = reconstruct_arrow(result)
    265 if return_type in {"polars", "polars2"}:

RuntimeError: Conversion error from type Text at index: 0, trailing input

表数据示例 #

index                       HP_Power    HP_Power_app    HP_PF   HP_L1_Power     HP_L1_Power_app     HP_L1_PF    HP_L2_Power     HP_L2_Power_app     HP_L2_PF    RV_Volts    HP_L1_Voltage   HP_L1_Amps  HP_L2_Amps  RV_Signal
0   2023-01-30 17:21:02+00:00   359545157   509860636   190007960   179712406   253025129   192524  179347720   256835507   190608  3547153805  179156934080    2076666670  2107492180  3084481599
1   2023-01-30 17:21:03+00:00   359547159   509862689   190008935   179713405   253026153   192525  179348723   256836536   190609  3547154472  179157057658    2076674956  2107500508  3084482179
2   2023-01-30 17:21:04+00:00   359549165   509864744   190009912   179714406   253027179   192526  179349728   256837565   190610  3547155162  179157181258    2076683256  2107508837  3084482779
3   2023-01-30 17:21:05+00:00   359551173   509866800   190010888   179715409   253028205   192527  179350733   256838595   190611  3547155890  179157304869    2076691560  2107517168  3084483412
4   2023-01-30 17:21:06+00:00   359553180   509868857   190011864   179716411   253029232   192528  179351738   256839625   190612  3547156762  179157428464    2076699864  210752550

依赖版本 #

pandas==1.5.0
jupyterlab==3.2.9
polars==0.16.13
connectorx==0.3.1
pyarrow==11.0.0

排查分析 #

1. 错误核心来自connectorx的类型转换逻辑，index:0指向结果集的第一列（即示例中的index时间列），SQLite中该列大概率存储为TEXT类型，connectorx在将其转换为Arrow数据类型时失败。
2. 其他表读取正常，说明问题集中在table1的特定列数据或类型定义上，而非通用配置问题。
3. 观察表数据示例，最后一行的HP_L2_Amps值（210752550）长度明显短于其他行，也需排查是否存在数据格式异常，但错误提示明确指向第一列，优先级较低。

解决方案建议 #

1. 隔离验证第一列：单独读取index列，确认是否为该列导致的报错：

query = "SELECT index FROM table1"
df = pl.read_database(query, conn, engine='connectorx')

2. 显式转换时间格式：在SQL查询中用SQLite函数处理时间列，避免connectorx自动转换出错：

query = """
SELECT 
    datetime(index) AS index,
    HP_Power, HP_Power_app, HP_PF, HP_L1_Power, HP_L1_Power_app,
    HP_L1_PF, HP_L2_Power, HP_L2_Power_app, HP_L2_PF, RV_Volts,
    HP_L1_Voltage, HP_L1_Amps, HP_L2_Amps, RV_Signal
FROM table1
"""
df = pl.read_database(query, conn, engine='connectorx')

3. 更换引擎尝试：改用Polars支持的adbc引擎读取，验证是否为connectorx的兼容性问题：

df = pl.read_database(query, conn, engine='adbc')

4. 检查表结构与异常数据：执行SQL查询查看table1的结构，重点确认index列的类型及是否存在不符合格式的行：

PRAGMA table_info(table1);
-- 检查异常行
SELECT * FROM table1 WHERE index NOT LIKE '____-__-__ __:__:__%';

5. 升级依赖版本：旧版本的Polars/connectorx存在已知的类型转换bug，升级到最新稳定版尝试修复：

pip install --upgrade polars connectorx pyarrow

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