1.4.13.4 Deleting Data

For example consider the table we created earlier:

# table Person
+----------+-----------+------------------+-------------+
| LastName | FirstName | Address          | DateOfBirth |
+----------+-----------+------------------+-------------+
| Smith    | John      | 5 Friendly Place | 1980-01-01  |
| Owen     | Jones     | 4 Great Corner   | 1990-01-01  |
+----------+-----------+------------------+-------------+

The SQL command to delete every address in the table is:

DELETE FROM Person

To delete all the data using a web.database cursor we would do the following:

cursor.delete(table="Person")

Note: This does not delete the table, it deletes all the data. To drop the table use the drop() method.

The table now looks like this:

+----------+-----------+---------+-------------+
| LastName | FirstName | Address | DateOfBirth |
+----------+-----------+---------+-------------+
+----------+-----------+---------+-------------+

To delete only some of the data you need to specify the where parameter. For example to delete all people with the first name 'Owen' we would use the SQL:

DELETE FROM Person WHERE FirstName='Owen'

Similarly the function to use to execute this SQL command is:

cursor.delete(table="Person", where="FirstName='Owen'")

The table now looks like this:

+----------+-----------+------------------+-------------+
| LastName | FirstName | Address          | DateOfBirth |
+----------+-----------+------------------+-------------+
| Smith    | John      | 5 Friendly Place | 1980-01-01  |
+----------+-----------+------------------+-------------+

The delete() method of a web.database cursor looks like this:

delete( table, [values=[]][, where] [, execute])

Delete records from the table according to where.

table: A string containing the name of the table to select from or if selecting from multiple tables, a sequence of table names.
values: A list of values to substitute for ? in the WHERE clause specified by where.
where: The WHERE clause as a web.database list as returned by cursor.where(). If where is a string it is converted to the correct format.
execute: If False the method returns the SQL string to perform the desired operations. If True the SQL is executed. If not specified takes the value specified in the cursor which by default is True