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