Python and SQL: The Not So Slippery Bits

Slide 1

Slide 1 text

Python and SQL: The Not So Slippery Bits Jonathan Stott 21st April 2022 Python and SQL: The Not So Slippery Bits @namelessjon

Slide 2

Slide 2 text

SELECT content FROM talk What is SQL? SQL Basics SQL in Python Relational Databases Python and SQL: The Not So Slippery Bits @namelessjon 2 / 37

Slide 3

Slide 3 text

What is SQL? Structured Query Language Python and SQL: The Not So Slippery Bits @namelessjon 3 / 37

Slide 4

Slide 4 text

What is SQL? Structured Query Language Declarative Python and SQL: The Not So Slippery Bits @namelessjon 4 / 37

Slide 5

Slide 5 text

What is SQL? Structured Query Language Declarative About fifty Python and SQL: The Not So Slippery Bits @namelessjon 5 / 37

Slide 6

Slide 6 text

SQL Python and SQL: The Not So Slippery Bits @namelessjon 6 / 37

Slide 7

Slide 7 text

The simplest query SELECT 1; => 1 or SELECT 2+2; => 4 Like the python interpreter, you always have a calculator! Python and SQL: The Not So Slippery Bits @namelessjon 7 / 37

Slide 8

Slide 8 text

Okay, show me a real query SELECT title, author FROM books_read; Python and SQL: The Not So Slippery Bits @namelessjon 8 / 37

Slide 9

Slide 9 text

So what does the data look like? id title author year_read rating 1 Spot's First Christmas Eric Hill 1984 2 The Very Hungry Caterpiller Eric Carle 1984 4 ... ... ... ... ... 671 the galaxy, and the ground within Becky Chambers 2022 5 Python and SQL: The Not So Slippery Bits @namelessjon 9 / 37

Slide 10

Slide 10 text

Okay, show me a real query SELECT title, author FROM books_read; title author Spot's First Christmas Eric Hill The Very Hungry Caterpiller Eric Carle ... ... the galaxy and the ground within Becky Chambers Python and SQL: The Not So Slippery Bits @namelessjon 10 / 37

Slide 11

Slide 11 text

Filtering results SELECT title, author FROM books_read WHERE year_read = 2022; title author Uncompromising Honor David Weber The Jasmine Throne Tasha Suri ... ... the galaxy, and the ground within Becky Chambers Python and SQL: The Not So Slippery Bits @namelessjon 11 / 37

Slide 12

Slide 12 text

Filtering results SELECT title, author FROM books_read WHERE year_read = 2022 LIMIT 2; title author Uncompromising Honor David Weber The Jasmine Throne Tasha Suri Python and SQL: The Not So Slippery Bits @namelessjon 12 / 37

Slide 13

Slide 13 text

Aggregating data SELECT year_read, count(*) FROM books_read GROUP BY year_read; year_read count 1984 2 ... ... 2022 16 Python and SQL: The Not So Slippery Bits @namelessjon 13 / 37

Slide 14

Slide 14 text

Python and SQL Python and SQL: The Not So Slippery Bits @namelessjon 14 / 37

Slide 15

Slide 15 text

DBAPI - PEP-249 connect() to connect conn.cursor() to get a cursor cursor.execute(sql, params) to run a query cursor.fetchall() or cursor.fetchone() to get results And a few other things Python and SQL: The Not So Slippery Bits @namelessjon 15 / 37

Slide 16

Slide 16 text

import sqlite3 Small. Fast. Reliable. Choose any three. Python and SQL: The Not So Slippery Bits @namelessjon 16 / 37

Slide 17

Slide 17 text

import sqlite3 import sqlite3 conn = sqlite3.connect("books.sqlite") Python and SQL: The Not So Slippery Bits @namelessjon 17 / 37

Slide 18

Slide 18 text

import sqlite3 import sqlite3 conn = sqlite3.connect("books.sqlite") cursor = conn.cursor() Python and SQL: The Not So Slippery Bits @namelessjon 18 / 37

Slide 19

Slide 19 text

import sqlite3 cursor.execute(""" CREATE TABLE books_read( id integer primary key, title varchar(50) not null, author varchar(50), year_read integer, rating integer, comment varchar(200) ) """) Python and SQL: The Not So Slippery Bits @namelessjon 19 / 37

Slide 20

Slide 20 text

import sqlite3 cursor.execute(""" INSERT INTO books_read(title, author, year_read) VALUES (?, ?, ?) """, ("the galaxy, and the ground within", "Becky Chambers", 2022)) ... Python and SQL: The Not So Slippery Bits @namelessjon 20 / 37

Slide 21

Slide 21 text

import sqlite3 import sqlite3 conn = sqlite3.connect("books.sqlite") cursor = conn.cursor() cursor.execute("SELECT title, author FROM books_read WHERE year_read = ?;", (2022,)) Python and SQL: The Not So Slippery Bits @namelessjon 21 / 37

Slide 22

Slide 22 text

import sqlite3 import sqlite3 conn = sqlite3.connect("books.sqlite") cursor = conn.cursor() cursor.execute("SELECT title, author FROM books_read WHERE year_read = ?;", (2022,)) result = cursor.fetchall() Python and SQL: The Not So Slippery Bits @namelessjon 22 / 37

Slide 23

Slide 23 text

import sqlite3 import sqlite3 conn = sqlite3.connect("books.sqlite") cursor = conn.cursor() cursor.execute("SELECT title, author FROM books_read WHERE year_read = ?;", (2022,)) result = cursor.fetchall(): for row in result: print(row) Python and SQL: The Not So Slippery Bits @namelessjon 23 / 37

Slide 24

Slide 24 text

ORMs Python and SQL: The Not So Slippery Bits @namelessjon 24 / 37

Slide 25

Slide 25 text

ORMs - what is an ORM? Object Relational Mapper Python and SQL: The Not So Slippery Bits @namelessjon 25 / 37

Slide 26

Slide 26 text

ORMs in python DjangoORM SQLAlchemy more! Python and SQL: The Not So Slippery Bits @namelessjon 26 / 37

Slide 27

Slide 27 text

Why use an ORM? Work in python Parameterised queries Python and SQL: The Not So Slippery Bits @namelessjon 27 / 37

Slide 28

Slide 28 text

Why not an ORM? Existing SQL Analytics Python and SQL: The Not So Slippery Bits @namelessjon 28 / 37

Slide 29

Slide 29 text

Relational Databases Relational DataBase Management System (RDBMS) Data (rows or tuples) are stored in relations (or tables) relations have a defined structure with a rich data model Query with SQL Python and SQL: The Not So Slippery Bits @namelessjon 29 / 37

Slide 30

Slide 30 text

Rich data model For example postgres has native strings, integers, floats, fixed precision decimal values, dates, times, datetimes ... ... ip addresses, ranges, uuids, enums, arrays, geography ... Oh, and directly queryable json in a column Python and SQL: The Not So Slippery Bits @namelessjon 30 / 37

Slide 31

Slide 31 text

You don't have to think about how you want to query ... much anyway Python and SQL: The Not So Slippery Bits @namelessjon 31 / 37

Slide 32

Slide 32 text

Aren't relational databases slow? Python and SQL: The Not So Slippery Bits @namelessjon 32 / 37

Slide 33

Slide 33 text

When should I NOT use a relational database? ... you should probably use a relational database. Buuuuut ... you might have a specialised use case. Python and SQL: The Not So Slippery Bits @namelessjon 33 / 37

Slide 34

Slide 34 text

NoSQL? Specialised stores for specialised use cases Key-value stores Document databases Graph databases Queues Python and SQL: The Not So Slippery Bits @namelessjon 34 / 37

Slide 35

Slide 35 text

What else can I query with SQL? BigQuery Elasticsearch terraform ... a lot more! Python and SQL: The Not So Slippery Bits @namelessjon 35 / 37

Slide 36

Slide 36 text

Neat libraries pysqlite3 - latest sqlite, packaged up datasette - Explore data sets in sqlite in the browser, and make them available to others etlhelper - tool for moving data between databases, especially geospatial data, from the British Geological Survey Python and SQL: The Not So Slippery Bits @namelessjon 36 / 37

Slide 37

Slide 37 text

When should I use a relational database? Python and SQL: The Not So Slippery Bits @namelessjon 37 / 37