Python and SQL: The Not So Slippery Bits

If you're working with data, the chances are sooner or later you'll need to use some SQL to get at part of it. Or you have some information of your own that you want to store, and you're wondering how to do that. In this talk, I will cover what SQL is, why you might want to use it, how you interact with SQL datastores from python, as well as some introductory SQL queries. Along the way, I’ll introduce concepts like Object-Relational Mappers (ORMs) and why you may or may not want to use them, and the SQL database available in the python standard library!

Transcript

1. Python and SQL: The Not So Slippery Bits Jonathan Stott 21st April 2022 Python and SQL: The Not So Slippery Bits @namelessjon
2. 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
3. What is SQL? Structured Query Language Python and SQL: The Not So Slippery Bits @namelessjon 3 / 37
4. What is SQL? Structured Query Language Declarative Python and SQL: The Not So Slippery Bits @namelessjon 4 / 37
5. What is SQL? Structured Query Language Declarative About fifty Python and SQL: The Not So Slippery Bits @namelessjon 5 / 37
6. SQL Python and SQL: The Not So Slippery Bits @namelessjon 6 / 37
7. 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
8. Okay, show me a real query SELECT title, author FROM books_read; Python and SQL: The Not So Slippery Bits @namelessjon 8 / 37
9. 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
10. 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
11. 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
12. 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
13. 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
14. Python and SQL Python and SQL: The Not So Slippery Bits @namelessjon 14 / 37
15. 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
16. import sqlite3 Small. Fast. Reliable. Choose any three. Python and SQL: The Not So Slippery Bits @namelessjon 16 / 37
17. import sqlite3 import sqlite3 conn = sqlite3.connect("books.sqlite") Python and SQL: The Not So Slippery Bits @namelessjon 17 / 37
18. import sqlite3 import sqlite3 conn = sqlite3.connect("books.sqlite") cursor = conn.cursor() Python and SQL: The Not So Slippery Bits @namelessjon 18 / 37
19. 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
20. 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
21. 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
22. 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
23. 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
24. ORMs Python and SQL: The Not So Slippery Bits @namelessjon 24 / 37
25. ORMs - what is an ORM? Object Relational Mapper Python and SQL: The Not So Slippery Bits @namelessjon 25 / 37
26. ORMs in python DjangoORM SQLAlchemy more! Python and SQL: The Not So Slippery Bits @namelessjon 26 / 37
27. Why use an ORM? Work in python Parameterised queries Python and SQL: The Not So Slippery Bits @namelessjon 27 / 37
28. Why not an ORM? Existing SQL Analytics Python and SQL: The Not So Slippery Bits @namelessjon 28 / 37
29. 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
30. 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
31. 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
32. Aren't relational databases slow? Python and SQL: The Not So Slippery Bits @namelessjon 32 / 37
33. 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
34. 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
35. What else can I query with SQL? BigQuery Elasticsearch terraform ... a lot more! Python and SQL: The Not So Slippery Bits @namelessjon 35 / 37
36. 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
37. When should I use a relational database? Python and SQL: The Not So Slippery Bits @namelessjon 37 / 37