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