SQLAlchemy ORM - Updating Objects

In this chapter, we will see how to modify or update the table with desired values.

To modify data of a certain attribute of any object, we have to assign new value to it and commit the changes to make the change persistent.

Let us fetch an object from the table whose primary key identifier, in our Customers table with ID=2. We can use get() method of session as follows −

x = session.query(Customers).get(2)

We can display contents of the selected object with the below given code −

print ("Name: ", x.name, "Address:", x.address, "Email:", x.email)

From our customers table, following output should be displayed −

Name: Komal Pande Address: Koti, Hyderabad Email: komal@gmail.com

Now we need to update the Address field by assigning new value as given below −

x.address = 'Banjara Hills Secunderabad'
session.commit()

The change will be persistently reflected in the database. Now we fetch object corresponding to first row in the table by using first() method as follows −

x = session.query(Customers).first()

This will execute following SQL expression −

SELECT customers.id 
AS customers_id, customers.name 
AS customers_name, customers.address 
AS customers_address, customers.email 
AS customers_email
FROM customers
LIMIT ? OFFSET ?

The bound parameters will be LIMIT = 1 and OFFSET = 0 respectively which means first row will be selected.

print ("Name: ", x.name, "Address:", x.address, "Email:", x.email)

Now, the output for the above code displaying the first row is as follows −

Name: Ravi Kumar Address: Station Road Nanded Email: ravi@gmail.com

Now change name attribute and display the contents using the below code −

x.name = 'Ravi Shrivastava'
print ("Name: ", x.name, "Address:", x.address, "Email:", x.email)

The output of the above code is −

Name: Ravi Shrivastava Address: Station Road Nanded Email: ravi@gmail.com

Even though the change is displayed, it is not committed. You can retain the earlier persistent position by using rollback() method with the code below.

session.rollback()

print ("Name: ", x.name, "Address:", x.address, "Email:", x.email)

Original contents of first record will be displayed.

For bulk updates, we shall use update() method of the Query object. Let us try and give a prefix, ‘Mr.’ to name in each row (except ID = 2). The corresponding update() statement is as follows −

session.query(Customers).filter(Customers.id! = 2).
update({Customers.name:"Mr."+Customers.name}, synchronize_session = False)

The update() method requires two parameters as follows −

  • A dictionary of key-values with key being the attribute to be updated, and value being the new contents of attribute.

  • synchronize_session attribute mentioning the strategy to update attributes in the session. Valid values are false: for not synchronizing the session, fetch: performs a select query before the update to find objects that are matched by the update query; and evaluate: evaluate criteria on objects in the session.

Three out of 4 rows in the table will have name prefixed with ‘Mr.’ However, the changes are not committed and hence will not be reflected in the table view of SQLiteStudio. It will be refreshed only when we commit the session.

Advertisements