Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I have a Python dataframe with NULL value in some rows, while inserting to postgresql, some null in datetype column turns into 'NaT' string or 'NaN', I like it to be a real NULL , which is nothing in that cell.

sample dataframe before insert

import psycopg2
import pandas as pd
import numpy as np
conn=psycopg2.connect(dbname= 'myDB', host='amazonaws.com', 
port= '2222', user= 'mysuser', password= 'mypass')
cur = conn.cursor()
df= pd.DataFrame({ 'zipcode':[1,np.nan,22,88],'city':['A','h','B',np.nan]})
subset = df[['zipcode', 'city']]
data = [tuple(x) for x in subset.values]
records_list_template = ','.join(['%s'] * len(data)) 
insert_query = 'insert into public.MyTable (zipcode, city) values {}'.format(records_list_template)
cur.execute(insert_query, data)
conn.commit()
result in postgresql table
expected result below
    'zipcode':[1,np.nan,22,88],
    'city':['A','h','B',np.nan],
    'date':['2019-01-01','2019-01-02',pd.NaT,pd.NaT]})
df['date'] = [d.strftime('%Y-%m-%d') if not pd.isnull(d) else None for d in df['date']]
subset = df.where((pd.notnull(df)), None)
See DataFrame.where
                my bad, my actual table contains a datetype column which has some NaT,  the df.where((pd.notnull(df)), None) can't convert NaT
– PyBoss
                Jan 29, 2019 at 22:49
                Indeed, it does not work with NaT (I thought it should). I added a conversion from NaT to None in a specific column, see the updated answer.
– klin
                Jan 30, 2019 at 0:06
                by the way, do you know how to speed up the process if I have 1 millions rows to insert? Bulk insert?
– PyBoss
                Jan 30, 2019 at 1:37
                if you do this, than there will be 'None' in the postgres db. the OP wants to have [null] in the db
– bucky
                Jan 22, 2020 at 14:23
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.