LIS 559 July 7 Week 9

1
LIS 559 July 7 - Week 9

• Summer 2006
• Margaret Kipp
• mkipp_at_uwo.ca
• x88687
• NCB 235

2
Concepts

• Sample DB
• library.mdb
• Querying a Database with SQL
• Commands
• SELECT
• INSERT
• UPDATE
• DELETE
• Database Access with Python
• Modules
• dbi, odbc
• win32com.client
• DAO - Data Access Object
• ADO - ActiveX Data Objects

3
Sample DB

• Sample Database
• http//publish.uwo.ca/mkipp/teaching/library.mdb
• Primitive Library Circulation System
• 3 tables
• User Table
• Number, Name, Telephone, DOB, Address
• Book Table
• Number, CallNo, Title, Author, Subject, Location,
  Comments
• Circulation Table
• Number, UserNo, BookNo, DueDate

4
(No Transcript)
5
Sample DB (cont.)

• Primary Keys (PK)
• Each table has a primary key called Number
• PK must be unique
• Uniquely identifies an entry in the DB
• Relationships
• User and Book Numbers are both used to create a
  row in the Circulation table, in the Circulation
  Table they are foreign keys (primary keys from
  other tables)
• Circulation is a special table which collects
  information about the connection between other
  tables

6
Querying a DB with SQL

• select "Queries"
• double click "Create Query in Design View"

7
Querying with SQL (cont.)

• Close show table window
• Select SQL button from top toolbar

8
Querying with SQL (cont.)

• SELECT FROM User

9
SQL SELECT

• SELECT - Retrieves items from the database
• Syntax
• SELECT ltcolumnsgt FROM lttablegt WHERE ltconditiongt
  ORDER BY ltcolumngt
• ltcolumnsgt represents the column names in the
  table
• lttablegt is the name of the table or tables from
  which we are retrieving data
• ltconditiongt this can be any condition to limit
  the amount of data returned, e.g. gt, lt, , LIKE
• ltcolumngt column name to sort by

10
SELECT Examples

• select all books in the library
• SELECT FROM Book
• select name from the user table
• SELECT Name FROM User
• select the patron with card number 2
• SELECT Name FROM User WHERE Number 2
• select title and author from the book table
• SELECT Title, Author FROM Book

11
SELECT Examples (cont.)

• select book titles that have been lent out
• SELECT Book.Title FROM Book, Circulation WHERE
  Circulation.BookNo Book.Number
• select all users with last name like Robert
• SELECT Name, Telephone FROM User WHERE Name Like
  Robert
• get the total circulation so far
• SELECT COUNT() FROM CIRCULATION
• COUNT() is a special operator that counts the
  number of items in the selection set (i.e. the
  number of rows you get back from select)

12
SELECT Examples (cont.)

• how many patrons does the library have?
• ?
• how many books does the library have?
• ?
• find out the subject of the books that are in
  circulation
• ?
• get a list of all books in the library, sorted by
  call number
• ?

13
SQL INSERT

• INSERT - Insert items into the database
• Syntax
• INSERT INTO lttablegt (column1, column2, column3)
  VALUES (value1, value2, value3)
• lttablegt is the name of the table or tables into
  which are are inserting data
• (column1, column2, column3) columns to match the
  values being inserted
• (value1, value2, value3) the values to be inserted

14
INSERT Examples

• add a new patron to the database
• INSERT INTO User (Name, Telephone, DOB, Address)
  VALUES ('Vincent, Ann', '519-672-3591', '22 Apr
  1957', '14 Meadow Brook Avenue')
• add a new book to the database
• INSERT INTO Book (CallNo, Title, Author, Subject,
  Location, Comments) VALUES ('j597.9', 'Frogs and
  Snakes', 'Taylor, Barbara', 'Amphibians',
  'Childrens', '')

15
INSERT Examples (cont.)

• circulate a book (have patron 4 borrow book 9)
• INSERT INTO Circulation (UserNo, BookNo, DueDate)
  VALUES (4, 9, '21 Jul 2006')

16
Database Access with Python

• Python can be used to programmatically access a
  database
• Some possible actions
• create a connection to the database
• create a cursor to scroll through tables
• select data from tables and print
• Some database modules
• odbc and dbi
• ODBC - Open Database Connectivity
• win32com.client
• specifically for MS Access
• DAO - Data Access Objects
• ADO - ActiveX Data Objects

17
ODBC module

• ODBC Open Database Connectivity
• DBI Database Interface
• Step 1 Import Modules
• import dbi
• import odbc
• Step 2 Connect to database
• conn odbc.odbc('DriverMicrosoft Access Driver
  (.mdb)Dbqlibrary.mdb')

18
ODBC module (cont.)

• Step 3 Create a cursor
• cursor conn.cursor()
• Step 4 Execute a select query
• cursor.execute("SELECT FROM User")
• data cursor.fetchall()
• for item in data
• print item
• Step 5 Close the connection
• conn.close()

19
ODBC module (cont.)

• Step 4 Execute an insert statement
• cursor.execute("INSERT INTO User (Name,
  Telephone, DOB, Address) VALUES ('Troy,
  Alexander', '519-622-3214', '18 Jan 1945', '2-54
  Bellvue Street')")
• Step 5 Commit or rollback the change
• conn.commit()
• OR conn.rollback()

20
Python with SQL

• Now try the SQL SELECT and SQL INSERT examples
  again via Python!
• For the INSERT Examples you will want to change
  the values.
• e.g. try circulating another book (e.g. have
  patron 6 borrow book 9)