In this chapter, we will see a way to modify or replace the table with desired values.
To alter statistics of a sure attribute of any item, we should assign new fee to it and dedicate the changes to make the exchange persistent.
Let us fetch an item from the desk whose primary key identifier, in our Customers desk with ID=2. We can use get() technique of consultation as follows −
x = session.query(Customers).get(2)
We can show contents of the chosen object with the under given code −
print ("Name: ", x.name, "Address:", x.address, "Email:", x.email)
From our customers desk, following output ought to be displayed −
Name: Komal Pande Address: Koti, Hyderabad Email: komal@gmail.com
Now we want to update the Address area by means of assigning new price as given beneath −
x.address = 'Banjara Hills Secunderabad'
session.commit()
The change may be constantly contemplated within the database. Now we fetch object similar to first row within the table by means of the use of first() approach 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 certain parameters will be LIMIT = 1 and OFFSET = 0 respectively which means that first row can be decided on.
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 trade call characteristic and show the contents the usage of the beneath 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 even though the trade is displayed, it isn't always dedicated. You can keep the sooner continual position by way of the use of rollback() technique with the code under.
session.rollback()
print ("Name: ", x.name, "Address:", x.address, "Email:", x.email)
Original contents of first file may be displayed.
For bulk updates, we will use replace() technique of the Query object. Let us try to deliver a prefix, ‘Mr.’ to name in each row (except ID = 2). The corresponding update() assertion is as follows −
session.query(Customers).filter(Customers.id! = 2).
update({Customers.name:"Mr."+Customers.name}, synchronize_session = False)
The update() technique requires two parameters as follows −
- A dictionary of key-values with key being the characteristic to be updated, and price being the brand new contents of attribute.
- Synchronize_session attribute mentioning the method to update attributes in the consultation. Valid values are false: for no longer synchronizing the session, fetch: plays a pick question before the replace to find objects which are matched by using the replace query; and evaluate: examine standards on objects in the session.
Three out of four rows inside the table may have name prefixed with ‘Mr.’ However, the modifications aren't dedicated and subsequently will no longer be reflected within the table view of SQLiteStudio. It may be refreshed only whilst we devote the session.