Introd u ction to relational databases IN TR OD U C TION TO IMP OR - PowerPoint PPT Presentation

Introd u ction to relational databases IN TR OD U C TION TO IMP OR TIN G DATA IN P YTH ON H u go Bo w ne - Anderson Data Scientist at DataCamp

What is a relational database ? Based on relational model of data First described b y Edgar “ Ted ” Codd INTRODUCTION TO IMPORTING DATA IN PYTHON

E x ample : North w ind database INTRODUCTION TO IMPORTING DATA IN PYTHON

E x ample : North w ind database INTRODUCTION TO IMPORTING DATA IN PYTHON

E x ample : North w ind database INTRODUCTION TO IMPORTING DATA IN PYTHON

E x ample : North w ind database INTRODUCTION TO IMPORTING DATA IN PYTHON

The Orders table INTRODUCTION TO IMPORTING DATA IN PYTHON

The Orders table INTRODUCTION TO IMPORTING DATA IN PYTHON

The Orders table INTRODUCTION TO IMPORTING DATA IN PYTHON

The Orders table INTRODUCTION TO IMPORTING DATA IN PYTHON

Tables are linked INTRODUCTION TO IMPORTING DATA IN PYTHON

Tables are linked INTRODUCTION TO IMPORTING DATA IN PYTHON

Tables are linked INTRODUCTION TO IMPORTING DATA IN PYTHON

Relational model Widel y adopted Codd ’ s 12 R u les / Commandments Consists of 13 r u les (z ero - inde x ed !) Describes w hat a Relational Database Management S y stem sho u ld adhere to to be considered relational INTRODUCTION TO IMPORTING DATA IN PYTHON

Relational Database Management S y stems INTRODUCTION TO IMPORTING DATA IN PYTHON

Relational Database Management S y stems INTRODUCTION TO IMPORTING DATA IN PYTHON

Relational Database Management S y stems INTRODUCTION TO IMPORTING DATA IN PYTHON

Relational Database Management S y stems INTRODUCTION TO IMPORTING DATA IN PYTHON

Let ' s practice ! IN TR OD U C TION TO IMP OR TIN G DATA IN P YTH ON

Creating a database engine in P y thon IN TR OD U C TION TO IMP OR TIN G DATA IN P YTH ON H u go Bo w ne - Anderson Data Scientist at DataCamp

Creating a database engine SQLite database Fast and simple SQLAlchem y Works w ith man y Relational Database Management S y stems from sqlalchemy import create_engine engine = create_engine('sqlite:///Northwind.sqlite') INTRODUCTION TO IMPORTING DATA IN PYTHON

Getting table names from sqlalchemy import create_engine engine = create_engine('sqlite:///Northwind.sqlite') table_names = engine.table_names() print(table_names) ['Categories', 'Customers', 'EmployeeTerritories', 'Employees', 'Order Details', 'Orders', 'Products', 'Region', 'Shippers', 'Suppliers', 'Territories'] INTRODUCTION TO IMPORTING DATA IN PYTHON

Let ' s practice ! IN TR OD U C TION TO IMP OR TIN G DATA IN P YTH ON

Q u er y ing relational databases in P y thon IN TR OD U C TION TO IMP OR TIN G DATA IN P YTH ON H u go Bo w ne - Anderson Data Scientist at DataCamp

Basic SQL q u er y SELECT * FROM Table_Name Ret u rns all col u mns of all ro w s of the table E x ample : SELECT * FROM Orders We ’ ll u se SQLAlchem y and pandas INTRODUCTION TO IMPORTING DATA IN PYTHON

Workflo w of SQL q u er y ing Import packages and f u nctions Create the database engine Connect to the engine Q u er y the database Sa v e q u er y res u lts to a DataFrame Close the connection INTRODUCTION TO IMPORTING DATA IN PYTHON

Yo u r first SQL q u er y from sqlalchemy import create_engine import pandas as pd engine = create_engine('sqlite:///Northwind.sqlite') con = engine.connect() rs = con.execute("SELECT * FROM Orders") df = pd.DataFrame(rs.fetchall()) con.close() INTRODUCTION TO IMPORTING DATA IN PYTHON

Printing y o u r q u er y res u lts print(df.head()) 0 1 2 3 4 0 10248 VINET 5 7/4/1996 12:00:00 AM 8/1/1996 12:00:00 AM 1 10251 VICTE 3 7/8/1996 12:00:00 AM 8/5/1996 12:00:00 AM 2 10254 CHOPS 5 7/11/1996 12:00:00 AM 8/8/1996 12:00:00 AM 3 10256 WELLI 3 7/15/1996 12:00:00 AM 8/12/1996 12:00:00 AM 4 10258 ERNSH 1 7/17/1996 12:00:00 AM 8/14/1996 12:00:00 AM INTRODUCTION TO IMPORTING DATA IN PYTHON

Set the DataFrame col u mn names from sqlalchemy import create_engine import pandas as pd engine = create_engine('sqlite:///Northwind.sqlite') con = engine.connect() rs = con.execute("SELECT * FROM Orders") df = pd.DataFrame(rs.fetchall()) df.columns = rs.keys() con.close() INTRODUCTION TO IMPORTING DATA IN PYTHON

Set the data frame col u mn names print(df.head()) OrderID CustomerID EmployeeID OrderDate 0 10248 VINET 5 7/4/1996 12:00:00 AM 1 10251 VICTE 3 7/8/1996 12:00:00 AM 2 10254 CHOPS 5 7/11/1996 12:00:00 AM 3 10256 WELLI 3 7/15/1996 12:00:00 AM 4 10258 ERNSH 1 7/17/1996 12:00:00 AM INTRODUCTION TO IMPORTING DATA IN PYTHON

Using the conte x t manager from sqlalchemy import create_engine import pandas as pd engine = create_engine('sqlite:///Northwind.sqlite') with engine.connect() as con: rs = con.execute("SELECT OrderID, OrderDate, ShipName FROM Orders") df = pd.DataFrame(rs.fetchmany(size=5)) df.columns = rs.keys() INTRODUCTION TO IMPORTING DATA IN PYTHON

Let ' s practice ! IN TR OD U C TION TO IMP OR TIN G DATA IN P YTH ON

Q u er y ing relational databases directl y w ith pandas IN TR OD U C TION TO IMP OR TIN G DATA IN P YTH ON H u go Bo w ne - Anderson Data Scientist at DataCamp

The pandas w a y to q u er y from sqlalchemy import create_engine import pandas as pd engine = create_engine('sqlite:///Northwind.sqlite') with engine.connect() as con: rs = con.execute("SELECT * FROM Orders") df = pd.DataFrame(rs.fetchall()) df.columns = rs.keys() df = pd.read_sql_query("SELECT * FROM Orders", engine) INTRODUCTION TO IMPORTING DATA IN PYTHON

Let ' s practice ! IN TR OD U C TION TO IMP OR TIN G DATA IN P YTH ON

Ad v anced q u er y ing : e x ploiting table relationships IN TR OD U C TION TO IMP OR TIN G DATA IN P YTH ON H u go Bo w ne - Anderson Data Scientist at DataCamp

Tables are linked INTRODUCTION TO IMPORTING DATA IN PYTHON

Tables are linked INTRODUCTION TO IMPORTING DATA IN PYTHON

Tables are linked INTRODUCTION TO IMPORTING DATA IN PYTHON

JOINing tables INTRODUCTION TO IMPORTING DATA IN PYTHON

JOINing tables INTRODUCTION TO IMPORTING DATA IN PYTHON

JOINing tables INTRODUCTION TO IMPORTING DATA IN PYTHON

JOINing tables INTRODUCTION TO IMPORTING DATA IN PYTHON

JOINing tables INTRODUCTION TO IMPORTING DATA IN PYTHON

INNER JOIN in P y thon ( pandas ) from sqlalchemy import create_engine import pandas as pd engine = create_engine('sqlite:///Northwind.sqlite') df = pd.read_sql_query("SELECT OrderID, CompanyName FROM Orders INNER JOIN Customers on Orders.CustomerID = Customers.CustomerID", engine) print(df.head()) OrderID CompanyName 0 10248 Vins et alcools Chevalier 1 10251 Victuailles en stock 2 10254 Chop-suey Chinese 3 10256 Wellington Importadora 4 10258 Ernst Handel INTRODUCTION TO IMPORTING DATA IN PYTHON

Let ' s practice ! IN TR OD U C TION TO IMP OR TIN G DATA IN P YTH ON

Final Tho u ghts IN TR OD U C TION TO IMP OR TIN G DATA IN P YTH ON H u go Bo w ne - Anderson Data Scientist at DataCamp

What y o u’v e learned : Relational databases Q u eries SELECT WHERE JOIN INTRODUCTION TO IMPORTING DATA IN PYTHON

Ne x t co u rse : Scrape data from the w eb Interact w ith APIs INTRODUCTION TO IMPORTING DATA IN PYTHON

Let ' s practice ! IN TR OD U C TION TO IMP OR TIN G DATA IN P YTH ON