#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.
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:42
venv\scripts\activate
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:53
Alright?
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:14
Now,
3:16
in order to parse the database,
3:18
P-A-R-S-E,
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:07
connection.close.
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.
