Facing problem updating a column of a table, while the id and alias column remains same – sqlalchemy

Hi my table named ‘total’ looks like this,

{id:1,alias:tiger,status:gold}

If I want to insert a new row with a new alias lets say alias = ‘lion’ and status = ‘gold’ it should create a new id and the table should look like this

{id:1,alias:tiger,status:gold}
{id:2,alias:lion,status:gold}

But if I keep the alias same as ‘lion’ and status = ‘silver’ it should just update the row with id = 2 and it should look like this

{id:1,alias:tiger,status:gold}
{id:2,alias:lion,status:silver}

How to achieve it in sqlalchemy using insert command?

the id is primary key and alias value is stored in a variable a and status value is stored in a variable b, which looks like

a = 'lion', b= 'gold'

Answer

Looks like your alias is unique identifier for the object/row. In this case an approach of find-and-update OR create should work:

a = 'lion'
b = 'gold'

# find the object (by `alias`) in the database
obj = session.query(Total).filter(Total.alias == a).first()
if obj:
    # object found: update the status
    obj.status = b
else:
    # object not found: create and add to the session
    obj = Total(alias=a, status=b)
    session.add(obj)
session.commit()

---

See documentation:

• Adding and Updating Objects
• Querying