1. Deleting data from a database
We're going to wrap up this chapter with deleting records and tables.
2. Deleting data from a table
First to delete data from a table, we use the delete statement. The delete statement targets a table and uses a where clause to determine which rows to delete. It's not simple or fast to restore large databases or tables if you delete them so be very cautious when using the delete statement!
3. Deleting all data from a table
In this example, we are going to delete all the data from a table named extra_employees. We start by importing the delete statement. next we're going to build a select statement to count the records in the extra_employees table in order to make sure that we delete the correct number of records. We execute the statement and use the scalar fetch method to get just the number of records back. Then we build a delete statement that targets the extra_employees table without a where clause. Now we execute the delete statement and check the row count to make sure it matches the count from our select statement.
4. Deleting specific rows
We can delete specific rows by using a where clause on the delete statement. Remember much like update, it's import to check the rowcount to make sure you didn't accidentally delete too many rows!
5. Deleting specific rows
Here we are removing employee 3 from the employees table. We begin by building a delete statement for the employees table and use a where clause to target the employee with the ID of 3. Then we execute the statement and check the row count to make sure we only removed one record.
6. Dropping a table completely
Sometimes, you need to delete the table itself from the database, this is done with the drop method on the table. We pass the engine in as an argument to specify which database we are dropping the table from. Dropping a table only removes it and the data it holds from the database, and does not remove it from metadata or our table object.
7. Dropping a table
In this example, we are going to drop the extra_employees table. We call the drop method on the table and pass it the engine that points to our database. Finally, we verify that it was deleted by using the exists method with the engine to check if it is still there.
8. Dropping all the tables
It's also possible to drop all the tables in a database by using the drop_all method on the metadata object. It works exactly the same as the drop method, except it affects all tables in the database.
9. Dropping all the tables
So to completely empty the database we are using, we call the drop_all method and pass in the engine to specify which database we are working on. Finally when we check the table_names on the engine object, we can see that there are no more tables!
10. Let's practice!
Remember with great power comes great responsibility. It's not simple or fast to restore large databases or tables if you delete them!