Django Models, Migrations and SQL

Question 1

SQL

Answer 1

Sequential Query Language designed for interating with relational database management systems, by querying data as a row or a table, creating data table, settings field, matching elements by automatically create relational tables, somes of SQL:
- MySQL, Postgres: satisfy demands of separating database server and application server.
- SQLite: storing all data into one files inside the application server.
Database types: SQL classifies data into several types for efficiency storing, classification depends on each SQL chosen.
SQLite have five simple data types:
Integer, Real: normal numeric data
Numeric: special numeric data (date and time, lists)
Text: string
BLOB: binary data (mp3, mp4, zip, exe, …)

Question 2

Relational Database Management Systems

Answer 2

Types of database store data as tables, with field as columns and element as rows. Relational DBMS has
advantage of relating different database with some special table, storing primary key (id) of each rows from 2 separated data tables to serve as relational tables, matching one element from table to another element from the other table without copying the actual data, saving storage.

Question 3

SQL Tables CREATE, INSERT

Answer 3

Create SQL tables command:
CREATE TABLE table_name (
columns_name columns_data_types constrain clue,
id INTEGER PRIMARY KEY AUTOINCREMENT,
origin TEXT NOT NULL,
destination TEXT NOT NULL,
duration INTEGER NOT NULL
)
Types of constraints:
PRIMARY KEY: identify the special columns contains id used for uniquely refers to particular rows, must having in every SQL, each rows having unique ids and handled by the language with AUTOINCREMENT clue
CHECK: require cell-value obeying some conditions
DEFAULT: default value
NOT NULL: not accept rows not having values in these columns
UNIQUE: rows must have unique values in this columns
Insert rows into SQL tables command:
INSERT INTO table_name
(origin, destination, duration)
VALUES (“New York”, “London”, 415)
Note: Ids will be auto incremented by the AUTOINCREMENT clue

Question 4

SQL SELECT queries

Answer 4

SELECT rows from table command:
SELECT chosen_columns FROM table_name WHERE rows_constrains_boolean_expression

Example:
SELECT * from flights / * means all columns /
SELECT * from flights WHERE id=3
SELECT origin, destination from flights WHERE origin=”New York”
SELECT * from flights WHERE duration > 500
SELECT * from flights WHERE duration > 500 AND(OR) destination=”PARIS”
SELECT origin, destination from flights WHERE origin IN (“New York”, “London”)
SELECT * from flights WHERE origin LIKE “%a%” (Choose if matched certain regular expression)

SQLite configures to display queries result nicely:
.mode columns
.header yes

Others queries commands: AVERAGE COUNT MIN MAX SUM,…

Question 5

SQL UPDATE, DELETE

Answer 5

UPDATE particular rows in tables commands:
UPDATE table_name
SET some_columns_name = values
SET duration = 430
WHERE destination = “London” AND origin = “LIMA”
( Some boolean expression choosing rows)
DELETE particulat rows from tables commands:
DELETE from table_name WHERE boolean_exp

Clause configure for the results of queries:
LIMIT number: Maximum number of rows returned
ORDER BY column_name: Returned result sorted by particular columns
GROUP BY column_name (HAVING number): Returned result grouped by same value in particular columns
only groups HAVING more rows than number

Question 6

SQL Foreign Keys

Answer 6

Example of fully-fledge passenger’s flight table columns:
FirstName-LastName-Origin-Destination-ori_code-des_code-duration
Complex table, duplicated or redundant information.
Ideas of separate out multiple tables related by foreign keys.
Element Tables:
.airport: id(key)-Name-Code
.passenger: id(key)-FirstName-LastName
Relation Tables:
.flights: id(key)-origin_id-destination_id-duration
related to .airport by foreign key airport_id = airport.id
.book: id(key)-passenger_id-flight_id
related to .passenger and .flight by foreign key passenger_id = passenger.id flight_id = flight.id

SQL relations:
One-to-many: an airport can associated to many flight
Many-to-one: on the contrary a flight cannot be associated with multiple origin / destination
Many-to-many: a passenger can book multiple flights, likewise a flight can contain multiple passengers.

Question 7

SQL JOIN queries

Answer 7

Command use for queries complete rows obtained from linking multiple tables’ information. Example:
SELECT FirstName, LastName, origin_id, destination_id
FROM flight JOIN passenger
ON passenger.flight_id = flights.id - > .temp table
before that make two JOIN queries each link origin_id and destination_id to airport name in flight table.

Question 8

SQL Race Condition Handling

Answer 8

Unexpected results happened when multiple queries or updates conducted simutaneously. Solution: lock
when query happened and unlock SQL database after finish that query, let’s the next one query access to database.

Question 9

Django Models

Answer 9

Django class represent one SQL table with one Instance. Example in models.py:

from django import models

class Airport(models.Model):
   city = models.CharField(max_length=64)
   code = models.CharField(max_length=3)
   def \_\_str\_\_(self):  / return string representation / 
      return f"{self.city} ({self.code})"   

class Flight(models.Model):
   origin = models.ForeignKey(Airport, on_delete=..., related_name="departures")
   destination = models.ForeignKey(Airport, on_delete=..., related_name="arrivals")
   duration = models.IntegerField()   
   def \_\_str\_\_(self):
      return f"{self.id}: from {self.origin} to {self.destination}"

class Passenger(models.Model):
   first = models.CharField(max_length=64)
   last = models.CharField(max_length=64)
   flights = models.ManyToManyField(Flight, related_name="passengers")

Each class variable (ex: city, code) represent column field in SQL table.
ForeignKey is special field types referencing to other table's row for accessing the data without replication.
"on_delete" parameter sets handling method when the other table's row that this table's row access is deleted. Example:
models.CASCADE: also delete columns in this table
models.PROTECT: deny deleting the referenced element 
"related_name" configure the reverse referencing relataion.

QuerySet vs List: Some Django models queries returned results of QuerySet type, means instances contains the queries’ result, for getting the data must use QuerySet method such as first(), get(), all(), …

Question 10

Django Admin Apps

Answer 10

Create superuser command:
python manage.py createsuperuser
Inside admin.py of Django apps (not Django project):

from .models import Airport, Flight

/ Customize display for admin apps /
class FlightAdmin(admin.ModelAdmin):
   list_display = ("id", "origin", "destination", "duration")
class PassengerAdmin(admin.ModelAdmin):
   filter_horizontal = ("flights",)

admin. site.register(Airport)
admin. site.register(Flight, FlightAdmin)
admin. site.register(Passenger, PassengerAdmin)

Question 11

Django SQL queries function

Answer 11

Each Django Models Instances equivalents to SQL tables, each ForeignKeys in one column create additional tables saving mapping between models.
Django SQL queries is models.Model 's class methods acts on individual instances likes SQL queries commands. 
Example in models.py has Flight, Airport, Passenger:
Get all Airport: Airport.objects.all()
Get first Flight: flight = Flights.objects.first()
Get all Passenger not on 'flight':
   Passenger.objects.exclude(flights = flight)
Get Flight with primary key: Flights.objects.get(pk = 1)