[Python] Unable to suppress scientific notation in datarame, which in turn is causing...

Below is what I'm trying to accomplish:

1. Get data from API
2. Clean the data
3. Insert the data into SQL Server

Issue that I'm facing:

• I convert the data that I receive from api to a pandas dataframe

response = requests.get(API_SCHEME_DATA, headers=headers, data=payload, timeout=20)
if response.status_code == 200:
data = response.json()["Data"]
df = pd.DataFrame(data, dtype=str)

• Now I, clean this data in a very simple step

# bigint list contains column names that I can treat as numeric
for col in bigint_list:
df[col] = pd.to_numeric(df[col], errors='coerce')
df[col] = df[col].fillna(0.0)

df = df.astype(str)
df = df.where(pd.notnull(df), None)

• I then use pyodbc connection to insert dataframe into SQL Server

# Prepare columns and placeholders for the SQL query
columns = [f"[{col}]" for col in df.columns]
placeholders = ', '.join(['?' for _ in range(len(df.columns))])

# SQL Insert query
insert_data_query = f"""
INSERT INTO {table_name} ({', '.join(columns)})
VALUES ({placeholders})
"""

# Convert the dataframe rows to a list of tuples for insertion
rows = [tuple(x) for x in df.replace({None: None}).values]
# Execute the insert query for multiple rows
connection_cursor.executemany(insert_data_query, rows)
connection_cursor.connection.commit()

• While inserting I get the below error:

pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type nvarchar to numeric. (8114) (SQLExecDirectW)')

• I believe, I have narrowed down the culprit to a parameter coming in the api

"CurrentValue": "0.00002"

• While inserting this parameter, the parameter value has been changed to scientific notation, and I can see the value being changed to '2e-05'. The column that I'm inserting in the SQL server is of DECIMAL type. And I believe this is why I'm getting the error.

What I have done so far to resolve:

• I have tried to suppress the scientific notation by:

pd.options.display.float_format = '{:.8f}'.format


and also tried to round off the columns like below during cleaning step:

# bigint list contains column names that I can treat as numeric
for col in bigint_list:
df[col] = pd.to_numeric(df[col], errors='coerce')
df[col] = df[col].fillna(0.0)
df[col] = df[col].round(10)

df = df.astype(str)
df = df.where(pd.notnull(df), None)


However, nothing seems to work and I'm still seeing the value converted to scientific notation. Any help would be appreciated.

Continue reading...