T18 Databases - tstorrnetnz/teaching2025 GitHub Wiki

Introduction

Your project will store data in some form or other. For example, you may include details of people, orders, things (books, cars, etc). We have already learned about using CSV/TXT files to store data. Databases are a more complex, but possibly more useful way to store and access data - depending on your need.

When designed correctly, a database can manage complex information easily. Repl (and Python) have many tools for managing databases, so it may be something you wish to use. If so, a simple relational database is what you will probably need.

The couple of videos below describe what a relational database is. You will need to watch these, so that you can begin to decide if they will be useful to you.

Flat File vs Relational Database Models (2 mins)

Relational Database Concepts (5 mins)

To make even the most simple database will require you to think carefully about your data. This includes what tables and fields and how they are related. Once you have designed your database, we need to create it. SQLite is a database that is commonly used for creating smallish databases in applications.

SQLAlchemy

This is really cool! SQLAlchemy is a python module that maps classes to a SQLite database meaning that you can use create and use the database directly from Python. It is soooooo useful and I expect many of you will use this in your projects.

To get started in SQLAlchemy, follow this tutorial, but with the following notes/modifications to use it in repl.it

Tutorial URL https://www.youtube.com/watch?v=AKQ3XEDI9Mw Setup to use SQLAlchemy in VS Code

  1. Create a new repl python project.
  2. In the shell for the project type pip install sqlalchemy and press enter - the shell should be next to the console.
  3. Click on the packages icon in repl.it, search for sqlalchemy and install it

A few changes to the code - the imports should look like below

from sqlalchemy import create_engine, ForeignKey, Column, String, Integer,CHAR

from sqlalchemy.orm import declarative_base //change here

from sqlalchemy.orm import sessionmaker

Watch out for indentation in the People class.

Using Pythonanywhere, make sure you create a separate folder for the program

You will need to delete the database file between each run of the program.

After you have run the program, look for the database file in folder.

My completed tutorial can be found here.

Documentation

SQLAlchemy simple overview with examples

SQLAlchemy homepage

SQLAlchemy ORM Query examples

SQLAchemy database queries

For you to do

Try designing and creating two databases:

  1. A database for a music collection for friends to share music
  2. A database for a library that lends out books