Python code sending None record as "None" to the database

Question

I have this for with the name of the text columns of my Dataframe, I want to replace the '' and nan by None for the database to be NULL, but when I try to replace nan to None it gives an error, so I tried to change from nan to '' and then None, but it is sending to the bank as a string "None"

for item in ['column1', 'column2', 'column3']:
    DataFrame[item] = DataFrame[item].astype(str).str.strip().replace('nan', np.nan).replace(np.nan, '').replace('', None)
    del item

Image

Does anyone know how to fix this or have a better idea of how to replace empty texts or 'nan' with None?

Don't make us retype code from an image. Please edit the question and include all code, output, and error messages as plain text. — John Gordon
– John Gordon, Commented Jan 2, 2023 at 19:13
It shouldn't be a string in the first place. Most DB drivers will handle that as NULL and you don't need to worry. Its hard to understand your question — roganjosh
– roganjosh, Commented Jan 2, 2023 at 19:19
@roganjosh Basically there are text records in the dataframe that come as '' or 'nan' and I want to replace them with None, so in the database they will be NULL, if there is an easier way to do this I would love to know! — Py Ton
– Py Ton, Commented Jan 2, 2023 at 19:21
I don't see any database code here at all. Did you mean "dataframe" instead of "database"? — John Gordon
– John Gordon, Commented Jan 2, 2023 at 19:24
@JohnGordon It's just that I didn't include it in the picture, the code that sends the Dataframe to the bank is later in the code, but everything is fine with it, the problem is in that data processing code that I'm trying to do! — Py Ton
– Py Ton, Commented Jan 2, 2023 at 19:26

Joe S · Accepted Answer · 2023-01-02 19:32:03Z

1

Change it to the line below instead

DataFrame[item] = DataFrame[item].astype(str).str.strip().replace('nan', np.nan).replace('', None).replace(np.nan, None)

I wrote a small test for it below :

import pandas as pd
import numpy as np

# Set up the test data
data = {'item': ['abc', '', 'def', 'nan', np.nan, 'ghi']}
DataFrame = pd.DataFrame(data)

# Apply the modified code to the 'item' column of the DataFrame
DataFrame['item'] = DataFrame['item'].astype(str).str.strip().replace('nan', np.nan).replace('', None).replace(np.nan, None)

# Verify that the values in the 'item' column are as expected
expected = ['abc', None, 'def', None, None, 'ghi']
assert (DataFrame['item'].values == expected).all()

I hope it helps

answered Jan 2, 2023 at 19:32

Joe S

722 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Py Ton Over a year ago

Unfortunately I got the same error:: Traceback (most recent call last): File "pydevd_bundle/pydevd_cython.pyx", line 1664, in _pydevd_bundle.pydevd_cython.ThreadTracer.__call_ RecursionError: maximum recursion depth exceeded Fatal Python error: _Py_CheckRecursiveCall: Cannot recover from stack overflow. Python runtime state: initialized

Joe S Over a year ago

You can also try fillna. I think this happened because of too many replace, see below an example : DataFrame[item] = DataFrame[item].replace('', np.nan).replace('nan', np.nan).fillna(None)

Collectives™ on Stack Overflow

Python code sending None record as "None" to the database

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related