1

Hi I am working with Python, already connected to Postgres, I want to insert a dataframe into a postgres table if exists do an update.

My issue is that the script is sending for Null values None, and postgres is showing me an issue with None, if I use replace is sending 'NULL' as string, how I can deal with NULL valueS? I am using SQLALCHEMY

sqlconn='postgresql://postgresql:mypass@localhost:port/db' 
db = create_engine(sqlconn, pool_recycle=3600); 

conn.execute(f"INSERT INTO mytable (id,name,address,created_by,created_date,active) VALUES {','.join([str(i) for i in list(mydataframe.to_records(index=False))])} ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, address = EXCLUDED.address,created_by=EXCLUDED.created_by, created_date=EXCLUDED.created_date, active=EXCLUDED.active ;") 

conn.close()

Error from Postgres:

ERROR:  column "none" does not exist
LINE 6:              (None,

Sample

INSERT INTO mytable (id,name,address,created_by,created_date,active) 
VALUES  (1,'Carlos','Address XY','user',None,True);

How I can send a NULL besides a None?

Regards

I already Tried with Nan values, Sending 'NULL' with a replace but doesn't work at all.

clarence
  • 13
  • 3
  • Does this help you? https://stackoverflow.com/a/4231583/11286032 – Marcelo Paco Mar 24 '23 at 04:32
  • Hi @MarceloPaco basically I am using a dataframe looks like the post is using single variables to insert. Also I tried but the null is getting as string – clarence Mar 24 '23 at 15:25

1 Answers1

0

I tried this a few weeks ago with Postgresql and replacing None values with NULL will solve that problem.

Worked on an online PostgresSQL website:

INSERT INTO mytable (id,name,address,created_by,created_date,active) 
VALUES  (1,'Carlos','Address XY','user',NULL,True); 
        select * from mytable;

I'm not sure how exactly you are importing the data, but with a dict it would work like this:

data_dict = {
    "id": 1,
    "name": "Carlos",
    "adress": "Adress XY",
    "created_by":"user",
    "created_date": None,
    "active": True

}
print(f"Before:\n{data_dict}")
for key,value in data_dict.items():
    if value == None:
        data_dict[key] = 'NULL'
print(f"After:\n{data_dict}")

I hope this helps.

Terminator
  • 34
  • 2