Python - Insert Dataframe on Postgres on Conflict Update with SQLAlchemy

Question

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.

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
– clarence, Commented Mar 24, 2023 at 15:25

Terminator · Accepted Answer · 2023-03-24 13:36:00Z

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.

answered Mar 24, 2023 at 13:36

Terminator

362 bronze badges

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

Collectives™ on Stack Overflow

Python - Insert Dataframe on Postgres on Conflict Update with SQLAlchemy

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related