Contents

• Overview
• CRUD Operations
• Terminology
• Inserting (Adding) Records
• Updating (Changing) Records
• Deleting Records

Overview

On the previous page we explored querying a database from within Python. In many forms of software development, we perform what is known as CRUD Operations with databases. CRUD stands for Create, Read, Update and Delete. These operations are so common that it is often assumed programmers know how to do them, not only in SQL but also in general purpose programming languages like Java, Python, C#, C++, etc. CRUD are the operations that we use to query a database, add new data to a database, update (change) data that exists in a database, and delete from a database, as needed. On this page we will explore inserting, updating and deleting data in a database.

In a production system databases are central to storing and maintaining data. The following diagram depicts an example of a web business that takes orders over the internet through a website. The website interacts with the company's database when customers create their accounts, place orders, etc. The employees of the company that operates the website also interact with the database to detect new orders, conduct financial and shipping steps, manage customer accounts, etc.

Each interaction with the database is part of CRUD operations. Here are a few examples using the above scenario:

• A new customer creates an account on the website so that they can create a customer profile and place an order. This is a CREATE operation [ CRUD ].
• An employee in the company runs a query to get a list of all new orders. This is a READ operation [ CRUD ].
• An existing customer updates their shipping address in their account. This is an UPDATE operation [ CRUD ].
• An administrator in the company is working to maintain the database and she locates a group of old customer accounts and decides to remove them from the database. This is a DELETE operation [ CRUD ].

Each of these actions are coded by a programmer to make the operations possible.

The acronym CRUD refers to the actions (operations) of creating, reading, updating, and deleting data from a database. CRUD is used in many contexts, not just in SQL programming. In different contexts, the actual commands used to perform those actions are a little different. Here are the equivalent SQL commands that correspond with CRUD actions.

The SQL INSERT command is used to add (insert) records (rows) to tables in our database. There are three general forms of SQL INSERT syntax as shown here:

Form 1:

INSERT INTO table_name (Column, Column, Column, ...)
VALUES (value, value, value, ...);

...this form will insert a row into table_name with the specified column(s) being populated by the specified value(s) in the VALUES list. This is particularly useful when you are inserting rows and only populating a subset of the table's attributes, not all of them.

Form 2:

INSERT INTO table_name
VALUES (value, value, value, value, value, value, value);

...this form is used when you are going to insert the row and populate every column in the table. Caution should be used with this form if the target table contains any autoincrementing attributes which are populated automatically by the database engine. Attempting to include a value for an autoincrementing attribute in an insert statement will cause an error.

Form 3:

INSERT INTO table_name (Column, Column, Column, ...) VALUES
(value, value, value, ...),
(value, value, value, ...),
(value, value, value, ...),
(value, value, value, ...),
(value, value, value, ...),
(value, value, value, ...),
(value, value, value, ...);

... this form is used when you want to insert multiple rows at one time. You specify the columns list once, and then a series of value statements, separated by commas. The very last value statement gets the semicolon to complete the statement. Note: This form can also be used in conjunction with Form 2 if you are populating every column in the table.

Example

To see how to use INSERT in Python, let's take a look at some example code that demonstrates a couple of different approaches to running embedded INSERT statements in Python. In this example we'll re-use the simple query to SELECT all students with the last name Cooper that we used previously.

Code

import sqlite3

def print_report():
  print("-" * 60)
  print("ID\t\tName\t\tClass\tGPA")
  print("-" * 60)
  for row in cursor:
    print (row[4] + "\t" + row[1] + " " + row[2] + "\t" + row[3] + "\t" + str(row[5]))
  print("-" * 60)

# Main

sql_report = """SELECT * FROM students WHERE last_name = 'Cooper'
    ORDER BY last_name, first_name;"""
sql_dynamic = ""

conn = sqlite3.connect('C:\SQLite\Data\grading_system.db')

print ("\nInitial List of 'Cooper' Students:")
cursor = conn.execute(sql_report)
print_report()

# Hardcoded INSERT example
sql_dynamic = """INSERT INTO students (first_name, last_name,
    class_standing, school_id, cumulative_gpa,
    major_id) VALUES ('Zachary', 'Cooper',
    'Junior', 'S77777777', 3.0, 11)"""
conn.execute(sql_dynamic)
conn.commit()

print ("Hardcoded INSERT Result:")
cursor = conn.execute(sql_report)
print_report()

# Dynamic INSERT example
print ("\nUser Prompts for New Student Record: ")
first_name = input("First Name: ")
last_name = input("Last Name: ")
class_standing = input("Class Standing: ")
school_id = input("School ID: ")
cumulative_gpa = input("Cumulative GPA: ")
major_id = input("Major ID: ")
sql_dynamic = "INSERT INTO students (first_name, last_name, "
sql_dynamic += "class_standing, school_id, cumulative_gpa, "
sql_dynamic += "major_id) VALUES ('" + first_name + "', '"
sql_dynamic += last_name + "', '" + class_standing + "', '"
sql_dynamic += school_id + "'," + str(cumulative_gpa) + ","
sql_dynamic += str(major_id) + ");"
conn.execute(sql_dynamic)
conn.commit()

print("\n")
print ("Dynamic INSERT Result:")
cursor = conn.execute(sql_report)
print_report()

conn.close()

Output

nitial List of 'Cooper' Students:
------------------------------------------------------------
ID              Name            Class   GPA
------------------------------------------------------------
S35743296       Kendra Cooper   Senior  4
S40063558       Patricia Cooper Senior  4
------------------------------------------------------------
Hardcoded INSERT Result:
------------------------------------------------------------
ID              Name            Class   GPA
------------------------------------------------------------
S35743296       Kendra Cooper   Senior  4
S40063558       Patricia Cooper Senior  4
S77777777       Zachary Cooper  Junior  3
------------------------------------------------------------

User Prompts for New Student Record:
First Name: Bob
Last Name: Cooper
Class Standing: Freshman
School ID: S99999999
Cumulative GPA: 3.5
Major ID: 11


Dynamic INSERT Result:
------------------------------------------------------------
ID              Name            Class   GPA
------------------------------------------------------------
S99999999       Bob Cooper      Freshman        3.5
S35743296       Kendra Cooper   Senior  4
S40063558       Patricia Cooper Senior  4
S77777777       Zachary Cooper  Junior  3
------------------------------------------------------------

Notes

• Code Lines 3 thru 9: This function prints the report we've seen previously. It was moved to a function for reuse throughout this example code.
• Code Lines 13 & 14: The variable sql_report will hold SQL query statements that we'll reuse for the print report function throughout this program.
• Code Line 15: Initializing another SQL variable that's we'll use to store dynamic queries.
• Code Line 17: This line uses the SQLite .connect function to establish as connection to our database.
• Code Lines 19 thru 21: This code block runs the SQL report query and displays the results, which at this point is the list of students with the last name Cooper in the databaes before we insert any records. In the output shown above, this is labeled Initial List of 'Cooper' Students. We can see that before we insert records there are two students with the last name of Cooper.
• Code Lines 24 & 27: This is an example of a embedded hardcoded SQL INSERT statement. It is labeled "dynamic" because it is rather arbitrary. It is not common to hardcode entire query statements, we're doing it here just for demonstration. Study the syntax of the embedded INSERT statement.
• Code Line 28: Uses the conn.execute() function to pass the INSERT statement (stored in the sql_dynamic variable) to the database engine.
• Code Line 29: Executes the conn.commit() function commits the INSERT statement so that it is a permanent change to the database.
• Code Lines 31 thru 33 This code block re-runs the SQL report query and displays the results, which at this point is the list of students with the last name Cooper in the databaes after we run the first INSERT statement. In the output shown above, this is labeled Hardcoded INSERT result. We can see that after the first INSERT we now see the new student record for Zachary Cooper.
• Code Lines 36 thru 42: This code block demonstrates prompting the user for each of the attributes for a new student record. This is an example of using variables to build a SQL INSERT statement.
• Code Lines 43 thru 48: These lines build the dynamic SQL statement that will be passed to the database engine. Study the syntax here and notice that the result of these three lines of code should produce a single string that is a valid SQL statement. A common approach to check one's work is to print the sql_dynamic variable to confirm the SQL statement is valid. You can also copy that printed statement and paste it into your database IDE to test the SQL statement directly there.
• Code Line 49: Uses the conn.execute() function to pass the dynamic SQL statement (stored in the sql_dynamic variable) to the database engine.
• Code Line 50: This line uses the .commit() function to save (commit) the insert in the database table.
• Code Line 54: This line uses the .execute() function to run the reporting query again, now that the insert has been saved we should now see the new record in the query results.
• Code Line 55: This line runs the print_report() function to display the results.
• Code Line 57: This line uses the .close() function to close the database connection.

In SQL we use the UPDATE command to modify ("edit") existing records in our database.

The general form of UPDATE specifies the table to be updated, the SET clause, the attributes (fields) and their new values, and (almost always) the WHERE clause.

UPDATE table_name SET column = value, column = value, ... WHERE condition;

Note: WHERE clause filtering is critical when using the UPDATE statement in SQL. When updating records in a database, we rarely want to update (change) all records with the same value(s). This is the source of some fairly common and very damaging mistakes that occur when using UPDATE. It is very important to realize that an UPDATE statement without a WHERE clause will make the specified changes to every record in the table.

Example

To see how to use UPDATE in Python, let's use the data we worked with in the INSERT statement example above to take a look at some example code that demonstrates a couple of different approaches to running embedded UPDATE statements in Python.

Code

Output

Notes

To see how to use DELETE in Python, .........

Code

Output

Notes