MCS 275 Spring 2022
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.
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.
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.
/usr/bin/sqlite3. You can probably just type
sqlite3in a terminal.
sqlite3in a terminal), otherwise use your package manager to install. In Ubuntu that package is called
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.
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.
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 planets ( name TEXT, dist REAL, year_discovered INTEGER );
Each item in parentheses is
INSERT command adds a row to a table.
To pass values to a statement in
? characters as placeholders and then give a tuple of values in the second argument.
con.execute( "INSERT INTO planets VALUES (?,?,?);", ("Earth", 1.0, None) )
("Neptune", 30.1, 1846).
|Name||Distance from sun||Year discovered|
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 (?);",  ) # OK con.execute("INSERT INTO tab VALUES (?);", (275,) ) # OK
These examples assume
tab is a table with just one column.
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
Examples of things that can appear after WHERE:
col = value -- Also supports >, >=, <, <=, != col IN (val1, val2, val3) col BETWEEN lowval AND highval col IS NULL col IS NOT NULL stringcol LIKE pattern -- string pattern matching condition1 AND condition2 condition1 OR condition2
coursetitle LIKE "Introduction to %" itemtype LIKE "electrical adapt_r"
In a pattern string:
%matches any number of characters (including 0)
_matches any single character
"fossil dig" and
"dog" but does not match
"dog toy", or
WHERE, ORDER BY, LIMIT can be used together, but must appear in that "WOBL" order. (Details.)
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.
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.
Deletes an entire table.
DROP TABLE table_name; -- no such table = ERROR DROP TABLE IF EXISTS table_name; -- no such table = ok
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.