Databases and SQL#

Databases and web applications#

Technically, a web application can use any method of storing persistent data that you would normally use (saving to files, etc)
However, standard database servers offer a wide range of useful functionality and are also designed for efficiency, reliability and (in particular) scalability. They are also well supported by web and database hosting services.
Using a full database server on a desktop or client app is not usually a good option because the end user probably would not want to install one just to run your app. But when writing a web app that runs on its own server or server farm, this is no longer an issue.

Relational Database#

CarReg	Make	Model	Colour	Year
AB02ERT	Peugeot	206	Silver	2002
AG51DRT	Ford	Focus	Green	2001

A collection of tables similar to the one above

CarReg	Make	Model	Colour	Year
AB02ERT	Peugeot	206	Silver	2002
AG51DRT	Ford	Focus	Green	2001

Records or Rows

CarReg	Make	Model	Colour	Year
AB02ERT	Peugeot	206	Silver	2002
AG51DRT	Ford	Focus	Green	2001

Fields or Columns

The Primary Key is a value that is different for every value in the database. In this case, the Car Registration is suitable because no two cars can have the same registration. If the real entities do not have any applicable value to be a primary key, we can make one up. In a large database it might be better to use a numeric type is a primary key, because they are more efficiently stored.

Other terminology#

Report – a human-readable document generated from data in a database.
SQL – the standard computer language used to communicate with the DBMS.
Index – an extra set of data stored in the database to speed up searches on a field.
Entity – a real life object about which data is stored.
Query – a question asked to a database.

An Example Data Dictionary#

Attribute	Data Type	Field Size	Required?	Format
Surname	Text	25	Yes
First Name	Text	15	No
Title	Text	6	Yes	Mr, Ms, Mrs, Miss, Dr, Rev
Street	Text	20	Yes
Town	Text	20	Yes
Post Code	Text	8	No	LLNN NNLL
Tel. No.	Text	15	No	(STD Code) - number

Relationship#

A relationship is a link or association between entities
Represented by matching values in multiple tables

One-to-one:
- One Husband has One Wife
- One Wife has One Husband
One-to-many
- One Mother has Many Children
- One Child has One Mother
Many-to-many
- One Student studies Many Courses
- One Course is studied by Many Students

Entity-Relationship Diagrams#

Specify both degree and name of relationship

One-to-one

One-to-many

Many-to-many

The name of a relationship helps to establish its degree

Many-to-many relationship

Representing a One-to-one relationship#

Store the primary key for each table in the other
When stored in this way, value is called a foreign key

PersonID	Name	DogID
293	Rob James	012
294	Andrea Wing	017
295	Sid Sackboy	048

DogID	Name
012	Rover
017	Fido
048	Amber

Querying a one-to-one relationship#

What is the name of Rob James’ dog?
- Search Person table for Rob James
- Store DogID value from that person record
- Look up this DogID in dog table
- Collect Name value from that dog record
Who owns Rover?
- Look up Rover in dog table
- Store DogID of Rover
- Search person table for this DogID
- Collect Name value from the person record

Other representations#

While it would be OK to add a PersonID field to the Dog table,to easily find who owns a dog, indexing the Person table byDogID has the same effect and removes risk of desyncing
We could also store the name of the person’s dog in the same table as the name of the person: whether or not this is a good idea depends on how many of the people in the database have dogs and what purposes the table is used for

Representing a One-to many relationship#

Similar to a one-to-one relationship, but foreign ID values

PatientID	Name	WardID
293	Peter Robb	012
294	Jenny Morel	014
295	Eric Blair	012

WardID	Name
012	General
013	Triage
014	Presurgery

Querying a one-to-many relationship#

Which ward is Jenny Morel in?
- Search Patient table for Jenny Morel
- Store WardID value from that patient record
- Look up this WardID in ward table
- Collect Name value from that ward record
Who is in the general ward?
- Look up General in ward table
- Store WardID of General ward
- Search patient table for this WardID
- Collect multiple name values from the patient records

Other representations#

We now cannot add a PatientID field to the Ward table since a ward may hold several patients
We should not store the name of a patient’s ward in their Patient record since an identical ward name (and any other data about the ward) would be stored once for each patient, wasting space in the database and risking desync

Many to Many relationships#

Representing a many-to-many relationship without wasting database space is difficult
A new table (link table or link entity) must be created to remove the many-to-many relationship
Each record in the link table represents a connection between two entries in the connected tables

Removing many-to-many#

One patient has many illnesses
One illness can affect many patients

One patient has many presentations
Each presentation is for one patient
One illness has many presentations
Each presentation is for one illness

Representing a normalized many-to-many relationship#

PatientID	Name
293	Peter Robb
294	Jenny Morel
295	Eric Blair

IllnessID	Name
201	Influenza
202	Tuberculosis
203	Hepatitis C

PresentationID	PatientID	IllnessID
1	293	201
2	294	202
3	294	201
4	295	203

Querying a normalized many-to-many relationship#

What illnesses is Jenny Morel presenting?
- Search patient table for Jenny Morel
- Store her PatientID
- Search Presentation table for this PatientID
- Store a list of all matching IllnessIDs
- Look each of these up in Illness table
- Collect the name of each

Structured Query Language (SQL)#

A standard language used to extract data from a database, used by almost all modern DBMSs
Key commands for queries:
- SELECT
- FROM
- WHERE
- ORDER BY

To create a table

1
CREATE TABLE <NAME> <DATA DICTIONARY>

To insert data

1
INSERT INTO <TABLENAME> <FIELDS>
2
VALUES <CORRESPONDING VALUES>

Create Table#

1
create table customer(
2
    customerID int not null primary key,
3
    firstName varchar(20),
4
    lastName varchar(20),
5
    dateOfBirth date);

1
create table car(
2
    carReg char(8) not null primary key,
3
    make varchar(20),
4
    model varchar(20),
5
    yearOfReg smallint unsigned);

MySQL types#

Name	Type
TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT	8, 16, 24, 32, and 64 bit int (like Java BYTE, SHORT, …, INT, LONG)
DECIMAL(x,y)	Fixed point value with x significant digits and y after the decimal point
FLOAT, DOUBLE	32 and 64 bit floating point (like Java FLOAT and DOUBLE)
DATE, DATETIME, TIME	Standard values for date and time
CHAR(x)	String up to x characters, allocating x characters of space. Use for known lengths or changing strings
VARCHAR(x)	String up to x characters, dynamically allocated. Use for variable lengths, ideally that do not change or do not change often

MySQL type modifiers#

Name	Type
UNSIGNED	Makes an int value unsigned in the same way as Java
AUTO_INCREMENT	Default to one larger than the highest current value in the table
NOT NULL	Column must be filled in. Needed for indexes to be generated
UNIQUE	Column value must not be duplicated in table
PRIMARY KEY	Column value is primary key; implies UNIQUE

INSERT INTO#

1
INSERT INTO car
2
  (carReg, make, model, yearOfReg)
3
Values
4
  ("MF59 YXS","Hyundai","i20",2009);

Listing the fields in the INSERT INTO statement is not mandatory, but avoids errors if other columns are later added to the table.

SELECT…FROM…WHERE#

1
SELECT CarReg, Make
2
FROM Car
3
WHERE YearOfReg=2009;

Creating a reference#

1
Create table person (
2
    personID int unsigned not null primary key,
3
    name varchar(40),
4
    dogID int unsigned not null);
5
Create table dog (
6
    dogID int unsigned not null primary key,
7
    name varchar(40));

Using a reference#

1
Select person.name, dog.name
2
    From person inner join dog
3
        On person.dogID = dog.dogID;

Using a many-to-many reference#

1
Select patient.name, illness.name
2
From presentation
3
inner join patient on presentation.patientID = patient.patientID
4
inner join illness on presentation.illnessID = illness.illnessID;

Using MySQL from Python#

import the MySQL connector

1
import sql.connector

Connect to the database:

1
cnx = mysql.connector.connect(user='dbuser', password='rubbish', host='127.0.0.1', database='dogs')

Note that the database must already exist (a database is not the same as a table)
Because the password must appear in the Python script, it is important that scripts are kept secure

Sending queries#

Create a cursor (an object used for a series of database interactions)

1
cursor = cnx.cursor()

Send a query via the cursor

1
cursor.execute(“select dogid, name from dog”)

The cursor then becomes a Python iterator which you can loop through to read the results

1
for (dogid, name) in cursor:
2
    print (dogid, name)

Sending definitions/commands#

Send the command via the cursor

1
cursor.execute(“INSERT INTO CAR (carReg, make, model, YearOfReg) VALUES (‘AB2 3CD’, ’Vauxhall’, ’Labrador’,2005)”)

Commit changes to the database

1
cnx.commit()

Close the database before your program ends

1
cnx.close()

Security#

Be very careful about using strings input from the user in SQL commands
This is a very easy route for hackers to damage the database.
SQL injection is among the most common attacks on sites!
For example:

1
for row in c.execute("SELECT ID FROM person WHERE name = " + request.form["name"] + ";"):
2
  print row

What happens if a hacker fakes the form, and sends the string “x”;

select password as ID from users as the name form field?

Security: query templating#

To avoid this, use query templates instead of raw string modification to build query strings

1
query = "Select id from person where name=%s;"
2
c.execute(query,("Robert",))

%s in the query string marks where a value is to be inserted. More than one can be used.
The execute statement fills in the values for insertion. They will only be allowed to be inserted as data values, not commands
Note:
- %s is the only marker supported. It is used even if the value is a number (unlike C or pyformat)
- If there is only one value to be filled in, the extra comma is mandatory to force Python to see the value as a tuple with one element

音乐

音乐

Databases and SQL#

Databases and web applications#

Relational Database#

Other terminology#

An Example Data Dictionary#

Relationship#

Entity-Relationship Diagrams#

Representing a One-to-one relationship#

Querying a one-to-one relationship#

Other representations#

Representing a One-to many relationship#

Querying a one-to-many relationship#

Other representations#

Many to Many relationships#

Removing many-to-many#

Representing a normalized many-to-many relationship#

Querying a normalized many-to-many relationship#

Structured Query Language (SQL)#

Create Table#

MySQL types#

MySQL type modifiers#

INSERT INTO#

SELECT…FROM…WHERE#

Creating a reference#

Using a reference#

Using a many-to-many reference#

Using MySQL from Python#

Sending queries#

Sending definitions/commands#

Security#

Security: query templating#

支持与分享

评论区

音乐

目录

音乐

音乐

Web Application Development - SQL

Databases and SQL#

Databases and web applications#

Relational Database#

Other terminology#

An Example Data Dictionary#

Relationship#

Entity-Relationship Diagrams#

Representing a One-to-one relationship#

Querying a one-to-one relationship#

Other representations#

Representing a One-to many relationship#

Querying a one-to-many relationship#

Other representations#

Many to Many relationships#

Removing many-to-many#

Representing a normalized many-to-many relationship#

Querying a normalized many-to-many relationship#

Structured Query Language (SQL)#

Create Table#

MySQL types#

MySQL type modifiers#

INSERT INTO#

SELECT…FROM…WHERE#

Creating a reference#

Using a reference#

Using a many-to-many reference#

Using MySQL from Python#

Sending queries#

Sending definitions/commands#

Security#

Security: query templating#

支持与分享

评论区

音乐

目录