Default type map float64 -> float is wrong

[shraik]

When saving a pandas frame with the dtype "float64" the wrong type is substituted.
It is more correct to substitute the type cratedb:double or sqlalchemy:DECIMAL

import sqlalchemy as sa
import pandas as pd

data = {
    "col_1": [19556.88, 629414.27, 51570.0, 2933.52, 20338.98],
    "col_2": [
        15379.920000000002,
        1107140.42,
        8081.999999999999,
        1570.0300000000002,
        29468.539999999997,
    ],
}
df_data = pd.DataFrame.from_dict(data)
print(df_data.dtypes)

dburi = "crate://panduser:a_secret_password@crate.local:4200?ssl=false"
engine = sa.create_engine(dburi, echo=False)
conn = engine.connect()

df_data.to_sql(
    "test_sum",
    conn,
    if_exists="replace",
    index=False,
)
conn.exec_driver_sql("REFRESH TABLE test_sum;")
df_load = pd.read_sql_table("test_sum", conn)

pd.options.display.float_format = "{:.12f}".format
print(df_data.sort_values(by="col_1"))
print("dataframe after loading")
print(df_load.sort_values(by="col_1"))

output:

col_1    float64
col_2    float64
dtype: object
                col_1                col_2
3   2933.520000000000    1570.030000000000
0  19556.880000000001   15379.920000000002
4  20338.980000000000   29468.539999999997
2  51570.000000000000    8081.999999999999
1 629414.270000000019 1107140.419999999925
dataframe after loading
                col_1                col_2
0   2933.520000000000    1570.030000000000
1  19556.880000000001   15379.920000000000
4  20338.980000000000   29468.540000000001
3  51570.000000000000    8082.000000000000
2 629414.250000000000 1107140.399999999907

[amotl]

Dear @shraik,

thank you for writing in. Your report is well appreciated to improve type mapping support for floating point types. We've staged possibly relevant improvements per GH-12 and GH-24 already. Do you think they could fit your bill?

With kind regards,
Andreas.

[amotl]

Dear @shraik,

indeed, GH-12 will improve the situation already. I've updated a8dc4ea to include your test case, bundled a few pending patches per GH-215, and released a preview version per sqlalchemy-cratedb==0.42.0.dev0. Please let us know if that works better for you.

With kind regards,
Andreas.

[amotl]

General notes about pandas and CrateDB

NB: If you are using pandas with CrateDB, you may want to have a look at the Efficient INSERT operations with pandas documentation. It describes the insert_bulk support method, which will unlock the code path to use the CrateDB bulk operations endpoint for improved efficiency.

from sqlalchemy_cratedb.support import insert_bulk

df.to_sql(
   ...
   method=insert_bulk,
   ...
)

[shraik]

Thank you!, everything works correctly in version sqlalchemy-cratedb==0.42.0.dev0.

col_1    float64
col_2    float64
dtype: object
                col_1                col_2
3   2933.520000000000    1570.030000000000
0  19556.880000000001   15379.920000000002
4  20338.980000000000   29468.539999999997
2  51570.000000000000    8081.999999999999
1 629414.270000000019 1107140.419999999925
dataframe after loading
                col_1                col_2
4   2933.520000000000    1570.030000000000
3  19556.880000000001   15379.920000000002
2  20338.980000000000   29468.539999999997
1  51570.000000000000    8081.999999999999
0 629414.270000000019 1107140.419999999925

schema with "sqlalchemy-cratedb==0.42.0.dev0"

I use the insert_bulk functionality, but removed it from the test example to simplify the code.
Thanks again.

[amotl]

Excellent, thanks. We will let you know here when a GA release is available.