NCSS: Databases and SQL Tim Dawborn Lecture 2, January, 2016 - PowerPoint PPT Presentation

NCSS: Databases and SQL Tim Dawborn Lecture 2, January, 2016

Python/sqlite3 DB Design API JOINs 2 Outline 1 Connecting to an SQLite database using Python 2 What is a ‘good’ database design? 3 A nice API 4 More on joins and subqueries... Tim Dawborn Databases & SQL Lecture 2, January, 2016

Python/sqlite3 DB Design API JOINs 3 Database Application Programming • Python standard library contains a module for interacting with SQLite databases: sqlite3 • You work primarily with Connection and Cursor objects • The connect method creates a Connection object to a SQLite database >>> import sqlite3 1 >>> conn = sqlite3.connect( ' sports.db ' ) 2 >>> conn 3 <sqlite3.Connection object at 0x312720> 4 >>> 5 • Once you are finished, the connection should be closed >>> conn.close() 6 >>> 7 Tim Dawborn Databases & SQL Lecture 2, January, 2016

Python/sqlite3 DB Design API JOINs 4 Cursor Objects • Cursors are the standard way to interact with a database from within a programming language • Cursor objects allow you to execute a query and iterate through the results of a query • A Connection object allows you to obtain a cursor which points into the database >>> cur = conn.cursor() 1 >>> cur 2 <sqlite3.Cursor object at 0x387b00> 3 >>> 4 Tim Dawborn Databases & SQL Lecture 2, January, 2016

Python/sqlite3 DB Design API JOINs 5 Executing Queries • The cursor has an execute method which allows you to execute one SQL query at a result • The cursor object itself can then be iterated through to obtain the resultant rows >>> cur.execute( ' SELECT * FROM events; ' ) 1 >>> for row in cur: 2 ... print(row) 3 4 (0, ' 100m ' , 16, ' M ' , ' 09:10 ' ) 5 (1, ' 200m ' , 16, ' M ' , ' 09:15 ' ) 6 (2, ' 100m ' , 17, ' M ' , ' 09:00 ' ) 7 (3, ' 100m ' , 17, ' F ' , ' 09:05 ' ) 8 >>> 9 Tim Dawborn Databases & SQL Lecture 2, January, 2016

Python/sqlite3 DB Design API JOINs 6 Executing Queries • The fetchone method returns one row at a result, or None • The previous is equivalent to the following >>> cur.execute( ' SELECT * FROM events; ' ) 1 >>> while True: 2 ... row = cur.fetchone() 3 ... if row is None: 4 ... break 5 ... print(row) 6 7 (0, ' 100m ' , 16, ' M ' , ' 09:10 ' ) 8 (1, ' 200m ' , 16, ' M ' , ' 09:15 ' ) 9 (2, ' 100m ' , 17, ' M ' , ' 09:00 ' ) 10 (3, ' 100m ' , 17, ' F ' , ' 09:05 ' ) 11 >>> 12 Tim Dawborn Databases & SQL Lecture 2, January, 2016

Python/sqlite3 DB Design API JOINs 7 Data Types: Python vs. SQL • We know SQLite has 5 data types • However, Python has a lot more than these five ... • str, int, float, list, dict, set, tuple, ... • How do we coerce Python data types to SQLite and vice-versa? Tim Dawborn Databases & SQL Lecture 2, January, 2016

Python/sqlite3 DB Design API JOINs 7 Data Types: Python vs. SQL • We know SQLite has 5 data types • However, Python has a lot more than these five ... • str, int, float, list, dict, set, tuple, ... • How do we coerce Python data types to SQLite and vice-versa? SQLite Python NULL None INTEGER int REAL float TEXT str BLOB bytes Tim Dawborn Databases & SQL Lecture 2, January, 2016

Python/sqlite3 DB Design API JOINs 8 Joining tables • Why do we need to join tables together? Tim Dawborn Databases & SQL Lecture 2, January, 2016

Python/sqlite3 DB Design API JOINs 8 Joining tables • Why do we need to join tables together? • Why not store all data in just one huge table? Tim Dawborn Databases & SQL Lecture 2, January, 2016

Python/sqlite3 DB Design API JOINs 8 Joining tables • Why do we need to join tables together? • Why not store all data in just one huge table? id fname lname gender age ename eage egender at result 0 Barry Schultz M 16 100m 16 M 09:10 00:15 0 Barry Schultz M 16 200m 16 M 09:15 00:40 1 Prue Robinson F 17 100m 17 F 09:05 00:20 2 Andrew Varvel M 16 100m 16 M 09:10 00:17 3 Mathew Nemes M 13 100m 13 M 09:10 00:20 4 Mara Barber F 17 100m 17 F 09:05 5 Scott Herdman M 16 100m 16 M 09:10 00:21 6 Alec Newton M 16 M 09:10 7 Karen Barber F 14 100m 14 8 Grant Ovzinsky M 17 200m 17 M 09:00 Tim Dawborn Databases & SQL Lecture 2, January, 2016

Python/sqlite3 DB Design API JOINs 8 Joining tables • Why do we need to join tables together? • Why not store all data in just one huge table? id fname lname gender age ename eage egender at result 0 Barry Schultz M 16 100m 16 M 09:10 00:15 0 Barry Schultz M 16 200m 16 M 09:15 00:40 1 Prue Robinson F 17 100m 17 F 09:05 00:20 2 Andrew Varvel M 16 100m 16 M 09:10 00:17 3 Mathew Nemes M 13 100m 13 M 09:10 00:20 4 Mara Barber F 17 100m 17 F 09:05 5 Scott Herdman M 16 100m 16 M 09:10 00:21 6 Alec Newton M 16 M 09:10 7 Karen Barber F 14 100m 14 8 Grant Ovzinsky M 17 200m 17 M 09:00 • Problem: A lot of data is repeated and too many NULLs... Tim Dawborn Databases & SQL Lecture 2, January, 2016

Python/sqlite3 DB Design API JOINs 9 Central goal: minimizing redundancy • Central goal of database design: minimizing data redundancy • There’s a large body of theoretical work on this • Data Normalization — in a nutshell: • Keep a table about data of just one concept. (such as persons or events) • Use foreign keys to link tables in your schema Tim Dawborn Databases & SQL Lecture 2, January, 2016

Python/sqlite3 DB Design API JOINs 9 Central goal: minimizing redundancy • Central goal of database design: minimizing data redundancy • There’s a large body of theoretical work on this • Data Normalization — in a nutshell: • Keep a table about data of just one concept. (such as persons or events) • Use foreign keys to link tables in your schema people results events id event id fname person name lname result age gender gender age at Tim Dawborn Databases & SQL Lecture 2, January, 2016

Python/sqlite3 DB Design API JOINs 10 A well-done database schema • The Good: • each table is about a well defined concept only • updates affect (typically) a single row only • The Bad: • many tables • tendency to introduce unique IDs • a lot of joins... • The Ugly: • it’s not always possible and very experience-driven Tim Dawborn Databases & SQL Lecture 2, January, 2016

Python/sqlite3 DB Design API JOINs 11 Piecing it all together • How are we going to integrate all of this into our NCSSBook? • Model-View-Controller mentality • Loading and writing data to and from a SQLite database • Database is stored in a single flatfile • Ease of setup – no server required • Convenient for version control • Correct data modelling is important Tim Dawborn Databases & SQL Lecture 2, January, 2016

Python/sqlite3 DB Design API JOINs 12 Example: Account table • What do you think about the following users table design? id user pass email school city friends 1 steve 1234 s@my.home St. Mary’s Sydney 2,3 2 elaine abcdef eora@yahoo.com St. Mary’s Sydney 1 3 miranda foobar m123@gmail.com X Public School Glebe 1,4,5 4 jesse 1Two3 jesse@town.org Y State High Townsville 3,5 5 mathew 156ytfv1h8 mat@hotmail.com Y State High Townsville 3,4,6,10 6 tim joshua tim@iinet.net.au Mercedes College Perth 5,10 Tim Dawborn Databases & SQL Lecture 2, January, 2016

Python/sqlite3 DB Design API JOINs 12 Example: Account table • What do you think about the following users table design? id user pass email school city friends 1 steve 1234 s@my.home St. Mary’s Sydney 2,3 2 elaine abcdef eora@yahoo.com St. Mary’s Sydney 1 3 miranda foobar m123@gmail.com X Public School Glebe 1,4,5 4 jesse 1Two3 jesse@town.org Y State High Townsville 3,5 5 mathew 156ytfv1h8 mat@hotmail.com Y State High Townsville 3,4,6,10 6 tim joshua tim@iinet.net.au Mercedes College Perth 5,10 • friends should be a separate table rather than a CSV-string • Better also have schools in a separate table • Unencrypted passwords? Seriously!? Tim Dawborn Databases & SQL Lecture 2, January, 2016

Python/sqlite3 DB Design API JOINs 13 Example: Login code • What do you think about the following login code? import sqlite3 1 def login(username, password): 2 conn = sqlite3.connect( ' ncssbook.db ' ) 3 cur = conn.execute( ' SELECT user, pass FROM users ' ) 4 found = False 5 for row in cur: 6 if row[0] == username and row[1] == password: 7 found = True 8 conn.close() 9 return found 10 Tim Dawborn Databases & SQL Lecture 2, January, 2016