Lecture 27

SQL queries

MCS 275 Spring 2024
Emily Dumas

Lecture 27: SQL queries

Reminders and announcements:

• Project 2 returned
• Project 3 autograder opens today
• Homework 9 due tomorrow
• Project 3 due Friday at 11:59pm
• No class next week

Popular SQL databases

• MySQL (open source)
• PostgreSQL (open source)
• Oracle
• Microsoft SQL Server
• IBM DB2
• SQLite

Using SQLite

Method 1: From a Python script

        import sqlite3
        con = sqlite3.connect("mydbfile.name") # often .db or .sqlite
        res = con.execute("SELECT * FROM evil_plans WHERE year=2024;")
        print(res.fetchall()) # or iterate over res in a for loop
        con.close()
    

Method 2: Run sqlite command line shell and type

            .open "mydbfile.name"  -- Alternative to command line arg
            SELECT * FROM evil_plans WHERE year=2024;
        

Today, we'll mostly practice making queries and learning more of SQL.

Sample databases

solarsystem.sqlite — Planets orbiting the sun.

powerplants.sqlite — Information about approximately 35,000 power plants around the world (derived from the Global Power Plant Database).

SQLite command line shell

Useful commands specific to the shell:

• .open FILENAME - Use a certain DB
• .tables - List tables in this DB
• .schema TABLENAME - Show table columns
• .quit - Exit sqlite3
• .headers on - Put column headings in output
• .mode columns - Pretty output
• .mode box - Even prettier output

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
    

Conditions can be e.g. equalities and inequalities.

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

Getting data from SQLite

After SELECT, where are the data?

execute() doesn't return the rows directly. It returns a Cursor object which is ready to give them to you.

To request rows from a Cursor c, several options:

• Use it as an iterable (it yields one tuple per row).
• c.fetchone() returns next row as a tuple.
• c.fetchall() returns a list of tuples.

CREATE TABLE

Creates a table. The set of tables doesn't change very often in most databases, and this setup step is often performed manually or by a separate program.

        CREATE TABLE [IF NOT EXISTS] table_name (
            col1 TYPE1 [MODIFIERS],
            col2 TYPE2 [MODIFIERS], ... 
        );  -- or you could write it all on one line!
    

Types include: TEXT, REAL, INTEGER

Modifiers include: UNIQUE, NOT NULL, PRIMARY KEY, and DEFAULT [val]

Reminder

Creating a table twice generates an error unless IF NOT EXISTS is given.

PRIMARY KEY

A unique identifier for each row. Recommended to use INTEGER PRIMARY KEY as type.

Useful to uniquely refer to a row in an UPDATE or DELETE query.

If you don't include one, then SQLite makes one and keeps it hidden.

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).

There is also a way to insert many rows at once, taken from the result of another query.

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 VALUES (?,?,?);",
            ("Earth", 1.0, None)
        )
        

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.

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).