#100DaysOfCode in Python Transcripts
Chapter: Days 79-81: Basic Database Access with SQLite3
Lecture: Demo: Script to Generate a DB

Learn more about this course
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.