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
Recommend
More recommend
