Django - 4

Single Table SQL

Structured Query Language

• SQL is the language we use to issue commands to the database
  • Create/Insert data
  • Read/Select some data
  • Update data
  • Delete data

Relational Database

Relational databases model data by storing rows and columns in tables. The power of the relational database lies in its ability to efficiently retrieve data from those tables and in particular where there are multiple tables and the relationships between those tables involved in the query.

Common database system

• Three major database management systems in wide use
  • Postgres - Open source, enterprise-scale, very tweakable
  • Oracle - Large, commercial, enterprise-scale, very tweakable
  • MySql - Simpler but very fast and scalable - commercial open source
  • SqlServer - Very nice - from Microsoft (also Access)
• Many other smaller projects, free and open source
  • HSQL, SQLite, …

Database Model

A database model or database schema is the structure or format of a database, described in a formal language supported by the database management system.

Make a Simple Database

Start Simple - A Single Table

CREATE TABLE Users(
	id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
	name VARCHAR(128),
	email VARCHAR(128)
);

SQL: Insert

The insert statement inserts a row into a table

INSERT INTO Users(name, email) Values('Kristin', 'kf@umich.edu')

SQL: Delete

Deletes a row in a table based on selection criteria

DELETE From Users Where email = 'ted@umich.edu'

SQL: Update

Allows the updating of a field with a where clause

UPDATE Users SET name = 'Charles' Where email = 'csev@umich.edu'

Retrieving Records: Select

The select statement retrieves a group of records - you can either retrieve all the records or a subset of the records with a WHERE clause

SELECT * FROM Users
SELECT * FROM Users WHERE email='csev@umich.edu'

Sorting with ORDER BY

Add an ORDER BY clause to SELECT statements to get the results sorted in ascending or descending order

SELECT * FROM Users ORDER BY email
SELECT * FROM Users ORDER BY name DESC

Simple Django Models

Object Relational Mapping (ORM)

• Allows us to map tables to objects and columns
• We use those objects to store and retrieve data from the database
• Improved portability across database dialects (SQLite, MySQL, Postgres, Oracle)

Benefits

• Can write only Python code (i.e. no explicit SQL)
• Can get database portability
• Migrations both create and evolve our database schema
• A sweet administrator interface
• Automatic form generation and validation

Defining a table

#SQL:

CREATE TABLE Users(
	name VARCHAR(128),
	email VARCHAR(128)
);

#models.py:

from django.db import models

class User(models.Model):
	name = models.CharField(max_length=128)
	email = models.CharField(max_length=128)

Creating the table

$python3 manage.py makemigrations
...
$python3 manage.py migrate
...

Inserting a record

$python3 manage.py shell
>>> from usermodel.models import User
>>> u = User(name='Kristen', email='kf@umich.edu'
>>> u.save()
>>> print(u.id)
1
>>> print(u.email)
kf@umich.edu
>>>

CRUD in the ORM

u = User(name='Sally', email='a2@umich.edu')
u.save()  #Insert

User.objects.values()   #Select
User.objects.filter(email='csev@umich.edu').values()   #Where

User.objects.filter(email='ted@umich.edu').delete()   #Delete
User.objects.values()

User.objects.filter(email='csev@umich.edu').update(name='Charles')   #Update
User.objects.values()

User.objects.values().order_by('email')
User.objects.values().order_by('-name')

Migrations: From Model to Database

• The makemigrations command reads all the models.py files in all the applications and creates / evolves the migration files
• Guided by the applications listed in settings.py
• Migrations are portable across databases
• The migrate command reads all the migrations folders in the application folders and creates / evolves the tables in database