I have the DataFrame and I need round(5) on the values in column -> 'result':
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
