a crash course in SQL Databases David Baumgold // @singingwolfboy 

Computers aren’t very good at remembering stuff… …unless you use a database. 

two basic types SQL NoSQL • More powerful • Better consistency • More mature • Transaction support • Simpler • Faster • Evolving quickly • More scalable 

two basic types SQL NoSQL • More powerful • Better consistency • More mature • Transaction support • Simpler • Faster • Evolving quickly • More scalable 

No content

Structured Query Language It’s how you talk to a SQL database! Jeez,! dude has! anger! management! issues 

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… 

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 

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 

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 

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 

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 

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 

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 

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 

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 

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 

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! 

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 

Database Customers Orders Addresses Items 

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 

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 

No content

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? 

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? 

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? 

Slice and Dice Your Data and answer questions you haven’t even thought of yet 

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;") 

Python and ORM ORM = Object Relational Mapper Organize your data in SQL tables Use your data as Python objects 

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

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

Questions? David Baumgold // @singingwolfboy