Updating and Deleting
Data often changes over time, so we need to be able to change the information stored in databases. To do that, we can use the UPDATE command, as shown in the code.
| | >>> cur.execute('SELECT * FROM PopByRegion WHERE Region = "Japan"') |
| | <sqlite3.Cursor object at 0x102e3e490> |
| | >>> cur.fetchone() |
| | ('Japan', 100562) |
| | >>> cur.execute('''UPDATE PopByRegion SET Population = 100600 |
| | WHERE Region = "Japan"''') |
| | <sqlite3.Cursor object at 0x102e3e490> |
| | >>> cur.execute('SELECT * FROM PopByRegion WHERE Region = "Japan"') |
| | <sqlite3.Cursor object at 0x102e3e490> |
| | >>> cur.fetchone() |
| | ('Japan', 100600) |
We can also delete records from the database:
| | >>> cur.execute('DELETE FROM PopByRegion WHERE Region < "L"') |
| | <sqlite3.Cursor object at 0x102e3e490> |
| | >>> cur.execute('SELECT * FROM PopByRegion') |
| | <sqlite3.Cursor object at 0x102e3e490> |
| | >>> cur.fetchall() |
| | [('Southeastern Africa', 743112), ('Northern Africa', 1037463), |
| | ('Southern Asia', 2051941), ('Middle East', 687630), ('South America', |
| | 593121), ('North America', 661157), ('Western Europe', 387933)])] |
In both cases, all records that meet the WHERE condition are affected. If we don’t include a WHERE condition, then all rows in the database are updated or removed. Of course, we can always put records back into the database:
| | >>> cur.execute('INSERT INTO PopByRegion VALUES ("Japan", 100562)') |
To remove an entire table from the database, we can use the DROP command:
| | DROP TABLE TableName |
For example, if we no longer want the table PopByRegion, we would execute this:
| | >>> cur.execute('DROP TABLE PopByRegion') |
When a table is dropped, all the data it contained is lost. You should be very, very sure you want to do this (and even then, it’s probably a good idea to make a backup copy of the database before deleting any sizable tables).