Deleting Data with ODBC
In this tutorial, we’ll learn how to perform basic delete or drop commands using ODBC. These cover the Delete part of the CRUD database operations.
Using the connection string and ZENDB database created in the earlier tutorial, we’ll continue with the topics listed at right.
Delete one Record from a Table
To delete a specific record from a table we use the SQL DELETE statement.
If we know the ID of the record we can specify that:
c.execute("DELETE FROM Students WHERE Id = 7")
We could also specify a value in another column, though we want to make sure the record is unique:
c.execute("DELETE FROM Students WHERE Name = 'Sue'")
Note that once you commit the change, the record will be deleted from the database and it’s not possible to undo the change.
If we go back to Zen Control Center and look at the data, you’ll see that student ID #7, Sue, no longer exists in our table.
Important: To continue with the tutorial in the next topic, Stored Procedures and Triggers, we’ll need the rest of the data to remain in this table, so don’t actually run the example statements to remove the other records, the table, or the database.
Delete Multiple Records from a Table
To delete multiple records from the table we could specify a WHERE clause that covered more than one record. For example, we could delete all English majors:
c.execute("DELETE FROM Students WHERE Major = 'English'")
To delete all records, we could simply leave out the WHERE clause:
c.execute("DELETE FROM Students")
Without a WHERE clause, we would simply delete everything from the table.
Drop a Table
Dropping a table means to delete the entire table from the database, including any data in the table. To do this we would use a SQL DROP TABLE statement:
c.execute("DROP TABLE Students")
Note: If a database has uncommitted transactions, Zen does not allow it to be dropped. To address this, in Python you must either call commit() after each transaction or have set autocommit=True when you first connected to the database.
Drop a Database
If we wanted to remove a database, we could do that in Zen Control Center or by using a SQL statement.
To drop a database in ZenCC, you would right-click the database that you wanted to delete and select Delete:
A dialog then would offer you a choice:
- If you deleted only the database name, it would disappear from ZenCC and no longer be able to accept SQL statements. However, its supporting metadata, data dictionary files (DDFs), would remain, along with the data files used to create tables. If you recreated a database in the same location, you could reuse the data files and metadata from the former database.
- If you deleted both the database name and its DDFs, then only the data files would remain, which are never deleted. If you recreated a database in the same location as the former database, you would have to use CREATE TABLE statements to restore the metadata for the table schemas and column data types.
In SQL, these two ways of removing a database are DROP DATABASE <DB name> and DROP DATABASE <DB name> DELETE FILES, respectively. As with deleting in ZenCC, data files are always left untouched.
More information: