Database Programming Joan Boone jpboone@email.unc.edu Summer 2020 - - PowerPoint PPT Presentation

Slide 1

Database Programming

Joan Boone

jpboone@email.unc.edu

Summer 2020

INLS 560

Programming for Information Professionals

Slide 2

Topics

Part 1

• Database overview
• Basic SQL

Part 2

• SQLite introduction
• DB Browser for SQLite
• Python database support

Part 3

• Example: Restaurant database
• Database exceptions
• List and sort restaurant database

Part 4

• Searching the restaurant database

Part 5

• Adding a GUI to restaurant apps

Slide 3

Varying Degrees of Structured Content

Unstructured text: e-book plain text format “Somewhat structured” text: web logs with fixed format Structured text: XML and JSON

<employees> <employee> <firstName>John</firstName><lastName>Doe</lastName> </employee> <employee> <firstName>Anna</firstName><lastName>Smith</lastName> </employee> <employee> <firstName>Peter</firstName><lastName>Jones</lastName> </employee> </employees>

{"employees":[ {"firstName":"John", "lastName":"Doe"}, {"firstName":"Anna", "lastName":"Smith"}, {"firstName":"Peter", "lastName":"Jones"} ]}

Slide 4

Very Structured Text Example COMPANY Database

Source: Introduction to Database Systems by Elmasri and Navathe

Slide 5

Why Databases (vs. files) ?

• Data sharing and multi-user transactions (concurrency control)
• Restrict unauthorized access
• Efficient query processing
• Backup and recovery support
• Multiple user interfaces (SQL, APIs, GUIs)
• Represent complex relationships and enforce integrity constraints
• Scalability, and economies of scale

When files may be a good choice

• Simple, well-defined data that doesn't change often
• Single user access
• DBMS investment is too high (hardware, software, training, support

and maintenance costs)

Slide 6

Basic SQL

Data Definition Language (DDL)

• Used to create database tables with attributes (or fields) and

their data types using the CREATE TABLE statement

• Attribute data types

– Character/text: VARCHAR, CHAR – Numeric: INT, DECIMAL – DATE, DATETIME – BOOLEAN

Data Manipulation Language (DML)

• Used to define retrieval queries with a SELECT statement
• Used to modify database contents with the INSERT, DELETE,

and UPDATE statements

Slide 7

SQL CREATE TABLE

CREATE TABLE EMPLOYEE (Fname VARCHAR(15) NOT NULL, Minit CHAR, Lname VARCHAR(15) NOT NULL, Ssn CHAR(9) NOT NULL, Bdate DATE, Address VARCHAR(30), Sex CHAR, Salary DECIMAL(10,2), Super_ssn CHAR(9), Dno INT NOT NULL, PRIMARY KEY (Ssn), FOREIGN KEY (Super_ssn) REFERENCES EMPLOYEE(Ssn), FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber));

Slide 8

SQL CREATE TABLE

CREATE TABLE EMPLOYEE (Fname VARCHAR(15) NOT NULL, Minit CHAR, Lname VARCHAR(15) NOT NULL, Ssn CHAR(9) NOT NULL, Bdate DATE, Address VARCHAR(30), Sex CHAR, Salary DECIMAL(10,2), Super_ssn CHAR(9), Dno INT NOT NULL, PRIMARY KEY (Ssn), FOREIGN KEY (Super_ssn) REFERENCES EMPLOYEE(Ssn), FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber)); CREATE TABLE DEPARTMENT (Dname VARCHAR(15) NOT NULL, Dnumber INT NOT NULL, Mgr_ssn CHAR(9) NOT NULL, Mgr_start_date DATE, PRIMARY KEY (Dnumber), UNIQUE (Dname), FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(Ssn)); DEPARTMENT EMPLOYEE

Slide 9

Very Basic Retrieval in SQL

How to retrieve all rows and fields in a given table: Syntax: SELECT * FROM tablename

SELECT * FROM EMPLOYEE

SELECT * FROM DEPARTMENT SELECT * FROM DEPT_LOCATIONS SELECT * FROM PROJECT SELECT * FROM WORKS_ON SELECT * FROM DEPENDENT

SELECT * is the simplest SQL query for retrieval

Slide 10

Other SQL Queries

List names of employees in department 5

SELECT Fname, Lname FROM EMPLOYEE WHERE Dno=5

List names of employees in Administration department

SELECT Fname, Lname, Address FROM EMPLOYEE, DEPARTMENT WHERE Dname='Administration' AND Dnumber=Dno

Insert a record for the new Sales department

INSERT INTO DEPARTMENT

VALUES ('Sales', 3, '8886655555', '1985-06-30')

Delete an employee

DELETE FROM EMPLOYEE WHERE Ssn='123456789'

Slide 11

Topics

Part 1

• Database overview
• Basic SQL

Part 2

• SQLite introduction
• DB Browser for SQLite
• Python database support

Part 3

• Example: Restaurant database
• Database exceptions
• List and sort restaurant database

Part 4

• Searching the restaurant database

Part 5

• Adding a GUI to restaurant apps

Slide 12

SQLite Introduction

• Features

– Self-contained, full-featured SQL database engine – Compact, portable,and reliable – Serverless, with zero-configuration – Most widely deployed and used database engine

• Well-known users
• SQLite3 is included with Python, so you do not need to

download and install anything

• SQLite has a command line interface, but a graphical user

interface is an easier approach for creating, searching, and editing SQLite databases

– Recommended: DB Browser for SQLite

Slide 13

Using DB Browser for SQLite

with COMPANY database

• Download company.db from the course website to your

PyCharm project directory

• Open DB Browser for SQLite, select 'Open Database', and

navigate to the directory where you downloaded the database

Slide 14

Using DB Browser for SQLite

Browsing EMPLOYEE data

Slide 15

Using DB Browser for SQLite

Executing SQL

SELECT Fname, Lname FROM EMPLOYEE WHERE Dno=5

Enter SQL Click to execute SQL Results

Slide 16

Python Database Support

PEP 249 – Python Database API Specification v2.0

• Purpose: to encourage consistent interfaces among the

modules that are used to access databases

– More easily understood modules – More portable code across databases – Broader reach of database connectivity from Python

• Important elements of the API

– Connection Objects – Cursor Objects – Exceptions

Note: many other database interfaces available for Python

Slide 17

Python Database API Architecture

MySQL module MS Access module Oracle module sqlite3 module MySQL Oracle MS Access sqlite3 Python Application Connection Objects Cursor Objects Row Objects Exceptions

Database API

Slide 18

Python Database API

• Connection Objects

– Manage the connection to the database from your program – Create and close a connection – Support commit and rollback of transactions

• Cursor Objects

– Represent a database cursor which manages the context of a

fetch operation

– Use to prepare and execute a database operation

• Exceptions

– Raised for any database error – Complete list of Exceptions

Slide 19

Topics

Part 1

• Database overview
• Basic SQL

Part 2

• SQLite introduction
• DB Browser for SQLite
• Python database support

Part 3

• Example: Restaurant database
• Database exceptions
• List and sort restaurant database

Part 4

• Searching the restaurant database

Part 5

• Adding a GUI to restaurant apps

Slide 20

Simple Database Example

import sqlite3 def main(): dbname = 'company.db' # opens the database and returns a database object db = sqlite3.connect(dbname) # returns a cursor through which SQL queries can be executed cursor = db.cursor() # prepares and executes a database operation cursor.execute('SELECT * FROM EMPLOYEE') # fetch all query results and return a list of tuples records = cursor.fetchall() # loop through results and print each record for rec in records: print(rec) db.close() # close the database main() employee_list.py, company.db

Slide 21

Restaurants Database

CREATE TABLE 'RESTAURANT' ( 'ID' VARCHAR ( 5 ) NOT NULL, 'Name' VARCHAR ( 50 ) NOT NULL, 'City' VARCHAR ( 20 ) NOT NULL, 'State' CHAR ( 2 ) NOT NULL, 'Cuisine' VARCHAR ( 20 ) NOT NULL, 'Description' VARCHAR, PRIMARY KEY('ID') );

restaurants.db SQL DDL

Slide 22

Example: List Restaurants

• Connect to the restaurants.db
• Execute the SQL query to list the restaurants
• Print the Name, City, State, and Cuisine for each restaurant
• Include database exception handlers

Sotto Sotto Atlanta GA Italian Umi Atlanta GA Japanese Toppers Nantucket MA American The Chanticleer Nantucket MA French Watermark Restaurant Nashville TN American Gary Danko San Francisco CA French Manresa Los Gatos CA American Nishino Seattle WA Japanese The Herbfarm Seattle WA American Fiamma Charlotte NC Italian Copper Charlotte NC Indian Carpe Diem Charlotte NC American Keystone Ranch Denver CO American Pablos Pizza Fort Collins CO Pizza Amu Boulder CO Japanese Sorellina Boston MA Italian Top of the Hill Chapel Hill NC American Geronimo Santa Fe NM Southwestern Mistral Boston MA Mediterranean Chef & the Farmer Kinston NC American Hillsborough BBQ Hillsborough AL American Herons Cary NC American Evvia Palo Alto CA Greek

Slide 23

Database Exceptions

• Exceptions defined by the

Python Database API

– Exception inheritance layout – 'Should be' implemented by

sqlite3

– Use Error as the 'catch all'

sqlite3 exception handler

Slide 24

List Restaurants, including Exception Handlers

# List the restaurants from the RESTAURANT table in RESTAURANTS database import sqlite3 import os def main(): try: dbname = 'restaurants.db' if os.path.exists(dbname): # does this database file exist? db = sqlite3.connect(dbname) # connect to the database cursor = db.cursor() # returns a cursor through which SQL queries can be executed cursor.execute('SELECT * FROM RESTAURANT') # SQL query for all restaurants # fetch all of the results of this query and return a list of tuples records = cursor.fetchall() # loop through the records retrieved by query and print selected contents from each record for rec in records: # print(rec[1], rec[2], rec[3], rec[4]) print(format(rec[1], '<25'), format(rec[2], '<15'), format(rec[3], '<5'), format(rec[4], '<20')) db.close() # close the database else: print('Error:', dbname, 'does not exist') except sqlite3.IntegrityError as err: print('Integrity Error:', err) except sqlite3.OperationalError as err: print('Operational Error:', err) except sqlite3.Error as err: print('Error:', err) main()

restaurant_list.py

Slide 25

Exercise: Sort the Restaurant List

By Restaurant Name

Amu Boulder CO Japanese Carpe Diem Charlotte NC American Chef & the Farmer Kinston NC American Copper Charlotte NC Indian Evvia Palo Alto CA Greek Fiamma Charlotte NC Italian Gary Danko San Francisco CA French Geronimo Santa Fe NM Southwestern Herons Cary NC American Hillsborough BBQ Hillsborough AL American Keystone Ranch Denver CO American Manresa Los Gatos CA American Mistral Boston MA Mediterranean Nishino Seattle WA Japanese Pablos Pizza Fort Collins CO Pizza Sorellina Boston MA Italian Sotto Sotto Atlanta GA Italian The Chanticleer Nantucket MA French The Herbfarm Seattle WA American Top of the Hill Chapel Hill NC American Toppers Nantucket MA American Umi Atlanta GA Japanese Watermark Restaurant Nashville TN American Carpe Diem Charlotte NC American Chef & the Farmer Kinston NC American Herons Cary NC American Hillsborough BBQ Hillsborough AL American Keystone Ranch Denver CO American Manresa Los Gatos CA American The Herbfarm Seattle WA American Top of the Hill Chapel Hill NC American Toppers Nantucket MA American Watermark Restaurant Nashville TN American Gary Danko San Francisco CA French The Chanticleer Nantucket MA French Evvia Palo Alto CA Greek Copper Charlotte NC Indian Sorellina Boston MA Italian Sotto Sotto Atlanta GA Italian Fiamma Charlotte NC Italian Amu Boulder CO Japanese Nishino Seattle WA Japanese Umi Atlanta GA Japanese Mistral Boston MA Mediterranean Pablos Pizza Fort Collins CO Pizza Geronimo Santa Fe NM Southwestern

By Cuisine and Name

w3schools: SQL ORDER BY Keyword

Slide 26

Topics

Part 1

• Database overview
• Basic SQL

Part 2

• SQLite introduction
• DB Browser for SQLite
• Python database support

Part 3

• Example: Restaurant database
• Database exceptions
• List and sort restaurant database

Part 4

• Searching the restaurant database

Part 5

• Adding a GUI to restaurant apps

Slide 27

Search Restaurants by Column

Search the restaurants for those with a specific value for a column, e.g., list the restaurants with American cuisine

Carpe Diem Charlotte NC American Chef & the Farmer Kinston NC American Herons Cary NC American Hillsborough BBQ Hillsborough AL American Keystone Ranch Denver CO American Manresa Los Gatos CA American The Herbfarm Seattle WA American Top of the Hill Chapel Hill NC American Toppers Nantucket MA American Watermark Restaurant Nashville TN American

w3schools: SQL WHERE Clause SELECT * FROM RESTAURANT WHERE Cuisine='American' ORDER BY Name

SQL WHERE clause allows you to specify a condition that must be met when selecting records from a database table

Column name Column value (must be in quotes)

Slide 28

Search Restaurants by Cuisine

where user specifies the type of cuisine

• Search for a restaurant by cuisine based on user input

Enter Cuisine: Italian

• main() function

–

Connects to the database

–

Prompts user for restaurant cuisine (saves as search_value)

–

Calls search_restaurants function to search the database

–

Closes the database

• search_restaurants(search_value) function

–

Obtains database cursor

–

Creates SQL query

–

Fetches all records

–

Prints results (if any)

restaurant_search.py

Fiamma Charlotte NC Italian Sorellina Boston MA Italian Sotto Sotto Atlanta GA Italian

SELECT * FROM RESTAURANT WHERE Cuisine='Italian'

Slide 29

Search Restaurants by Cuisine

where user specifies the type of cuisine

Exercise: How to make the search case-insensitive?

Enter Cuisine: Italian Enter Cuisine: italian

Fiamma Charlotte NC Italian Sorellina Boston MA Italian Sotto Sotto Atlanta GA Italian There are no restaurants with 'italian' cuisine

Enter Cuisine: italian

Fiamma Charlotte NC Italian Sorellina Boston MA Italian Sotto Sotto Atlanta GA Italian

Slide 30

Search Restaurants by Column

List the restaurants with American cuisine in North Carolina

Carpe Diem Charlotte NC American Chef & the Farmer Kinston NC American Herons Cary NC American Top of the Hill Chapel Hill NC American

w3schools: SQL WHERE Clause

Exercise: How do you change the SQL WHERE clause to specify multiple conditions when selecting records from a database table?

Slide 31

'Wildcard' Search of Restaurants

Search the restaurants for those with a 'wildcard' value for a column, e.g., list the restaurants with a Name containing 'farm'

Chef & the Farmer Kinston NC American The Herbfarm Seattle WA American

w3schools: SQL LIKE Operator Enter Restaurant Name: farm SELECT * FROM RESTAURANT WHERE Name LIKE '%farm%' ORDER BY Name

SQL LIKE operator allows you to search for a specified pattern in a column value.

Pattern for the column value, enclosed in quotes. % represents zero, one, or multiple characters. restaurant_search_LIKE.py

Slide 32

'Wildcard' Search using SQL LIKE with a user-specified column

Search the restaurants on a user-specified column and a user-specified 'wildcard' value for the column. For example, list the restaurants with a City containing 'san'

Gary Danko San Francisco CA French Geronimo Santa Fe NM Southwestern

w3schools: SQL LIKE Operator Enter search column: city Enter name: san SELECT * FROM RESTAURANT WHERE city LIKE '%san%' ORDER BY Name

Prompt the user for the column name and the column value

Pattern for the column value, enclosed in quotes. % represents zero, one, or multiple characters. restaurant_search_ColValue_LIKE.py

Slide 33

'Wildcard' Search using SQL LIKE to search across all columns

Prompt for wildcard value to search across all columns

Enter Restaurant search term: nc

Carpe Diem Charlotte NC American Chef & the Farmer Kinston NC American Copper Charlotte NC Indian Fiamma Charlotte NC Italian Gary Danko San Francisco CA French Herons Cary NC American Hillsborough BBQ Hillsborough NC American Keystone Ranch Denver CO American The Chanticleer Nantucket MA French Top of the Hill Chapel Hill NC American

Results

Slide 34

Using the OR operator to search across all columns

Recall the Python OR operator

if temperature < 20 or temperature > 100:

print('The temperature is too extreme')

SQL OR operator will select a record if any of the conditions separated by the OR operator are true

SELECT * FROM Customers

WHERE City='Berlin' OR City='München' w3schools SQL OR operator

Slide 35

'Wildcard' Search using SQL LIKE to search across all columns

Enter Restaurant search term: nc

Carpe Diem Charlotte NC American Chef & the Farmer Kinston NC American Copper Charlotte NC Indian Fiamma Charlotte NC Italian Gary Danko San Francisco CA French Herons Cary NC American Hillsborough BBQ Hillsborough NC American Keystone Ranch Denver CO American The Chanticleer Nantucket MA French Top of the Hill Chapel Hill NC American

SQL Query

SELECT * FROM RESTAURANT WHERE Name LIKE '%nc%' OR City LIKE '%nc%' OR State LIKE '%nc%' OR Cuisine LIKE '%nc%' ORDER BY Name Exercise: modify one of the restaurant examples to search across all columns

Slide 36

Topics

Part 1

• Database overview
• Basic SQL

Part 2

• SQLite introduction
• DB Browser for SQLite
• Python database support

Part 3

• Example: Restaurant database
• Database exceptions
• List and sort restaurant database

Part 4

• Searching the restaurant database

Part 5

• Adding a GUI to restaurant apps

Slide 37

Tkinter Grid Geometry Manager

The Grid Geometry Manager places widgets in a 2-dimensional table. A widget's position is defined by row and column.

Reference: The Tkinter Grid Geometry Manager

self.main_window = tkinter.Tk() self.main_window.title("Simple Grid") self.main_window.geometry('250x70') tkinter.Label(self.main_window, text="First").grid(row=0, sticky=tkinter.constants.W) tkinter.Label(self.main_window, text="Second").grid(row=1, sticky=tkinter.constants.W) tkinter.Entry(self.main_window).grid(row=0, column=1) tkinter.Entry(self.main_window).grid(row=1, column=1)

row 0 row 1 column 1 column 0

Widgets are center- positioned by default. You can use the sticky

• ption to change the

alignment: N, S, E, W. For example, W is west,

• r left-aligned.

Slide 38

Using Grid Layout for Restaurants Database: defining column headers

tkinter.Label(self.main_window, text='Name').grid(row=0, column=0, sticky=tkinter.constants.W) tkinter.Label(self.main_window, text='City').grid(row=0, column=1, sticky=tkinter.constants.W) tkinter.Label(self.main_window, text='State').grid(row=0, column=2, sticky=tkinter.constants.W) tkinter.Label(self.main_window, text='Cuisine').grid(row=0, column=3, sticky=tkinter.constants.W)

4 Label widgets for each column, located in row 0

Slide 39

Using Grid Layout: adding data rows

for record in records: tkinter.Label(self.main_window, text=”??”).grid(row=?, column=0, sticky=tkinter.constants.W) tkinter.Label(self.main_window, text=”??”).grid(row=?, column=1, sticky=tkinter.constants.W) tkinter.Label(self.main_window, text=”??”).grid(row=?, column=2, sticky=tkinter.constants.W) tkinter.Label(self.main_window, text=”??”).grid(row=?, column=3, sticky=tkinter.constants.W)

Defines Label widgets for each column value, for as many rows as are fetched by the SQL query

restaurant_list_GUI.py