Lecture 28

SQL CRUD Example

MCS 275 Spring 2024
Emily Dumas

Lecture 28: SQL CRUD example

Reminders and announcements:

• Please use the project 3 autograder early and often!
• Project 3 due Friday at 11:59pm
• No class next week

CRUD

CRUD is an acryonym for Create, Read, Update, Delete.

These map to SQL statements INSERT, SELECT, UPDATE, and DELETE.

Some applications are "just" a convenient interface for these operations on a database table.

Task list

Let's make a Python-based task list application meant to be used in the terminal.

Features:

• Add a task
• Mark a task done
• List tasks
• List tasks and include completed ones
• Delete a task

INSERT INTO ... VALUES

Add one row to an existing table.

        -- Set every column (need to know column order!)
        INSERT INTO table_name
        VALUES ( val1, val2, val3, val4, val5, val6, val7 );

        -- Set some columns, in an order I specify
        INSERT INTO table_name ( col1, col7, col3 )
        VALUES ( val1, val7, val3 );
    

Missing columns are set to default values (often null).

Exceptions indicate constraint violations (e.g. typing).

Giving data to SQLite

Don't use string formatting to embed data in a call to execute(). Instead, use ? characters as placeholders and then give a tuple of values in the second argument.

        # do this instead; it keeps data in native types
        # separate from the SQL code
        con.execute(
            "INSERT INTO planets (name, radius_au) VALUES (?,?);",
            ("Earth", 1.0)
        )
        

What row did I just create?

The statement

                    SELECT last_insert_rowid();
                

will return the primary key of the most recently added row (by this connection).

UPDATE

Change values in a row (or rows).

        UPDATE table_name SET col1=val1, col5=val5 WHERE condition;
    

Warning: Every row meeting the condition is changed!

Also supports ORDER BY and LIMIT.

Use ? placeholders for values when executing from Python.

How many rows were changed?

The return value of sqlite3.insert has an attribute rowcount indicating how many rows were changed.

DELETE

Remove rows matching a condition.

        DELETE FROM table_name WHERE condition;
    

Also supports ORDER BY and LIMIT (e.g. to remove n rows with largest values in a given column).

Immediate, irreversible.

Omit WHERE clause to delete all rows.

DROP TABLE

Deletes an entire table.

        DROP TABLE table_name;           -- no such table = ERROR
        DROP TABLE IF EXISTS table_name; -- no such table = ok
    

Immediate, irreversible. Think of it as "throw the only copy of this table into a pool of lava". Use caution.

Transaction context manager

You can use a sqlite3 Connection object as a context manager (i.e. in with) to create a transaction.

        with con:
            # Make all the changes necessary to reflect the closing
            # of the Scranton office.
            con.execute("UPDATE...")
            con.execute("UPDATE...")
    

Another connection to the same database will never see it in a state other than "everything in the transaction happened" (if no exceptions) or "nothing in the transaction happened" (if an exception occurs).