#100DaysOfCode in Python Transcripts
Chapter: Days 79-81: Basic Database Access with SQLite3
Lecture: Concepts: what did we learn
0:00 Okay, done and done.
0:02 Let's look over everything we've covered
0:04 in the past couple of days
0:05 before you move onto the third day.
0:08 So the first thing you had to do
0:10 was install the SQLite browser,
0:13 and this little application
0:15 just lets you load up your database files
0:17 so you could see everything that was stored in your database
0:21 in a nice GUI environment.
0:25 Now then, the next thing we did was we created
0:27 a simple database, so I've outlined, I've highlighted
0:30 the important things you need to keep in mind here.
0:33 So first thing, you begin
0:35 by importing sqlite3 as you'd expect.
0:38 Then we connected to the database
0:41 using the sqlite3.connect, okay?
0:44 And if the database that you specify
0:49 between the brackets doesn't exist,
0:51 it creates the database for you,
0:54 very important to remember.
0:56 So if you make a typo there, it's going to create
0:58 a new database rather than connect to the existing one.
1:03 Alright, then we actually take the cursor
1:06 that allows you to talk to your database.
1:08 So you've connected to it.
1:09 Now you have to be able to type into it,
1:11 and you use a cursor for that,
1:13 and you assign that to the variable c, which just makes it
1:17 a bit easier to type further on within your application.
1:21 Alright, then we execute the SQL commands,
1:24 and this is true for any SQL command that you want to run,
1:28 not just to create one.
1:30 In this specific instance, we created a table
1:33 with two text columns
1:37 and one integer column, alright?
1:41 And then we always close the connection when we're done.
1:45 Very important to remember to close it,
1:47 'cause if you don't, then the database may still be in use
1:50 and may prevent other connections
1:52 and potentially cause corruption,
1:56 so we'll always close it when we're done.
1:59 Next one, we wanted to add data to the database.
2:03 So again we connected to the database
2:06 using the connect command,
2:08 and we loaded the cursor into the c variable.
2:13 Alright, and more SQL syntax.
2:16 This time we're inserting the data into the database
2:20 and we were inserting a couple of values there.
2:22 We were doing two text values.
2:24 That was the name and the address columns,
2:27 and then the integer column of the phone number.
2:32 And then we can close the connection.
2:34 Now the more Pythonic way to do this
2:37 is to use a with statement, okay?
2:40 And this will allow you to run
2:43 your cursor and your execute commands,
2:46 but then it will actually close it by itself.
2:50 It'll close it for you, it'll auto-close
2:52 once the commands have finished running,
2:55 once the with statement has completed, okay?
2:58 So in this instance, it inserts the contents of the list
3:04 using the wildcards of the three question marks,
3:07 and you saw that in our slightly automated script for this.
3:12 And that's it, so now it's your turn for Day 3.
3:16 Go ahead and implement your own database.
3:18 Try to come up with something interesting,
3:20 and see what other abilities you can try and run.
3:24 So for example, maybe try editing the data
3:28 inside the database, so we inserted and we selected
3:31 the data, but now maybe try
3:33 and actually edit the data.
3:36 I think that's a great challenge for you.
3:38 So enjoy, that's SQLite databases,
3:41 and keep calm and code in Python.