Save 37% off PRO during our Black Friday Sale! »

SQL Crash Course

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

42c77de97965d620ba0b6ae624c3ba7b?s=128

David Baumgold

October 29, 2013
Tweet

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
  5. None
  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
  23. None
  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