Deleting data from a database

Introduction to Databases in Python

Jason Myers

Co-Author of Essential SQLAlchemy and Software Engineer

Deleting data from a table

  • Done with the delete() statement
  • delete() takes the table we are loading data into as the argument
  • A where() clause is used to choose which rows to delete
  • Hard to undo so be careful!
Introduction to Databases in Python

Deleting all data from a table

from sqlalchemy import delete

stmt = select([func.count(extra_employees.columns.id)])

connection.execute(stmt).scalar()

3

delete_stmt = delete(extra_employees)

result_proxy = connection.execute(delete_stmt)
result_proxy.rowcount

3
Introduction to Databases in Python

Deleting specific rows

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

Deleting specific rows

stmt = delete(employees).where(employees.columns.id == 3)

result_proxy = connection.execute(stmt)
result_proxy.rowcount

1
Introduction to Databases in Python

Dropping a table completely

  • Uses the drop() method on the table
  • Accepts the engine as an argument so it knows where to remove the table from
  • Won't remove it from metadata until the Python process is restarted
Introduction to Databases in Python

Dropping a table

extra_employees.drop(engine)

print(extra_employees.exists(engine))

False
Introduction to Databases in Python

Dropping all the tables

  • Uses the drop_all() method on MetaData
Introduction to Databases in Python

Dropping all the tables

metadata.drop_all(engine)

engine.table_names()

[]
Introduction to Databases in Python

Let's practice!

Introduction to Databases in Python

Preparing Video For Download...