INLS 560 Programming for Information Professionals Database Programming Joan Boone jpboone@email.unc.edu Summer 2020 Slide 1
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 2
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> {"employees":[ </employee> {"firstName":"John", "lastName":"Doe"}, <employee> {"firstName":"Anna", "lastName":"Smith"}, <firstName>Peter</firstName><lastName>Jones</lastName> {"firstName":"Peter", "lastName":"Jones"} </employee> ]} </employees> Slide 3
Very Structured Text Example COMPANY Database Source: Introduction to Database Systems by Elmasri and Navathe Slide 4
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 5
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 6
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 7
SQL CREATE TABLE EMPLOYEE DEPARTMENT CREATE TABLE EMPLOYEE (Fname VARCHAR(15) NOT NULL, CREATE TABLE DEPARTMENT Minit CHAR, (Dname VARCHAR(15) NOT NULL, Lname VARCHAR(15) NOT NULL, Dnumber INT NOT NULL, Ssn CHAR(9) NOT NULL, Mgr_ssn CHAR(9) NOT NULL, Bdate DATE, Mgr_start_date DATE, Address VARCHAR(30), PRIMARY KEY (Dnumber), Sex CHAR, UNIQUE (Dname), Salary DECIMAL(10,2), FOREIGN KEY (Mgr_ssn) REFERENCES Super_ssn CHAR(9), EMPLOYEE(Ssn)); Dno INT NOT NULL, PRIMARY KEY (Ssn), FOREIGN KEY (Super_ssn) REFERENCES EMPLOYEE(Ssn), FOREIGN KEY (Dno) REFERENCES DEPARTMENT(Dnumber)); Slide 8
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 9
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 10
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 11
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 12
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 13
Using DB Browser for SQLite Browsing EMPLOYEE data Slide 14
Using DB Browser for SQLite Executing SQL SELECT Fname, Lname FROM EMPLOYEE WHERE Dno=5 Click to execute SQL Enter SQL Results Slide 15
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 16
Python Database API Architecture Python Application Database API Connection Cursor Row Exceptions Objects Objects Objects MySQL module Oracle module MS Access module sqlite3 module MS Access MySQL Oracle sqlite3 Slide 17
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 18
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 19
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 20
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') ); SQL DDL restaurants.db Slide 21
Recommend
More recommend
