SQL Crash Course

Databases are powerful, and to a beginner, can seem quite daunting. What is SQL, and why should I use it? This presentation provides a brief crash-course in what SQL databases are and how to write SQL queries. It also touches on the usage of object-relational mappers in Python. (Presented at the Boston Python meetup group.)

Transcript

1. a crash course in SQL Databases David Baumgold // @singingwolfboy
2. Computers aren’t very good at remembering stuff… …unless you use a database.
3. two basic types SQL NoSQL • More powerful • Better consistency • More mature • Transaction support • Simpler • Faster • Evolving quickly • More scalable
4. two basic types SQL NoSQL • More powerful • Better consistency • More mature • Transaction support • Simpler • Faster • Evolving quickly • More scalable
6. Structured Query Language It’s how you talk to a SQL database! Jeez,! dude has! anger! management! issues
7. It’s actually not that hard SELECT u.id, u.username FROM users AS u WHERE u.active; That’s a real SQL query! It’s almost like English! ! Sort of like another language we all know…
8. Tabular data id username! first_name last_name active 1 singingwolfboy David Baumgold TRUE 2 nedbat Ned Batchelder TRUE 3 gvanrossum Guido Van Rossum FALSE 4 divagoddess Cher NULL FALSE 5 pitydafool Mr. T TRUE
9. Unordered data id username! first_name last_name active 4 divagoddess Cher NULL FALSE 2 nedbat Ned Batchelder TRUE 5 pitydafool Mr. T TRUE 1 singingwolfboy David Baumgold TRUE 3 gvanrossum Guido Van Rossum FALSE SAME
10. Basic Data Query SELECT u.id, u.username FROM users AS u WHERE u.active; SELECT: what columns do you want? FROM: what tables do you want to use? WHERE: how do you want to filter the contents of the table? “SFW” query: very common
11. Shorter SELECT * FROM users WHERE active; SELECT * means give me EVERY column AS is optional; it’s just an alias If your column names are unambiguous, you don’t need to specify which table they’re from WHERE is optional too, if you want EVERY row in the table
12. WHERE clause SELECT * FROM users WHERE age >= 18; SELECT * FROM users WHERE last_login < '2013-09-29'; It’s just a comparison! ! Booleans, dates, strings, many different types SELECT * FROM users WHERE last_name IS NOT NULL; NULL means a lack of data, but the database will happily return it unless you specify otherwise
13. Chaining SELECT * FROM users WHERE job='knight' AND quest='seek_grail' AND fav_color='blue'; Use AND for multiple conditions that must all apply SELECT * FROM users WHERE job='enchanter' OR first_name='Tim'; Use OR for multiple conditions where any may apply
14. ORDER BY clause • Databases are built for speed • Data might be organized weirdly on disk • By default, queries will return data as soon as possible, even if it’s out of order id name 5 Sue 18 Zach 3 Ariel 11 James 7 Mark 19 Eve 27 Charlie
15. ORDER BY clause SELECT id, name FROM users ORDER BY id; id name 3 Ariel 5 Sue 7 Mark 11 James 18 Zach 19 Eve 27 Charlie
16. ORDER BY clause SELECT id, name FROM users ORDER BY name; id name 3 Ariel 27 Charlie 19 Eve 11 James 7 Mark 5 Sue 18 Zach
17. ORDER BY clause SELECT id, name FROM users ORDER BY name desc; id name 18 Zach 5 Sue 7 Mark 11 James 19 Eve 27 Charlie 3 Ariel
18. JOIN Hoo-boy.! This is where it gets! a bit complicated. But it’s also the most powerful part of SQL, so hang in there!
19. OnlineStore.com id name order1_item1 order1_item2 order2_item1 order2_item2 1 Frank wrench screw toaster mongoose … Let’s build a database! • Customers • Items • Orders
20. Database Customers Orders Addresses Items
21. id name 1 Frank 2 Joanne Customer id street cust_id 1 1 main street 1 2 11 cambridge 1 3 98 myrtle ave 2 4 NERD center 2 Address Foreign Key SELECT c.name, a.street FROM customers AS c, address AS a WHERE a.cust_id = c.id; One-To-Many Table Primary Key
22. id name 1 Frank 2 Joanne Customer id name price 1 hammer 6.00 2 nail 0.50 3 toaster 4.50 4 mongoose 129.99 Item id date cust_id 1 2013-10-23 1 2 2013-09-01 1 Order order_id item_id quantity 1 1 1 1 2 12 1 3 1 2 2 5 2 4 1 Order_Item Many-To-Many Table
24. Check this out! SELECT i.name FROM customer AS c, order AS o, item AS i, order_item AS oi WHERE o.cust_id = c.id AND oi.order_id = o.id AND oi.item_id = i.id AND c.name = 'Santa' AND o.date >= '2012-12-01' AND o.date <= '2012-12-31'; What items did people named Santa buy in December of last year?
25. Check this out! SELECT SUM(i.price) FROM customer AS c, order AS o, item AS i, order_item AS oi WHERE o.cust_id = c.id AND oi.order_id = o.id AND oi.item_id = i.id AND c.name = 'Santa' AND o.date >= '2012-12-01' AND o.date <= '2012-12-31'; How much did he spend?
26. Check this out! SELECT c.name FROM customer AS c, order AS o, item AS i, order_item AS oi WHERE o.cust_id = c.id AND oi.order_id = o.id AND oi.item_id = i.id AND i.name = 'mongoose'; Who bought the mongoose?
27. Slice and Dice Your Data and answer questions you haven’t even thought of yet
28. Python! Ships with the sqlite3 module, which gives you a full SQL database right inside your Python console! >>> import sqlite3 >>> db = sqlite3.connect("example.db") >>> db.execute("SELECT * FROM users;")
29. Python and ORM ORM = Object Relational Mapper Organize your data in SQL tables Use your data as Python objects
30. / Two good Python ORMs >>> from sqlalchemy import Column, Integer, String >>> class User(Base): ... __tablename__ = 'users' ... id = Column(Integer, primary_key=True) ... name = Column(String) ... password = Column(String) ... >>> u = User("Joe Average", "123456") >>> u.save() >>> u.id 1 (Django ORM is very similar)
31. ORM queries SELECT i.name FROM customer AS c, order AS o, item AS i, order_item AS oi WHERE o.cust_id = c.id AND oi.order_id = o.id AND oi.item_id = i.id AND c.name = 'Santa' AND o.date >= '2012-12-01' AND o.date <= '2012-12-31'; from datetime import date q = (session.query(Item.name).join(Customer).join(Order) .filter(Customer.name == "Santa") .filter(Order.date >= date(2012, 12, 1)) .filter(Order.date <= date(2012, 12, 31)) )
32. Questions? David Baumgold // @singingwolfboy