Transcript Chapter 4 – Control Structures Part 1

1
Chapter 17 – Customizing Classes
Outline
17.1
17.2
17.3
17.4
17.5
17.6
17.7
17.8
Introduction
Relational Database Model
Relational Database Overview: Books Database
Structured Query Language (SQL)
17.4.1 Basic SELECT Query
17.4.2 WHERE Clause
17.4.3 ORDER BY Clause
17.4.4 Merging Data from Multiple Tables: INNER JOIN
17.4.5 Joining Data from Tables Authors, AuthorISBN, Titles
and Publishers
17.4.6 INSERT Statement
17.4.7 UPDATE Statement
17.4.8 DELETE Statement
Python DB-API Specification
Case Study: A SimpleDictionary Class
Querying the Books Database
Reading, Inserting and Updating a Database
 2002 Prentice Hall. All rights reserved.
2
17.1 Introduction
• Database: integrated collection of data
• Database management system (DBMS): provides
mechanisms for storing and organizing data in a
manner consistent with the database’s format
• Relational databases: store data in tables and
define relationships between the tables
• Examples of popular relational database systems
includes Microsoft SQL Server, Oracle, Sybase,
DB2, Informix and MySQL
 2002 Prentice Hall. All rights reserved.
3
17.2 Relational Database Model
• Relational database model: logical representation
of data that allows relationships among data to be
considered without concern for the data’s physical
structure
• Relational database composed of tables
• Row called record
• Column called field
• Primary key is a field that contains unique data
 2002 Prentice Hall. All rights reserved.
4
17.2 Relational Database Model
Record/Row
Number
Name
Department
Salary
23603
Jones
413
1100
New Jersey
24568
Kerwin
413
2000
New Jersey
34589
Larson
642
1800
Los Angeles
35761
Myers
611
1400
Orlando
47132
Neumann
413
78321
Stephens
611
Primary key
 2002 Prentice Hall. All rights reserved.
Field/Column
9000
8500
Location
New Jersey
Orlando
5
17.2 Relational Database Model
Fig. 17.2
Department
Location
413
New Jersey
611
Orlando
642
Los Angeles
Result set formed by selecting Department and Location data from the Employee table.
 2002 Prentice Hall. All rights reserved.
17.3 Relational Database Overview: Books
Database
• Books database has four tables: Authors,
Publishers, AuthorISBN and Titles
• Authors table has three fields: author’s unique
ID number, first name and last name
• Publishers table has two fields: publisher’s
unique ID and name
• AuthorISBN table has two fields: authors’ ID
numbers and corresponding ISBN numbers
• Titles has seven fields: ISBN number, title,
edition number, copyright year, publisher’s ID
number, book price and filename of cover image
 2002 Prentice Hall. All rights reserved.
6
17.3 Relational Database Overview: Books
Database
Field
De sc rip tio n
AuthorID
Author’s ID number in the database. In the Books database, this int field
is defined as an auto-incremented field. For each new record inserted in
this table, the database increments the AuthorID value, ensuring that each
record has a unique AuthorID. This field is the table’s primary key.
FirstNam Author’s first name (a string).
e
LastName Author’s last name (a string).
Fig. 17.3 Authors ta b le from Books.
 2002 Prentice Hall. All rights reserved.
7
17.3 Relational Database Overview: Books
Database
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
13
Jeffrey
Listfield
Fig. 17.4 Da ta
from the
 2002 Prentice Hall. All rights reserved.
Authors ta b le of Books.
8
17.3 Relational Database Overview: Books
Database
Field
De sc rip tio n
PublisherID
The publisher’s ID number in the database. This auto-incremented int
field is the table’s primary-key field.
PublisherNa The name of the publisher (a string).
me
Fig. 17.5 Publishers ta b le from Books.
PublisherID PublisherName
1
Prentice Hall
2
Prentice Hall PTG
Fig. 17.6 Da ta
from the
 2002 Prentice Hall. All rights reserved.
Publishers ta b le of Books.
9
17.3 Relational Database Overview: Books
Database
Field
De sc rip tio n
AuthorID
The author’s ID number, which allows the database to associate each
book with a specific author. The integer ID number in this field must
also appear in the Authors table.
ISBN
The ISBN number for a book (a string).
Fig. 17.7 AuthorISBN ta b le from Books.
AuthorID
ISBN
AuthorID
ISBN
1
0130895725
1
0130284181
1
0132261197
1
0130895601
1
0130895717
2
0130895725
1
0135289106
2
0132261197
1
0139163050
2
0130895717
1
013028419x
2
0135289106
1
0130161438
2
0139163050
1
0130856118
2
013028419x
1
0130125075
2
0130161438
1
0138993947
2
0130856118
1
0130852473
2
0130125075
1
0130829277
2
0138993947
1
0134569555
2
0130852473
 2002 Prentice Hall. All rights reserved.
10
17.3 Relational Database Overview: Books
Database
1
0130829293
2
0130829277
1
0130284173
2
0134569555
2
0130829293
3
0130856118
2
0130284173
3
0134569555
2
0130284181
3
0130829293
2
0130895601
3
0130284173
3
013028419x
3
0130284181
3
0130161438
4
0130895601
Fig. 17.8 Da ta
from AuthorISBN ta b le in
a p ortion of the sa m p le d a ta .]
Books. [Note: This ta b le sho w s only
Field
De sc rip tio n
ISBN
ISBN number of the book (a string).
Title
EditionNumb
er
Copyright
PublisherID
Title of the book (a string).
Edition number of the book (a string).
Copyright year of the book (an int).
Publisher’s ID number (an int). This value must correspond to an ID
number in the Publishers table.
ImageFile
Price
Name of the file containing the book’s cover image (a string).
Suggested retail price of the book (a real number). [Note: The prices
shown in this database are for example purposes only.]
Fig. 17.9 Titles ta b le from Books.
 2002 Prentice Hall. All rights reserved.
11
17.3 Relational Database Overview: Books
Database
ISBN
Title
Edition Publish-Number erID
Copy- ImageFile Price
right
0130923613
Python How to
Program
1
1
2002
python.jpg
$69.95
0130622214
C# How to
Program
Java How to
Program
1
1
2002
cshtp.jpg
$69.95
4
1
2002
jhtp4.jpg
$69.95
0130649341
The Complete Java 4
Training Course
2
2002
javactc4.jpg
$109.95
0130895601
Advanced Java 2
Platform How to
Program
1
1
2002
advjhtp1.jpg
$69.95
0130308978
Internet and World 2
Wide Web How to
Program
1
2002
iw3htp2.jpg
$69.95
0130293636
Visual Basic .NET
How to Program
2
1
2002
vbnet.jpg
$69.95
0130895636
The Complete C++ 3
Training Course
2
2001
cppctc3.jpg
$109.95
0130341517
 2002 Prentice Hall. All rights reserved.
12
17.3 Relational Database Overview: Books
Database
0130895512
The Complete eBusiness & eCommerce
Programming
Training Course
1
2
2001
ebecctc.jpg
$109.95
013089561X
The Complete
2
Internet & World
Wide Web
Programming
Training Course
The Complete Perl 1
Training Course
2
2001
iw3ctc2.jpg
$109.95
2
2001
perl.jpg
$109.95
The Complete
1
XML
Programming
Training Course
C How to Program 3
2
2001
xmlctc.jpg
$109.95
1
2001
chtp3.jpg
$69.95
0130895717
C++ How to
Program
3
1
2001
cpphtp3.jpg
$69.95
013028419X
e-Business and e- 1
Commerce How to
Program
1
2001
ebechtp1.jpg $69.95
0130622265
Wireless Internet
and Mobile
Business How to
Program
1
1
2001
wireless.jpg $69.95
0130284181
Perl How to
Program
1
1
2001
perlhtp1.jpg $69.95
0130284173
XML How to
Program
1
1
2001
xmlhtp1.jpg
$69.95
0130856118
The Complete
1
Internet and World
Wide Web
Programming
Training Course
2
2000
iw3ctc1.jpg
$109.95
0130895547
0130895563
0130895725
 2002 Prentice Hall. All rights reserved.
13
17.3 Relational Database Overview: Books
Database
0130125075
Java How to
Program (Java 2)
3
1
2000
jhtp3.jpg
0130852481
The Complete Java 3
2 Training Course
2
2000
javactc3.jpg $109.95
0130323640
e-Business and eCommerce for
Managers
1
1
2000
ebecm.jpg
$69.95
0130161438
Internet and World 1
Wide Web How to
Program
1
2000
iw3htp1.jpg
$69.95
0130132497
Getting Started
with Visual C++ 6
with an
Introduction to
MFC
The Complete
Visual Basic 6
Training Course
1
1
1999
gsvc.jpg
$49.95
1
2
1999
vbctc1.jpg
$109.95
0134569555
Visual Basic 6
How to Program
1
1
1999
vbhtp1.jpg
$69.95
0132719746
Java Multimedia
Cyber Classroom
1
2
1998
javactc.jpg
$109.95
0136325890
Java How to
Program
1
1
1998
jhtp1.jpg
$69.95
0139163050
The Complete C++ 2
Training Course
2
1998
cppctc2.jpg
$109.95
0135289106
C++ How to
Program
1
1998
cpphtp2.jpg
$49.95
0130829293
 2002 Prentice Hall. All rights reserved.
2
$69.95
14
17.3 Relational Database Overview: Books
Database
0137905696
The Complete Java 2
Training Course
2
1998
javactc2.jpg $109.95
0130829277
The Complete Java 2
Training Course
(Java 1.1)
2
1998
javactc2.jpg $99.95
0138993947
Java How to
2
Program (Java 1.1)
1
1998
jhtp2.jpg
$49.95
0131173340
C++ How to
Program
1
1
1994
cpphtp1.jpg
$69.95
0132261197
C How to Program 2
1
1994
chtp2.jpg
$49.95
0131180436
C How to Program 1
1
1992
chtp.jpg
$69.95
Fig. 17.10
Da ta from the
 2002 Prentice Hall. All rights reserved.
Titles ta b le of Books.
15
17.3 Relational Database Overview: Books
Database
Authors
AuthorID
FirstName
LastName
1

AuthorISBN
AuthorID
ISBN
Publishers
PublisherID
PublisherNam
e
Fig. 17.11
 2002 Prentice Hall. All rights reserved.
Table relationships in Books.
1


1
Titles
ISBN
Title
EditionNumbe
r
Copyright
PublisherID
ImageFile
Price
16
17
17.4 Structured Query Language (SQL)
• Used almost universally with relational database
systems to perform queries (i.e., to request
information that satisfies given criteria) and to
manipulate data
 2002 Prentice Hall. All rights reserved.
18
17.4 Structured Query Language (SQL)
SQL keyw ord
De sc rip tio n
SELECT
Selects (retrieves) fields from one or more tables.
FROM
Specifies tables from which to get fields or delete records. Required in
every SELECT and DELETE statement.
Specifies criteria that determine the rows to be retrieved.
WHERE
Joins records from multiple tables to produce a single set of records.
INNER JOIN
Specifies criteria for grouping records.
GROUP BY
Specifies criteria for ordering records.
ORDER BY
Inserts data into a specified table.
INSERT
Updates data in a specified table.
UPDATE
Deletes data from a specified table.
DELETE
Fig. 17.12
SQL q uery keyw ord s.
 2002 Prentice Hall. All rights reserved.
19
17.4.1 Basic SELECT Query
• SELECT queries select information from one or
more database tables
• Basic format: SELECT * FROM tableName
 2002 Prentice Hall. All rights reserved.
20
17.4.1 Basic SELECT Query
AuthorID
LastName
AuthorID
LastName
1
Deitel
8
McPhie
2
Deitel
9
Yaeger
3
Nieto
10
Zlatkina
4
Steinbuhler
12
Wiedermann
5
Santry
12
Liperi
6
Lin
13
Listfield
7
Sadhu
Fig. 17.13
 2002 Prentice Hall. All rights reserved.
AuthorID a nd LastName from the Authors ta b le.
21
17.4.2 WHERE Clause
• Users can search a database for records that satisfy
selection criteria
• Optional WHERE clause in a SELECT query
specifies selection criteria for the query
• Simplest format: SELECT fieldName FROM
tableName WHERE criteria
• WHERE clause condition can contain operators <,
>, <=, >=, =, <> and LIKE (used for pattern
matching with wildcard characters like % and _)
 2002 Prentice Hall. All rights reserved.
22
17.4.2 WHERE Clause
Title
EditionNumber Copyright
Internet and World Wide Web How to Program
2
2002
Java How to Program
4
2002
The Complete Java Training Course
4
2002
The Complete e-Business & e-Commerce Programming 1
Training Course
2001
The Complete Internet & World Wide Web
Programming Training Course
2
2001
The Complete Perl Training Course
1
2001
The Complete XML Programming Training Course
1
2001
C How to Program
3
2001
C++ How to Program
3
2001
The Complete C++ Training Course
3
2001
e-Business and e-Commerce How to Program
1
2001
 2002 Prentice Hall. All rights reserved.
23
17.4.2 WHERE Clause
Internet and World Wide Web How to Program
1
2000
The Complete Internet and World Wide Web
Programming Training Course
1
2000
Java How to Program (Java 2)
3
2000
The Complete Java 2 Training Course
3
2000
XML How to Program
1
2001
Perl How to Program
1
2001
Advanced Java 2 Platform How to Program
1
2002
e-Business and e-Commerce for Managers
1
2000
Wireless Internet and Mobile Business How to Program 1
2001
C# How To Program
1
2002
Python How to Program
1
2002
Visual Basic .NET How to Program
2
2002
Fig. 17.14
 2002 Prentice Hall. All rights reserved.
Titles w ith c op yrig hts a fter 1999 from ta b le
Titles.
24
17.4.2 WHERE Clause
AuthorID
FirstName
LastName
1
Harvey
Deitel
2
Paul
Deitel
Fig. 17.15
Authors from the
 2002 Prentice Hall. All rights reserved.
Authors ta b le w hose la st na m es sta rt w ith D.
25
17.4.2 WHERE Clause
AuthorID
FirstName
LastName
3
Tem
Nieto
6
Ted
Lin
11
Ben
Wiedermann
12
Jonathan
Liperi
13
Jeffrey
Listfield
Fig. 17.16
Authors from ta b le
sec ond le tte r.
 2002 Prentice Hall. All rights reserved.
Authors w hose la st na m es c onta in i a s the
26
17.4.3 ORDER BY Clause
• Option ORDER BY clause can arrange query
results in ascending or descending order
• To specify ascending order: ORDER BY field ASC
• To specify descending order: ORDER BY field
DESC
 2002 Prentice Hall. All rights reserved.
27
17.4.3 ORDER BY Clause
AuthorID
FirstName
LastName
2
Paul
Deitel
1
Harvey
Deitel
6
Ted
Lin
12
Jonathan
Liperi
13
Jeffrey
Listfield
8
David
McPhie
3
Tem
Nieto
7
Praveen
Sadhu
5
Sean
Santry
4
Kate
Steinbuhler
11
Ben
Wiedermann
9
Cheryl
Yaeger
10
Marina
Zlatkina
Fig. 17.17
Authors from ta b le
 2002 Prentice Hall. All rights reserved.
Authors in a sc end ing o rd er b y LastName.
28
17.4.3 ORDER BY Clause
AuthorID
FirstName
LastName
10
Marina
Zlatkina
9
Cheryl
Yaeger
11
Ben
Wiedermann
4
Kate
Steinbuhler
5
Sean
Santry
7
Praveen
Sadhu
3
Tem
Nieto
8
David
McPhie
13
Jeffrey
Listfield
12
Jonathan
Liperi
6
Ted
Lin
2
Paul
Deitel
1
Harvey
Deitel
Fig. 17.18
Authors from ta b le
LastName.
 2002 Prentice Hall. All rights reserved.
Authors in d esc end ing o rd er b y
29
17.4.3 ORDER BY Clause
AuthorID
FirstName
LastName
1
Harvey
Deitel
2
Paul
Deitel
6
Ted
Lin
12
Jonathan
Liperi
13
Jeffrey
Listfield
8
David
McPhie
3
Tem
Nieto
7
Praveen
Sadhu
5
Sean
Santry
4
Kate
Steinbuhler
11
Ben
Wiedermann
9
Cheryl
Yaeger
10
Marina
Zlatkina
Fig. 17.19
Authors from ta b le
a nd b y FirstName.
 2002 Prentice Hall. All rights reserved.
Authors in a sc end ing o rd er b y LastName
30
17.4.3 ORDER BY Clause
ISBN
Title
Edition- CopyNumber
right
Price
0130895601
Advanced Java 2 Platform
How to Program
1
2002
$69.95
0131180436
C How to Program
1
1992
$69.95
0130895725
C How to Program
3
2001
$69.95
0132261197
C How to Program
2
1994
$49.95
0130622214
C# How To Program
1
2002
$69.95
0135289106
C++ How to Program
2
1998
$49.95
0131173340
C++ How to Program
1
1994
$69.95
0130895717
C++ How to Program
3
2001
$69.95
013028419X
e-Business and e-Commerce
How to Program
1
2001
$69.95
0130308978
Internet and World Wide Web 2
How to Program
2002
$69.95
 2002 Prentice Hall. All rights reserved.
31
17.4.3 ORDER BY Clause
0130161438
Internet and World Wide Web 1
How to Program
2000
$69.95
0130341517
Java How to Program
4
2002
$69.95
0136325890
Java How to Program
1
1998
$49.95
0130284181
Perl How to Program
1
2001
$69.95
0130923613
Python How to Program
1
2002
$69.95
0130293636
Visual Basic .NET How to
Program
2
2002
$69.95
0134569555
Visual Basic 6 How to
Program
1
1999
$69.95
0130622265
Wireless Internet and Mobile
Business How to Program
1
2001
$69.95
0130284173
XML How to Program
1
2001
$69.95
Fig. 17.20
Books from ta b le Titles w hose titles e nd w ith
Program in a sc end ing ord e r b y Title.
 2002 Prentice Hall. All rights reserved.
How to
32
17.4.4 Merging Data from Multiple Tables:
INNER JOIN
• Merging data from multiple tables into a single set
of data is referred to as joining the tables
• INNER JOIN operation in SELECT query
accomplishes joining
• ON part of an INNER JOIN clause specifies the
fields from each each table that are compared to
determine which records are joined
 2002 Prentice Hall. All rights reserved.
33
17.4.4 Merging Data from Multiple Tables:
INNER JOIN
FirstNa LastNam ISBN
me
e
FirstNa LastNam ISBN
me
e
Harvey
Deitel
0130895601
Harvey
Deitel
0130829293
Harvey
Deitel
0130284181
Harvey
Deitel
0134569555
Harvey
Deitel
0130284173
Harvey
Deitel
0130829277
Harvey
Deitel
0130852473
Paul
Deitel
0130125075
Harvey
Deitel
0138993947
Paul
Deitel
0130856118
Harvey
Deitel
0130856118
Paul
Deitel
0130161438
Harvey
Deitel
0130161438
Paul
Deitel
013028419x
Harvey
Deitel
013028419x
Paul
Deitel
0139163050
Harvey
Deitel
0139163050
Paul
Deitel
0130895601
Harvey
Deitel
0135289106
Paul
Deitel
0135289106
Harvey
Deitel
0130895717
Paul
Deitel
0130895717
Harvey
Deitel
0132261197
Paul
Deitel
0132261197
Harvey
Deitel
0130895725
Paul
Deitel
0130895725
Harvey
Deitel
0130125075
Tem
Nieto
0130284181
Paul
Deitel
0130284181
Tem
Nieto
0130284173
Paul
Deitel
0130284173
Tem
Nieto
0130829293
Paul
Deitel
0130829293
Tem
Nieto
0134569555
Paul
Deitel
0134569555
Tem
Nieto
0130856118
Paul
Deitel
0130829277
Tem
Nieto
0130161438
Paul
Deitel
0130852473
Tem
Nieto
013028419x
Paul
Deitel
0138993947
Fig. 17.21
Authors from ta b le Authors a nd ISBN num b ers of the a uthors’
b ooks, sorted in a sc end ing o rd er b y LastName a nd FirstName.
 2002 Prentice Hall. All rights reserved.
17.4.5 Joining Data from Tables Authors,
AuthorISBN, Titles and Publishers
• Books database predefined query
(TitleAuthor) selects as its results the title,
ISBN number, author’s first name, author’s last
name, copyright year and publisher’s name for
each book in the database
• Query depicted in Fig. 17.22
• Figure 17.23 contains portion of the query results
 2002 Prentice Hall. All rights reserved.
34
35
1
2
3
4
5
6
7
8
9
10
11
SELECT Titles.Title, Titles.ISBN, Authors.FirstName,
Authors.LastName, Titles.Copyright,
Publishers.PublisherName
FROM
( Publishers INNER JOIN Titles
ON Publishers.PublisherID = Titles.PublisherID )
INNER JOIN
( Authors INNER JOIN AuthorISBN
ON Authors.AuthorID = AuthorISBN.AuthorID )
ON Titles.ISBN = AuthorISBN.ISBN
ORDER BY Titles.Title
Outline
Fig. 17.22 TitleAuthor
query of Books
database
 2002 Prentice Hall.
All rights reserved.
17.4.5 Joining Data from Tables Authors,
AuthorISBN, Titles and Publishers
Title
ISBN
First Last- Copy-Name Name right
PublisherName
Advanced Java 2 Platform
How to Program
0130895601
Paul
Deitel
2002
Prentice Hall
Advanced Java 2 Platform
How to Program
0130895601
Harvey
Deitel
2002
Prentice Hall
Advanced Java 2 Platform
How to Program
0130895601
Sean
Santry
2002
Prentice Hall
C How to Program
0131180436
Harvey
Deitel
1992
Prentice Hall
C How to Program
0131180436
Paul
Deitel
1992
Prentice Hall
C How to Program
0132261197
Harvey
Deitel
1994
Prentice Hall
C How to Program
0132261197
Paul
Deitel
1994
Prentice Hall
C How to Program
0130895725
Harvey
Deitel
2001
Prentice Hall
C How to Program
0130895725
Paul
Deitel
2001
Prentice Hall
C# How To Program
0130622214
Tem
Nieto
2002
Prentice Hall
C# How To Program
0130622214
Paul
Deitel
2002
Prentice Hall
C# How To Program
0130622214
Jeffrey
Listfield
2002
Prentice Hall
C# How To Program
0130622214
Cheryl
Yaeger
2002
Prentice Hall
C# How To Program
0130622214
Marina
Zlatkina
2002
Prentice Hall
 2002 Prentice Hall. All rights reserved.
36
17.4.5 Joining Data from Tables Authors,
AuthorISBN, Titles and Publishers
C# How To Program
0130622214
Harvey
Deitel
2002
Prentice Hall
C++ How to Program
0130895717
Paul
Deitel
2001
Prentice Hall
C++ How to Program
0130895717
Harvey
Deitel
2001
Prentice Hall
C++ How to Program
0131173340
Paul
Deitel
1994
Prentice Hall
C++ How to Program
0131173340
Harvey
Deitel
1994
Prentice Hall
C++ How to Program
0135289106
Harvey
Deitel
1998
Prentice Hall
C++ How to Program
0135289106
Paul
Deitel
1998
Prentice Hall
e-Business and e-Commerce
for Managers
0130323640
Harvey
Deitel
2000
Prentice Hall
e-Business and e-Commerce
for Managers
0130323640
Kate
Steinbuhl 2000
er
Prentice Hall
e-Business and e-Commerce
for Managers
0130323640
Paul
Deitel
2000
Prentice Hall
e-Business and e-Commerce
How to Program
013028419X
Harvey
Deitel
2001
Prentice Hall
e-Business and e-Commerce
How to Program
013028419X
Paul
Deitel
2001
Prentice Hall
e-Business and e-Commerce
How to Program
013028419X
Tem
Nieto
2001
Prentice Hall
Fig. 17.23
Portion of the result se t p rod uc ed b y the q uery in Fig . 17.22.
 2002 Prentice Hall. All rights reserved.
37
38
17.4.6 INSERT statement
• INSERT statement inserts new record in the table
• Simplest form: INSERT INTO tableName (
fieldName1, . . ., fieldNameN ) VALUES ( value1, .
. ., valueN )
 2002 Prentice Hall. All rights reserved.
39
17.4.6 INSERT statement
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
13
Jeffrey
Listfield
14
Sue
Smith
Fig. 17.24
 2002 Prentice Hall. All rights reserved.
Authors a fte r a n INSERT op era tion to a d d a rec ord .
40
17.4.7 UPDATE Statement
• UPDATE statement modifies table data
• Simplest form: UPDATE tableName SET
fieldName = value WHERE criteria
 2002 Prentice Hall. All rights reserved.
41
17.4.7 UPDATE Statement
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
13
Jeffrey
Listfield
14
Sue
Jones
Fig. 17.25
Ta b le
rec o rd .
 2002 Prentice Hall. All rights reserved.
Authors a fte r a n UPDATE op era tion to c ha ng e a
42
17.4.8 DELETE Statement
• DELETE statement removes table data
• Simplest form: DELETE FROM tableName WHERE
criteria
 2002 Prentice Hall. All rights reserved.
43
17.4.8 DELETE Statement
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
13
Jeffrey
Listfield
Fig. 17.26
 2002 Prentice Hall. All rights reserved.
Ta b le
Authors a fte r a DELETE op era tion to rem ove a re c ord .
44
17.5 Python DB-API Specification
• Python Database Application Programming
Interface (DB-API): document that specifies
common object and method names for
manipulating any database
• Describes a Connection object that accesses
the database
• Cursor object, created by Connection object,
manipulates and retrieves data
• Three methods for fetching rows of a query result
set – fetchone, fetchmany and fetchall
 2002 Prentice Hall. All rights reserved.
45
17.6 Database Query Example
• Presents a CGI program that performs a simple
query on the Books database and displays result
set in an XHTML table
 2002 Prentice Hall. All rights reserved.
46
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
#!c:\python\python.exe
# Fig. 17.27: fig17_27.py
# Displays contents of the Authors table,
classes
and functions
# ordered by a Contains
specified
field.
Outline
for manipulating MySQL databases
fig17_27.py
import MySQLdb
import cgi
import sys
def printHeader( title ):
print """Content-type: text/html
<?xml version = "1.0" encoding = "UTF-8"?>
<!DOCTYPE html PUBLIC
"-//W3C//DTD XHTML 1.0 Transitional//EN"
"DTD/xhtml1-transitional.dtd">
<html xmlns = "http://www.w3.org/1999/xhtml"
xml:lang = "en" lang = "en">
<head><title>%s</title></head>
<body>""" % title
Obtain form data
# obtain user query specifications
form = cgi.FieldStorage()
Get “sort by” value for ORDER BY
# get "sortBy" value
if form.has_key( "sortBy" ):
sortBy = form[ "sortBy" ].value
else:
sortBy = "firstName"
Get sorting order for ORDER BY
# get "sortOrder" value
if form.has_key( "sortOrder" ):
sortOrder = form[ "sortOrder" ].value
else:
 2002 Prentice Hall.
All rights reserved.
47
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
Outline
sortOrder = "ASC"
printHeader( "Authors table from Books" )
fig17_27.py
# connect to database and retrieve
a cursor
Create Connection
object
to manage
connection
Specify
database
as value
of keyword db
try:
connection = MySQLdb.connect( db = "Books" )
MySQLdb.connect failure raises MySQLdb.OperationalError exception
# error connecting to database
except MySQLdb.OperationalError, error:
print "Error:", error
sys.exit( 1 )
# retrieve cursor
Create Cursor object
else:
cursor = connection.cursor()
Execute query against database
# query all records from Authors table
cursor.execute( "SELECT * FROM Authors ORDER BY %s %s" %
( sortBy, sortOrder ) )
Attribute
contains information about fields
Obtain alldescription
records
allFields = cursor.description
allRecords = cursor.fetchall()
# get field names
# get records
Cursor object
# close cursor Close
and connection
Close Connection
cursor.close()
connection.close()
object
results in table
# output results Output
in a table
print """\n<table border = "1" cellpadding = "3" >
<tr bgcolor = "silver" >"""
# create table header
for field in allFields:
print "<td>%s</td>" % field[ 0 ]
 2002 Prentice Hall.
All rights reserved.
48
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
print "</tr>"
Display each record as a table row
# display each record as a row
for author in allRecords:
print "<tr>"
Outline
fig17_27.py
for item in author:
print "<td>%s</td>" % item
print "</tr>"
print "</table>"
# obtain sorting method from user
Print form to obtain sorting information from user
print """
\n<form method = "post" action = "/cgi-bin/fig17_27.py">
Sort By:<br />"""
# display sorting options
for field in allFields:
print """<input type = "radio" name = "sortBy"
value = "%s" />""" % field[ 0 ]
print field[ 0 ]
print "<br />"
print """<br />\nSort Order:<br />
<input type = "radio" name = "sortOrder"
value = "ASC" checked = "checked" />
Ascending
<input type = "radio" name = "sortOrder"
value = "DESC" />
Descending
<br /><br />\n<input type = "submit" value = "SORT" />
</form>\n\n</body>\n</html>"""
 2002 Prentice Hall.
All rights reserved.
49
Outline
fig17_27.py
 2002 Prentice Hall.
All rights reserved.
50
17.7 Querying the Books Database
• Creates GUI interface for user to enter a query
• Introduces Pmw components ScrolledFrame
and PanedWidget
 2002 Prentice Hall. All rights reserved.
51
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
# Fig. 17.28: fig17_28.py
# Displays results returned by a
# query on Books database.
import MySQLdb
from Tkinter import *
from tkMessageBox import *
import Pmw
Outline
fig17_28.py
GUI allows user to enter query
class QueryWindow( Frame ):
"""GUI Database Query Frame"""
def __init__( self ):
"""QueryWindow Constructor"""
Frame.__init__( self )
Pmw.initialise()
self.pack( expand = YES, fill = BOTH )
self.master.title( \
"Enter Query, Click Submit to See Results." )
self.master.geometry( "525x525" )
# scrolled text pane for query string
self.query = Pmw.ScrolledText( self, text_height = 8 )
self.query.pack( fill = X )
# button to submit query
self.submit = Button( self, text = "Submit query",
command = self.submitQuery )
self.submit.pack( fill = X )
 2002 Prentice Hall.
All rights reserved.
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
Createresults
Pmw ScrolledFrame component to display query
results
query
Outline
# frame to display
self.frame = Pmw.ScrolledFrame( self,
hscrollmode = "static", vscrollmode = "static" )
PanedWidget
self.frame.pack( expandCreate
= YES,Pmw
fill
= BOTH )
fig17_28.py
self.panes = Pmw.PanedWidget( self.frame.interior(),
orient = "horizontal" )
self.panes.pack( expand = YES, fill = BOTH )
def submitQuery( self ):
"""Execute user-entered query against database"""
# open connection, retrieve cursor and execute query
try:
connection = MySQLdb.connect( db = "Books" )
Execute user-entered query
cursor = connection.cursor()
cursor.execute( self.query.get() )
except MySQLdb.OperationalError, message:
errorMessage = "Error %d:\n%s" % \
( message[ 0 ], message[ 1 ] )
showerror( "Error", errorMessage )
return
else:
# obtain user-requested information
data = cursor.fetchall()
fields = cursor.description
# metadata from query
cursor.close()
connection.close()
Clearofresults
last query with method destroy
# clear results
last of
query
self.panes.destroy()
self.panes = Pmw.PanedWidget( self.frame.interior(),
orient = "horizontal" )
self.panes.pack( expand = YES, fill = BOTH )
 2002 Prentice Hall.
All rights reserved.
52
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
Create
panefor
and each
label field
for each result
and label
# create pane
for item in fields:
self.panes.add( item[ 0 ] )
label = Label( self.panes.pane( item[ 0 ] ),
text = item[ 0 ], relief = RAISED )
label.pack( fill = X )
53
Outline
fig17_28.py
Uses labels to enter results into panes
# enter results into panes, using labels
for entry in data:
for i in range( len( entry ) ):
label = Label( self.panes.pane( fields[ i ][ 0 ] ),
text = str( entry[ i ] ), anchor = W,
relief = GROOVE, bg = "white" )
label.pack( fill = X )
Set size of each pane to be large enough to view largest label
self.panes.setnaturalsize()
def main():
QueryWindow().mainloop()
if __name__ == "__main__":
main()
 2002 Prentice Hall.
All rights reserved.
54
Outline
fig17_28.py
 2002 Prentice Hall.
All rights reserved.
55
17.8 Reading, Inserting and Updating a
Database
• Example manipulates a MySQL AddressBook
database that contains one table (addresses) with
11 columns-ID, FirstName, LastName,
Address, City, StateOrProvince,
PostalCode, Country, EmailAddress,
HomePhone and FaxNumber
 2002 Prentice Hall. All rights reserved.
56
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
# Fig. 17.29: fig17_29.py
# Inserts into, updates and searches a database
import MySQLdb
from Tkinter import *
from tkMessageBox import *
import Pmw
Outline
fig17_29.py
class AddressBook( Frame ):
"""GUI Database Address Book Frame"""
def __init__( self ):
"""Address Book constructor"""
Frame.__init__( self )
Pmw.initialise()
self.pack( expand = YES, fill = BOTH )
self.master.title( "Address Book Database Application" )
Create Pmw ButtonBox for application commands
# buttons to execute commands
self.buttons = Pmw.ButtonBox( self, padx = 0 )
self.buttons.grid( columnspan = 2 )
self.buttons.add( "Find", command = self.findAddress )
self.buttons.add( "Add", command = self.addAddress )
self.buttons.add( "Update", command = self.updateAddress )
self.buttons.add( "Clear", command = self.clearContents )
self.buttons.add( "Help", command = self.help, width = 14 )
self.buttons.alignbuttons()
List of fields in an address record
# list of fields in an address record
fields = [ "ID", "First name", "Last name",
"Address", "City", "State Province", "Postal Code",
"Country", "Email Address", "Home phone", "Fax Number" ]
 2002 Prentice Hall.
All rights reserved.
57
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
# dictionary with Entry components for values, keyed by
fields
and corresponding
values
# corresponding Dictionary
addresses for
table
field
names
self.entries = {}
self.IDEntry = StringVar()
self.IDEntry.set( "" )
# current address id text
Outline
fig17_29.py
Createfor
Entry
for each field
# create entries
each component
field
for i in range( len( fields ) ):
label = Label( self, text = fields[ i ] + ":" )
label.grid( row = i + 1, column = 0 )
entry = Entry( self, name = fields[ i ].lower(),
font = "Courier 12" )
entry.grid( row = i + 1 , column = 1,
sticky = W+E+N+S, padx = 5 )
# user cannot User
typecannot
in ID type
field
in ID field
if fields[ i ] == "ID":
entry.config( state = DISABLED,
textvariable = self.IDEntry, bg = "gray" )
Add entry field to dictionary
# add entry field to dictionary
key = fields[ i ].replace( " ", "_" )
key = key.upper()
self.entries[ key ] = entry
Add address record to database
def addAddress( self ):
"""Add address record to database"""
if self.entries[ "LAST_NAME" ].get() != "" and \
self.entries[ "FIRST_NAME"].get() != "":
 2002 Prentice Hall.
All rights reserved.
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
Create
INSERT
query command
INSERT
query
command
# create
query = """INSERT INTO addresses (
FIRST_NAME, LAST_NAME, ADDRESS, CITY,
STATE_PROVINCE, POSTAL_CODE, COUNTRY,
EMAIL_ADDRESS, HOME_PHONE, FAX_NUMBER
) VALUES (""" + \
"'%s', " * 10 % \
( self.entries[ "FIRST_NAME" ].get(),
self.entries[ "LAST_NAME" ].get(),
self.entries[ "ADDRESS" ].get(),
self.entries[ "CITY" ].get(),
self.entries[ "STATE_PROVINCE" ].get(),
self.entries[ "POSTAL_CODE" ].get(),
self.entries[ "COUNTRY" ].get(),
self.entries[ "EMAIL_ADDRESS" ].get(),
self.entries[ "HOME_PHONE" ].get(),
self.entries[ "FAX_NUMBER" ].get() )
query = query[ :-2 ] + ")"
58
Outline
fig17_29.py
# open connection, retrieve cursor and execute query
try:
connection = MySQLdb.connect( db = "AddressBook" )
cursor = connection.cursor()
cursor.execute( query )
except MySQLdb.OperationalError, message:
errorMessage = "Error %d:\n%s" % \
( message[ 0 ], message[ 1 ] )
showerror( "Error", errorMessage )
else:
cursor.close()
connection.close()
self.clearContents()
 2002 Prentice Hall.
All rights reserved.
59
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
else:
# user has not filled out first/last name fields
showwarning( "Missing fields", "Please enter name" )
Outline
Query database for address record and display results
def findAddress( self ):
"""Query database for address record and display results"""
fig17_29.py
if self.entries[ "LAST_NAME" ].get() != "":
Create SELECT query with WHERE clause
# create SELECT query
query = "SELECT * FROM addresses " + \
"WHERE LAST_NAME = ’" + \
self.entries[ "LAST_NAME" ].get() + "'"
# open connection, retrieve cursor and execute query
try:
connection = MySQLdb.connect( db = "AddressBook" )
cursor = connection.cursor()
cursor.execute( query )
except MySQLdb.OperationalError, message:
errorMessage = "Error %d:\n%s" % \
( message[ 0 ], message[ 1 ] )
showerror( "Error", errorMessage )
self.clearContents()
else:
# process results
results = cursor.fetchall()
fields = cursor.description
if not results:
# no results for this person
showinfo( "Not found", "Nonexistent record" )
else:
# display information in GUI
self.clearContents()
# display results
for i in range( len( fields ) ):
 2002 Prentice Hall.
All rights reserved.
60
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
if fields[ i ][ 0 ] == "ID":
self.IDEntry.set( str( results[ 0 ][ i ] ) )
else:
self.entries[ fields[ i ][ 0 ] ].insert(
INSERT, str( results[ 0 ][ i ] ) )
Outline
fig17_29.py
cursor.close()
connection.close()
else:
# user did not enter last name
showwarning(
"Missing
fields",
"Please enter last name" )
Update address
record
in database
def updateAddress( self ):
"""Update address record in database"""
if self.entries[ "ID" ].get():
# create UPDATE query command
entryItems= self.entries.items()
query = "UPDATE addresses SET"
for key, value in entryItems:
if key != "ID":
query += " %s='%s'," % ( key, value.get() )
query = query[ :-1 ] + " WHERE ID=" + self.IDEntry.get()
# open connection, retrieve cursor and execute query
try:
UPDATE query
connection Execute
= MySQLdb.connect(
db command
= "AddressBook" )
cursor = connection.cursor()
cursor.execute( query )
 2002 Prentice Hall.
All rights reserved.
61
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
except MySQLdb.OperationalError, message:
errorMessage = "Error %d:\n%s" % \
( message[ 0 ], message[ 1 ] )
showerror( "Error", errorMessage )
self.clearContents()
else:
showinfo( "database updated", "Database Updated." )
cursor.close()
connection.close()
Outline
fig17_29.py
else:
# user has not specified ID
showwarning( "No ID specified", """
You may only update an existing record.
Use Find to locate the record,
then modify the information and press Update.""" )
Clear GUI display
def clearContents( self ):
"""Clear GUI panel"""
for entry in self.entries.values():
entry.delete( 0, END )
self.IDEntry.set( "" )
Display help dialog
def help( self ):
"Display help message to user"
showinfo( "Help", """Click Find to locate a record.
Click Add to insert a new record.
Click Update to update the information in a record.
Click Clear to empty the Entry fields.\n""" )
def main():
AddressBook().mainloop()
 2002 Prentice Hall.
All rights reserved.
62
204
205
if __name__ == "__main__":
main()
Outline
fig17_29.py
 2002 Prentice Hall.
All rights reserved.