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