CSCICMPE 4341 Topic Programming in Python Chapter 10

CSCI/CMPE 4341 Topic: Programming in Python Chapter 10: Database Application Programming Interface Xiang Lian The University of Texas – Pan American Edinburg, TX 78539 lianx@utpa. edu 1

Objectives • In this chapter, you will: – Understand relational database model – Learn basic database SQL queries – Use packages to create and query a database 2

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 DB 2 3

Introduction (cont'd) • Postgre. SQL and My. SQL are popular opensource 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

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

Example of Table: Employees 6

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

SQL Results 8

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

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. , Author. ISBN 10

Example of Relational Database: Books Database • Books database has four tables: Authors, Publishers, Author. ISBN 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 • Author. ISBN 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 11

Example of ER Diagram Authors Author. ID First. Name 1 Author. ISBN • 1 Author. ID ISBN • Titles ISBN Title Last. Name Edition. Number Copyright Publishers Publisher. ID Publisher. Name • 1 Publisher. ID Image. File Price 12

Authors Table Field Author. ID First. Name Description 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 Author. ID value, ensuring that each record has a unique Author. ID. This field is the table’s primary key. Author’s first name (a string). Last. Name Author’s last name (a string). Fig. 17. 3 Authors table from Books. Author. ID First. Name Last. Name 1 Harvey Deitel 2 Paul Deitel 3 Tem Nieto 4 Kate Steinbuhler 5 Sean Santry 6 Ted Lin 7 Praveen Sadhu 8 David Mc. Phie 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. 13

Publishers Table Field Publisher. ID Description The publisher’s ID number in the database. This autoincremented int field is the table’s primary-key field. Publisher. Name The name of the publisher (a string). Fig. 17. 5 Publishers table from Books. Publisher. ID Publisher. Name 1 Prentice Hall 2 Prentice Hall PTG Fig. 17. 6 Data from the Publishers table of Books. 14

Author. ISBN Table Field Author. ID Description 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 Author. ISBN table from Books. Author. ID ISBN 1 1 0130895725 0132261197 1 1 0130284181 0130895601 1 0130895717 2 0130895725 1 0135289106 2 0132261197 1 0139163050 2 0130895717 1 013028419 x 2 0135289106 1 0130161438 2 0139163050 1 0130856118 2 013028419 x 1 0130125075 2 0130161438 1 0138993947 2 0130856118 1 0130852473 2 0130125075 1 0130829277 2 0138993947 1 0134569555 2 0130852473 15

Field ISBN Title Edition. Number Copyright Publisher. ID Titles Table Description ISBN number of the book (a string). 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. Image. File 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 Editio Publish Copynright Number er. ID Image. File Price 0130923613 Python How to Program 1 1 2002 python. jpg $69. 95 0130622214 0130341517 C# How to Program Java How to Program 1 4 1 1 2002 cshtp. jpg jhtp 4. jpg $69. 95 0130649341 The Complete Java Training Course 4 2 2002 javactc 4. jpg $109. 95 0130895601 Advanced Java 2 Platform How to Program 1 1 2002 advjhtp 1. jpg $69. 95 0130308978 Internet and World Wide Web How to Program Visual Basic. NET How to Program 2 1 2002 iw 3 htp 2. jpg $69. 95 2 1 2002 vbnet. jpg $69. 95 2001 cppctc 3. jpg $109. 95 0130293636 0130895636 The Complete C++ Training Course 3 2 16

Structured Query Language (SQL) SQL keyword SELECT FROM Description Selects (retrieves) fields from one or more tables. 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. 17

SQL on Books Database • SELECT * FROM table. Name – SELECT * FROM Authors – SELECT Author. ID, Last. Name FROM Authors • SELECT column. Name 1, column. Name 2, … FROM table. Name WHERE criteria – SELECT Title, Edition. Number, Copyright FROM Titles WHERE Copyright > '2014' 18

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 Author. ID, First. Name, Last. Name FROM Authors WHERE Last. Name LIKE 'D%' – SELECT Author. ID, First. Name, Last. Name FROM Authors WHERE Last. Name LIKE '_y%' Deitel Ayer 19

SQL on Books Database (cont'd) • SELECT column. Name 1, column. Name 2, … FROM table. Name ORDER BY column ASC – DESC – SELECT Author. ID, First. Name, Last. Name FROM Authors ORDER BY Last. Name DESC • Other statements in SQL – table 1 INNER JOIN table 2 ON table 1. column. Name=table 2. column. Name – INSERT INTO table. Name (column. Name 1, column. Name 2, … ) VALUES (value 1, value 2, …) – UPDATE table. Name SET column. Name 1 = value 1, column. Name 2 = value 2, … WHERE criteria – DELETE FROM table. Name WHERE criteria 20

1 2 3 4 5 6 7 8 9 10 11 SELECT Titles. Title, Titles. ISBN, Authors. First. Name, Authors. Last. Name, Titles. Copyright, Publishers. Publisher. Name FROM ( Publishers INNER JOIN Titles ON Publishers. Publisher. ID = Titles. Publisher. ID ) INNER JOIN ( Authors INNER JOIN Author. ISBN ON Authors. Author. ID = Author. ISBN. Author. ID ) ON Titles. ISBN = Author. ISBN ORDER BY Titles. Title Outline 21 Fig. 17. 22 Title. Author query of Books database 2002 Prentice Hall. All rights reserved.

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 22

Pypyodbc: https: //code. google. com/p/pypyodbc/ import pypyodbc. win_create_mdb('C: \Python 34\Books. mdb') connection_string = 'Driver={Microsoft Access Driver (*. mdb)}; DBQ= C: \Python 34\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

Example of Using pyodbc Package http: //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, Album. Name FROM Record. Collection ORDER BY Year; ' for row in cursor. execute(SQL): # cursors are iterable print row. Artist, row. Album. Name # print row # if print row it will return tuple of all fields cursor. close() conn. close() 24

Database Query Example • Presents a CGI program that performs a simple query on the Books database and displays result set in an XHTML table 25

#!c: python. exe # Fig. 17. 27: fig 17_27. py # Displays contents of the Authors table, Contains classes and functions # ordered by a specified field. import My. SQLdb import cgi import sys Outline for manipulating My. SQL databases fig 17_27. py # for Python 2. X, My. SQLdb does not support Python 3 def print. Header( title ): print ("""Content-type: text/html <? xml version = "1. 0" encoding = "UTF-8"? > <!DOCTYPE html PUBLIC "-//W 3 C//DTD XHTML 1. 0 Transitional//EN" "DTD/xhtml 1 -transitional. dtd"> <html xmlns = "http: //www. w 3. org/1999/xhtml" xml: lang = "en"> <head><title>%s</title></head> <body>""" % title) Obtain form data # obtain user query specifications form = cgi. Field. Storage() Get “sort by” value for ORDER BY # get "sort. By" value if form. has_key( "sort. By" ): sort. By = form[ "sort. By" ]. value else: sort. By = "first. Name" Get sorting order for ORDER BY # get "sort. Order" value if form. has_key( "sort. Order" ): sort. Order = form[ "sort. Order" ]. value else: 2002 Prentice Hall. All rights reserved. 26

Outline sort. Order = "ASC" print. Header( "Authors table from Books" ) fig 17_27. py Connection object to manage connection # connect to database and retrieve. Create a cursor Specify database as value of keyword db try: connection = My. SQLdb. connect( db = "Books" ) My. SQLdb. connect failure raises My. SQLdb. Operational. Error exception # error connecting to database except My. SQLdb. Operational. Error, 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" % ( sort. By, sort. Order ) ) all. Fields = cursor. description all. Records = cursor. fetchall() Attribute contains information about fields Obtain alldescription records # get field names # get records Close Cursor object # close cursor and connection Close Connection cursor. close() connection. close() object # output results in a Output table results in table print ("""n<table border = "1" cellpadding = "3" > <tr bgcolor = "silver" >""" ) # create table header for field in all. Fields: print ("<td>%s</td>" % field[ 0 ] ) 2002 Prentice Hall. All rights reserved. 27

print ("</tr>") Display each record as a table row # display each record as a row for author in all. Records: print ("<tr>") Outline fig 17_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/fig 17_27. py"> Sort By: """ ) # display sorting options for field in all. Fields: print ("""<input type = "radio" name = "sort. By" value = "%s" />""" % field[ 0 ] ) print (field[ 0 ]) print (" " ) print (""" n. Sort Order: <input type = "radio" name = "sort. Order" value = "ASC" checked = "checked" /> Ascending <input type = "radio" name = "sort. Order" value = "DESC" /> Descending n<input type = "submit" value = "SORT" /> </form>nn</body>n</html>""" ) 2002 Prentice Hall. All rights reserved. 28

29