[Python] Pandas - decimal.InvalidOperation: [<class 'decimal.ConversionSyntax'>] during...

I am running an extraction of a table in Python using Pandas. I am getting this output:

PS: The extraction works with other tables, this is happening with a specific one, but it's not the first time I face this error. It has already happened with other table in the past.

Pandas version: 2.2.3
SQLAlchemy version: 2.0.37
Oracledb version: 2.5.1
Python 3.10.12
Ubuntu 22.04.4 LTS

Traceback (most recent call last):
File "/SECRET/test.py", line 23, in <module>
df = pd.read_sql(f'SELECT * FROM {origin_schema}.{table}', oracle_engine)
File "/usr/local/lib/python3.10/dist-packages/pandas/io/sql.py", line 734, in read_sql
return pandas_sql.read_query(
File "/usr/local/lib/python3.10/dist-packages/pandas/io/sql.py", line 1853, in read_query
data = result.fetchall()
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/result.py", line 1315, in fetchall
return self._allrows()
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/result.py", line 548, in _allrows
rows = self._fetchall_impl()
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/cursor.py", line 2130, in _fetchall_impl
return self.cursor_strategy.fetchall(self, self.cursor)
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/cursor.py", line 1140, in fetchall
self.handle_exception(result, dbapi_cursor, e)
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/cursor.py", line 1081, in handle_exception
result.connection._handle_dbapi_exception(
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/base.py", line 2355, in _handle_dbapi_exception
raise exc_info[1].with_traceback(exc_info[2])
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/engine/cursor.py", line 1136, in fetchall
rows = dbapi_cursor.fetchall()
File "/usr/local/lib/python3.10/dist-packages/oracledb/cursor.py", line 779, in fetchall
row = fetch_next_row(self)
File "src/oracledb/impl/base/cursor.pyx", line 552, in oracledb.base_impl.BaseCursorImpl.fetch_next_row
File "src/oracledb/impl/thin/cursor.pyx", line 186, in oracledb.thin_impl.ThinCursorImpl._fetch_rows
File "src/oracledb/impl/thin/protocol.pyx", line 443, in oracledb.thin_impl.Protocol._process_single_message
File "src/oracledb/impl/thin/messages.pyx", line 1388, in oracledb.thin_impl.MessageWithData.postprocess
File "/usr/local/lib/python3.10/dist-packages/sqlalchemy/dialects/oracle/cx_oracle.py", line 1284, in _detect_decimal
return self._to_decimal(value)
decimal.InvalidOperation: [<class 'decimal.ConversionSyntax'>]



The code:

import pandas as pd
import sqlalchemy
from sqlalchemy import create_engine, text
import oracledb

from os import getenv, system
from dotenv import load_dotenv
load_dotenv()

oracle_user = getenv('oracle_user')
oracle_password = getenv('oracle_password')
oracle_dsn = "SECRET"

oracle_engine = create_engine(f'oracle+oracledb://{oracle_user}:{oracle_password}@{oracle_dsn}')

origin_schema = getenv('origin_schema')
table = getenv('table')

# Show package version
print(f'Pandas version: {pd.__version__}')
print(f'SQLAlchemy version: {sqlalchemy.__version__}')
print(f'Oracledb version: {oracledb.__version__}')
print(system('python --version'))
print(system('cat /etc/issue'))

df = pd.read_sql(f'SELECT * FROM {origin_schema}.{table}', oracle_engine)


Probably is an error in some column, how do I resolve this?

I've already tried with coerce_float=False:

df = pd.read_sql(f'SELECT * FROM {origin_schema}.{table}', oracle_engine, coerce_float=False)


Or astype(str):

df = pd.read_sql(f'SELECT * FROM {origin_schema}.{table}', oracle_engine, coerce_float=False).astype(str)


I have no idea what column is the problem, because the traceback does not return this information.

This is a describe of the table in DBeaver

Edit: I'm doing SELECT clauses for every column individually to see which column is returning the error, but it'll take a lot of time, because the table is huge.

Continue reading...