Lecture 27

SQL and SQLite

MCS 275 Spring 2023
David Dumas

Lecture 27: SQL and SQLite

Reminders and announcements:

• Project 3 grading underway
• Project 4 coming soon
• Homework 10 due tomorrow

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
• Search a table for rows meeting given criteria
• Add or update rows

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. (Some people say "sequel".)

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.

Dual goals

• Learn how to use the sqlite3 module
• Learn enough SQL to make it useful

Learning a new language is challenging!

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
    

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 or data in it.

Common bug:

• Program looks in the wrong place for a DB file
• It creates an empty DB but fails to work (no tables!)
• You try to debug but notice there's a DB file in correct place with correct name