Reading with SQL
SQL is a very necessary topic for junior Python developers to understand. Knowing how to structure data into a SQL database will teach you how to think logically about your data storage requirements. There’s a long established method for deconstructing data, storing it efficiently, and accessing it. In recent years the development of NoSQL databases has made this different, but the basic concepts behind relational database design are still useful. Everywhere that you need to store data there’s a need to structure it well and understand it.
In this exercise, you will dive into SQLite3 and the SQL SELECT command.
Save 35% off the list price* of the related book or multi-format eBook (EPUB + MOBI + PDF) with discount code ARTICLE.
* See informit.com/terms
This chapter is from the book
This chapter is from the book
This chapter is from the book
Out of the CRUD matrix you only know create. You can create tables and you can create rows in those tables. I’ll now show you how to read, or in the case of SQL, SELECT:
ex5.sql
1 SELECT * FROM person;
2
3 SELECT name, age FROM pet;
4
5 SELECT name, age FROM pet WHERE dead = 0;
6
7 SELECT * FROM person WHERE first_name != "Zed";
Here’s what each of these lines does:
Line 1 This says “select all columns from person and return all rows.” The format for SELECT is SELECT what FROM tables(s) WHERE (tests), and the WHERE clause is optional. The * (asterisk) character is what says you want all columns.
Line 3 In this one I’m only asking for two columns, name and age, from the pet table. It will return all rows.
Line 5 Now I’m looking for the same columns from the pet table, but I’m asking for only the rows where dead = 0. This gives me all the pets that are alive.
Line 7 Finally, I’m selecting all columns from person just like in the first line, but now I’m saying only if they do not equal “Zed.” That WHERE clause is what determines which rows to return or not.
Select across Many Tables
Hopefully you’re getting your head around selecting data out of tables. Always remember this: SQL ONLY KNOWS TABLES. SQL LOVES TABLES. SQL ONLY RETURNS TABLES. TABLES. TABLES. TABLES. TABLES! I repeat this in this rather crazy manner so that you will start to realize that what you know in programming isn’t going to help. In programming you deal in graphs, and in SQL you deal in tables. They’re related concepts, but the mental model is different.
Here’s an example of where it becomes different. Imagine you want to know what pets Zed owns. You need to write a SELECT that looks in person and then “somehow” finds my pets. To do that you have to query the person_pet table to get the id columns you need. Here’s how I’d do it:
ex6.sql
1 SELECT pet.id, pet.name, pet.age, pet.dead
2 FROM pet, person_pet, person
3 WHERE
4 pet.id = person_pet.pet_id AND
5 person_pet.person_id = person.id AND
6 person.first_name = "Zed";
Now, this looks huge, but I’ll break it down so you can see it’s simply crafting a new table based on data in the three tables and the WHERE clause:
Line 1 I only want some columns from pet, so I specify them in the select. In the last exercise you used * to say “every column” but that’s going to be a bad idea here. Instead, you want to be explicit and say what column from each table you want, and you do that by using table.column as in pet.name.
Line 2 To connect pet to person I need to go through the person_pet relation table. In SQL that means I need to list all three tables after the FROM.
Line 3 Start the WHERE clause.
Line 4 First, I connect pet to person_pet by the related id columns pet.id and person_pet.id.
Line 5 AND I need to connect person to person_pet in the same way. Now the database can search for only the rows where the id columns all match up, and those are the ones that are connected.
Line 6 AND I finally ask for only the pets that I own by adding a person.first_name test for my first name.