-
-
Notifications
You must be signed in to change notification settings - Fork 17.9k
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
BUG: read_sql tries to convert blob/varbinary to string with pyarrow backend #59242
Open
3 tasks done
Labels
Arrow
pyarrow functionality
Bug
Dtype Conversions
Unexpected or buggy dtype conversions
IO SQL
to_sql, read_sql, read_sql_query
Comments
dbrownems
added
Bug
Needs Triage
Issue that has not been reviewed by a pandas team member
labels
Jul 13, 2024
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 |
mroeschke
added
Dtype Conversions
Unexpected or buggy dtype conversions
IO SQL
to_sql, read_sql, read_sql_query
Arrow
pyarrow functionality
and removed
Needs Triage
Issue that has not been reviewed by a pandas team member
labels
Jul 15, 2024
take |
take |
kastkeepitjumpinlikekangaroos
added a commit
to kastkeepitjumpinlikekangaroos/pandas
that referenced
this issue
Oct 25, 2024
5 tasks
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Labels
Arrow
pyarrow functionality
Bug
Dtype Conversions
Unexpected or buggy dtype conversions
IO SQL
to_sql, read_sql, read_sql_query
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
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
The text was updated successfully, but these errors were encountered: