[ Python 文章收集 ] SQLAlchemy ORM - Updating Objects

Source From Here 
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: 

view plaincopy to clipboardprint?

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

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

view plaincopy to clipboardprint?

1. 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: 

view plaincopy to clipboardprint?

1. >>> x.address = 'Banjara Hills Secunderabad'  
2. >>> session.commit()  
3. 2019-04-18 14:56:28,346 INFO sqlalchemy.engine.base.Engine UPDATE customers SET address=%(address)s WHERE customers.id = %(customers_id)s  
4. 2019-04-18 14:56:28,346 INFO sqlalchemy.engine.base.Engine {'address': 'Banjara Hills Secunderabad', 'customers_id': 2}  
5. 2019-04-18 14:56:28,349 INFO sqlalchemy.engine.base.Engine 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: 

view plaincopy to clipboardprint?

1. >>> x = session.query(Customers).filter(Customers.id == 2).first()  
2. 2019-04-18 14:59:19,520 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)  
3. 2019-04-18 14:59:19,521 INFO sqlalchemy.engine.base.Engine SELECT customers.id AS customers_id, customers.name AS customers_name, customers.address AS customers_address, customers.email AS customers_email  
4. FROM customers  
5. WHERE customers.id = %(id_1)s  
6. LIMIT %(param_1)s  
7. 2019-04-18 14:59:19,521 INFO sqlalchemy.engine.base.Engine {'id_1': 2, 'param_1': 1}  
8.   
9. >>> print ("Name: ", x.name, "Address:", x.address, "Email:", x.email)  

Now, the output for the above code displaying the updated row is as follows: 

Name: Komal Pande Address: Banjara Hills Secunderabad Email: komal@gmail.com

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

>>> x.name = 'John Lee'
>>> print ("Name: ", x.name, "Address:", x.address, "Email:", x.email)
Name: John Lee Address: Banjara Hills Secunderabad Email: komal@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()
2019-04-18 15:04:18,402 INFO sqlalchemy.engine.base.Engine ROLLBACK

>>> print ("Name: ", x.name, "Address:", x.address, "Email:", x.email)
2019-04-18 15:04:20,483 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2019-04-18 15:04:20,484 INFO sqlalchemy.engine.base.Engine SELECT customers.id AS customers_id, customers.name AS customers_name, customers.address AS customers_address, customers.email AS customers_email
FROM customers
WHERE customers.id = %(param_1)s
2019-04-18 15:04:20,484 INFO sqlalchemy.engine.base.Engine {'param_1': 2}
Name: Komal Pande Address: Banjara Hills Secunderabad Email: komal@gmail.com

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)
2019-04-18 15:07:38,472 INFO sqlalchemy.engine.base.Engine UPDATE customers SET name=(%(name_1)s || customers.name) WHERE customers.id != %(id_1)s
2019-04-18 15:07:38,473 INFO sqlalchemy.engine.base.Engine {'name_1': 'Mr.', 'id_1': 2}
3
>>> for row in session.query(Customers).all():
... print("ID={}; Name={}".format(row.id, row.name))
...
2019-04-18 15:09:05,153 INFO sqlalchemy.engine.base.Engine SELECT customers.id AS customers_id, customers.name AS customers_name, customers.address AS customers_address, customers.email AS customers_email
FROM customers
2019-04-18 15:09:05,154 INFO sqlalchemy.engine.base.Engine {}
ID=2; Name=Komal Pande
ID=1; Name=Mr.Ravi Kumar
ID=3; Name=Mr.Rajender Nath
ID=4; Name=Mr.S.M.Krishna

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. It will be refreshed only when we commit the session.