CS 200: Data Bases and SQL

Video: Socratica SQL Youtube channel

SQL implements a relational database. This model was introduced by E.F.Codd in his 1970 paper: A Relational Model of Data for Large Shared Data Banks.

This notebook implements the code from SQL tutorial 18 lessons

SQL Keywords (we will not focus on the last four):

• CREATE + INTEGER, TEXT, BLOB, PRIMARY KEY, AUTOINCREMENT, UNIQUE, NOT NULL, FOREIGN KEY (referential integrity check)
• INSERT INTO ... VALUES
• SELECT FROM + WHERE, ORDER BY (DESC), LIMIT, OFFSET, AND, OR, LIKE, IS, IS NOT
• JOIN: LEFT, RIGHT, INNER, OUTER, ON
• UPDATE ... SET
• DELETE FROM
• ALTER TABLE + ADD, DROP, RENAME
• DROP TABLE

sqlite3 tutorial There are many out there among which to choose.

sql.py The source code for most of this notebook.

An introduction to SQL using Python's sqlite3 module

Demonstrates Python module sqlite3 using examples from the SQL tutorial

import sqlite3
## for capturing exception error messages
import sys

Create a connection to database: mydb and a cursor to point inside the object.

connection = sqlite3.connect("mydb.db")
cursor = connection.cursor()

Use the CREATE command to build a table with the given fields. The example database is for movies.

c1 = """
CREATE TABLE movies (
    id INTEGER PRIMARY KEY,
    title TEXT,
    director TEXT,
    year INTEGER, 
    length_minutes INTEGER
);
"""

Execute the command c1, after first dropping the movies table.

cursor.execute("drop table movies")

<sqlite3.Cursor at 0x7f54854bdb90>

cursor.execute(c1)

<sqlite3.Cursor at 0x7f54854bdb90>

Here is how you get column names for a table.

def desc(table = 'movies'):
    cursor = connection.cursor()
    command = "PRAGMA table_info('{}')".format(table)
    print ("SQL Command: ", command)
    result = cursor.execute(command)
    for r in result:
        print (r)

desc()

SQL Command:  PRAGMA table_info('movies')
(0, 'id', 'INTEGER', 0, None, 1)
(1, 'title', 'TEXT', 0, None, 0)
(2, 'director', 'TEXT', 0, None, 0)
(3, 'year', 'INTEGER', 0, None, 0)
(4, 'length_minutes', 'INTEGER', 0, None, 0)

It is advisable to wrap your database commands with try.

The output here is hardwired for the movies database.

def tryit(command):
    cursor = connection.cursor()
    try:
        cursor.execute(command)
        print("fetchall: " + command)
        result = cursor.fetchall() 
        for r in result:
            rformat = "{:3d} {:20} {:15} {:4} {:3}".format(r[0], 
                                                           r[1], r[2], r[3], r[4])
            print(rformat)
        return list(result)
    except:
        e = sys.exc_info()[0]
        print ( "Error: {}".format(e))

Below we use the INSERT command to add a record to the movie database.

c2 = """
INSERT INTO movies
(id, title, director, year, length_minutes)
VALUES
(1, 'Toy Story', 'John Lasseter', 1995, 81);
"""

cursor.execute(c2)

<sqlite3.Cursor at 0x7f54854bdb90>

There is now one record in the database.

tryit('select *  from movies')

fetchall: select *  from movies
  1 Toy Story            John Lasseter   1995  81

[(1, 'Toy Story', 'John Lasseter', 1995, 81)]

We can use Python to insert a bunch of data:

movie_data = [
    (1, 'Toy Story', 'John Lasseter', 1995, 81),
    (2, "A Bug's Life", 'John Lasseter', 1998, 95),
    (3, 'Toy Story 2', 'John Lasseter', 1999, 93),
    (4,	"Monsters, Inc.","Pete Docter",	2001, 92),
    (5,	"Finding Nemo","Andrew Stanton", 2003, 107),
    (6,	"The Incredibles","Brad Bird", 2004, 116),
    (7,	"Cars","John Lasseter",	2006, 117),
    (8,	"Ratatouille","Brad Bird", 2007, 115),
    (9,	"WALL-E","Andrew Stanton", 2008, 104),
    (10, "Up","Pete Docter", 2009, 101),
    (11, "Toy Story 3","Lee Unkrich", 2010, 103),
    (12, "Cars 2","John Lasseter", 2011, 120),
    (13, "Brave","Brenda Chapman", 2012, 102),
    (14, "Monsters University","Dan Scanlon", 2013, 110) ]

def loadmovies():
    for m in movie_data:
        format_str = """INSERT INTO movies 
        (id, title, director, year, length_minutes)
        VALUES ({id}, "{title}", "{director}", "{year}", "{length_minutes}");"""

        sql_command = format_str.format(id=m[0], title=m[1], director=m[2], 
                                        year = m[3], length_minutes = m[4])
        cursor.execute(sql_command)

Before we add these movies, we should remove the first entry, using the DELETE command.

loadmovies()

---------------------------------------------------------------------------
IntegrityError                            Traceback (most recent call last)
/tmp/ipykernel_3583178/486945081.py in <module>
----> 1 loadmovies()

/tmp/ipykernel_3583178/1473066970.py in loadmovies()
      7         sql_command = format_str.format(id=m[0], title=m[1], director=m[2], 
      8                                         year = m[3], length_minutes = m[4])
----> 9         cursor.execute(sql_command)

IntegrityError: UNIQUE constraint failed: movies.id

Aha, we have a duplicate entry from having INSERTed Toy Story before.

tryit('select * from movies')

fetchall: select * from movies
  1 Toy Story            John Lasseter   1995  81

[(1, 'Toy Story', 'John Lasseter', 1995, 81)]

We will erase the table using the DELETE command. Note: WHERE 1=1 is an SQL idiom for everywhere. That is, it will delete every record.

tryit('DELETE FROM movies WHERE 1=1')

fetchall: DELETE FROM movies WHERE 1=1

[]

tryit('select * from movies')

fetchall: select * from movies

[]

Success! Now let's try loading the movies again.

loadmovies()

tryit('select * from movies')

fetchall: select * from movies
  1 Toy Story            John Lasseter   1995  81
  2 A Bug's Life         John Lasseter   1998  95
  3 Toy Story 2          John Lasseter   1999  93
  4 Monsters, Inc.       Pete Docter     2001  92
  5 Finding Nemo         Andrew Stanton  2003 107
  6 The Incredibles      Brad Bird       2004 116
  7 Cars                 John Lasseter   2006 117
  8 Ratatouille          Brad Bird       2007 115
  9 WALL-E               Andrew Stanton  2008 104
 10 Up                   Pete Docter     2009 101
 11 Toy Story 3          Lee Unkrich     2010 103
 12 Cars 2               John Lasseter   2011 120
 13 Brave                Brenda Chapman  2012 102
 14 Monsters University  Dan Scanlon     2013 110

[(1, 'Toy Story', 'John Lasseter', 1995, 81),
 (2, "A Bug's Life", 'John Lasseter', 1998, 95),
 (3, 'Toy Story 2', 'John Lasseter', 1999, 93),
 (4, 'Monsters, Inc.', 'Pete Docter', 2001, 92),
 (5, 'Finding Nemo', 'Andrew Stanton', 2003, 107),
 (6, 'The Incredibles', 'Brad Bird', 2004, 116),
 (7, 'Cars', 'John Lasseter', 2006, 117),
 (8, 'Ratatouille', 'Brad Bird', 2007, 115),
 (9, 'WALL-E', 'Andrew Stanton', 2008, 104),
 (10, 'Up', 'Pete Docter', 2009, 101),
 (11, 'Toy Story 3', 'Lee Unkrich', 2010, 103),
 (12, 'Cars 2', 'John Lasseter', 2011, 120),
 (13, 'Brave', 'Brenda Chapman', 2012, 102),
 (14, 'Monsters University', 'Dan Scanlon', 2013, 110)]

This looks good. Now we will write the database to the file using commit().

connection.commit()

SQL Lesson 1. SELECT Queries 101

We define a fetchall() function to query the database.

def fetchall(fields = [], table = 'movies'):
    cursor = connection.cursor()
    fstr = '*'
    if fields:
        fstr = ', '.join(fields)
    command = "SELECT " + fstr + " FROM " + table
    print ("SQL Command: ", command)
    cursor.execute(command)
    print("fetchall:")
    result = cursor.fetchall() 
    for r in result:
        print(r)
    return list(result)

fetchall()

SQL Command:  SELECT * FROM movies
fetchall:
(1, 'Toy Story', 'John Lasseter', 1995, 81)
(2, "A Bug's Life", 'John Lasseter', 1998, 95)
(3, 'Toy Story 2', 'John Lasseter', 1999, 93)
(4, 'Monsters, Inc.', 'Pete Docter', 2001, 92)
(5, 'Finding Nemo', 'Andrew Stanton', 2003, 107)
(6, 'The Incredibles', 'Brad Bird', 2004, 116)
(7, 'Cars', 'John Lasseter', 2006, 117)
(8, 'Ratatouille', 'Brad Bird', 2007, 115)
(9, 'WALL-E', 'Andrew Stanton', 2008, 104)
(10, 'Up', 'Pete Docter', 2009, 101)
(11, 'Toy Story 3', 'Lee Unkrich', 2010, 103)
(12, 'Cars 2', 'John Lasseter', 2011, 120)
(13, 'Brave', 'Brenda Chapman', 2012, 102)
(14, 'Monsters University', 'Dan Scanlon', 2013, 110)

[(1, 'Toy Story', 'John Lasseter', 1995, 81),
 (2, "A Bug's Life", 'John Lasseter', 1998, 95),
 (3, 'Toy Story 2', 'John Lasseter', 1999, 93),
 (4, 'Monsters, Inc.', 'Pete Docter', 2001, 92),
 (5, 'Finding Nemo', 'Andrew Stanton', 2003, 107),
 (6, 'The Incredibles', 'Brad Bird', 2004, 116),
 (7, 'Cars', 'John Lasseter', 2006, 117),
 (8, 'Ratatouille', 'Brad Bird', 2007, 115),
 (9, 'WALL-E', 'Andrew Stanton', 2008, 104),
 (10, 'Up', 'Pete Docter', 2009, 101),
 (11, 'Toy Story 3', 'Lee Unkrich', 2010, 103),
 (12, 'Cars 2', 'John Lasseter', 2011, 120),
 (13, 'Brave', 'Brenda Chapman', 2012, 102),
 (14, 'Monsters University', 'Dan Scanlon', 2013, 110)]

fetchall(['title', 'year'])

SQL Command:  SELECT title, year FROM movies
fetchall:
('Toy Story', 1995)
("A Bug's Life", 1998)
('Toy Story 2', 1999)
('Monsters, Inc.', 2001)
('Finding Nemo', 2003)
('The Incredibles', 2004)
('Cars', 2006)
('Ratatouille', 2007)
('WALL-E', 2008)
('Up', 2009)
('Toy Story 3', 2010)
('Cars 2', 2011)
('Brave', 2012)
('Monsters University', 2013)

[('Toy Story', 1995),
 ("A Bug's Life", 1998),
 ('Toy Story 2', 1999),
 ('Monsters, Inc.', 2001),
 ('Finding Nemo', 2003),
 ('The Incredibles', 2004),
 ('Cars', 2006),
 ('Ratatouille', 2007),
 ('WALL-E', 2008),
 ('Up', 2009),
 ('Toy Story 3', 2010),
 ('Cars 2', 2011),
 ('Brave', 2012),
 ('Monsters University', 2013)]

fetchall(['title','year','year'])

SQL Command:  SELECT title, year, year FROM movies
fetchall:
('Toy Story', 1995, 1995)
("A Bug's Life", 1998, 1998)
('Toy Story 2', 1999, 1999)
('Monsters, Inc.', 2001, 2001)
('Finding Nemo', 2003, 2003)
('The Incredibles', 2004, 2004)
('Cars', 2006, 2006)
('Ratatouille', 2007, 2007)
('WALL-E', 2008, 2008)
('Up', 2009, 2009)
('Toy Story 3', 2010, 2010)
('Cars 2', 2011, 2011)
('Brave', 2012, 2012)
('Monsters University', 2013, 2013)

[('Toy Story', 1995, 1995),
 ("A Bug's Life", 1998, 1998),
 ('Toy Story 2', 1999, 1999),
 ('Monsters, Inc.', 2001, 2001),
 ('Finding Nemo', 2003, 2003),
 ('The Incredibles', 2004, 2004),
 ('Cars', 2006, 2006),
 ('Ratatouille', 2007, 2007),
 ('WALL-E', 2008, 2008),
 ('Up', 2009, 2009),
 ('Toy Story 3', 2010, 2010),
 ('Cars 2', 2011, 2011),
 ('Brave', 2012, 2012),
 ('Monsters University', 2013, 2013)]

We use fetchone to get a single record.

def fetchone(table = 'movies'):
    cursor = connection.cursor()
    command = "SELECT * FROM " + table
    print ("SQL Command: ", command)
    cursor.execute(command)
    print("fetchone:")
    result = cursor.fetchone() 
    print (result)
    result = cursor.fetchone()
    print (result)
    return result

fetchone()

SQL Command:  SELECT * FROM movies
fetchone:
(1, 'Toy Story', 'John Lasseter', 1995, 81)
(2, "A Bug's Life", 'John Lasseter', 1998, 95)

(2, "A Bug's Life", 'John Lasseter', 1998, 95)

SQL Lesson 2: Queries with constraints (Pt. 2)

The WHERE clause adds constraints to the query.

def fetchwhere(fields = [], where = [], table = 'movies'):
    cursor = connection.cursor()
    fstr = '*'
    wstr = ' 1=1 '
    if fields:
        fstr = ', '.join(fields)
    if where:
        wstr = ' AND '.join(where)
    command = "SELECT " + fstr + " FROM " + table + " WHERE " + wstr
    print ("SQL Command: ", command)
    cursor.execute(command)
    print("fetchall:")
    result = cursor.fetchall() 
    for r in result:
        print(r)
    return list(result)

We can use comparison operators for numeric fields.

Multiple where clauses are AND conditions. Use the OR operator for disjoint conditions.

fetchwhere(['title', 'year'], ['year > 2000'])

SQL Command:  SELECT title, year FROM movies WHERE year > 2000
fetchall:
('Monsters, Inc.', 2001)
('Finding Nemo', 2003)
('The Incredibles', 2004)
('Cars', 2006)
('Ratatouille', 2007)
('WALL-E', 2008)
('Up', 2009)
('Toy Story 3', 2010)
('Cars 2', 2011)
('Brave', 2012)
('Monsters University', 2013)

[('Monsters, Inc.', 2001),
 ('Finding Nemo', 2003),
 ('The Incredibles', 2004),
 ('Cars', 2006),
 ('Ratatouille', 2007),
 ('WALL-E', 2008),
 ('Up', 2009),
 ('Toy Story 3', 2010),
 ('Cars 2', 2011),
 ('Brave', 2012),
 ('Monsters University', 2013)]

fetchwhere(['title', 'year'], ['year > 2000', 'year < 2010'])

SQL Command:  SELECT title, year FROM movies WHERE year > 2000 AND year < 2010
fetchall:
('Monsters, Inc.', 2001)
('Finding Nemo', 2003)
('The Incredibles', 2004)
('Cars', 2006)
('Ratatouille', 2007)
('WALL-E', 2008)
('Up', 2009)

[('Monsters, Inc.', 2001),
 ('Finding Nemo', 2003),
 ('The Incredibles', 2004),
 ('Cars', 2006),
 ('Ratatouille', 2007),
 ('WALL-E', 2008),
 ('Up', 2009)]

fetchwhere(['title', 'year'], ['year < 2000 OR year > 2010'])

SQL Command:  SELECT title, year FROM movies WHERE year < 2000 OR year > 2010
fetchall:
('Toy Story', 1995)
("A Bug's Life", 1998)
('Toy Story 2', 1999)
('Cars 2', 2011)
('Brave', 2012)
('Monsters University', 2013)

[('Toy Story', 1995),
 ("A Bug's Life", 1998),
 ('Toy Story 2', 1999),
 ('Cars 2', 2011),
 ('Brave', 2012),
 ('Monsters University', 2013)]

SQL Lesson 3: Queries with constraints (Pt. 2)

You can use LIKE for pattern matching, with % as a wildcard character.

fetchwhere(['title', 'director'], ['director like "Doc"'])

SQL Command:  SELECT title, director FROM movies WHERE director like "Doc"
fetchall:

[]

fetchwhere(['title', 'director'], ['director like "Pete%"'])

SQL Command:  SELECT title, director FROM movies WHERE director like "Pete%"
fetchall:
('Monsters, Inc.', 'Pete Docter')
('Up', 'Pete Docter')

[('Monsters, Inc.', 'Pete Docter'), ('Up', 'Pete Docter')]

fetchwhere(['title', 'director'], ['director like "%Doc%"'])

SQL Command:  SELECT title, director FROM movies WHERE director like "%Doc%"
fetchall:
('Monsters, Inc.', 'Pete Docter')
('Up', 'Pete Docter')

[('Monsters, Inc.', 'Pete Docter'), ('Up', 'Pete Docter')]

SQL Lesson 4: Filtering and sorting Query results

We use the ORDER BY keywords to sort the output, and the LIMIT keyword to cap the number of results.

We incorporate both in fetchwheresort().

def fetchwheresort(fields = [], where = [], sort = [], limit = [], table = 'movies'):
    cursor = connection.cursor()
    fstr = '*'
    wstr = ' 1=1 '
    sstr = ''
    if fields:
        fstr = ', '.join(fields)
    if where:
        wstr = ' AND '.join(where)
    command = "SELECT " + fstr + " FROM " + table + " WHERE " + wstr
    if sort:
        sstr = ', '.join(sort)
        command += " ORDER BY " + sstr
    if limit:
        command += " LIMIT " + str(limit)
    print ("SQL Command: ", command)
    cursor.execute(command)
    print("fetchall:")
    result = cursor.fetchall() 
    for r in result:
        print(r)
    return list(result)

fetchwheresort([], ['year > 2000'], ['title'], 4)

SQL Command:  SELECT * FROM movies WHERE year > 2000 ORDER BY title LIMIT 4
fetchall:
(13, 'Brave', 'Brenda Chapman', 2012, 102)
(7, 'Cars', 'John Lasseter', 2006, 117)
(12, 'Cars 2', 'John Lasseter', 2011, 120)
(5, 'Finding Nemo', 'Andrew Stanton', 2003, 107)

[(13, 'Brave', 'Brenda Chapman', 2012, 102),
 (7, 'Cars', 'John Lasseter', 2006, 117),
 (12, 'Cars 2', 'John Lasseter', 2011, 120),
 (5, 'Finding Nemo', 'Andrew Stanton', 2003, 107)]

SQL Lesson 6: Multi-table queries with JOINs

Joins are commonly used to link together tables.

For example, you might have the following tables:

students (id, name, DOB, college, etc.)
course (id, title, department, hours, credithours, etc.)
classroom (id, building, room, size, AV, etc.)

Then you would have other tables linking the above:

enrollment (student.id, course.id, term, status, etc.)
assignedrooms (course.id, classroom.id, time)

This way you can have one student enrolled in multiple courses in multiple terms, and have a classroom allocated to multiple courses over time.

This approach is known as normalization.

See https://en.wikipedia.org/wiki/Database_normalization .

Below we create a new table of box office revenue for movies, both domestic and international sales.

c10 = """
CREATE TABLE Boxoffice (
    Movie_id INTEGER PRIMARY KEY,
    Rating float,
    Domestic_sales integer,
    International_sales integer
);
"""

cursor.execute('DROP TABLE Boxoffice')

<sqlite3.Cursor at 0x7f54854bdb90>

cursor.execute(c10)

<sqlite3.Cursor at 0x7f54854bdb90>

desc('boxoffice')

SQL Command:  PRAGMA table_info('boxoffice')
(0, 'Movie_id', 'INTEGER', 0, None, 1)
(1, 'Rating', 'float', 0, None, 0)
(2, 'Domestic_sales', 'integer', 0, None, 0)
(3, 'International_sales', 'integer', 0, None, 0)

'''
Movie_id	Rating	Domestic_sales	International_sales
5	8.2	380843261	555900000
14	7.4	268492764	475066843
8	8	206445654	417277164
12	6.4	191452396	368400000
3	7.9	245852179	239163000
6	8	261441092	370001000
9	8.5	223808164	297503696
11	8.4	415004880	648167031
1	8.3	191796233	170162503
7	7.2	244082982	217900167
10	8.3	293004164	438338580
4	8.1	289916256	272900000
2	7.2	162798565	200600000
13	7.2	237283207	301700000
'''

boxoffice_data = [
    (5,8.2,380843261,555900000),
    (14,7.4,268492764,475066843),
    (8,8,206445654,417277164),
    (12,6.4,191452396,368400000),
    (3,7.9,245852179,239163000),
    (6,8,261441092,370001000),
    (9,8.5,223808164,297503696),
    (11,8.4,415004880,648167031),
    (1,8.3,191796233,170162503),
    (7,7.2,244082982,217900167),
    (10,8.3,293004164,438338580),
    (4,8.1,289916256,272900000),
    (2,7.2,162798565,200600000),
    (13,7.2,237283207,301700000) ]

def loadboxoffice():
    for m in boxoffice_data:
        format_str = """INSERT INTO Boxoffice
        (Movie_id, Rating, Domestic_sales, International_sales)
        VALUES ({id}, {rating}, {domestic}, {international});"""

        sql_command = format_str.format(id=m[0], rating=m[1],
                                        domestic=m[2], international= m[3])
        cursor.execute(sql_command)

loadboxoffice()

fetchall([],'boxoffice')

SQL Command:  SELECT * FROM boxoffice
fetchall:
(1, 8.3, 191796233, 170162503)
(2, 7.2, 162798565, 200600000)
(3, 7.9, 245852179, 239163000)
(4, 8.1, 289916256, 272900000)
(5, 8.2, 380843261, 555900000)
(6, 8.0, 261441092, 370001000)
(7, 7.2, 244082982, 217900167)
(8, 8.0, 206445654, 417277164)
(9, 8.5, 223808164, 297503696)
(10, 8.3, 293004164, 438338580)
(11, 8.4, 415004880, 648167031)
(12, 6.4, 191452396, 368400000)
(13, 7.2, 237283207, 301700000)
(14, 7.4, 268492764, 475066843)

[(1, 8.3, 191796233, 170162503),
 (2, 7.2, 162798565, 200600000),
 (3, 7.9, 245852179, 239163000),
 (4, 8.1, 289916256, 272900000),
 (5, 8.2, 380843261, 555900000),
 (6, 8.0, 261441092, 370001000),
 (7, 7.2, 244082982, 217900167),
 (8, 8.0, 206445654, 417277164),
 (9, 8.5, 223808164, 297503696),
 (10, 8.3, 293004164, 438338580),
 (11, 8.4, 415004880, 648167031),
 (12, 6.4, 191452396, 368400000),
 (13, 7.2, 237283207, 301700000),
 (14, 7.4, 268492764, 475066843)]

fetchwheresort([], [], [], 4, "Boxoffice")

SQL Command:  SELECT * FROM Boxoffice WHERE  1=1  LIMIT 4
fetchall:
(1, 8.3, 191796233, 170162503)
(2, 7.2, 162798565, 200600000)
(3, 7.9, 245852179, 239163000)
(4, 8.1, 289916256, 272900000)

[(1, 8.3, 191796233, 170162503),
 (2, 7.2, 162798565, 200600000),
 (3, 7.9, 245852179, 239163000),
 (4, 8.1, 289916256, 272900000)]

We create fectchwherejoin() to JOIN the movies table with the boxoffice table.

def fetchwherejoin(fields = [], where = [], sort = [], limit = [], 
                   table = 'movies', join = []):
    cursor = connection.cursor()
    fstr = '*'
    wstr = ' 1=1 '
    sstr = ''
    if fields:
        fstr = ', '.join(fields)
    command = "SELECT " + fstr + " FROM " + table 
    ## can handle LEFT, RIGHT, OUTER joins
    ## default is INNER join
    if join:
        if (join.lower().find('join') < 0):
            command += " INNER JOIN " + join
        else:
            command += " " + join
    if where:
        wstr = ' AND '.join(where)
        command += " WHERE " + wstr
    if sort:
        sstr = ', '.join(sort)
        command += " ORDER BY " + sstr
    if limit:
        command += " LIMIT " + str(limit)

    print ("SQL Command: ", command)
    cursor.execute(command)
    print("fetchall:")
    result = cursor.fetchall() 
    for r in result:
        print(r)
    return list(result)

fetchwherejoin([], [], [], [], 'movies', 'Boxoffice ON movies.id = Boxoffice.movie_id')

SQL Command:  SELECT * FROM movies INNER JOIN Boxoffice ON movies.id = Boxoffice.movie_id
fetchall:
(1, 'Toy Story', 'John Lasseter', 1995, 81, 1, 8.3, 191796233, 170162503)
(2, "A Bug's Life", 'John Lasseter', 1998, 95, 2, 7.2, 162798565, 200600000)
(3, 'Toy Story 2', 'John Lasseter', 1999, 93, 3, 7.9, 245852179, 239163000)
(4, 'Monsters, Inc.', 'Pete Docter', 2001, 92, 4, 8.1, 289916256, 272900000)
(5, 'Finding Nemo', 'Andrew Stanton', 2003, 107, 5, 8.2, 380843261, 555900000)
(6, 'The Incredibles', 'Brad Bird', 2004, 116, 6, 8.0, 261441092, 370001000)
(7, 'Cars', 'John Lasseter', 2006, 117, 7, 7.2, 244082982, 217900167)
(8, 'Ratatouille', 'Brad Bird', 2007, 115, 8, 8.0, 206445654, 417277164)
(9, 'WALL-E', 'Andrew Stanton', 2008, 104, 9, 8.5, 223808164, 297503696)
(10, 'Up', 'Pete Docter', 2009, 101, 10, 8.3, 293004164, 438338580)
(11, 'Toy Story 3', 'Lee Unkrich', 2010, 103, 11, 8.4, 415004880, 648167031)
(12, 'Cars 2', 'John Lasseter', 2011, 120, 12, 6.4, 191452396, 368400000)
(13, 'Brave', 'Brenda Chapman', 2012, 102, 13, 7.2, 237283207, 301700000)
(14, 'Monsters University', 'Dan Scanlon', 2013, 110, 14, 7.4, 268492764, 475066843)

[(1, 'Toy Story', 'John Lasseter', 1995, 81, 1, 8.3, 191796233, 170162503),
 (2, "A Bug's Life", 'John Lasseter', 1998, 95, 2, 7.2, 162798565, 200600000),
 (3, 'Toy Story 2', 'John Lasseter', 1999, 93, 3, 7.9, 245852179, 239163000),
 (4, 'Monsters, Inc.', 'Pete Docter', 2001, 92, 4, 8.1, 289916256, 272900000),
 (5,
  'Finding Nemo',
  'Andrew Stanton',
  2003,
  107,
  5,
  8.2,
  380843261,
  555900000),
 (6, 'The Incredibles', 'Brad Bird', 2004, 116, 6, 8.0, 261441092, 370001000),
 (7, 'Cars', 'John Lasseter', 2006, 117, 7, 7.2, 244082982, 217900167),
 (8, 'Ratatouille', 'Brad Bird', 2007, 115, 8, 8.0, 206445654, 417277164),
 (9, 'WALL-E', 'Andrew Stanton', 2008, 104, 9, 8.5, 223808164, 297503696),
 (10, 'Up', 'Pete Docter', 2009, 101, 10, 8.3, 293004164, 438338580),
 (11, 'Toy Story 3', 'Lee Unkrich', 2010, 103, 11, 8.4, 415004880, 648167031),
 (12, 'Cars 2', 'John Lasseter', 2011, 120, 12, 6.4, 191452396, 368400000),
 (13, 'Brave', 'Brenda Chapman', 2012, 102, 13, 7.2, 237283207, 301700000),
 (14,
  'Monsters University',
  'Dan Scanlon',
  2013,
  110,
  14,
  7.4,
  268492764,
  475066843)]

Show the sales numbers for each movie that did better internationally rather than domestically

fetchwherejoin(['Rating', 'title', 'Domestic_sales', 'International_sales'], 
               ['Domestic_sales < International_sales'], ['Rating'], [], 'movies', 
               'Boxoffice ON movies.id = Boxoffice.movie_id')

SQL Command:  SELECT Rating, title, Domestic_sales, International_sales FROM movies INNER JOIN Boxoffice ON movies.id = Boxoffice.movie_id WHERE Domestic_sales < International_sales ORDER BY Rating
fetchall:
(6.4, 'Cars 2', 191452396, 368400000)
(7.2, "A Bug's Life", 162798565, 200600000)
(7.2, 'Brave', 237283207, 301700000)
(7.4, 'Monsters University', 268492764, 475066843)
(8.0, 'The Incredibles', 261441092, 370001000)
(8.0, 'Ratatouille', 206445654, 417277164)
(8.2, 'Finding Nemo', 380843261, 555900000)
(8.3, 'Up', 293004164, 438338580)
(8.4, 'Toy Story 3', 415004880, 648167031)
(8.5, 'WALL-E', 223808164, 297503696)

[(6.4, 'Cars 2', 191452396, 368400000),
 (7.2, "A Bug's Life", 162798565, 200600000),
 (7.2, 'Brave', 237283207, 301700000),
 (7.4, 'Monsters University', 268492764, 475066843),
 (8.0, 'The Incredibles', 261441092, 370001000),
 (8.0, 'Ratatouille', 206445654, 417277164),
 (8.2, 'Finding Nemo', 380843261, 555900000),
 (8.3, 'Up', 293004164, 438338580),
 (8.4, 'Toy Story 3', 415004880, 648167031),
 (8.5, 'WALL-E', 223808164, 297503696)]

SQL Lesson 7: OUTER JOINs

Used when tables have asymetric data - may not have sales data for some movies.

SQL Lesson 8: A short note on NULLs

To process or identify missing data use WHERE column IS / IS NOT NULL.

c12 = """
INSERT INTO movies
(id, title, director, year)
VALUES
(100, 'Gone With the Wind', 'Victor Fleming', 1939);

"""

cursor.execute(c12)

<sqlite3.Cursor at 0x7f54854bdb90>

fetchall()

SQL Command:  SELECT * FROM movies
fetchall:
(1, 'Toy Story', 'John Lasseter', 1995, 81)
(2, "A Bug's Life", 'John Lasseter', 1998, 95)
(3, 'Toy Story 2', 'John Lasseter', 1999, 93)
(4, 'Monsters, Inc.', 'Pete Docter', 2001, 92)
(5, 'Finding Nemo', 'Andrew Stanton', 2003, 107)
(6, 'The Incredibles', 'Brad Bird', 2004, 116)
(7, 'Cars', 'John Lasseter', 2006, 117)
(8, 'Ratatouille', 'Brad Bird', 2007, 115)
(9, 'WALL-E', 'Andrew Stanton', 2008, 104)
(10, 'Up', 'Pete Docter', 2009, 101)
(11, 'Toy Story 3', 'Lee Unkrich', 2010, 103)
(12, 'Cars 2', 'John Lasseter', 2011, 120)
(13, 'Brave', 'Brenda Chapman', 2012, 102)
(14, 'Monsters University', 'Dan Scanlon', 2013, 110)
(100, 'Gone With the Wind', 'Victor Fleming', 1939, None)

[(1, 'Toy Story', 'John Lasseter', 1995, 81),
 (2, "A Bug's Life", 'John Lasseter', 1998, 95),
 (3, 'Toy Story 2', 'John Lasseter', 1999, 93),
 (4, 'Monsters, Inc.', 'Pete Docter', 2001, 92),
 (5, 'Finding Nemo', 'Andrew Stanton', 2003, 107),
 (6, 'The Incredibles', 'Brad Bird', 2004, 116),
 (7, 'Cars', 'John Lasseter', 2006, 117),
 (8, 'Ratatouille', 'Brad Bird', 2007, 115),
 (9, 'WALL-E', 'Andrew Stanton', 2008, 104),
 (10, 'Up', 'Pete Docter', 2009, 101),
 (11, 'Toy Story 3', 'Lee Unkrich', 2010, 103),
 (12, 'Cars 2', 'John Lasseter', 2011, 120),
 (13, 'Brave', 'Brenda Chapman', 2012, 102),
 (14, 'Monsters University', 'Dan Scanlon', 2013, 110),
 (100, 'Gone With the Wind', 'Victor Fleming', 1939, None)]

fetchwhere([], ['Length_minutes IS NULL'])

SQL Command:  SELECT * FROM movies WHERE Length_minutes IS NULL
fetchall:
(100, 'Gone With the Wind', 'Victor Fleming', 1939, None)

[(100, 'Gone With the Wind', 'Victor Fleming', 1939, None)]

fetchwherejoin([], [], [], [], 'movies', 
               'Boxoffice ON movies.id = Boxoffice.movie_id')

SQL Command:  SELECT * FROM movies INNER JOIN Boxoffice ON movies.id = Boxoffice.movie_id
fetchall:
(1, 'Toy Story', 'John Lasseter', 1995, 81, 1, 8.3, 191796233, 170162503)
(2, "A Bug's Life", 'John Lasseter', 1998, 95, 2, 7.2, 162798565, 200600000)
(3, 'Toy Story 2', 'John Lasseter', 1999, 93, 3, 7.9, 245852179, 239163000)
(4, 'Monsters, Inc.', 'Pete Docter', 2001, 92, 4, 8.1, 289916256, 272900000)
(5, 'Finding Nemo', 'Andrew Stanton', 2003, 107, 5, 8.2, 380843261, 555900000)
(6, 'The Incredibles', 'Brad Bird', 2004, 116, 6, 8.0, 261441092, 370001000)
(7, 'Cars', 'John Lasseter', 2006, 117, 7, 7.2, 244082982, 217900167)
(8, 'Ratatouille', 'Brad Bird', 2007, 115, 8, 8.0, 206445654, 417277164)
(9, 'WALL-E', 'Andrew Stanton', 2008, 104, 9, 8.5, 223808164, 297503696)
(10, 'Up', 'Pete Docter', 2009, 101, 10, 8.3, 293004164, 438338580)
(11, 'Toy Story 3', 'Lee Unkrich', 2010, 103, 11, 8.4, 415004880, 648167031)
(12, 'Cars 2', 'John Lasseter', 2011, 120, 12, 6.4, 191452396, 368400000)
(13, 'Brave', 'Brenda Chapman', 2012, 102, 13, 7.2, 237283207, 301700000)
(14, 'Monsters University', 'Dan Scanlon', 2013, 110, 14, 7.4, 268492764, 475066843)

[(1, 'Toy Story', 'John Lasseter', 1995, 81, 1, 8.3, 191796233, 170162503),
 (2, "A Bug's Life", 'John Lasseter', 1998, 95, 2, 7.2, 162798565, 200600000),
 (3, 'Toy Story 2', 'John Lasseter', 1999, 93, 3, 7.9, 245852179, 239163000),
 (4, 'Monsters, Inc.', 'Pete Docter', 2001, 92, 4, 8.1, 289916256, 272900000),
 (5,
  'Finding Nemo',
  'Andrew Stanton',
  2003,
  107,
  5,
  8.2,
  380843261,
  555900000),
 (6, 'The Incredibles', 'Brad Bird', 2004, 116, 6, 8.0, 261441092, 370001000),
 (7, 'Cars', 'John Lasseter', 2006, 117, 7, 7.2, 244082982, 217900167),
 (8, 'Ratatouille', 'Brad Bird', 2007, 115, 8, 8.0, 206445654, 417277164),
 (9, 'WALL-E', 'Andrew Stanton', 2008, 104, 9, 8.5, 223808164, 297503696),
 (10, 'Up', 'Pete Docter', 2009, 101, 10, 8.3, 293004164, 438338580),
 (11, 'Toy Story 3', 'Lee Unkrich', 2010, 103, 11, 8.4, 415004880, 648167031),
 (12, 'Cars 2', 'John Lasseter', 2011, 120, 12, 6.4, 191452396, 368400000),
 (13, 'Brave', 'Brenda Chapman', 2012, 102, 13, 7.2, 237283207, 301700000),
 (14,
  'Monsters University',
  'Dan Scanlon',
  2013,
  110,
  14,
  7.4,
  268492764,
  475066843)]

fetchwherejoin([], [], [], [], 'movies', 
               'LEFT JOIN Boxoffice ON movies.id = Boxoffice.movie_id')

SQL Command:  SELECT * FROM movies LEFT JOIN Boxoffice ON movies.id = Boxoffice.movie_id
fetchall:
(1, 'Toy Story', 'John Lasseter', 1995, 81, 1, 8.3, 191796233, 170162503)
(2, "A Bug's Life", 'John Lasseter', 1998, 95, 2, 7.2, 162798565, 200600000)
(3, 'Toy Story 2', 'John Lasseter', 1999, 93, 3, 7.9, 245852179, 239163000)
(4, 'Monsters, Inc.', 'Pete Docter', 2001, 92, 4, 8.1, 289916256, 272900000)
(5, 'Finding Nemo', 'Andrew Stanton', 2003, 107, 5, 8.2, 380843261, 555900000)
(6, 'The Incredibles', 'Brad Bird', 2004, 116, 6, 8.0, 261441092, 370001000)
(7, 'Cars', 'John Lasseter', 2006, 117, 7, 7.2, 244082982, 217900167)
(8, 'Ratatouille', 'Brad Bird', 2007, 115, 8, 8.0, 206445654, 417277164)
(9, 'WALL-E', 'Andrew Stanton', 2008, 104, 9, 8.5, 223808164, 297503696)
(10, 'Up', 'Pete Docter', 2009, 101, 10, 8.3, 293004164, 438338580)
(11, 'Toy Story 3', 'Lee Unkrich', 2010, 103, 11, 8.4, 415004880, 648167031)
(12, 'Cars 2', 'John Lasseter', 2011, 120, 12, 6.4, 191452396, 368400000)
(13, 'Brave', 'Brenda Chapman', 2012, 102, 13, 7.2, 237283207, 301700000)
(14, 'Monsters University', 'Dan Scanlon', 2013, 110, 14, 7.4, 268492764, 475066843)
(100, 'Gone With the Wind', 'Victor Fleming', 1939, None, None, None, None, None)

[(1, 'Toy Story', 'John Lasseter', 1995, 81, 1, 8.3, 191796233, 170162503),
 (2, "A Bug's Life", 'John Lasseter', 1998, 95, 2, 7.2, 162798565, 200600000),
 (3, 'Toy Story 2', 'John Lasseter', 1999, 93, 3, 7.9, 245852179, 239163000),
 (4, 'Monsters, Inc.', 'Pete Docter', 2001, 92, 4, 8.1, 289916256, 272900000),
 (5,
  'Finding Nemo',
  'Andrew Stanton',
  2003,
  107,
  5,
  8.2,
  380843261,
  555900000),
 (6, 'The Incredibles', 'Brad Bird', 2004, 116, 6, 8.0, 261441092, 370001000),
 (7, 'Cars', 'John Lasseter', 2006, 117, 7, 7.2, 244082982, 217900167),
 (8, 'Ratatouille', 'Brad Bird', 2007, 115, 8, 8.0, 206445654, 417277164),
 (9, 'WALL-E', 'Andrew Stanton', 2008, 104, 9, 8.5, 223808164, 297503696),
 (10, 'Up', 'Pete Docter', 2009, 101, 10, 8.3, 293004164, 438338580),
 (11, 'Toy Story 3', 'Lee Unkrich', 2010, 103, 11, 8.4, 415004880, 648167031),
 (12, 'Cars 2', 'John Lasseter', 2011, 120, 12, 6.4, 191452396, 368400000),
 (13, 'Brave', 'Brenda Chapman', 2012, 102, 13, 7.2, 237283207, 301700000),
 (14,
  'Monsters University',
  'Dan Scanlon',
  2013,
  110,
  14,
  7.4,
  268492764,
  475066843),
 (100,
  'Gone With the Wind',
  'Victor Fleming',
  1939,
  None,
  None,
  None,
  None,
  None)]

RIGHT and FULL are not currently supported in sqlite3

switch order of tables

fetchwherejoin([], [], [], [], 'Boxoffice', 
               'LEFT JOIN Movies ON movies.id = Boxoffice.movie_id')

SQL Command:  SELECT * FROM Boxoffice LEFT JOIN Movies ON movies.id = Boxoffice.movie_id
fetchall:
(1, 8.3, 191796233, 170162503, 1, 'Toy Story', 'John Lasseter', 1995, 81)
(2, 7.2, 162798565, 200600000, 2, "A Bug's Life", 'John Lasseter', 1998, 95)
(3, 7.9, 245852179, 239163000, 3, 'Toy Story 2', 'John Lasseter', 1999, 93)
(4, 8.1, 289916256, 272900000, 4, 'Monsters, Inc.', 'Pete Docter', 2001, 92)
(5, 8.2, 380843261, 555900000, 5, 'Finding Nemo', 'Andrew Stanton', 2003, 107)
(6, 8.0, 261441092, 370001000, 6, 'The Incredibles', 'Brad Bird', 2004, 116)
(7, 7.2, 244082982, 217900167, 7, 'Cars', 'John Lasseter', 2006, 117)
(8, 8.0, 206445654, 417277164, 8, 'Ratatouille', 'Brad Bird', 2007, 115)
(9, 8.5, 223808164, 297503696, 9, 'WALL-E', 'Andrew Stanton', 2008, 104)
(10, 8.3, 293004164, 438338580, 10, 'Up', 'Pete Docter', 2009, 101)
(11, 8.4, 415004880, 648167031, 11, 'Toy Story 3', 'Lee Unkrich', 2010, 103)
(12, 6.4, 191452396, 368400000, 12, 'Cars 2', 'John Lasseter', 2011, 120)
(13, 7.2, 237283207, 301700000, 13, 'Brave', 'Brenda Chapman', 2012, 102)
(14, 7.4, 268492764, 475066843, 14, 'Monsters University', 'Dan Scanlon', 2013, 110)

[(1, 8.3, 191796233, 170162503, 1, 'Toy Story', 'John Lasseter', 1995, 81),
 (2, 7.2, 162798565, 200600000, 2, "A Bug's Life", 'John Lasseter', 1998, 95),
 (3, 7.9, 245852179, 239163000, 3, 'Toy Story 2', 'John Lasseter', 1999, 93),
 (4, 8.1, 289916256, 272900000, 4, 'Monsters, Inc.', 'Pete Docter', 2001, 92),
 (5,
  8.2,
  380843261,
  555900000,
  5,
  'Finding Nemo',
  'Andrew Stanton',
  2003,
  107),
 (6, 8.0, 261441092, 370001000, 6, 'The Incredibles', 'Brad Bird', 2004, 116),
 (7, 7.2, 244082982, 217900167, 7, 'Cars', 'John Lasseter', 2006, 117),
 (8, 8.0, 206445654, 417277164, 8, 'Ratatouille', 'Brad Bird', 2007, 115),
 (9, 8.5, 223808164, 297503696, 9, 'WALL-E', 'Andrew Stanton', 2008, 104),
 (10, 8.3, 293004164, 438338580, 10, 'Up', 'Pete Docter', 2009, 101),
 (11, 8.4, 415004880, 648167031, 11, 'Toy Story 3', 'Lee Unkrich', 2010, 103),
 (12, 6.4, 191452396, 368400000, 12, 'Cars 2', 'John Lasseter', 2011, 120),
 (13, 7.2, 237283207, 301700000, 13, 'Brave', 'Brenda Chapman', 2012, 102),
 (14,
  7.4,
  268492764,
  475066843,
  14,
  'Monsters University',
  'Dan Scanlon',
  2013,
  110)]

SQL Lesson 9: Queries with expressions

Use AS for column aliases.

fetchwherejoin(['title', 'International_sales AS Isales', 
                'Domestic_sales AS Dsales'], ['Dsales < Isales'], [], [], 
               'movies', 'LEFT JOIN Boxoffice ON movies.id = Boxoffice.movie_id')

SQL Command:  SELECT title, International_sales AS Isales, Domestic_sales AS Dsales FROM movies LEFT JOIN Boxoffice ON movies.id = Boxoffice.movie_id WHERE Dsales < Isales
fetchall:
("A Bug's Life", 200600000, 162798565)
('Finding Nemo', 555900000, 380843261)
('The Incredibles', 370001000, 261441092)
('Ratatouille', 417277164, 206445654)
('WALL-E', 297503696, 223808164)
('Up', 438338580, 293004164)
('Toy Story 3', 648167031, 415004880)
('Cars 2', 368400000, 191452396)
('Brave', 301700000, 237283207)
('Monsters University', 475066843, 268492764)

[("A Bug's Life", 200600000, 162798565),
 ('Finding Nemo', 555900000, 380843261),
 ('The Incredibles', 370001000, 261441092),
 ('Ratatouille', 417277164, 206445654),
 ('WALL-E', 297503696, 223808164),
 ('Up', 438338580, 293004164),
 ('Toy Story 3', 648167031, 415004880),
 ('Cars 2', 368400000, 191452396),
 ('Brave', 301700000, 237283207),
 ('Monsters University', 475066843, 268492764)]

Create new column: Sales ratio.

fetchwherejoin(['title', 'International_sales / Domestic_sales AS Sratio'], 
               ['Sratio > .5'], [], [], 'movies', 
               'LEFT JOIN Boxoffice ON movies.id = Boxoffice.movie_id')

SQL Command:  SELECT title, International_sales / Domestic_sales AS Sratio FROM movies LEFT JOIN Boxoffice ON movies.id = Boxoffice.movie_id WHERE Sratio > .5
fetchall:
("A Bug's Life", 1)
('Finding Nemo', 1)
('The Incredibles', 1)
('Ratatouille', 2)
('WALL-E', 1)
('Up', 1)
('Toy Story 3', 1)
('Cars 2', 1)
('Brave', 1)
('Monsters University', 1)

[("A Bug's Life", 1),
 ('Finding Nemo', 1),
 ('The Incredibles', 1),
 ('Ratatouille', 2),
 ('WALL-E', 1),
 ('Up', 1),
 ('Toy Story 3', 1),
 ('Cars 2', 1),
 ('Brave', 1),
 ('Monsters University', 1)]

Create new column: gross sales millions.

fetchwherejoin(['title', 
                '(International_sales + Domestic_sales) / 1000000 AS Gross_sales_MM'], 
               [], ['Gross_sales_MM'], [], 'movies', 
               'LEFT JOIN Boxoffice ON movies.id = Boxoffice.movie_id')

SQL Command:  SELECT title, (International_sales + Domestic_sales) / 1000000 AS Gross_sales_MM FROM movies LEFT JOIN Boxoffice ON movies.id = Boxoffice.movie_id ORDER BY Gross_sales_MM
fetchall:
('Gone With the Wind', None)
('Toy Story', 361)
("A Bug's Life", 363)
('Cars', 461)
('Toy Story 2', 485)
('WALL-E', 521)
('Brave', 538)
('Cars 2', 559)
('Monsters, Inc.', 562)
('Ratatouille', 623)
('The Incredibles', 631)
('Up', 731)
('Monsters University', 743)
('Finding Nemo', 936)
('Toy Story 3', 1063)

[('Gone With the Wind', None),
 ('Toy Story', 361),
 ("A Bug's Life", 363),
 ('Cars', 461),
 ('Toy Story 2', 485),
 ('WALL-E', 521),
 ('Brave', 538),
 ('Cars 2', 559),
 ('Monsters, Inc.', 562),
 ('Ratatouille', 623),
 ('The Incredibles', 631),
 ('Up', 731),
 ('Monsters University', 743),
 ('Finding Nemo', 936),
 ('Toy Story 3', 1063)]

SQL Lesson 10: Queries with aggregates (Pt. 1)

Calculate average domestic sales using AVG().

fetchwherejoin(['AVG(Domestic_sales)'], [], [], [], 'movies', 
               'LEFT JOIN Boxoffice ON movies.id = Boxoffice.movie_id')

SQL Command:  SELECT AVG(Domestic_sales) FROM movies LEFT JOIN Boxoffice ON movies.id = Boxoffice.movie_id
fetchall:
(258015842.64285713,)

[(258015842.64285713,)]

How many movies have a rating < 8? Use COUNT().

fetchwherejoin(['COUNT()'], ['Rating < 8'], [], [], 'movies', 
               'LEFT JOIN Boxoffice ON movies.id = Boxoffice.movie_id')

SQL Command:  SELECT COUNT() FROM movies LEFT JOIN Boxoffice ON movies.id = Boxoffice.movie_id WHERE Rating < 8
fetchall:
(6,)

[(6,)]

What are the movies with a rating < 8?

fetchwherejoin(['title', 'Rating'], ['Rating < 8'], ['Rating'], [], 'movies', 
               'LEFT JOIN Boxoffice ON movies.id = Boxoffice.movie_id')

SQL Command:  SELECT title, Rating FROM movies LEFT JOIN Boxoffice ON movies.id = Boxoffice.movie_id WHERE Rating < 8 ORDER BY Rating
fetchall:
('Cars 2', 6.4)
("A Bug's Life", 7.2)
('Cars', 7.2)
('Brave', 7.2)
('Monsters University', 7.4)
('Toy Story 2', 7.9)

[('Cars 2', 6.4),
 ("A Bug's Life", 7.2),
 ('Cars', 7.2),
 ('Brave', 7.2),
 ('Monsters University', 7.4),
 ('Toy Story 2', 7.9)]

Calculate the max, min, avg ratings.

fetchwherejoin(['max(Rating)', 'min(Rating)', 'avg(Rating)'], [], [], [], 
               'movies', 'LEFT JOIN Boxoffice ON movies.id = Boxoffice.movie_id')

SQL Command:  SELECT max(Rating), min(Rating), avg(Rating) FROM movies LEFT JOIN Boxoffice ON movies.id = Boxoffice.movie_id
fetchall:
(8.5, 6.4, 7.7928571428571445)

[(8.5, 6.4, 7.7928571428571445)]

SQL Lesson 11: Queries with aggregates (Pt. 2)

SQL Lesson 12: Order of execution of a Query

SQL Lesson 13: Inserting rows

We did this above after creating the table

SQL Lesson 14: Updating rows

def updatewhere(pairs = [], where = [], table = 'movies'):
    cursor = connection.cursor()
    fstr = '*'
    wstr = ' 1=1 '
    command = "UPDATE " + table + " SET "
    if pairs:
        count = len(pairs)
        for p in pairs:
            (column, value) = p
            command += column + " = " + str(value)
            if (count > 1):
                command += ", "
                count -= 1
    if where:
        wstr = ' AND '.join(where)
    command += " WHERE " + wstr
    print ("SQL Command: ", command)
    cursor.execute(command)
    ## show results
    fetchall([], table)

Change title and year. This is a bogus example.

updatewhere([('title', '"s"'), ('year', 1000)], ['year < 2000'])

SQL Command:  UPDATE movies SET title = "s", year = 1000 WHERE year < 2000
SQL Command:  SELECT * FROM movies
fetchall:
(1, 's', 'John Lasseter', 1000, 81)
(2, 's', 'John Lasseter', 1000, 95)
(3, 's', 'John Lasseter', 1000, 93)
(4, 'Monsters, Inc.', 'Pete Docter', 2001, 92)
(5, 'Finding Nemo', 'Andrew Stanton', 2003, 107)
(6, 'The Incredibles', 'Brad Bird', 2004, 116)
(7, 'Cars', 'John Lasseter', 2006, 117)
(8, 'Ratatouille', 'Brad Bird', 2007, 115)
(9, 'WALL-E', 'Andrew Stanton', 2008, 104)
(10, 'Up', 'Pete Docter', 2009, 101)
(11, 'Toy Story 3', 'Lee Unkrich', 2010, 103)
(12, 'Cars 2', 'John Lasseter', 2011, 120)
(13, 'Brave', 'Brenda Chapman', 2012, 102)
(14, 'Monsters University', 'Dan Scanlon', 2013, 110)
(100, 's', 'Victor Fleming', 1000, None)

fetchall()

SQL Command:  SELECT * FROM movies
fetchall:
(1, 's', 'John Lasseter', 1000, 81)
(2, 's', 'John Lasseter', 1000, 95)
(3, 's', 'John Lasseter', 1000, 93)
(4, 'Monsters, Inc.', 'Pete Docter', 2001, 92)
(5, 'Finding Nemo', 'Andrew Stanton', 2003, 107)
(6, 'The Incredibles', 'Brad Bird', 2004, 116)
(7, 'Cars', 'John Lasseter', 2006, 117)
(8, 'Ratatouille', 'Brad Bird', 2007, 115)
(9, 'WALL-E', 'Andrew Stanton', 2008, 104)
(10, 'Up', 'Pete Docter', 2009, 101)
(11, 'Toy Story 3', 'Lee Unkrich', 2010, 103)
(12, 'Cars 2', 'John Lasseter', 2011, 120)
(13, 'Brave', 'Brenda Chapman', 2012, 102)
(14, 'Monsters University', 'Dan Scanlon', 2013, 110)
(100, 's', 'Victor Fleming', 1000, None)

[(1, 's', 'John Lasseter', 1000, 81),
 (2, 's', 'John Lasseter', 1000, 95),
 (3, 's', 'John Lasseter', 1000, 93),
 (4, 'Monsters, Inc.', 'Pete Docter', 2001, 92),
 (5, 'Finding Nemo', 'Andrew Stanton', 2003, 107),
 (6, 'The Incredibles', 'Brad Bird', 2004, 116),
 (7, 'Cars', 'John Lasseter', 2006, 117),
 (8, 'Ratatouille', 'Brad Bird', 2007, 115),
 (9, 'WALL-E', 'Andrew Stanton', 2008, 104),
 (10, 'Up', 'Pete Docter', 2009, 101),
 (11, 'Toy Story 3', 'Lee Unkrich', 2010, 103),
 (12, 'Cars 2', 'John Lasseter', 2011, 120),
 (13, 'Brave', 'Brenda Chapman', 2012, 102),
 (14, 'Monsters University', 'Dan Scanlon', 2013, 110),
 (100, 's', 'Victor Fleming', 1000, None)]

SQL Lesson 15: Deleting rows

def deletewhere(where = [], table = 'movies'):
    cursor = connection.cursor()
    wstr = ' 1=1 '
    command = "DELETE FROM " + table 
    if where:
        wstr = ' AND '.join(where)
    command += " WHERE " + wstr
    print ("SQL Command: ", command)
    cursor.execute(command)
    ## show results
    fetchall([], table)

deletewhere(['year < 2000'])

SQL Command:  DELETE FROM movies WHERE year < 2000
SQL Command:  SELECT * FROM movies
fetchall:
(4, 'Monsters, Inc.', 'Pete Docter', 2001, 92)
(5, 'Finding Nemo', 'Andrew Stanton', 2003, 107)
(6, 'The Incredibles', 'Brad Bird', 2004, 116)
(7, 'Cars', 'John Lasseter', 2006, 117)
(8, 'Ratatouille', 'Brad Bird', 2007, 115)
(9, 'WALL-E', 'Andrew Stanton', 2008, 104)
(10, 'Up', 'Pete Docter', 2009, 101)
(11, 'Toy Story 3', 'Lee Unkrich', 2010, 103)
(12, 'Cars 2', 'John Lasseter', 2011, 120)
(13, 'Brave', 'Brenda Chapman', 2012, 102)
(14, 'Monsters University', 'Dan Scanlon', 2013, 110)

SQL Lesson 16: Creating tables

CREATE TABLE IF NOT EXISTS mytable (
    column DataType TableConstraint DEFAULT default_value,
    another_column DataType TableConstraint DEFAULT default_value,
    …
);

Common data types:

• Integer
• Boolean
• Float, Double, Real
• Character(size)
• Varchar(size)
• Text
• Date
• DateTime
• Blob (binary objects, e.g., video, audio, code)

Common table constraints:

• PRIMARY KEY - most efficient index
• AUTOINCREMENT - useful for id fields
• UNIQUE - no duplicate entries
• NOT NULL - required field
• CHECK (expression) - check for type, range, etc.
• FOREIGN KEY - referential integrity check

SQL Lesson 17: Altering tables

c12 = '''
ALTER TABLE movies
ADD seenit BOOLEAN DEFAULT FALSE
'''

cursor.execute(c12)

<sqlite3.Cursor at 0x7f54854bdb90>

desc()

SQL Command:  PRAGMA table_info('movies')
(0, 'id', 'INTEGER', 0, None, 1)
(1, 'title', 'TEXT', 0, None, 0)
(2, 'director', 'TEXT', 0, None, 0)
(3, 'year', 'INTEGER', 0, None, 0)
(4, 'length_minutes', 'INTEGER', 0, None, 0)
(5, 'seenit', 'BOOLEAN', 0, 'FALSE', 0)

updatewhere([('seenit', '"TRUE"')], ['year < 2010'])

SQL Command:  UPDATE movies SET seenit = "TRUE" WHERE year < 2010
SQL Command:  SELECT * FROM movies
fetchall:
(4, 'Monsters, Inc.', 'Pete Docter', 2001, 92, 'TRUE')
(5, 'Finding Nemo', 'Andrew Stanton', 2003, 107, 'TRUE')
(6, 'The Incredibles', 'Brad Bird', 2004, 116, 'TRUE')
(7, 'Cars', 'John Lasseter', 2006, 117, 'TRUE')
(8, 'Ratatouille', 'Brad Bird', 2007, 115, 'TRUE')
(9, 'WALL-E', 'Andrew Stanton', 2008, 104, 'TRUE')
(10, 'Up', 'Pete Docter', 2009, 101, 'TRUE')
(11, 'Toy Story 3', 'Lee Unkrich', 2010, 103, 0)
(12, 'Cars 2', 'John Lasseter', 2011, 120, 0)
(13, 'Brave', 'Brenda Chapman', 2012, 102, 0)
(14, 'Monsters University', 'Dan Scanlon', 2013, 110, 0)

fetchall()

SQL Command:  SELECT * FROM movies
fetchall:
(4, 'Monsters, Inc.', 'Pete Docter', 2001, 92, 'TRUE')
(5, 'Finding Nemo', 'Andrew Stanton', 2003, 107, 'TRUE')
(6, 'The Incredibles', 'Brad Bird', 2004, 116, 'TRUE')
(7, 'Cars', 'John Lasseter', 2006, 117, 'TRUE')
(8, 'Ratatouille', 'Brad Bird', 2007, 115, 'TRUE')
(9, 'WALL-E', 'Andrew Stanton', 2008, 104, 'TRUE')
(10, 'Up', 'Pete Docter', 2009, 101, 'TRUE')
(11, 'Toy Story 3', 'Lee Unkrich', 2010, 103, 0)
(12, 'Cars 2', 'John Lasseter', 2011, 120, 0)
(13, 'Brave', 'Brenda Chapman', 2012, 102, 0)
(14, 'Monsters University', 'Dan Scanlon', 2013, 110, 0)

[(4, 'Monsters, Inc.', 'Pete Docter', 2001, 92, 'TRUE'),
 (5, 'Finding Nemo', 'Andrew Stanton', 2003, 107, 'TRUE'),
 (6, 'The Incredibles', 'Brad Bird', 2004, 116, 'TRUE'),
 (7, 'Cars', 'John Lasseter', 2006, 117, 'TRUE'),
 (8, 'Ratatouille', 'Brad Bird', 2007, 115, 'TRUE'),
 (9, 'WALL-E', 'Andrew Stanton', 2008, 104, 'TRUE'),
 (10, 'Up', 'Pete Docter', 2009, 101, 'TRUE'),
 (11, 'Toy Story 3', 'Lee Unkrich', 2010, 103, 0),
 (12, 'Cars 2', 'John Lasseter', 2011, 120, 0),
 (13, 'Brave', 'Brenda Chapman', 2012, 102, 0),
 (14, 'Monsters University', 'Dan Scanlon', 2013, 110, 0)]

c13 = '''
ALTER TABLE movies
DROP seenit 
'''

cursor.execute(c13)

---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
/tmp/ipykernel_3583178/3994672272.py in <module>
----> 1 cursor.execute(c13)

OperationalError: near "DROP": syntax error

DROP not supported by sqlite.

c14 = '''
ALTER TABLE movies
RENAME TO films;
'''

cursor.execute(c14)

<sqlite3.Cursor at 0x7f54854bdb90>

fetchall([], 'films')

SQL Command:  SELECT * FROM films
fetchall:
(4, 'Monsters, Inc.', 'Pete Docter', 2001, 92, 'TRUE')
(5, 'Finding Nemo', 'Andrew Stanton', 2003, 107, 'TRUE')
(6, 'The Incredibles', 'Brad Bird', 2004, 116, 'TRUE')
(7, 'Cars', 'John Lasseter', 2006, 117, 'TRUE')
(8, 'Ratatouille', 'Brad Bird', 2007, 115, 'TRUE')
(9, 'WALL-E', 'Andrew Stanton', 2008, 104, 'TRUE')
(10, 'Up', 'Pete Docter', 2009, 101, 'TRUE')
(11, 'Toy Story 3', 'Lee Unkrich', 2010, 103, 0)
(12, 'Cars 2', 'John Lasseter', 2011, 120, 0)
(13, 'Brave', 'Brenda Chapman', 2012, 102, 0)
(14, 'Monsters University', 'Dan Scanlon', 2013, 110, 0)

[(4, 'Monsters, Inc.', 'Pete Docter', 2001, 92, 'TRUE'),
 (5, 'Finding Nemo', 'Andrew Stanton', 2003, 107, 'TRUE'),
 (6, 'The Incredibles', 'Brad Bird', 2004, 116, 'TRUE'),
 (7, 'Cars', 'John Lasseter', 2006, 117, 'TRUE'),
 (8, 'Ratatouille', 'Brad Bird', 2007, 115, 'TRUE'),
 (9, 'WALL-E', 'Andrew Stanton', 2008, 104, 'TRUE'),
 (10, 'Up', 'Pete Docter', 2009, 101, 'TRUE'),
 (11, 'Toy Story 3', 'Lee Unkrich', 2010, 103, 0),
 (12, 'Cars 2', 'John Lasseter', 2011, 120, 0),
 (13, 'Brave', 'Brenda Chapman', 2012, 102, 0),
 (14, 'Monsters University', 'Dan Scanlon', 2013, 110, 0)]

SQL Lesson 18: Dropping tables

c15 = '''
DROP TABLE IF EXISTS films
'''

cursor.execute(c15)

<sqlite3.Cursor at 0x7f54854bdb90>

desc('films')

SQL Command:  PRAGMA table_info('films')

fetchall([],'films')

SQL Command:  SELECT * FROM films

---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
/tmp/ipykernel_3583178/4189900663.py in <module>
----> 1 fetchall([],'films')

/tmp/ipykernel_3583178/3724683776.py in fetchall(fields, table)
      6     command = "SELECT " + fstr + " FROM " + table
      7     print ("SQL Command: ", command)
----> 8     cursor.execute(command)
      9     print("fetchall:")
     10     result = cursor.fetchall()

OperationalError: no such table: films

Advanced sqlite

From: http://sebastianraschka.com/Articles/2014_sqlite_in_python_tutorial.html

Should execute "connection.close()" before running doall()

def total_rows(cursor, table_name, print_out=False):
    """ Returns the total number of rows in the database """
    cursor.execute('SELECT COUNT(*) FROM {}'.format(table_name))
    count = cursor.fetchall()
    if print_out:
        print('\nTotal rows: {}'.format(count[0][0]))
    return count[0][0]

def table_col_info(cursor, table_name, print_out=False):
    """ Returns a list of tuples with column informations:
        (id, name, type, notnull, default_value, primary_key)
    """
    cursor.execute('PRAGMA TABLE_INFO({})'.format(table_name))
    info = cursor.fetchall()

    if print_out:
        print("\nColumn Info:\nID, Name, Type, NotNull, DefaultVal, PrimaryKey")
        for col in info:
            print(col)
    return info

def values_in_col(cursor, table_name, print_out=True):
    """ Returns a dictionary with columns as keys and the number of not-null
        entries as associated values.
    """
    cursor.execute('PRAGMA TABLE_INFO({})'.format(table_name))
    info = cursor.fetchall()
    col_dict = dict()
    for col in info:
        col_dict[col[1]] = 0
    for col in col_dict:
        cursor.execute('SELECT ({0}) FROM {1} WHERE {0} IS NOT NULL'.
                       format(col, table_name))
        # In my case this approach resulted in a better performance than using COUNT
        number_rows = len(cursor.fetchall())
        col_dict[col] = number_rows
    if print_out:
        print("\nNumber of entries per column:")
        for i in col_dict.items():
            print('{}: {}'.format(i[0], i[1]))
    return col_dict


## should execute "connection.close()" before running doall()
def doall():
    sqlite_file = 'mydb.db'
    table_name = 'movies'

    conn = sqlite3.connect(sqlite_file)
    cursor = conn.cursor()
    total_rows(cursor, table_name, print_out=True)
    table_col_info(cursor, table_name, print_out=True)
    values_in_col(cursor, table_name, print_out=True) # slow on large data bases

connection.close()

doall()

Total rows: 14

Column Info:
ID, Name, Type, NotNull, DefaultVal, PrimaryKey
(0, 'id', 'INTEGER', 0, None, 1)
(1, 'title', 'TEXT', 0, None, 0)
(2, 'director', 'TEXT', 0, None, 0)
(3, 'year', 'INTEGER', 0, None, 0)
(4, 'length_minutes', 'INTEGER', 0, None, 0)

Number of entries per column:
id: 14
title: 14
director: 14
year: 14
length_minutes: 14

evalsql()

From: https://docs.python.org/2/library/sqlite3.html

def evalsql():
    con = sqlite3.connect("mydb.db")
    con.isolation_level = None
    cur = con.cursor()

    buffer = ""

    print ("Enter your SQL commands to execute in sqlite3.")
    print ("Enter a blank line to exit.")

    while True:
        line = input()
        if line == "":
            break
        buffer += line
        print (buffer)
        if sqlite3.complete_statement(buffer):
            try:
                buffer = buffer.strip()
                cur.execute(buffer)
                
                if buffer.lstrip().upper().startswith("SELECT"):
                    print (cur.fetchall())
                buffer = ""
            except sqlite3.Error as e:
                print ("An error occurred:", e.args[0])
                buffer = ""

    con.close()    

evalsql()

Enter your SQL commands to execute in sqlite3.
Enter a blank line to exit.
select * from movies
select * from movies