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

▶
introd u ction to relational databases
SMART_READER_LITE
LIVE PREVIEW

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

May 23, 2023 117 likes •639 views

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

slide-1
SLIDE 1

Introduction to relational databases

IN TR OD U C TION TO IMP OR TIN G DATA IN P YTH ON

Hugo Bowne-Anderson

Data Scientist at DataCamp

slide-2
SLIDE 2

INTRODUCTION TO IMPORTING DATA IN PYTHON

What is a relational database?

Based on relational model of data First described by Edgar “Ted” Codd

slide-3
SLIDE 3

INTRODUCTION TO IMPORTING DATA IN PYTHON

Example: Northwind database

slide-4
SLIDE 4

INTRODUCTION TO IMPORTING DATA IN PYTHON

Example: Northwind database

slide-5
SLIDE 5

INTRODUCTION TO IMPORTING DATA IN PYTHON

Example: Northwind database

slide-6
SLIDE 6

INTRODUCTION TO IMPORTING DATA IN PYTHON

Example: Northwind database

slide-7
SLIDE 7

INTRODUCTION TO IMPORTING DATA IN PYTHON

The Orders table

slide-8
SLIDE 8

INTRODUCTION TO IMPORTING DATA IN PYTHON

The Orders table

slide-9
SLIDE 9

INTRODUCTION TO IMPORTING DATA IN PYTHON

The Orders table

slide-10
SLIDE 10

INTRODUCTION TO IMPORTING DATA IN PYTHON

The Orders table

slide-11
SLIDE 11

INTRODUCTION TO IMPORTING DATA IN PYTHON

Tables are linked

slide-12
SLIDE 12

INTRODUCTION TO IMPORTING DATA IN PYTHON

Tables are linked

slide-13
SLIDE 13

INTRODUCTION TO IMPORTING DATA IN PYTHON

Tables are linked

slide-14
SLIDE 14

INTRODUCTION TO IMPORTING DATA IN PYTHON

Relational model

Widely adopted Codd’s 12 Rules/Commandments Consists of 13 rules (zero-indexed!) Describes what a Relational Database Management System should adhere to to be considered relational

slide-15
SLIDE 15

INTRODUCTION TO IMPORTING DATA IN PYTHON

Relational Database Management Systems

slide-16
SLIDE 16

INTRODUCTION TO IMPORTING DATA IN PYTHON

Relational Database Management Systems

slide-17
SLIDE 17

INTRODUCTION TO IMPORTING DATA IN PYTHON

Relational Database Management Systems

slide-18
SLIDE 18

INTRODUCTION TO IMPORTING DATA IN PYTHON

Relational Database Management Systems

slide-19
SLIDE 19

Let's practice!

IN TR OD U C TION TO IMP OR TIN G DATA IN P YTH ON

slide-20
SLIDE 20

Creating a database engine in Python

IN TR OD U C TION TO IMP OR TIN G DATA IN P YTH ON

Hugo Bowne-Anderson

Data Scientist at DataCamp

slide-21
SLIDE 21

INTRODUCTION TO IMPORTING DATA IN PYTHON

Creating a database engine

SQLite database Fast and simple SQLAlchemy Works with many Relational Database Management Systems

from sqlalchemy import create_engine engine = create_engine('sqlite:///Northwind.sqlite')

slide-22
SLIDE 22

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']

slide-23
SLIDE 23

Let's practice!

IN TR OD U C TION TO IMP OR TIN G DATA IN P YTH ON

slide-24
SLIDE 24

Querying relational databases in Python

IN TR OD U C TION TO IMP OR TIN G DATA IN P YTH ON

Hugo Bowne-Anderson

Data Scientist at DataCamp

slide-25
SLIDE 25

INTRODUCTION TO IMPORTING DATA IN PYTHON

Basic SQL query

SELECT * FROM Table_Name

Returns all columns of all rows of the table Example:

SELECT * FROM Orders

We’ll use SQLAlchemy and pandas

slide-26
SLIDE 26

INTRODUCTION TO IMPORTING DATA IN PYTHON

Workflow of SQL querying

Import packages and functions Create the database engine Connect to the engine Query the database Save query results to a DataFrame Close the connection

slide-27
SLIDE 27

INTRODUCTION TO IMPORTING DATA IN PYTHON

Your first SQL query

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()

slide-28
SLIDE 28

INTRODUCTION TO IMPORTING DATA IN PYTHON

Printing your query results

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

slide-29
SLIDE 29

INTRODUCTION TO IMPORTING DATA IN PYTHON

Set the DataFrame column 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()

slide-30
SLIDE 30

INTRODUCTION TO IMPORTING DATA IN PYTHON

Set the data frame column 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

slide-31
SLIDE 31

INTRODUCTION TO IMPORTING DATA IN PYTHON

Using the context 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()

slide-32
SLIDE 32

Let's practice!

IN TR OD U C TION TO IMP OR TIN G DATA IN P YTH ON

slide-33
SLIDE 33

Querying relational databases directly with pandas

IN TR OD U C TION TO IMP OR TIN G DATA IN P YTH ON

Hugo Bowne-Anderson

Data Scientist at DataCamp

slide-34
SLIDE 34

INTRODUCTION TO IMPORTING DATA IN PYTHON

The pandas way to query

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)

slide-35
SLIDE 35

Let's practice!

IN TR OD U C TION TO IMP OR TIN G DATA IN P YTH ON

slide-36
SLIDE 36

Advanced querying: exploiting table relationships

IN TR OD U C TION TO IMP OR TIN G DATA IN P YTH ON

Hugo Bowne-Anderson

Data Scientist at DataCamp

slide-37
SLIDE 37

INTRODUCTION TO IMPORTING DATA IN PYTHON

Tables are linked

slide-38
SLIDE 38

INTRODUCTION TO IMPORTING DATA IN PYTHON

Tables are linked

slide-39
SLIDE 39

INTRODUCTION TO IMPORTING DATA IN PYTHON

Tables are linked

slide-40
SLIDE 40

INTRODUCTION TO IMPORTING DATA IN PYTHON

JOINing tables

slide-41
SLIDE 41

INTRODUCTION TO IMPORTING DATA IN PYTHON

JOINing tables

slide-42
SLIDE 42

INTRODUCTION TO IMPORTING DATA IN PYTHON

JOINing tables

slide-43
SLIDE 43

INTRODUCTION TO IMPORTING DATA IN PYTHON

JOINing tables

slide-44
SLIDE 44

INTRODUCTION TO IMPORTING DATA IN PYTHON

JOINing tables

slide-45
SLIDE 45

INTRODUCTION TO IMPORTING DATA IN PYTHON

INNER JOIN in Python (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

slide-46
SLIDE 46

Let's practice!

IN TR OD U C TION TO IMP OR TIN G DATA IN P YTH ON

slide-47
SLIDE 47

Final Thoughts

IN TR OD U C TION TO IMP OR TIN G DATA IN P YTH ON

Hugo Bowne-Anderson

Data Scientist at DataCamp

slide-48
SLIDE 48

INTRODUCTION TO IMPORTING DATA IN PYTHON

What you’ve learned:

Relational databases Queries SELECT WHERE JOIN

slide-49
SLIDE 49

INTRODUCTION TO IMPORTING DATA IN PYTHON

Next course:

Scrape data from the web Interact with APIs

slide-50
SLIDE 50

Let's practice!

IN TR OD U C TION TO IMP OR TIN G DATA IN P YTH ON