Lecture 17.

Database, MySQL,
SQL
 RECAP: HTTP Server (Python 3.x)

• Python comes with a mini-webserver in the

http.server module
• To set it up:
– 1. Create an HTML directory. E.g call it
myserver
– 2. Make a cgi-bin folder under myserver.
– Put your cgi scripts (hello.py) in cgi-bin.
– 5. Run http.ser, as python –m http.server
8000 --cgi
– 6. Point your browser at
http://localhost:8000/cgi-bin/home.py
• Replace Print “hello” to print (“Hello”) 2
 Databases
• A database is a structured collection of records or data that is
stored in a computer system.

• Examples of databases:
– Telephone book white pages
– T.V. Guide
– Airline reservation system
– Motor vehicle registration records
– Papers in your filing cabinet
– Files on your computer hard drive
 Relational Databases

• A relational database describes the relationships

among different kinds of data
– E.g student takes courses, courses are offered in semester
– Allows the software to answer queries about them

• A relational database allows you to:

– … easily find specific information.
– … sort based on any field and generate reports that contain
only certain fields from each record.

• Relational databases stores entities

– Entities are similar to Objects
 Entities

• Anything that can de identified by a fixed number of

its characteristics (attributes aka fields)
– E.g a student entity can have name, id, grade attributes
• Attributes have names, values, and type
– E.g ID attribute can have the value ATR/1234/05
• Type – form of data that can be stored – String, Integer, Boolean

• An entity is stored in a table

– Name of the entity is the name of the table
– Each attribute is assigned a column

• Each row defines one entity object

– Primary key is the column that uniquely identifies each row
 Relational Database

number name department salary location

23603 Jones 413 1100 New Jersey
24568 Kerwin 413 2000 New Jersey
Row 34589 Larson 642 1800 Los Angeles
35761 Myers 611 1400 Orlando
47132 Neumann 413 9000 New Jersey
78321 Stephens 611 8500 Orlando

Primary key Column

Fig. 22.1 Relational database structure of an Employee table.

 Relational Database

department location
413 New Jersey
611 Orlando
642 Los Angeles

Fig. 22.2 Table formed by selecting department and location data from the Employee table.
 Relational Database Overview: Books
Database – Titles Table Structure

Foreign key column:

Stores the Primary key of an entity in another table to indicate relationships
(Which publisher published this book)
 Relational Database Overview: Books
Database – Titles Table Data
isbn title edition- publisherID copy- price
Number right
0130923613 Python How to Program 1 1 2002 $69.95
0130622214 C# How to Program 1 1 2002 $69.95
0130341517 Java How to Program 4 1 2002 $69.95
0130649341 The Complete Java Training 4 2 2002 $109.95
Course
0130895601 Advanced Java 2 Platform 1 1 2002 $69.95
How to Program
0130308978 Internet and World Wide 2 1 2002 $69.95
Web How to Program
0130293636 Visual Basic .NET How to 2 1 2002 $69.95
Program
0130895636 The Complete C++ Training 3 2 2001 $109.95
Course
0130895512 The Complete e-Business & 1 2 2001 $109.95
e-Commerce Programming
Training Course
Fig. 22.10 Portion of the data from the Titles table of Books.mdb.
Relational Database Overview: Books.mdb
Database – Publishers Table Structure

Field Description
publisherID The publisher’s ID number in the database. This
auto-incremented Integer is the table’s primary
key.
publisherName The name of the publisher (a String).
Fig. 22.5 Publishers table from Books.mdb.
Relational Database Overview: Books.mdb
Database – Publishers Table Data
 Relational Database Overview: Books
Database – Authors Table Structure

Field Description
authorID Author’s ID number in the database. In the Books.mdb
database, this Integer column is defined as auto-
increment. For each new row inserted in this table, the
database increments the authorID value, ensuring that
each row has a unique authorID. This column represents
the table’s primary key.
firstName Author’s first name (a String).
lastName Author’s last name (a String).
Fig. 22.3 Authors table from Books.mdb.
Relational Database Overview: Books
Database – Authors Table Data

authorID firstName lastName

1 Harvey Deitel
2 Paul Deitel
3 Tem Nieto
4 Kate Steinbuhler
5 Sean Santry
6 Ted Lin
7 Praveen Sadhu
8 David McPhie
9 Cheryl Yaeger
10 Marina Zlatkina
11 Ben Wiedermann
12 Jonathan Liperi
Fig. 22.4 Data from the Authors table of
Books.mdb.
 Relationships

• Author and Title have no relationships currently.

– Database cannot answer queries such as “Who wrote C#
How to Program?”
– Or “What book titles did Harvey write?”

• We need to store this relationship (which author

participated in writing which book)
– One Book can be written by many Authors
– One Author can write many books
– We call this kind of relationship Many-to-Many

• When a relationship is many-to-many we create a

separate table which stores the ids of each table
 Relational Database Overview: Books
Database – AuthorsISBN Table Structure

Field Description
authorID The author’s ID number, which allows the database
to associate each book with a specific author. The
integer ID number in this column must also appear
in the Authors table.
isbn The ISBN number for a book (a String).
Fig. 22.7 AuthorISBN table from Books.mdb.
Relational Database Overview: Books
Database – AuthorsISBN Table Data
 DBMS

• Database management system (DBMS)

– Store and organize data consistent with database’s format
– E.g MySQL, Microsoft SQL server, Oracle, DB2

• We will use a DBMS called MySQL

– It is free
– Big companies including facebook use it

• Structured Query Language (SQL)

– Language used to manipulate databases
 MySQL and SQL Commands

• MySQL database installation:

– https://www.youtube.com/watch?v=fwQyZz6cNGU
– Use the default (press next) except in the following screens
• 1. Check “Include BIN directory in windows PATH” – default is
not selected
• 2. New root Password and confirm password type your
PASSWORD
– Common to make the password root
• To give SQL commands to MySQL
– Start Command Prompt:
C:Users\Cylic> mysql -u root –p
<type the password you provided during installation>

Mysql> <Write your SQL COMMANDS HERE>

 SQL (Structured Query Language)

• Here are the most widely used SQL commands.

 CREATE DATABASE

• Creating Database
– SQL syntax
• create database <database-name>;
– Example
• create database Books;

• Selecting Database
– SQL syntax
• use <database-name>;
– Example
• use Books;
 CREATE TABLE: Column Types

• Integer: tinyint, int(eger), bigint

• Floating point: float, double, real

• Decimal: decimal(m,d) (for $)

• Date: date, datetime, timestamp,

time, year

• String: char(N), varchar(N), text,

enum
– All strings must be enclosed in single quotes ' '
21
 CREATE TABLE

• Create table SQL syntax:

CREATE TABLE <Table_Name> (
<column_name_1> <type_1>,
<column_name_2> <type_2>,
<column_name_3> <type_3>,
....
);

• Create author table:

CREATE TABLE Authors (
authorId integer unsigned auto_increment
primary key,
firstName nvarchar(100),
lastName nvarchar(100)
);
 CREATE TABLE

• Create titles table:

CREATE TABLE Titles (
isbn nvarchar(20) primary key,
title nvarchar(100),
editionNumber nvarchar(100),
copyright integer,
description nvarchar(100),
publisherId integer,
price decimal(6,2)
);
• Create AuthorsISBN table:
CREATE TABLE AuthorsISBN (
isbn nvarchar(20),
authorId integer
);
 INSERT

• Insertion SQL Syntax:

– INSERT table SET
col1=value1,col2=value2, ...
• Example: Authors
– INSERT Authors SET firstName='Harvey',
lastName='Deitel';
– INSERT Authors SET firstName='Paul',
lastName='Deitel';
– INSERT Authors SET firstName='Tem',
lastName='Neito';
– INSERT Titles SET firstName='Kate',
lastName='Stienbuhler';
 INSERT

• Insertion SQL Syntax:

– INSERT table SET
col1=value1,col2=value2, ...
• Example: Titles
– INSERT Titles SET isbn = '0130923613',
title='Python how to
program',editionNumber='1',
copyright=2002, description='good book',
publisherId = 1,price = 45.67;
– INSERT Titles SET isbn = '0130923214',
title='C# how to
program',editionNumber='1',
copyright=2002, description='good book',
publisherId = 1,price = 45.67;
 INSERT

• Insertion SQL Syntax:

– INSERT table SET
col1=value1,col2=value2, ...

• Example: AuthorsISBN
– INSERT AuthorsISBN SET isbn =
'0130923613', authorId=1;
– INSERT AuthorsISBN SET isbn =
'0130923214', authorId=1;
 Basic SELECT SQL Query

• SELECT * FROM tableName

– (*) means all columns

• Examples
– E.g SELECT * FROM Authors

– SELECT authorID, lastName FROM Authors

authorID lastName authorID lastName
1 Deitel 7 Sadhu
2 Deitel 8 McPhie
3 Nieto 9 Yaeger
4 Steinbuhler 10 Zlatkina
5 Santry 11 Wiedermann
6 Lin 12 Liperi
Fig. 22.13 authorID and lastName from the Authors table.
 WHERE Clause

• Specify selection criteria for query

– SELECT columnName1, columnName2, … FROM tableName
WHERE criteria
• SELECT title, editionNumber, copyright FROM
Titles WHERE copyright > 1999
 WHERE Clause

• Specify selection criteria for query

– LIKE
• Pattern matching
– Asterisk ( * )
• SELECT authorID, firstName, lastName
FROM Authors WHERE lastName LIKE ‘D*’

authorID firstName lastName

1 Harvey Deitel
2 Paul Deitel
Fig. 22.15 Authors from the Authors table whose last names start with D.
 Merging Data from Multiple Tables: INNER
JOIN
• Normalize databases
– Ensure database does not store data redundantly
– SELECT columnName1, columnName2, … FROM table1 INNER
JOIN table2 ON table1, columnName = table2.columnName

• Example:
– SELECT Authors.firstName, Authors.lastName,
Titles.isbn
FROM Authors, Titles, AuthorsISBN
Where AuthorsISBN.authorId = Authors.authorId
and AuthorsISBN.isbn = Titles.isbn;
Merging Data from Multiple Tables: INNER
JOIN
firstName lastName isbn firstName lastName isbn
Harvey Deitel 0130895601 Harvey Deitel 0130856118
Harvey Deitel 0130284181 Harvey Deitel 0130161438
Harvey Deitel 0130284173 Harvey Deitel 013028419x
Harvey Deitel 0130829293 Harvey Deitel 0139163050
Harvey Deitel 0134569555 Harvey Deitel 0135289106
Harvey Deitel 0130829277 Harvey Deitel 0130895717
Harvey Deitel 0130852473 Harvey Deitel 0132261197
Harvey Deitel 0138993947 Harvey Deitel 0130895725
Harvey Deitel 0130125075 Paul Deitel 0130895601
Paul Deitel 0130284181 Paul Deitel 0135289106
Paul Deitel 0130284173 Paul Deitel 0130895717
Paul Deitel 0130829293 Paul Deitel 0132261197
Paul Deitel 0134569555 Paul Deitel 0130895725
Paul Deitel 0130829277 Tem Nieto 0130284181
Paul Deitel 0130852473 Tem Nieto 0130284173
Paul Deitel 0138993947 Tem Nieto 0130829293
Paul Deitel 0130125075 Tem Nieto 0134569555
Paul Deitel 0130856118 Tem Nieto 0130856118
Paul Deitel 0130161438 Tem Nieto 0130161438
Paul Deitel 013028419x Tem Nieto 013028419x
Paul Deitel 0139163050 Sean Santry 0130895601
Fig. 22.21 Authors from table Authors and ISBN numbers of the authors’
books, sorted in ascending order by lastName and firstName.
 UPDATE Statement

• Modifies data in a table

– UPDATE tableName SET columnName1 = value1,
columnName2 = value2, …, columnNameN = valueN
WHERE criteria

• Example
– UPDATE Authors SET firstName='Timothy'
WHERE lastName='Neito';
 UPDATE Statement

• Use a SELECT command to see the results

– SELECT * FROM Authors
 DELETE Statement

• Removes data from a table

– DELETE FROM tableName WHERE criteria

• E.g
– DELETE FROM Authors WHERE firstName = 'Sue';
 DELETE Statement

• Use a SELECT command to see the results

– SELECT * FROM Authors
– Timothy Nieto has been deleted
 Python + MySQL

• There are currently a few options for using Python

3 with mysql:
– https://pypi.python.org/pypi/mysql-connector-python
• Officially supported by Oracle
• Pure python
• A little slow
– https://pypi.python.org/pypi/pymysql
• Pure python
• Faster than mysql-connector
– https://pypi.python.org/pypi/mysqlclient
• Django's recommended library.
• Friendly fork of the Famous MySQLdb
 Python + MySQL

• Sample Python code to access data base using pymysql

– Install pymysql writing easy_install pip followed by pip install
pymysql on CMD

import pymysql
conn = pymysql.connect(host='127.0.0.1’,
user='root', passwd='root', db=’Books')
cur = conn.cursor()
cur.execute("SELECT firstName,lastName FROM
Authors")
for r in cur:
print(r)
cur.close()
conn.close()