BUG: read_sql tries to convert blob/varbinary to string with pyarrow backend

[dbrownems]

Pandas version checks

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

import sqlite3
import pandas as pd

db = sqlite3.connect("file::memory:?cache=shared")

query = f"""

select cast(x'0123456789abcdef0123456789abcdef' as blob) a

"""

df = pd.read_sql(query, db, dtype_backend='pyarrow')

display(df)

Issue Description

This fails with

UnicodeDecodeError: 'utf-8' codec can't decode byte 0x89 in position 4: invalid start byte

The repro is for sqllite, but the issue is the same with sqlalchemy and pyodbc.

Also read_sql_table fails with the same error.

Expected Behavior

Should succeed and return a dataframe with a binary column. It works with the default backend.

Installed Versions

INSTALLED VERSIONS

commit : d9cdd2e
python : 3.10.12.final.0
python-bits : 64
OS : Linux
OS-release : 5.15.158.2-1.cm2
Version : #1 SMP Sun Jun 9 18:33:38 UTC 2024
machine : x86_64
processor : x86_64
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8

pandas : 2.2.2
numpy : 1.24.3
pytz : 2023.3.post1
dateutil : 2.8.2
setuptools : 68.2.2
pip : 23.1.2
Cython : 3.0.4
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : 4.9.3
html5lib : 1.1
pymysql : None
psycopg2 : None
jinja2 : 3.1.2
IPython : 8.14.0
pandas_datareader : None
adbc-driver-postgresql: None
adbc-driver-sqlite : None
bs4 : 4.12.2
bottleneck : None
dataframe-api-compat : None
fastparquet : None
fsspec : 2023.10.0
gcsfs : None
matplotlib : 3.7.2
numba : 0.57.1
numexpr : None
odfpy : None
openpyxl : 3.1.2
pandas_gbq : None
pyarrow : 12.0.1
pyreadstat : None
python-calamine : None
pyxlsb : None
s3fs : None
scipy : 1.10.1
sqlalchemy : 2.0.22
tables : None
tabulate : 0.9.0
xarray : None
xlrd : None
zstandard : 0.21.0
tzdata : 2023.3
qtpy : None
pyqt5 : None

[asishm]

Thanks for the report. This was likely introduced with #50048 when the nullable keyword (now renamed to dtype_backend) was added to sql functions.

There is a comment #50048 (comment) which is where it assumes it's string data. cc @phofl

Full Traceback

Exception has occurred: UnicodeDecodeError
'utf-8' codec can't decode byte 0x89 in position 4: invalid start byte
  File "/home/asishm/pandas-asishm/pandas/core/arrays/string_.py", line 412, in _from_sequence
    result = lib.ensure_string_array(scalars, na_value=libmissing.NA, copy=copy)
  File "/home/asishm/pandas-asishm/pandas/core/internals/construction.py", line 972, in convert
    arr = arr_cls._from_sequence(arr, dtype=new_dtype)
  File "/home/asishm/pandas-asishm/pandas/core/internals/construction.py", line 993, in <listcomp>
    arrays = [convert(arr) for arr in content]
  File "/home/asishm/pandas-asishm/pandas/core/internals/construction.py", line 993, in convert_object_array
    arrays = [convert(arr) for arr in content]
  File "/home/asishm/pandas-asishm/pandas/io/sql.py", line 161, in _convert_arrays_to_dataframe
    arrays = convert_object_array(
  File "/home/asishm/pandas-asishm/pandas/io/sql.py", line 198, in _wrap_result
    frame = _convert_arrays_to_dataframe(data, columns, coerce_float, dtype_backend)
  File "/home/asishm/pandas-asishm/pandas/io/sql.py", line 2738, in read_query
    frame = _wrap_result(
  File "/home/asishm/pandas-asishm/pandas/io/sql.py", line 691, in read_sql
    return pandas_sql.read_query(
  File "/home/asishm/pd-issues/59242.py", line 12, in <module>
    df = pd.read_sql(query, db, dtype_backend='pyarrow')
UnicodeDecodeError: 'utf-8' codec can't decode byte 0x89 in position 4: invalid start byte

[sukriti1]

take

[kastkeepitjumpinlikekangaroos]

take