Polars能否导入SQLite的BLOB字段？导入报错求助

我尝试用以下代码将SQLite表导入Polars DataFrame：

import polars as pl

uri = "sqlite:///tmp/dbname.db"
query = ("SELECT * from alib")
df = pl.read_database_uri(query=query, uri=uri)

该SQLite表的首列__path为BLOB类型，执行时抛出错误：

Traceback (most recent call last):
  File "/home/x/mypy/lib/python3.12/site-packages/polars/io/database/_utils.py", line 54, in _read_sql_connectorx
    tbl = cx.read_sql(
          ^^^^^^^^^^^^
  File "/home/x/mypy/lib/python3.12/site-packages/connectorx/__init__.py", line 386, in read_sql
    result = _read_sql(
             ^^^^^^^^^^
RuntimeError: Invalid column type Text at index: 0, name: __path
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
  File "<input>", line 1, in <module>
  File "/home/x/mypy/lib/python3.12/site-packages/polars/io/database/functions.py", line 396, in read_database_uri
    return _read_sql_connectorx(
           ^^^^^^^^^^^^^^^^^^^^^
  File "/home/x/mypy/lib/python3.12/site-packages/polars/io/database/_utils.py", line 66, in _read_sql_connectorx
    raise type(err)(errmsg) from err
RuntimeError: Invalid column type Text at index: 0, name: __path

附该表Schema：

CREATE TABLE alib (
    __path                            BLOB UNIQUE,
    __filename                        BLOB,
    __dirpath                         BLOB,
    __filename_no_ext                 BLOB,
    __ext                             BLOB,
    __accessed                        TEXT,
    __app                             TEXT,
    __bitrate                         TEXT,
    __bitspersample                   TEXT,
    __bitrate_num                     TEXT,
    __frequency_num                   TEXT,
    __frequency                       TEXT,
    __channels                        TEXT,
    __created                         TEXT,
    __dirname                         TEXT,
    __file_access_date                TEXT,
    __file_access_datetime            TEXT,
    __file_access_datetime_raw        TEXT,
    __file_create_date                TEXT,
    __file_create_datetime            TEXT,
    __file_create_datetime_raw        TEXT,
    __file_mod_date                   TEXT,
    __file_mod_datetime               TEXT,
    __file_mod_datetime_raw           TEXT,
    __file_size                       TEXT,
    __file_size_bytes                 TEXT,
    __file_size_kb                    TEXT,
    __file_size_mb                    TEXT,
    __filetype                        TEXT,
    __image_mimetype                  TEXT,
    __image_type                      TEXT,
    __layer                           TEXT,
    __length                          TEXT,
    __length_seconds                  TEXT,
    __mode                            TEXT,
    __modified                        TEXT,
    __num_images                      TEXT,
    __parent_dir                      TEXT,
    __size                            TEXT,
    __tag                             TEXT,
    __tag_read                        TEXT,
    __version                         TEXT,
    __vendorstring                    TEXT,
    __md5sig                          TEXT,
    tagminder_uuid                    BLOB,
    sqlmodded                         TEXT,
    reflac                            TEXT,
    discnumber                        TEXT,
    track                             TEXT,
    title                             TEXT,
    subtitle                          TEXT,
    work                              TEXT,
    part                              TEXT,
    live                              TEXT,
    composer                          TEXT,
    arranger                          TEXT,
    lyricist                          TEXT,
    writer                            TEXT,
    artist                            TEXT,
    performer                         TEXT,
    personnel                         TEXT,
    conductor                         TEXT,
    engineer                          TEXT,
    producer                          TEXT,
    mixer                             TEXT,
    remixer                           TEXT,
    albumartist                       TEXT,
    discsubtitle                      TEXT,
    album                             TEXT,
    version                           TEXT,
    releasetype                       TEXT,
    year                              TEXT,
    originaldate                      TEXT,
    originalreleasedate               TEXT,
    originalyear                      TEXT,
    genre                             TEXT,
    style                             TEXT,
    mood                              TEXT,
    theme                             TEXT,
    rating                            TEXT,
    compilation                       TEXT,
    bootleg                           TEXT,
    label                             TEXT,
    musicbrainz_albumartistid         TEXT,
    musicbrainz_albumid               TEXT,
    musicbrainz_artistid              TEXT,
    musicbrainz_composerid            TEXT,
    musicbrainz_discid                TEXT,
    musicbrainz_producerid            TEXT,
    musicbrainz_releasegroupid        TEXT,
    musicbrainz_releasetrackid        TEXT,
    musicbrainz_trackid               TEXT,
    musicbrainz_workid                TEXT,
    lyrics                            TEXT,
    unsyncedlyrics                    TEXT,
    performancedate                   TEXT,
    acoustid_fingerprint              TEXT,
    acoustid_id                       TEXT,
    analysis                          TEXT,
    asin                              TEXT,
    barcode                           TEXT,
    catalog                           TEXT,
    catalognumber                     TEXT,
    country                           TEXT,
    discogs_artist_url                TEXT,
    discogs_release_url               TEXT,
    fingerprint                       TEXT,
    isrc                              TEXT,
    recordinglocation                 TEXT,
    recordingstartdate                TEXT,
    replaygain_album_gain             TEXT,
    replaygain_album_peak             TEXT,
    replaygain_track_gain             TEXT,
    replaygain_track_peak             TEXT,
    review                            TEXT,
    upc                               TEXT,
    musicip_data                      TEXT,
    type                              TEXT,
    musicip_puid                      TEXT,
    movement                          TEXT
);

请问Polars完全不支持SQLite的BLOB字段，还是需要特殊配置才能正常导入？

Polars本身并非完全不支持SQLite的BLOB字段，问题出在它默认使用的connectorx库对BLOB类型的识别错误上。可以通过以下两种方式解决：

方法1：修改查询语句，显式转换BLOB字段 #

在SQL查询中用SQLite的hex()函数将BLOB转为十六进制字符串，导入后再转回二进制：

import polars as pl

uri = "sqlite:///tmp/dbname.db"
query = """
SELECT 
    hex(__path) as __path,
    hex(__filename) as __filename,
    hex(__dirpath) as __dirpath,
    hex(__filename_no_ext) as __filename_no_ext,
    hex(__ext) as __ext,
    hex(tagminder_uuid) as tagminder_uuid,
    __accessed, __app, __bitrate, __bitspersample, __bitrate_num,
    __frequency_num, __frequency, __channels, __created, __dirname,
    __file_access_date, __file_access_datetime, __file_access_datetime_raw,
    __file_create_date, __file_create_datetime, __file_create_datetime_raw,
    __file_mod_date, __file_mod_datetime, __file_mod_datetime_raw,
    __file_size, __file_size_bytes, __file_size_kb, __file_size_mb,
    __filetype, __image_mimetype, __image_type, __layer, __length,
    __length_seconds, __mode, __modified, __num_images, __parent_dir,
    __size, __tag, __tag_read, __version, __vendorstring, __md5sig,
    sqlmodded, reflac, discnumber, track, title, subtitle, work, part,
    live, composer, arranger, lyricist, writer, artist, performer, personnel,
    conductor, engineer, producer, mixer, remixer, albumartist, discsubtitle,
    album, version, releasetype, year, originaldate, originalreleasedate,
    originalyear, genre, style, mood, theme, rating, compilation, bootleg,
    label, musicbrainz_albumartistid, musicbrainz_albumid, musicbrainz_artistid,
    musicbrainz_composerid, musicbrainz_discid, musicbrainz_producerid,
    musicbrainz_releasegroupid, musicbrainz_releasetrackid, musicbrainz_trackid,
    musicbrainz_workid, lyrics, unsyncedlyrics, performancedate, acoustid_fingerprint,
    acoustid_id, analysis, asin, barcode, catalog, catalognumber, country,
    discogs_artist_url, discogs_release_url, fingerprint, isrc, recordinglocation,
    recordingstartdate, replaygain_album_gain, replaygain_album_peak,
    replaygain_track_gain, replaygain_track_peak, review, upc, musicip_data,
    type, musicip_puid, movement
FROM alib
"""
df = pl.read_database_uri(query=query, uri=uri)

# 将十六进制字符串转回二进制
df = df.with_columns([
    pl.col(col).str.from_hex().alias(col) 
    for col in ["__path", "__filename", "__dirpath", "__filename_no_ext", "__ext", "tagminder_uuid"]
])

方法2：改用adbc-sqlite驱动 #

ADBC驱动对SQLite的BLOB类型兼容性更好，无需修改查询语句：

1. 先安装依赖：

pip install adbc-driver-sqlite polars[adbc]

2. 修改代码：

import polars as pl
from adbc_driver_sqlite import dbapi

with dbapi.connect("tmp/dbname.db") as conn:
    df = pl.read_database("SELECT * FROM alib", connection=conn)

这种方式会自动将BLOB字段映射为Polars的Binary类型。

• connectorx库存在类型识别bug，错误将SQLite BLOB识别为Text类型，导致导入失败；
• ADBC是更推荐的数据库读取方式，对类型支持更完善；
• 若必须使用connectorx，只能通过SQL转换BLOB字段的方式绕过问题。

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