Updating Data with ODBC

In this tutorial, we’ll learn how to perform commands to modify records and tables using ODBC. These cover the Update part of the CRUD database operations.

Using the connection string and ZENDB database created in the previous tutorial, we’ll proceed with the topics listed at right.

Update Tables by Adding a New Column

To demonstrate modifying a table, let’s add a new column to the Students table. We’ll use the SQL ALTER TABLE statement with the ADD clause:

c.execute("ALTER TABLE Students ADD Major CHAR(32) DEFAULT 'Undecided'")

We added a column called Major with a default value of “Undecided”.

This will update our Students table to have a new Major column which sets the default value to be undecided for all students.

Update Records by Changing Values

To update the values of specific records in a table, we can use the UPDATE statement:

c.execute("UPDATE Students Set Major = 'English' WHERE Major = 'Undecided'")

This command will set the Major column for all records in the Students table to have the value “English” where the value is currently “Undecided”. In our case this will change all of the records.

Here’s what the table should look like in Zen Control Center:

Update Default Values for a Table Column

When we created the Students table, we specified a default value for the Account_Balance column. Likewise, we specified a default value of “Undecided” for the Major column. (The default for the Id column is implied by its autoincrementing type.)

To change the default value (or add a default if one is not already defined), we use the ALTER TABLE statement with the MODIFY clause to specify a new DEFAULT value for a column:

c.execute("ALTER TABLE Students MODIFY Major CHAR(32) DEFAULT 'Math'")

Now if we insert a new record without a value for the Major column, “Math” will be the default value.

c.execute("INSERT INTO Students(Name, Account_Balance) VALUES ('Sue', 700)")

Looking at the results in the table, we can see that student Sue is majoring in Math.