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
   

   View Slide

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

   View Slide

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

   View Slide

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

   View Slide

5. View Slide

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

   View Slide

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…
   

   View Slide

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
   

   View Slide

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
   

   View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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
    

    View Slide

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!
    

    View Slide

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
    

    View Slide

20. Database
    Customers
    Orders
    Addresses
    Items
    

    View Slide

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
    

    View Slide

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
    

    View Slide

23. View Slide

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?
    

    View Slide

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?
    

    View Slide

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?
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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)
    

    View Slide

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

    View Slide

32. Questions?
    David Baumgold // @singingwolfboy
    

    View Slide