#100DaysOfCode in Python Transcripts
Chapter: Days 79-81: Basic Database Access with SQLite3
Lecture: Creating a simple SQLite3 address book
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
Okay, blank slate, here we go. The first thing you need to do is create a Python file called simpledb.py.
0:10
That's this file here in your project directory that you're going to use for this video. So you see I've got simpledb.py.
0:18
Next up, we're going to start our virtual environment, so Python -m venv venv Now we're not actually going to be installing any third party modules
0:29
or anything crazy so, I suppose technically the virtual environment isn't necessary but, for me, I always do it no matter how little the project is.
0:40
So we'll activate that, venv\scripts\activate and now we're safe. Now what we can do is we will actually launch
0:52
the Python shell just so that we're using it in here. There we go, Python 3.6. Now technically what we're about to do here
1:00
is we're about to use commands that we could sort of run in this virtual shell here. But I'd like to actually run these commands
1:10
in a script just to show you how it works in a different sort of way. So just bear with me. The first thing we're going to do is create a database.
1:22
It's the first day of SQLite 3, so let's create a database. We have to do that to work with one, right? What we will do is
1:31
we'll just throw this in the top, and we'll import sqlite3. That's it, that's pretty much all we need to do to create our database.
1:41
It's all we're actually importing. Now, I want you to visualize this. Don't just read what I'm going to be typing here
1:48
and what you'll be following along, I'm going to use the full form words just so that it makes sense. Alright?
1:56
We're going to create a connection object and that object is going to store our actual connection to the database. Think of the database as, you know,
2:08
some sort of a, something you have to tap into, right? And in order to do that you need to create a connection to it just like you connect
2:15
to the internet or what have you. So we're going to connect using the SQLite3.connect command and
2:24
this is now where we specify the name of our database. So let's, for this exercise, let's create an address book. Address book with your name,
2:33
your phone number, your address, maybe. And let's call it addressbook.db. There's our database. Now what this command does is
2:46
sqlite3.connect addressbook that is actually going to create this database if it doesn't exist.
2:56
If this database did exist, it would just connect us to it. Which is really cool in that if it doesn't exist
3:03
it thinks "Well, hey, you just want me to create it, "so I'll create it." And we're going to store this connection, this connection to this database,
3:11
in the connection object. Now, in order to parse the database, P-A-R-S-E, in order to parse the database, we need to have a cursor.
3:25
So just like this cursor here allows us to move through text and say Microsoft Word document, or what have you, we need this cursor for the database.
3:37
And using this cursor we can execute commands. We can send commands to the database to do certain things such as
3:46
select information, overwrite information, create things, hint hint. But we need to store that inside another variable.
3:58
Generally the rule of thumb is to just call this cursor c, and that cursor is part of connection. So part of our actual SQLite 3 connection.
4:10
So, connection.cursor. c =, or c is assigned connection.cursor. Now, as I hinted, we want to execute commands. So c.execute.
4:26
Now in the brackets here, what are we actually executing? This is where you actually start to use your SQL commands.
4:35
We're going to put those within a few of these, 'cause it's going to be multi-line. And the first SQL command is create. Because, what are we doing?
4:49
We're creating a table. And now, this next word we're going to type in is going to be the name of your table. So within our address book database,
5:01
we have a table named, let's call it details because I have no imagination. Now, we have a table named details. That's what this command is creating.
5:14
Now what do we want to be in that table? Well, I can imagine in my address book I might have a name. So creating, pretty much we're creating
5:25
a column here named "name" in our table. And that name, what kind of information is going into that name?
5:34
What type of information is going into that name column? Well, it's going to be text. Same with the address. We want an address column.
5:44
And that's also going to be text. But our phone number, while that could be text let's just shake things up a little.
5:52
That's going to be an int, an integer. So that's it. Or so you think. But then last thing as you know from some of you other Python work so far
6:02
is that you actually need to close your connection. Now, to do that, as you'd expect, connection.close. Nice and straightforward.
6:13
And that is so simple. So as I mentioned before, all of this, all of these commands that we're typing in here, all of this Python code,
6:20
it's actually stuff you can run on your Python shell, in your Python shell, I should say. But we'll put it in a script.
6:30
That way, we can just run the script from the command line. We'll go directory here, let's see, there's our simple db.py,
6:40
and let's run Python simpledb.py. And look how quickly that returned. I hope it actually did something.
6:50
Now we should have a database called addressbook.db. Now this database is completely empty because all we've done is create a table
7:01
with name, address, and phone number, but no data. So let's open up SQLite database field, the application you installed,
7:11
and let's have a look at that.