The following four canonical CRUD operations map directly to SQL language statements:

• Creation is done via the INSERT statement.
• Retrieval is done via the SELECT statement.
• Update is done via the UPDATE statement. Some SQL dialects also include a REPLACE statement.
• Deletion is done via the DELETE statement.

We have to note that we'll often look at literal SQL syntax with all values supplied. This is separate from SQL syntax with binding variable placeholders instead of literal values. The literal SQL syntax is acceptable for scripts; it is perfectly awful for application programming. Building literal SQL statements in an application involves endless string manipulation and famous security problems. See the XKCD comic at http://xkcd.com/327/ for a specific security issue with assembling literal SQL from user-provided text. We'll focus exclusively on SQL with binding variables.

Literal SQL is widely used for scripts. Building literal SQL with user-supplied text is a mistake.

The Python DB-API interface defines several ways to bind application variables into SQL statements. SQLite can use positional bindings with ? or named bindings with :name. We'll show you both styles of binding variables. We use an INSERT statement to create a new BLOG row as shown in the following code snippet:

create_blog = """
    INSERT INTO blog(title) VALUES(?)
"""
with closing(database.cursor()) as cursor:
    cursor.execute(create_blog, ("Travel Blog",))
database.commit()

We created an SQL statement with a positional bind variable, ?, for the title column of the blog table. A cursor object is used to execute the statement after binding a tuple of values. There's only one bind variable, so there's only one value in the tuple. Once the statement has been executed, we have a row in the database. The final commit makes this change persistent, releasing any locks that were held. 

We show the SQL statements clearly separated from the surrounding Python code in triple-quoted long string literals. In some applications, the SQL is stored as a separate configuration item. Keeping SQL separate is best handled as a mapping from a statement name to the SQL text. This can simplify application maintenance by keeping the SQL out of the Python programming.

The DELETE and UPDATE statements require a WHERE clause to specify which rows will be changed or removed. To change a blog's title, we might do something like the following:

update_blog = """
    UPDATE blog SET title=:new_title WHERE title=:old_title
"""
with closing(database.cursor()) as cursor:
    cursor.execute(
        update_blog, 
        dict(
            new_title="2013-2014 Travel", 
            old_title="Travel Blog"
        )
    )
database.commit()

The UPDATE statement shown here has two named bind variables: :new_title and :old_title. This transaction will update all the rows in the blog table that have the given old title, setting the title to the new title. Ideally, the title is unique, and only a single row is touched. SQL operations are defined to work on sets of rows. It's a matter of database design to ensure that a desired row is the content of a set. Hence, the suggestion is to have a unique primary key for every table.

When implementing a delete operation, we always have two choices. We can either prohibit deletes of a parent when children still exist, or we can cascade the deletion of a parent to also delete the relevant children. We'll look at a cascading delete of blog, post, and tag associations. Here's a DELETE sequence of statements:

delete_post_tag_by_blog_title = """ 
    DELETE FROM assoc_post_tag
    WHERE post_id IN ( 
        SELECT DISTINCT post_id 
        FROM blog JOIN post ON blog.id = post.blog_id 
        WHERE blog.title=:old_title) 
""" 
delete_post_by_blog_title = """ 
    DELETE FROM post WHERE blog_id IN ( 
        SELECT id FROM BLOG WHERE title=:old_title) 
""" 
delete_blog_by_title =""" 
    DELETE FROM blog WHERE title=:old_title 
""" 
try:
    with closing(database.cursor()) as cursor:
        title = dict(old_title="2013-2014 Travel")
        cursor.execute("BEGIN")
        cursor.execute(delete_post_tag_by_blog_title, title)
        cursor.execute(delete_post_by_blog_title, title)
        cursor.execute(delete_blog_by_title, title)
    database.commit()
    print("Delete finished normally.") 
except Exception as ex:
    print(f"Rollback due to {ex!r}")
    database.rollback()

The DELETE operation for an entire blog cascades into three separate delete operation. First, we deleted all the rows from assoc_post_tag for a given blog based on the title. Note the nested query inside the value of delete_post_tag_by_blog_title; we'll look at queries in the next section. Navigation among tables is a common issue with SQL construction.

In this case, we have to query the blog-to-post relationship to locate the post table keys that will be removed; then, we can remove rows from assoc_post_tag for the posts associated with a blog that will be removed. Next, we deleted all the posts belonging to a particular blog. This too involves a nested query to locate the IDs of the blog based on the title. Finally, we can delete the blog itself.

This is an example of an explicit cascade delete design, where we have to cascade the operation from the blog table to two other tables. We wrapped the entire suite of delete in a with context so that it would all commit as a single transaction. In the event of failure, it would roll back the partial changes, leaving the database as it was.

It seems like this kind of operation would benefit from using the executescript() method of the database. The problem with executescript() is that all of the values in the SQL statements must be literal; it does not bind values. It works nicely for data definition. It is a poor choice for data manipulation shown here, where the blog title must be bound into each statement.

Let's discuss row querying with the SQL SELECT statement in the next section.