How to correct insert data to SQL Server when using the float data with round(5) and have 0.0000 values data frame column?

Viewed 29

I have the DataFrame and I need round(5) on the values in column -> 'result':

enter image description here

Using the def insert_to_mssql(df) I'm trying to insert data into SQL Server, but I get an error:

def insert_to_mssql(df):
    conn = "DRIVER={ODBC Driver 17 for SQL Server};SERVER=..;DATABASE=..;UID=..;PWD=.."
    quoted = quote_plus(conn)
    new_con = 'mssql+pyodbc:///?odbc_connect={}'.format(quoted)
    engine = create_engine(new_con)

    @event.listens_for(engine, 'before_cursor_execute')
    def receive_before_cursor_execute(conn, cursor, statement, params, context, executemany):
        print("FUNC call --> Data has been inserted")
        if executemany:
            cursor.fast_executemany = True

    df.to_sql('table', engine, if_exists='replace', chunksize=None, index=False)

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError): The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 5 (""): The supplied value is not a valid instance of data type float. Check the source data for invalid values. An example of an invalid value is data of numeric type with scale greater than precision

For example, I convert df['result'].astype(str) and insert into SQL Server, and data is recorded, but I have the problem with convert varchar to float or numeric in SQL Server.

How do I avoid 0.00000 when writing to database. I can't replace 0.0000 with NaN, I need 0 in the result column for further calculations

0 Answers
Related