Database
Usually saved in form of table
column: field that you need to keep track on
row: from where you are going to go
SQL
database language that interact with relational database management systems
database organize data into tables (with rows and column)
Management Systems: MySQL, PostgreSQL, SQLite
SQLite → all data store in single file ← easier to handle, syntax also applicable in other management system
PostgreSQL /MySQL→ heavier server, with separate database (if one go down, the other will not go down)
each data has a type, each management system support different types:
Text: string
Numeric: data that represent by number, Boolean value, date, numeric type of data
Integer: positive and negative integers
Real: float
Blob: binary large object, the 0 and 1 → audio / pictures/ video
MySQL:
Create a table in SQLite
NOT NULL: not empty
id: each of them are unique
autoincrement: will increase every time
unique: no repetition
check: make sure the value obey certain conditions (eg is between 0 to 10)
Add data to table — Insert
no need to add in id as it will automatically added by system with constraints autoincrement
Select data from table
Demonstration
created the table in the above example
if you input .tables → will show you name of existing table
How to present sqlite data neat and tidy:
.mode columns
.headers yes
SELECT * FROM flights;
Other conditions still works: duration > 500
You could use AND and OR syntax in SQL too while dealing with condition
IN( sth , sth)
LIKE “% keywords %”
Function that could be used in the condition:
Update the data
Delete the data (the row)
Other command that could be used
Limit: limit number of result shown
ordered by: how the result is ordered by “destination”
Group by: how the result is group by
having: similar usage with where but put into group by
Django can help to write the command
What if we have more than 1 tables
Foreign keys
But it is so repetitive, so we could put it into another table
Store the origin and destination as foreign keys
Then look up the id from table airports
Similarly, there are table for passengers
Limitation: only 1 flight_id associated with one passenger
many-to-one relationship: one flight can be related to many passengers
one-to-one relationship: one flight can be related to only one passenger
many-to-many relationship: many passengers related to many flights
like flight id 1 could link with passenger 2,3,4,5,6,7 → one-to-many relationship
many passenger could be related to many flights?????
- flight could have many passenger while passenger could have many flight
Take out passengers to people table
Then create a join table to join the information of this two table together:
Then person id could have more flight and flight id could have more person
JOIN query to join multiple tables together
SELECT first origin destination FROM flight JOIN passengers ON passengers.flight_id = flights.id;
Other JOINS method
How to make queries more efficient? — Index on table
Create index — make query much more efficient
As you will look of the last name more frequently, you would like to create index base on the last name — only by creating it would be enough → system will deal with it
Problem:
SQL injection
But what if the user in put weird stuff?
— — ← comment in sql ← sql will then ignore the AND password= “”
How to solve?
1. add escape character (add back slashes to let sql knows that they are data but not command) escape character: \n, \
2. Add abstraction layer on top of SQL
no need to use SQL command at all ← so it would not be affected
Race conditions
1 thing happening and another thing happen in the same time
Data might not be updated when two persons check the same thing (eg the number of likes in FB)
Place a lock on database, other can view it after I finish
Django model
Model: way of creating a Python class that is going to represent data that I want to store in database
Under every applications created, there is a file called models.py
1 model for each of the main table
But no database existed yet
How to tell Django to include the model to database? Migration
- Tell Django the command of how to manipulate the database (Above)
- Tell system to execute the command to database
Command to execute the migration (command execute to database):
1. python manage.py makemigrations
the order will be converted to the version that database can understand
Don’t even need to order for id !!!!!
The file was created to determine how the order should be execute on database
How to really apply the command?
2. use command: python manage.py migrate
Then the database was created
Shell — run python command
command: python manage.py shell
Then we can use Python command on the terminal to save the information
make up a variable f to save the data to the table, and then save it
f.save() ← save it
Flight.objects.all() ← select all data
Give back a set of result without the data
string representation of any flight = string that gives its id and say origin to destination
If you only want to have the first result:
Then you could query information easier
Also for deletion:
Add table Airport
on_delete=models.CASCADE ← when you delete the city in airport table, it will be deleted in the origin table
on_delete=models.PROTECT ← even you delete city in airport table, it will NOT be deleted in the origin table
related_name:
when you typed Flight.origin → you will find the origin of the flight
what if I have an airport and I want to find all the flight with origin??
→ related_name= “”
Changed only applied on Python code now
2-step:
convert to database language (makemigrations)
apply to the database (migrate)
Need to create them everytime you open the shell?????
How to let django to do this for me?
Create the url to flights first
Import tables in models
Input the python command in the variables passing to html
layout.html
index.html
Need to specify how to present the value
Or else
How to query only one object from the table?
.objects.filter(column= “data”) : return the sets that fulfill the condition
.objects.filter(condition).first(): return the first data that fulfill the condition
.objects.get(column= “data”): return ONE data that fulfill condition
— return error if there are more than one that fulfill condition
Adding the data using shell
After adding the information:
But could we simply add them using the interface instead of using shell all the time?