Pandas version checks
-
[X] I have checked that this issue has not already been reported.
-
[X] I have confirmed this bug exists on the latest version of pandas.
-
[X] I have confirmed this bug exists on the main branch of pandas.
Reproducible Example
create table attachment
(
att_id number not null
, att_contents blob not null
)
import pandas as pd
from sqlalchemy import create_engine, text as sql_text
con_src = create_engine("oracle+oracledb://<username>:<password>@<source_database>")
con_dst = create_engine("oracle+oracledb://<username>:<password>@<destination_database>")
with con_src.connect() as conn:
rd = pd.read_sql(sql=sql_text('select * from attachment'), con=conn)
with con_dst.connect() as conn:
rd.to_sql(name="attachment", con=conn, if_exists='append', index=False)
Issue Description
---------------------------------------------------------------------------
UnicodeDecodeError Traceback (most recent call last)
Cell In[7], line 3
1 con_dst = eng_test
2 with con_dst.connect() as conn:
----> 3 rd.to_sql(name="attachment", con=conn, if_exists='append', index=False)
File C:\dev\jupyter\venv\Lib\site-packages\pandas\util\_decorators.py:333, in deprecate_nonkeyword_arguments.<locals>.decorate.<locals>.wrapper(*args, **kwargs)
327 if len(args) > num_allow_args:
328 warnings.warn(
329 msg.format(arguments=_format_argument_list(allow_args)),
330 FutureWarning,
331 stacklevel=find_stack_level(),
332 )
--> 333 return func(*args, **kwargs)
File C:\dev\jupyter\venv\Lib\site-packages\pandas\core\generic.py:3084, in NDFrame.to_sql(self, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)
2886 """
2887 Write records stored in a DataFrame to a SQL database.
2888
(...)
3080 [(1,), (None,), (2,)]
3081 """ # noqa: E501
3082 from pandas.io import sql
-> 3084 return sql.to_sql(
3085 self,
3086 name,
3087 con,
3088 schema=schema,
3089 if_exists=if_exists,
3090 index=index,
3091 index_label=index_label,
3092 chunksize=chunksize,
3093 dtype=dtype,
3094 method=method,
3095 )
File C:\dev\jupyter\venv\Lib\site-packages\pandas\io\sql.py:842, in to_sql(frame, name, con, schema, if_exists, index, index_label, chunksize, dtype, method, engine, **engine_kwargs)
837 raise NotImplementedError(
838 "'frame' argument should be either a Series or a DataFrame"
839 )
841 with pandasSQL_builder(con, schema=schema, need_transaction=True) as pandas_sql:
--> 842 return pandas_sql.to_sql(
843 frame,
844 name,
845 if_exists=if_exists,
846 index=index,
847 index_label=index_label,
848 schema=schema,
849 chunksize=chunksize,
850 dtype=dtype,
851 method=method,
852 engine=engine,
853 **engine_kwargs,
854 )
File C:\dev\jupyter\venv\Lib\site-packages\pandas\io\sql.py:2018, in SQLDatabase.to_sql(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype, method, engine, **engine_kwargs)
2006 sql_engine = get_engine(engine)
2008 table = self.prep_table(
2009 frame=frame,
2010 name=name,
(...)
2015 dtype=dtype,
2016 )
-> 2018 total_inserted = sql_engine.insert_records(
2019 table=table,
2020 con=self.con,
2021 frame=frame,
2022 name=name,
2023 index=index,
2024 schema=schema,
2025 chunksize=chunksize,
2026 method=method,
2027 **engine_kwargs,
2028 )
2030 self.check_case_sensitive(name=name, schema=schema)
2031 return total_inserted
File C:\dev\jupyter\venv\Lib\site-packages\pandas\io\sql.py:1558, in SQLAlchemyEngine.insert_records(self, table, con, frame, name, index, schema, chunksize, method, **engine_kwargs)
1555 from sqlalchemy import exc
1557 try:
-> 1558 return table.insert(chunksize=chunksize, method=method)
1559 except exc.StatementError as err:
1560 # GH34431
1561 # https://stackoverflow.com/a/67358288/6067848
1562 msg = r"""(\(1054, "Unknown column 'inf(e0)?' in 'field list'"\))(?#
1563 )|inf can not be used with MySQL"""
File C:\dev\jupyter\venv\Lib\site-packages\pandas\io\sql.py:1119, in SQLTable.insert(self, chunksize, method)
1116 break
1118 chunk_iter = zip(*(arr[start_i:end_i] for arr in data_list))
-> 1119 num_inserted = exec_insert(conn, keys, chunk_iter)
1120 # GH 46891
1121 if num_inserted is not None:
File C:\dev\jupyter\venv\Lib\site-packages\pandas\io\sql.py:1010, in SQLTable._execute_insert(self, conn, keys, data_iter)
998 """
999 Execute SQL statement inserting data
1000
(...)
1007 Each item contains a list of values to be inserted
1008 """
1009 data = [dict(zip(keys, row)) for row in data_iter]
-> 1010 result = conn.execute(self.table.insert(), data)
1011 return result.rowcount
File C:\dev\jupyter\venv\Lib\site-packages\sqlalchemy\engine\base.py:1416, in Connection.execute(self, statement, parameters, execution_options)
1414 raise exc.ObjectNotExecutableError(statement) from err
1415 else:
-> 1416 return meth(
1417 self,
1418 distilled_parameters,
1419 execution_options or NO_OPTIONS,
1420 )
File C:\dev\jupyter\venv\Lib\site-packages\sqlalchemy\sql\elements.py:516, in ClauseElement._execute_on_connection(self, connection, distilled_params, execution_options)
514 if TYPE_CHECKING:
515 assert isinstance(self, Executable)
--> 516 return connection._execute_clauseelement(
517 self, distilled_params, execution_options
518 )
519 else:
520 raise exc.ObjectNotExecutableError(self)
File C:\dev\jupyter\venv\Lib\site-packages\sqlalchemy\engine\base.py:1639, in Connection._execute_clauseelement(self, elem, distilled_parameters, execution_options)
1627 compiled_cache: Optional[CompiledCacheType] = execution_options.get(
1628 "compiled_cache", self.engine._compiled_cache
1629 )
1631 compiled_sql, extracted_params, cache_hit = elem._compile_w_cache(
1632 dialect=dialect,
1633 compiled_cache=compiled_cache,
(...)
1637 linting=self.dialect.compiler_linting | compiler.WARN_LINTING,
1638 )
-> 1639 ret = self._execute_context(
1640 dialect,
1641 dialect.execution_ctx_cls._init_compiled,
1642 compiled_sql,
1643 distilled_parameters,
1644 execution_options,
1645 compiled_sql,
1646 distilled_parameters,
1647 elem,
1648 extracted_params,
1649 cache_hit=cache_hit,
1650 )
1651 if has_events:
1652 self.dispatch.after_execute(
1653 self,
1654 elem,
(...)
1658 ret,
1659 )
File C:\dev\jupyter\venv\Lib\site-packages\sqlalchemy\engine\base.py:1848, in Connection._execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
1843 return self._exec_insertmany_context(
1844 dialect,
1845 context,
1846 )
1847 else:
-> 1848 return self._exec_single_context(
1849 dialect, context, statement, parameters
1850 )
File C:\dev\jupyter\venv\Lib\site-packages\sqlalchemy\engine\base.py:1988, in Connection._exec_single_context(self, dialect, context, statement, parameters)
1985 result = context._setup_result_proxy()
1987 except BaseException as e:
-> 1988 self._handle_dbapi_exception(
1989 e, str_statement, effective_parameters, cursor, context
1990 )
1992 return result
File C:\dev\jupyter\venv\Lib\site-packages\sqlalchemy\engine\base.py:2346, in Connection._handle_dbapi_exception(self, e, statement, parameters, cursor, context, is_sub_exec)
2344 else:
2345 assert exc_info[1] is not None
-> 2346 raise exc_info[1].with_traceback(exc_info[2])
2347 finally:
2348 del self._reentrant_error
File C:\dev\jupyter\venv\Lib\site-packages\sqlalchemy\engine\base.py:1969, in Connection._exec_single_context(self, dialect, context, statement, parameters)
1967 break
1968 if not evt_handled:
-> 1969 self.dialect.do_execute(
1970 cursor, str_statement, effective_parameters, context
1971 )
1973 if self._has_events or self.engine._has_events:
1974 self.dispatch.after_cursor_execute(
1975 self,
1976 cursor,
(...)
1980 context.executemany,
1981 )
File C:\dev\jupyter\venv\Lib\site-packages\sqlalchemy\engine\default.py:922, in DefaultDialect.do_execute(self, cursor, statement, parameters, context)
921 def do_execute(self, cursor, statement, parameters, context=None):
--> 922 cursor.execute(statement, parameters)
File C:\dev\jupyter\venv\Lib\site-packages\oracledb\cursor.py:382, in Cursor.execute(self, statement, parameters, **keyword_parameters)
380 self._set_input_sizes = False
381 if parameters is not None:
--> 382 impl.bind_one(self, parameters)
383 impl.execute(self)
384 if impl.fetch_vars is not None:
File src\\oracledb\\impl/base/cursor.pyx:391, in oracledb.base_impl.BaseCursorImpl.bind_one()
File src\\oracledb\\impl/base/cursor.pyx:57, in oracledb.base_impl.BaseCursorImpl._bind_values()
File src\\oracledb\\impl/base/cursor.pyx:98, in oracledb.base_impl.BaseCursorImpl._bind_values_by_name()
File src\\oracledb\\impl/base/bind_var.pyx:130, in oracledb.base_impl.BindVar._set_by_value()
File src\\oracledb\\impl/base/var.pyx:86, in oracledb.base_impl.BaseVarImpl._check_and_set_value()
File src\\oracledb\\impl/base/var.pyx:59, in oracledb.base_impl.BaseVarImpl._check_and_set_scalar_value()
File src\\oracledb\\impl/base/connection.pyx:76, in oracledb.base_impl.BaseConnImpl._check_value()
UnicodeDecodeError: 'utf-8' codec can't decode byte 0xe2 in position 10: invalid continuation byte
Expected Behavior
Rows are successfully inserted into the table.
Installed Versions
INSTALLED VERSIONS
------------------
commit : bdc79c146c2e32f2cab629be240f01658cfb6cc2
python : 3.11.0.final.0
python-bits : 64
OS : Windows
OS-release : 10
Version : 10.0.19045
machine : AMD64
processor : Intel64 Family 6 Model 85 Stepping 4, GenuineIntel
byteorder : little
LC_ALL : None
LANG : None
LOCALE : English_Switzerland.1252
pandas : 2.2.1
numpy : 1.26.2
pytz : 2023.3.post1
dateutil : 2.8.2
setuptools : 69.0.2
pip : 24.0
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : 3.1.2
IPython : 8.18.1
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.8.2
numba : None
numexpr : None
odfpy : None
openpyxl : 3.1.2
pandas_gbq : None
pyarrow : None
pyreadstat : None
python-calamine : None
pyxlsb : None
s3fs : None
scipy : 1.11.4
sqlalchemy : 2.0.23
tables : None
tabulate : 0.9.0
xarray : None
xlrd : None
zstandard : None
tzdata : 2023.3
qtpy : None
pyqt5 : None