Lecture 30

SQL and SQLite

MCS 275 Spring 2022
David Dumas

Database

A database is a storage and retrieval system for structured data, usually in a persistent storage medium.

Usually this term is used when the system offers a rich command language.

We'll only cover relational databases, which are based on collections of tables.

Typical relational DB features

• Add, delete or modify tables
• Find and return rows of a table meeting given criteria
• Add or update data

DB commands usually express intent (find and remove all rows with this property...), whereas file IO modules operate at a lower level (get the next line of text, ...) requiring you to build the required operations.

Application structure

SQLite

SQLite is an open source relational database that stores an entire database in a single file.

It uses the same command language as many other popular databases: The Structured Query Language, or SQL.

It consists of a standalone program where you can run database commands in a REPL environment, as well as libraries for most popular programming languages.

SQLite command line shell installation

• Windows — Download the "sqlite-tools" zip file for windows from the sqlite download page. Extract it to get "sqlite.exe". Put it somewhere you can find in the terminal and run it from powershell.
• MacOS — You already have it as /usr/bin/sqlite3. You can probably just type sqlite3 in a terminal.
• Linux — You may already have it (try sqlite3 in a terminal), otherwise use your package manager to install. In Ubuntu that package is called sqlite3.

SQLite command line shell

Then you use it as one of:

sqlite3 DBFILENAME
sqlite3.exe DBFILENAME

If the file exists, it is opened in the SQLite REPL.

If it does not exist, it is created and opened in the SQLite REPL.

SQLite hello world

Let's write a Python program to make SQLite database, add one table to it, add a couple of rows of data to the table, then read them back.

Connecting to a database

In sqlite3, opening a "connection" means opening or creating a database file.

import sqlite3
con = sqlite3.connect("solarsystem.sqlite")  # .db also popular
con.execute( ...sql_statement_goes_here... )
con.commit() # Save any changes to disk
con.close()  # Close the database file
    

CREATE TABLE

        CREATE TABLE planets (
            name TEXT,
            dist REAL, 
            year_discovered INTEGER
        );
    

Each item in parentheses is column_name COLUMN_TYPE.

Placeholder gotcha

When calling execute() with placeholders in the SQL statement, the second argument MUST be an iterable of values.

So if you have only one value, you need to wrap it in a list or tuple.

        con.execute("INSERT INTO tab VALUES (?);", 275)  # FAILS
        con.execute("INSERT INTO tab VALUES (?);", [275] )  # OK
        con.execute("INSERT INTO tab VALUES (?);", (275,) ) # OK
        

These examples assume tab is a table with just one column.

SELECT

Find and return rows. The most common query!

        SELECT * FROM table_name; -- give me everything
        SELECT * FROM table_name WHERE condition; -- some rows
        SELECT col3, col1 FROM table_name; -- some columns
        SELECT * FROM table_name LIMIT 10; -- at most 10 rows
        
        SELECT * FROM table_name 
        ORDER BY col2; -- sort by col2, smallest first
        
        SELECT * FROM table_name
        ORDER BY col2 DESC; -- sort by col2, biggest first

        SELECT DISTINCT ... ; -- no repeat answers
    

WOBL

WHERE, ORDER BY, LIMIT can be used together, but must appear in that "WOBL" order. (Details.)

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.

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. Also, an empty table isn't the same thing as a deleted table.

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.

Initial state

SQLite creates the database file if it doesn't exist, but with no tables in it.

Most programs will need to contain code to set up the necessary tables if they do not already exist.