Updating data in a table

Introduction to Databases in Python

Jason Myers

Co-Author of Essential SQLAlchemy and Software Engineer

Updating data in a table

  • Done with the update() statement
  • Similar to the insert() statement but includes a where clause to determine what record will be updated
  • We add all the values we want to update with the values() clause as column=value pairs
Introduction to Databases in Python

Updating one row

from sqlalchemy import update

stmt = update(employees) stmt = stmt.where(employees.columns.id == 3) stmt = stmt.values(active=True)
result_proxy = connection.execute(stmt) print(result_proxy.rowcount)
1
Introduction to Databases in Python

Updating multiple rows

  • Build a where clause that will select all the records you want to update
Introduction to Databases in Python

Inserting multiple rows

stmt = update(employees)
stmt = stmt.where(employees.columns.active == True)

stmt = stmt.values(active=False, salary=0.00)
result_proxy = connection.execute(stmt) print(result_proxy.rowcount)
3
Introduction to Databases in Python

Correlated updates

new_salary = select([employees.columns.salary])
new_salary = new_salary.order_by(
    desc(employees.columns.salary))
new_salary = new_salary.limit(1)

stmt = update(employees)
stmt = stmt.values(salary=new_salary)
result_proxy = connection.execute(stmt)
print(result_proxy.rowcount)
3
Introduction to Databases in Python

Correlated updates

  • Uses a select() statement to find the value for the column we are updating
  • Commonly used to update records to a maximum value or change a string to match an abbreviation from another table
Introduction to Databases in Python

Let's practice!

Introduction to Databases in Python

Preparing Video For Download...