Organizing Bears Lab
Learning Goals
- Use SQL to store data and retrieve it later on.
- Use SQLite to build relational databases on your computer.
- Perform CRUD operations on relational databases using SQL.
Key Vocab
- SQL (Structured Query Language): a programming language that is used to manage relational databases and perform operations on the data that they contain.
- Relational Database: a collection of data that is organized in well-defined relationships. The most common type of database.
- Query: a statement used to return data from a database.
- Table: a collection of related data in a database. Composed of rows and columns. Similar to a class in Python.
- Row: a single record in a database table. Each column represents an attribute of the record. Similar to an object in Python.
- Column: a single field in a database table. Each row contains values in each column. Similar to a Python object’s attributes.
- Schema: a blueprint of the construction of the tables in a database and how they relate to one another.
Lab Structure
This lab might seem a bit different than what you've seen before. Take a look at the file structure and read the comments to understand what each file is used for:
├── __init__.py # designates "python-p3-organizing-bears-lab" as package
├── Pipfile
├── Pipfile.lock
├── README.md
├── lib
├── __init__.py # designates "lib" as package
│ ├── create.sql # where you CREATE your schema
│ ├── insert.sql # where you INSERT your data
│ ├── seed.sql # data for in-memory test database
│ ├── sql_queries.py # where you write your SELECT queries
└── testing # all the tests
├── __init__.py # designates "testing" as package
├── create_test.py # this tests your create.sql file
├── insert_test.py # this tests your insert.sql file
├── select_test.py # this tests the queries you write in sql_queries.py
└── conftest.py # configuration for pytest
This lab uses the sqlite3
module from Python's standard library to allow us
to connect to a SQL database from Python. How cool is that!? We'll use this
module more in the lessons to come.
A Note on Testing
Let's briefly go over what is happening in setup blocks that our tests will be using.
connection = sqlite3.connect(":memory:")
cursor = connection.cursor()
create_file = open("lib/create.sql")
create_as_string = create_file.read()
cursor.executescript(create_as_string)
Before each test, two important things happen.
First, a new in-memory database is created. Why do we do this instead of creating a database file? Let's say we run our tests and they add ten items to our database. If we did not use an in-memory store, those would be in there forever. This way, our database gets thrown out after every running of the tests. You can learn more about in-memory databases here.
Next, the test opens the .sql
file, and runs the SQL code in that file in
that in-memory database.
CREATE TABLE
Part 1: Get the tests in testing/create_test.py
to pass by writing code in the
lib/create.sql
file. Your CREATE
statement should look something like this:
CREATE TABLE bears (
//columns here
);
Your columns should be the following types:
column | type |
---|---|
id | integer |
name | text |
age | integer |
sex | text |
color | text |
temperament | text |
alive | boolean |
Read about SQLite3 Datatypes to determine what your insert values are going to be. Be sure to pay attention to how booleans are expressed in SQLite3.
INSERT
Part 2: Get the tests in testing/insert_test_.py
to pass by writing code in the
lib/insert.sql
file. Input the following 8 bears (you can make up details
about them, but make sex either 'M' or 'F'):
- Mr. Chocolate
- Rowdy
- Tabitha
- Sergeant Brown
- Melissa
- Grinch
- Wendy
- unnamed (refer back to how to create a record that doesn't have one value)
SELECT
Part 3: Get the tests in testing/select_test.py
to pass. Note that for this section, the
database will be seeded with external data from the lib/seed.sql
file so don't
expect it to reflect the data you added above.
Note: Since it's a Python file, write your queries as strings in the
global
scope in the lib/sql_queries.py
file. For example, to pass the first
test, your Python string should look like this:
select_all_female_bears_return_name_and_age = """
SELECT
bears.name,
bears.age
FROM bears
WHERE sex='F';
"""
You may be expected to use SQL statements that you're not particularly familiar with. Make sure you use the resources and Google to find the right statements.