Lecture

Contributors: Alicia Wang, Conner Swenberg

Lecture Slides

550KB

lecture2.pdf

pdf

Databases

The Need for Databases

Last time we setup a simple flask app that can receive requests and return responses. However, our application stores data in memory; what if we want to persist this non-static data? In any sort of meaningful application, various entities that exist within the logic of your app have relationships with other entities. In the example of a social network: users, posts, photos, comments are all entities that are related to each other in some way. Databases allow you to store these entities and and make said “connections” between other entities.

What is a Database?

A database is a collection of information organized so that it can be easily accessed, updated, and managed. This is executed with a collection of tables. Each column represents a specific piece of information you wish to store and has a defined data type. Each row contains a set of values corresponding with each column. A row represents a singular item. Example: A grocery databaseThis database contains two tables, perhaps named fruits and employees. Both tables contain three rows (also referred to as records or tuples) and four columns (also referred to as fields or attributes).

Why use a Database?

1. Querying

You can query data, i.e. ask database questions. With SQL databases, it is easy to receive data under some constraints. For example, you can easily find all items with at least 10 in stock in the database above.

2. Structure

All data in a SQL database is structured according to a pre-defined schema, and the structure will be enforced to avoid potential data mishandling. In the database above, the age column in the Employee table is an integer, so we could not create a new entry with a string in that field. SQL databases also grant the ability to relate data from two different tables together, which will be covered next lecture.

SQL

Structured Query Language: Language used to make queries that can read, insert, update or delete data from a database. SQL is our means of communicating with the database.

SQLite

SQLite is a relational database management system (RDBMS). A RDBMS allows you to create, update, and administer a relational database.

Unlike other RDBMSs (MySQL, PostgreSQL, etc), SQLite:

• Does not require installation, setup, or configuration

• Is serverless

• Reads and writes directly to ordinary disk files

• Has databases is contained in a single file

Pros

• Doesn’t rely on a server to run

• Has nothing to configure

• Is small (library only 250KB)

• Is file based and extremely portable

Cons

• Is only a subset of SQL

• Requires a lot of memory to run for large databases

• Has no user/privilege management

SQL Operation Examples

Creating a new table

CREATE TABLE table_name (
    column1 datatype PRIMARY KEY AUTOINCREMENT,
    column2 datatype NOT NULL
);

This query will create a new table in your database. The column parameters specify the name of each table attribute/field. The datatype parameters specify what type of data each column can hold (e.g. INT, TEXT, etc.)

Columns can have special constraints placed on them. These constraints are specified after the column type, like so:

columnname datatype constraint 

Column constraints are used to limit the data that is inputted into the table, ensuring the reliability of the data. If a query that violates a constraint is made, the operation will be aborted. The NOT NULL constraint forces a column to not accept NULL values such that you cannot insert or update a row without giving a value for this column. The PRIMARY KEY constraint makes a column a unique identifier of a row in the table. Each value in a PRIMARY KEY column must be unique to the table and not NULL.

Inserting data into a table

INSERT INTO table_name (column1, ..., columnN) 
VALUES (value1, ..., valueN);

INSERT INTO is used to add new rows to a table. This query specifies each column and its respective value to be inserted. If you are adding values for all columns of a table, you don’t need to specify the columns as long as the values are listed in the same order as the columns

INSERT INTO table_name VALUES (value1, ..., valueN);

Retrieving data from a table

SELECT column1, ..., columnN FROM table_name;

SELECT is used to retrieve data from a table. The data returned is stored in a result-set, which looks like a table with columns that are a subset of the original table’s columns. The column parameters are the columns you want to select data from. You can also select data from all columns in a table using the * symbol:

SELECT * FROM table_name;

To filter rows in a SELECT query, you can use the WHERE clause, which is used to select only those records that fulfill a specified boolean condition:

SELECT column1, ..., columnN FROM table_name WHERE condition;

Updating data in a table

UPDATE table_name SET column1=value1, ..., columnN=valueN WHERE condition;

UPDATE is used to modify existing rows in a table. It will change the columns to the value you specified for rows that meet the boolean condition. If no WHERE clause is included, then all rows will be updated.

Destroying data in a table

DELETE FROM table_name WHERE condition;

DELETE FROM will delete all rows in a table that satisfy the WHERE clause.

Case Sensitivity

SQL keywords are case insensitive, so you can use SELECT or select interchangeably. In SQLite, database and column names are also case insensitive, but they may be in other database management systems!