To download SQLite, click here.
00:00 In the previous lesson, I covered flat file storage. In this lesson, I’ll introduce you to databases and SQL. A relational database gets its name from the fact that it stores the relationships between things. It does this through treating data as a series of tuples. This is kind of like the row in the CSV file you saw in the last lesson, except a row can contain references to another row or in another table altogether. The relationships are identified through the use of keys.
00:31 There are two main types. Primary keys are a unique identifier for a tuple. These are usually integers and typically are auto-incremented by the database engine, so you don’t have to think about it.
00:45 The second kind of key is a foreign key—foreign, in this case, meaning from another table. This would be the primary key for another object. A tuple containing a foreign key is indicating a relationship with the tuple for which the value is its primary key.
Consider the following example. This is a table with authors.
PK is short for primary key. Each row in our table is a tuple of the primary key, first name, and last name. I can add a different table of books.
01:47 This isn’t actually a very good design, as means an author can only have one alias, but it makes my point about foreign keys being able to be on the same table. In a future lesson, I’ll address how you would do this properly.
02:01 As far as I know, every relational database out there supports some dialect of the structured query language, or SQL to its friends. This language is a little different from what you might be used to in coding.
02:34 This is what is known in the industry as vendor lock-in. For the most part, I’ll be sticking to the generic stuff in this course. The beauty of SQL is you don’t have to think about the underlying format.
02:59 This is a small, single-file, self-contained engine, which doesn’t require a server. According to its website, it’s the most-used engine in the world, and whether or not you know it, you’re probably using it. It gets embedded in applications all the time, and there’s a good chance it’s on your phone right now.
03:18 SQLite comes with a command-line tool that works like a Python REPL, allowing you to interact directly with your database. I’ll be starting with that before moving on SQLAlchemy in a future lesson.
03:31 Your operating system may or may not come with SQLite. You can grab either the source code or a binary at the link here if you’re planning to follow along and it doesn’t come by default in whatever you’re using.
03:58 Once inside, you get prompted. There are two kinds of things you can do here. You can run dot commands or enter SQL directly. The dot commands are built-in utilities provided by SQLite, whereas the SQL will be the language you use to do things in the database.
05:13 SQLite doesn’t care about string length. No matter what kind of text declaration you use, or how much space you specify, you’ll get the same underlying thing. On other database systems, you may have to specify how big this field is. Here, I’m being lazy, knowing that it’s SQLite and it doesn’t care.
SELECT is probably the most-used SQL command, and its purpose is to select some data out of some tables. The star (
*) here indicates that I want all of the columns from the table and everything to the right of the
FROM is what table to look for data in.
INSERT puts data into a table. Here, I’ve inserted into the
author table. The contents of the first parentheses are the columns in the
author table being populated. I’m specifying all of them, not counting the primary key, which is automatic.
But if your table has a column that allows empty values or has a default, you don’t necessarily have to specify it. The content to the right of the
VALUES keyword matches the tuples specifying the column names.
Note the foreign key in the
book table specified by using the
author_id integer references author says that the
book table contains a foreign key to the table
author. Now I’ll add a book.
And there it is. The first one is the primary key of the book. The second one is the foreign key to the author. I had to set it to Asimov’s primary key. This establishes the relationship between this
book row and the Asimov row in the
I’m still choosing what to output at this point. This is the second column, which will be the title of the book. Now I’m telling
SELECT where to get the
a info—that’s the
author table—and to join that with the
book table, known as
b in the query.
The end result is some data in tabular format with the author’s name in the first column and their book in the second column. Think back to the CSV file in the previous lesson. Ignoring the publisher part, you now have enough knowledge to create a
SELECT statement with a join that could produce the data you found in the CSV. That’s enough for now.
13:37 To leave the SQLite command-line tool, you can press Control + D. You’ve had a whirlwind intro to SQL. Next up, I’ll add some Python to your Python course and use SQLAlchemy to do some SQL in your Python code.
Become a Member to join the conversation.