I'm not sure if this is specifically a changed behaviour of SQLAlchemy 2.0 in combination with Pracle as it's not easy to reproduce. (seems to depend on the amount of decimal places)
I have a Pandas dataframe with just two columns and one line of data in the form of:
seq_id price
2 318 -9.860008
With the Dtypes:
seq_id int64
price float64
When using to_sql I get the following Error (shortened):
sqlalchemy.exc.ArgumentError: Oracle FLOAT types use 'binary precision', which does not convert cleanly from decimal 'precision'.
After some trial and error the solution seems to be to pass the dtype specifically as a SQLAlchemy Type when inserting: (see also: https://docs.sqlalchemy.org/en/20/dialects/oracle.html#sqlalchemy.dialects.oracle.FLOAT)
dataframe.to_sql(name=temp_table, con=connection, if_exists='replace', index=False, dtype={'price': sa.FLOAT})
Is this the correct way to handle this? (Never needed any of this with other DB Backends) Any risk of losing Precision? Shouldn't SQLAlchemy be able to infer the correct type (as it seems to be the case with other float values)
