#100DaysOfCode in Python Transcripts
Chapter: Days 79-81: Basic Database Access with SQLite3
Lecture: Demo: Script to Generate a DB
Login or
purchase this course
to watch this video and the rest of the course contents.
0:00
So for this video, I just wanted to show you
0:02
a cool little script that you can create to
0:05
sort of generate your own database files.
0:08
Just for testing purposes, right?
0:10
'Cause that's one of the great things about SQLite3.
0:13
It's super lightweight, and you can use it for testing.
0:16
So just create a new file.
0:19
All right, and, I'd like you to
0:22
pretty much use the file, I mean, you could
0:24
edit whichever way you wish, of course.
0:27
But you'll find this file in the course materials.
0:29
So don't feel like you have to copy everything
0:31
I'm typing here, in fact, I'm going to use
0:33
some of my black magic to make it appear on the screen.
0:37
So, I'm going to explain this text to you in just a minute.
0:40
But what we will do is, let's save the file as
0:44
so, create this Python file for yourself.
0:47
generate_db.py, all right?
0:55
Now, here comes the magic.
0:58
Okay, so I know this looks daunting.
1:00
So, just don't panic, if you don't know
1:02
what you're looking at here.
1:04
I'll explain things in a simple way, but,
1:06
I'm going to try and skip over the stuff that isn't
1:08
really SQLite3 relative, but just bear with me.
1:12
Okay.
1:13
So we're creating a context manager here.
1:15
We're creating a generator.
1:16
And that uses a with statement, well that's one way.
1:19
It uses a with statement.
1:20
And it'll have a function in there with this decorator.
1:24
And you can read this stuff up.
1:25
We'll link to that in the course notes.
1:28
And it will yield something.
1:30
In this case, it's going to yield that cursor.
1:34
That we use here.
1:36
So you've got your connection.cursor, right?
1:38
Well, we have that here.
1:41
We've just abbreviated it down to con.
1:43
Which is generally a standard, right?
1:45
So, the first thing that this script if going to do is
1:47
it's going to prompt you for a name.
1:49
So when you run it, it's going to say, well,
1:51
what's the name of your database?
1:52
What would you like to name your database file, all right?
1:55
And, you enter in a name.
1:57
It returns the name.
1:59
And your context manager, this with statement,
2:03
will create the database using that name.
2:07
All right, so runs create_db which is here.
2:10
And create_db when invoked is going to
2:13
set up your connection cursor, all right?
2:15
It's going to yield that cursor line right here,
2:20
with create_db() as cursor so it returns,
2:23
it yields the cursor into here.
2:27
And then now, your width statement, runscursor.execute.
2:32
Okay, so this is just a generator, very simple generator.
2:35
And then it goes cursor.execute,
2:37
and it creates a table called test table.
2:40
With three columns.
2:43
Sorry, four columns.
2:44
Column one, two, three and four.
2:46
Three as text, and one as int.
2:49
And when it's done, it prints.
2:51
The database has been created.
2:53
This is just a simple, string formatting.
2:56
And, again, substitutes the name in,
2:59
you can see that here as well.
3:01
And that's it, that's literally all this script does.
3:04
Now, as you can tell, this is hard coded.
3:07
And this is why I said this is great for testing.
3:10
And this is something I use.
3:11
And I will just quickly pop in here
3:13
and change this if I have to, but for the most part,
3:16
three text columns and an integer column
3:18
is more than enough for me.
3:20
And I've used this on multiple occasions.
3:21
Just to create a really quick, simple SQLite3 database.
3:25
Without having to go through, and create it myself
3:28
manually using these connection commands, all right?
3:32
So let's save this file.
3:34
And, with that out of the way.
3:37
We will run, let's just make sure it's in here.
3:40
python generate_db.py
3:44
What would you like to name your test DB file?
3:48
Well, let's just call it Julian.
3:52
Julian.db has been created.
3:55
There we go.
3:57
Right down there.
3:58
Open up our database browser, again.
4:02
Let's close this database.
4:06
Open up Julian.
4:09
And there we go.
4:10
We've got test table, with one, two, three, four columns.
4:14
It is a very useful script.
4:16
You can edit it to something that's much more, there we go.
4:20
You can edit it to something that's much more
4:22
appropriate for you, and for your testing purposes.
4:24
But it's a really cool one just to keep handy just in case.