csci/cmpe 4341 topic: programming in python chapter 10: database application programming interface...
TRANSCRIPT
![Page 1: CSCI/CMPE 4341 Topic: Programming in Python Chapter 10: Database Application Programming Interface Xiang Lian The University of Texas – Pan American Edinburg,](https://reader035.vdocuments.us/reader035/viewer/2022062217/56649e8f5503460f94b9295e/html5/thumbnails/1.jpg)
CSCI/CMPE 4341 Topic: CSCI/CMPE 4341 Topic: Programming in PythonProgramming in Python
Chapter 10: Database Application Chapter 10: Database Application Programming InterfaceProgramming Interface
Xiang Lian
The University of Texas – Pan American
Edinburg, TX 78539
1
![Page 2: CSCI/CMPE 4341 Topic: Programming in Python Chapter 10: Database Application Programming Interface Xiang Lian The University of Texas – Pan American Edinburg,](https://reader035.vdocuments.us/reader035/viewer/2022062217/56649e8f5503460f94b9295e/html5/thumbnails/2.jpg)
Objectives
• In this chapter, you will:– Understand relational database model
– Learn basic database SQL queries
– Use packages to create and query a database
2
![Page 3: CSCI/CMPE 4341 Topic: Programming in Python Chapter 10: Database Application Programming Interface Xiang Lian The University of Texas – Pan American Edinburg,](https://reader035.vdocuments.us/reader035/viewer/2022062217/56649e8f5503460f94b9295e/html5/thumbnails/3.jpg)
Introduction
• A database is an organized collection of data• A database management system (DBMS)
provides mechanisms for storing, organizing, retrieving and modifying data
• Existing DBMSs–Microsoft SQL Server– Oracle– Sybase– IBM DB2
3
![Page 4: CSCI/CMPE 4341 Topic: Programming in Python Chapter 10: Database Application Programming Interface Xiang Lian The University of Texas – Pan American Edinburg,](https://reader035.vdocuments.us/reader035/viewer/2022062217/56649e8f5503460f94b9295e/html5/thumbnails/4.jpg)
Introduction (cont'd)
• PostgreSQL and MySQL are popular open-source DBMSs that can be downloaded and used freely by anyone
• Microsoft’s free SQL Server Express, which is installed with Visual Studio, can be also downloaded separately from Microsoft (www.microsoft.com/express/sql)
4
![Page 5: CSCI/CMPE 4341 Topic: Programming in Python Chapter 10: Database Application Programming Interface Xiang Lian The University of Texas – Pan American Edinburg,](https://reader035.vdocuments.us/reader035/viewer/2022062217/56649e8f5503460f94b9295e/html5/thumbnails/5.jpg)
Relational Database
• A relational database organizes data simply in tables– rows (also called records)
– columns (also called fields, attributes)• Primary key: a column (or group of columns) requiring
a unique value that cannot be duplicated in other rows
• A primary key composed of two or more columns is known as a composite key
• Foreign key—a column in this table that matches the primary-key column in another table
5
![Page 6: CSCI/CMPE 4341 Topic: Programming in Python Chapter 10: Database Application Programming Interface Xiang Lian The University of Texas – Pan American Edinburg,](https://reader035.vdocuments.us/reader035/viewer/2022062217/56649e8f5503460f94b9295e/html5/thumbnails/6.jpg)
Example of Table: Employees
6
![Page 7: CSCI/CMPE 4341 Topic: Programming in Python Chapter 10: Database Application Programming Interface Xiang Lian The University of Texas – Pan American Edinburg,](https://reader035.vdocuments.us/reader035/viewer/2022062217/56649e8f5503460f94b9295e/html5/thumbnails/7.jpg)
SQL
• A program might select data from the table to create a query result – E.g., to retrieve the location of each department, in
increasing order by Department number
– SQL: • SELECT DISTINCT Department, Location
FROM Employees
ORDER BY Department
7
![Page 8: CSCI/CMPE 4341 Topic: Programming in Python Chapter 10: Database Application Programming Interface Xiang Lian The University of Texas – Pan American Edinburg,](https://reader035.vdocuments.us/reader035/viewer/2022062217/56649e8f5503460f94b9295e/html5/thumbnails/8.jpg)
SQL Results
8
![Page 9: CSCI/CMPE 4341 Topic: Programming in Python Chapter 10: Database Application Programming Interface Xiang Lian The University of Texas – Pan American Edinburg,](https://reader035.vdocuments.us/reader035/viewer/2022062217/56649e8f5503460f94b9295e/html5/thumbnails/9.jpg)
Schema
• A database may contain one or multiple tables
• A database’s tables, their fields and the relationships among them are collectively known as a database schema
9
![Page 10: CSCI/CMPE 4341 Topic: Programming in Python Chapter 10: Database Application Programming Interface Xiang Lian The University of Texas – Pan American Edinburg,](https://reader035.vdocuments.us/reader035/viewer/2022062217/56649e8f5503460f94b9295e/html5/thumbnails/10.jpg)
Entity-Relationship Model
• Entity-Relationship (ER) model– Entity• Authors
• Titles
– Relationship• There is a one-to-many relationship between a primary
key and a corresponding foreign key– E.g., one author can write many books and one book can be
written by many authors
• Others: many-to-many or one-to-one relationship
• E.g., AuthorISBN10
![Page 11: CSCI/CMPE 4341 Topic: Programming in Python Chapter 10: Database Application Programming Interface Xiang Lian The University of Texas – Pan American Edinburg,](https://reader035.vdocuments.us/reader035/viewer/2022062217/56649e8f5503460f94b9295e/html5/thumbnails/11.jpg)
11
Example of Relational Database: 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
![Page 12: CSCI/CMPE 4341 Topic: Programming in Python Chapter 10: Database Application Programming Interface Xiang Lian The University of Texas – Pan American Edinburg,](https://reader035.vdocuments.us/reader035/viewer/2022062217/56649e8f5503460f94b9295e/html5/thumbnails/12.jpg)
Example of ER Diagram
12
AuthorISBN
AuthorID
ISBN
Authors
AuthorID
FirstName
LastName
Publishers
PublisherID
PublisherName
Titles
ISBN
Title
EditionNumber
Copyright
PublisherID
ImageFile
Price
1 • 1
•
1
•
![Page 13: CSCI/CMPE 4341 Topic: Programming in Python Chapter 10: Database Application Programming Interface Xiang Lian The University of Texas – Pan American Edinburg,](https://reader035.vdocuments.us/reader035/viewer/2022062217/56649e8f5503460f94b9295e/html5/thumbnails/13.jpg)
Authors Table
13
Field DescriptionAuthorID 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.
FirstName Author’s first name (a string).
LastName Author’s last name (a string).
Fig. 17.3 Authors table from Books.
AuthorID FirstName LastName1 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 Data from the Authors table of Books.
![Page 14: CSCI/CMPE 4341 Topic: Programming in Python Chapter 10: Database Application Programming Interface Xiang Lian The University of Texas – Pan American Edinburg,](https://reader035.vdocuments.us/reader035/viewer/2022062217/56649e8f5503460f94b9295e/html5/thumbnails/14.jpg)
Publishers Table
14
Field DescriptionPublisherID The publisher’s ID number in the database. This auto-
incremented int field is the table’s primary-key field.
PublisherName The name of the publisher (a string).
Fig. 17.5 Publishers table from Books.
PublisherID PublisherName1 Prentice Hall2 Prentice Hall PTGFig. 17.6 Data from the Publishers table of
Books.
![Page 15: CSCI/CMPE 4341 Topic: Programming in Python Chapter 10: Database Application Programming Interface Xiang Lian The University of Texas – Pan American Edinburg,](https://reader035.vdocuments.us/reader035/viewer/2022062217/56649e8f5503460f94b9295e/html5/thumbnails/15.jpg)
AuthorISBN Table
15
Field DescriptionAuthorID 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 table from Books.
AuthorID ISBN AuthorID ISBN1 0130895725 1 01302841811 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
![Page 16: CSCI/CMPE 4341 Topic: Programming in Python Chapter 10: Database Application Programming Interface Xiang Lian The University of Texas – Pan American Edinburg,](https://reader035.vdocuments.us/reader035/viewer/2022062217/56649e8f5503460f94b9295e/html5/thumbnails/16.jpg)
Titles Table
16
Field DescriptionISBN ISBN number of the book (a string).Title Title of the book (a string).EditionNumber Edition number of the book (a string).Copyright Copyright year of the book (an int).PublisherID Publisher’s ID number (an int). This value must
correspond to an ID number in the Publishers table.ImageFile Name of the file containing the book’s cover image (a
string).Price 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 table from Books.
ISBN Title Edition-Number
Publish-erID
Copy-right
ImageFile Price
0130923613 Python How to Program 1 1 2002 python.jpg $69.95
0130622214 C# How to Program 1 1 2002 cshtp.jpg $69.95
0130341517 Java How to Program 4 1 2002 jhtp4.jpg $69.95
0130649341 The Complete Java Training Course 4 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 Wide Web How to Program
2 1 2002 iw3htp2.jpg $69.95
0130293636 Visual Basic .NET How to Program 2 1 2002 vbnet.jpg $69.95
0130895636 The Complete C++ Training Course 3 2 2001 cppctc3.jpg $109.95
![Page 17: CSCI/CMPE 4341 Topic: Programming in Python Chapter 10: Database Application Programming Interface Xiang Lian The University of Texas – Pan American Edinburg,](https://reader035.vdocuments.us/reader035/viewer/2022062217/56649e8f5503460f94b9295e/html5/thumbnails/17.jpg)
17
Structured Query Language (SQL)
SQL keyword DescriptionSELECT 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.WHERE Specifies criteria that determine the rows to be retrieved.INNER JOIN Joins records from multiple tables to produce a single set of
records.GROUP BY Specifies criteria for grouping records.ORDER BY Specifies criteria for ordering records.INSERT Inserts data into a specified table.UPDATE Updates data in a specified table.DELETE Deletes data from a specified table.Fig. 17.12 SQL query keywords.
![Page 18: CSCI/CMPE 4341 Topic: Programming in Python Chapter 10: Database Application Programming Interface Xiang Lian The University of Texas – Pan American Edinburg,](https://reader035.vdocuments.us/reader035/viewer/2022062217/56649e8f5503460f94b9295e/html5/thumbnails/18.jpg)
SQL on Books Database
• SELECT * FROM tableName– SELECT * FROM Authors
– SELECT AuthorID, LastName FROM Authors
• SELECT columnName1, columnName2, … FROM tableName WHERE criteria– SELECT Title, EditionNumber, Copyright
FROM Titles
WHERE Copyright > '2014'
18
![Page 19: CSCI/CMPE 4341 Topic: Programming in Python Chapter 10: Database Application Programming Interface Xiang Lian The University of Texas – Pan American Edinburg,](https://reader035.vdocuments.us/reader035/viewer/2022062217/56649e8f5503460f94b9295e/html5/thumbnails/19.jpg)
SQL on Books Database (cont'd)
• Operator LIKE is used for pattern matching– Wildcard character
• Percent (%): zero or more characters
• Underscore (_): a single wildcard character
– SELECT AuthorID, FirstName, LastName
FROM Authors
WHERE LastName LIKE 'D%'
– SELECT AuthorID, FirstName, LastName
FROM Authors
WHERE LastName LIKE '_y%'
19
Deitel
Ayer
![Page 20: CSCI/CMPE 4341 Topic: Programming in Python Chapter 10: Database Application Programming Interface Xiang Lian The University of Texas – Pan American Edinburg,](https://reader035.vdocuments.us/reader035/viewer/2022062217/56649e8f5503460f94b9295e/html5/thumbnails/20.jpg)
SQL on Books Database (cont'd)
• SELECT columnName1, columnName2, … FROM tableName ORDER BY column ASC– ASC– DESC– SELECT AuthorID, FirstName, LastName FROM Authors ORDER BY LastName DESC
• Other statements in SQL– table1 INNER JOIN table2 ON table1.columnName=table2.columnName– INSERT INTO tableName (columnName1, columnName2, … ) VALUES (value1,
value2, …)– UPDATE tableName SET columnName1 = value1, columnName2 = value2, …
WHERE criteria– DELETE FROM tableName WHERE criteria
20
![Page 21: CSCI/CMPE 4341 Topic: Programming in Python Chapter 10: Database Application Programming Interface Xiang Lian The University of Texas – Pan American Edinburg,](https://reader035.vdocuments.us/reader035/viewer/2022062217/56649e8f5503460f94b9295e/html5/thumbnails/21.jpg)
2002 Prentice Hall.All rights reserved.
Outline21
Fig. 17.22 TitleAuthor query of Books database
1 SELECT Titles.Title, Titles.ISBN, Authors.FirstName, 2 Authors.LastName, Titles.Copyright, 3 Publishers.PublisherName4 FROM 5 ( Publishers INNER JOIN Titles 6 ON Publishers.PublisherID = Titles.PublisherID ) 7 INNER JOIN 8 ( Authors INNER JOIN AuthorISBN 9 ON Authors.AuthorID = AuthorISBN.AuthorID ) 10 ON Titles.ISBN = AuthorISBN.ISBN11 ORDER BY Titles.Title
![Page 22: CSCI/CMPE 4341 Topic: Programming in Python Chapter 10: Database Application Programming Interface Xiang Lian The University of Texas – Pan American Edinburg,](https://reader035.vdocuments.us/reader035/viewer/2022062217/56649e8f5503460f94b9295e/html5/thumbnails/22.jpg)
22
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
![Page 23: CSCI/CMPE 4341 Topic: Programming in Python Chapter 10: Database Application Programming Interface Xiang Lian The University of Texas – Pan American Edinburg,](https://reader035.vdocuments.us/reader035/viewer/2022062217/56649e8f5503460f94b9295e/html5/thumbnails/23.jpg)
Pypyodbc: https://code.google.com/p/pypyodbc/
import pypyodbc
pypyodbc.win_create_mdb('C:\\Python34\\Books.mdb')
connection_string = 'Driver={Microsoft Access Driver (*.mdb)};DBQ= C:\\Python34\\Books.mdb'
connection = pypyodbc.connect(connection_string)
SQL = 'CREATE TABLE saleout (id COUNTER PRIMARY KEY,product_name VARCHAR(25));'
connection.cursor().execute(SQL).commit()
23
![Page 24: CSCI/CMPE 4341 Topic: Programming in Python Chapter 10: Database Application Programming Interface Xiang Lian The University of Texas – Pan American Edinburg,](https://reader035.vdocuments.us/reader035/viewer/2022062217/56649e8f5503460f94b9295e/html5/thumbnails/24.jpg)
Example of Using pyodbc Packagehttp://en.wikibooks.org/wiki/Python_Programming/Dat
abase_Programming
import pyodbc DBfile = '/data/MSAccess/Music_Library.mdb'conn = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb)};DBQ='+DBfile)#use below conn if using with Access 2007, 2010 .accdb file#conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ='+DBfile)cursor = conn.cursor() SQL = 'SELECT Artist, AlbumName FROM RecordCollection ORDER BY Year;'for row in cursor.execute(SQL): # cursors are iterable print row.Artist, row.AlbumName # print row # if print row it will return tuple of all fields cursor.close()conn.close()
24
![Page 25: CSCI/CMPE 4341 Topic: Programming in Python Chapter 10: Database Application Programming Interface Xiang Lian The University of Texas – Pan American Edinburg,](https://reader035.vdocuments.us/reader035/viewer/2022062217/56649e8f5503460f94b9295e/html5/thumbnails/25.jpg)
25
Database Query Example
• Presents a CGI program that performs a simple query on the Books database and displays result set in an XHTML table
![Page 26: CSCI/CMPE 4341 Topic: Programming in Python Chapter 10: Database Application Programming Interface Xiang Lian The University of Texas – Pan American Edinburg,](https://reader035.vdocuments.us/reader035/viewer/2022062217/56649e8f5503460f94b9295e/html5/thumbnails/26.jpg)
2002 Prentice Hall.All rights reserved.
Outline26
fig17_27.py
#!c:\python\python.exe# Fig. 17.27: fig17_27.py# Displays contents of the Authors table,# ordered by a specified field. import MySQLdb # for Python 2.X, MySQLdb does not support Python 3import cgiimport 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 user query specificationsform = cgi.FieldStorage() # get "sortBy" valueif form.has_key( "sortBy" ):
sortBy = form[ "sortBy" ].valueelse:
sortBy = "firstName" # get "sortOrder" valueif form.has_key( "sortOrder" ):
sortOrder = form[ "sortOrder" ].valueelse:
Contains classes and functions for manipulating MySQL databases
Obtain form data
Get “sort by” value for ORDER BY
Get sorting order for ORDER BY
![Page 27: CSCI/CMPE 4341 Topic: Programming in Python Chapter 10: Database Application Programming Interface Xiang Lian The University of Texas – Pan American Edinburg,](https://reader035.vdocuments.us/reader035/viewer/2022062217/56649e8f5503460f94b9295e/html5/thumbnails/27.jpg)
2002 Prentice Hall.All rights reserved.
Outline27
fig17_27.py
sortOrder = "ASC" printHeader( "Authors table from Books" ) # connect to database and retrieve a cursortry:
connection = MySQLdb.connect( db = "Books" )
# error connecting to databaseexcept MySQLdb.OperationalError, error:
print ("Error:", error)sys.exit( 1 )
# retrieve cursorelse:
cursor = connection.cursor() # query all records from Authors tablecursor.execute( "SELECT * FROM Authors ORDER BY %s %s" %
( sortBy, sortOrder ) ) allFields = cursor.description # get field namesallRecords = cursor.fetchall() # get records # close cursor and connectioncursor.close()connection.close() # output results in a tableprint ("""\n<table border = "1" cellpadding = "3" > <tr bgcolor = "silver" >""") # create table headerfor field in allFields:
print ("<td>%s</td>" % field[ 0 ] )
Create Connection object to manage connectionSpecify database as value of keyword db
MySQLdb.connect failure raises MySQLdb.OperationalError exception
Create Cursor object
Execute query against database
Attribute description contains information about fieldsObtain all records
Close Cursor objectClose Connection object
Output results in table
![Page 28: CSCI/CMPE 4341 Topic: Programming in Python Chapter 10: Database Application Programming Interface Xiang Lian The University of Texas – Pan American Edinburg,](https://reader035.vdocuments.us/reader035/viewer/2022062217/56649e8f5503460f94b9295e/html5/thumbnails/28.jpg)
2002 Prentice Hall.All rights reserved.
Outline28
fig17_27.py
print ("</tr>") # display each record as a rowfor author in allRecords:
print ("<tr>")
for item in author:print ("<td>%s</td>" % item)
print ("</tr>")
print ("</table>" ) # obtain sorting method from userprint ("""
\n<form method = "post" action = "/cgi-bin/fig17_27.py">Sort By:<br />""" )
# display sorting optionsfor 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>""" )
Display each record as a table row
Print form to obtain sorting information from user
![Page 29: CSCI/CMPE 4341 Topic: Programming in Python Chapter 10: Database Application Programming Interface Xiang Lian The University of Texas – Pan American Edinburg,](https://reader035.vdocuments.us/reader035/viewer/2022062217/56649e8f5503460f94b9295e/html5/thumbnails/29.jpg)
29