SQL Alchemy updating and deleting data

Now, you are more familiar with the SQLAlchemy library, and you know how to create, select, and filter tables. In this topic, we will take you one step further in mastering this useful library. You will also learn how to update the tables you have created, how to update all fields or only the required ones with the update() method, and how to delete rows with the delete() method. We will also discuss the precautions when performing these tasks.

Creating a table

We need a table first! Let's create one and populate it with data. The procedure is familiar to you, so we won't dive into much detail. We create a table named Employee with the following fields: id, name, position, and salary.

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Employee(Base):
    __tablename__ = "employee"

    id = Column(Integer, primary_key=True)
    name = Column(String(30))
    position = Column(String(30))
    salary = Column(Integer)

We have created our table and mapped it. Now, we need to add data into this table to work with it. Before this, we need to create a session:

from sqlalchemy.orm import sessionmaker

engine = create_engine("sqlite:///alchemy.db", echo=True)
Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

Once the session is created, it's time to fill it. We will add four employees with positions and salaries:

session.add(Employee(name="William", position="Data Scientist", salary="12,000"))
session.add(Employee(name="Susan", position="Product Manager", salary="14,000"))
session.add(Employee(name="Andrew", position="Senior Software Engineer", salary="17,000"))
session.add(Employee(name="Nancy", position="Account Executive", salary="13,000"))

session.commit()

This is how the table will look like:

The update method

Let's now talk about updating data. It is done with the help of the update() method, but we need to specify the field we want to update first. We'll also need a session, a query, and the mapped class. Let's start by creating a query:

query = session.query(Employee)

If you want to increase the salary of all employees by 1000, use the following syntax:

query.update({"salary": Employee.salary + 1000})

The syntax is similar to a dictionary. You need to specify the fields in the curly brackets. In this case, the salary field is the key, and the quantity is the value. We take the current salary Employee.salary and increase it by 1000:

UPDATE employee
SET salary = salary + 1000

After that, we need to commit the changes:

query.update({"salary": Employee.salary + 1000})
session.commit()

Now, let's see how the table will look like:

We can see that the salary fields in the table have been changed, but what if we want to update just one field? We can do that by filtering only the fields we want.

Updating by criteria

Let's say we want to increase the salary of one employee. We can do this in several ways. We can filter by id, by name, by position, and so on. In our example, we want to increase William's salary by 1000, so we will select it by name. To select only one row, use the filter() method and pass the name as a criterion:

empl_filter = query.filter(Employee.name == "William")

After that, use the update() method on the filter in the same way as for updating the values above:

empl_filter.update({"salary": Employee.salary + 1000})
session.commit()

The above code represents the following SQL statement:

UPDATE employee
SET salary = salary + 1000
WHERE name = 'William'

Updating several fields

We can also change several fields with one update. You need to separate each key-value pair by commas like in a dictionary. Let's say we want to decrease the salary of the person who has a senior position and change their position just for kicks. First, we need to filter by position:

empl_filter = query.filter(Employee.position == "Senior Software Engineer")

Inside the update() method, we need to specify the fields we want to update. In this case, it's salary, position, and their respective values:

empl_filter.update({
    "salary": Employee.salary - 1500,
    "position": "Middle Software Engineer"
})
session.commit()

empl_filter.update({    
    "position": "Middle Software Engineer",
    "company": "Rockstar Games"
})
session.commit()

# sqlalchemy.exc.InvalidRequestError:
# Entity namespace for "mapped class Employee->employee" has no property "company"

The delete method

The delete() method works just like the update() method. The main difference between them is that you don't need to pass any arguments.

If you use this method in your query, all rows in your table will be deleted. Be careful! If you run the following code, it will delete all rows in your table, and you end up with an empty table:

query = session.query(Employee)
query.delete()
session.commit()

To delete only the desired rows, you can filter the field just like with the update() method:

query.filter(Employee.name == "Nancy").delete()

The above code is equivalent to the following SQL statement:

DELETE FROM employee
WHERE name = 'Nancy'

As you can see, the entire row that contains Nancy has been deleted. It helps avoid data inconsistency. After all, it doesn't make sense to have all the other fields without the field name.

That is why it is advisable to delete by specific criteria or by unique fields, for example, id.

Conclusion

In this topic, you have learned two essential update() and delete() methods. They both will help you a lot when you work with tables.

• The update() method changes specific values of the rows in your table. You can filter the ones you want, or you can update all rows at once.

• The delete() method works similarly to the update() method, but be careful with it because you can end up deleting all data in your table.