Inspect Database Return Types

Python Basics: Lists and Tuples Martin Breuss 03:18

Transcript
Discussion (2)

00:00 In this lesson, I want to sidetrack a little bit just to continue establishing this connection in your brain between lists and database records. I told you before that lists are kind of like read-only database records, and it’s by no accident that a lot of Python libraries that allow you to interact with databases can return the rows of a database as tuples.

00:22 And then you can also unpack these if you need access to individual values. So let’s try that out in IDLE.

00:30 Here’s a script that creates a database named "company_db" and with a table employees. And then just inserts values into this. And you can see down here the tuple that we’re inserting is, again, the one that you’ve seen before with employee ID, a name, and then a role.

00:49 You don’t have to worry about this code too much. It’ll also be included in the downloadable resources. It uses sqlite3 to create an SQLite database with this one record in it.

01:00 Basically, I’ll go ahead and press F5 to run this code. And you can see that the database company_db was created successfully. So now I should have this sitting in my documents folder and now I’ll be able to open a different file,

01:19 the read.py file.

01:24 And what this file does is it reads and displays data from an SQLite database, so the one that we just created called company_db,

01:35 and I’m connecting to this database and then selecting everything that’s in the employees table. And then just fetch the first row. There’s only one row in there, and that’ll be returned as a tuple.

01:46 So Python will give you that back as a tuple. And then the script is going to print out the row, print out the type of the row, and then also unpack the values.

01:55 So it’ll take employee_id, name, and role by unpacking the row tuple. And then I’m just printing out the tuple to show that this actually functions as expected.

02:07 Let’s run the script, and you can see that we get first the print-out of the row. So Python connects to the SQLite database, fetches the one record that’s in there, and then prints it out.

02:18 And as you can see, this is in fact a tuple. So Python represents the data that it gets back from the database, this one record, as a tuple. And then you can also see here that we unpacked them successfully.

02:30 So employee_id now points to 1, name points to Adisa, and role points to software engineer.

02:40 And again, we can also look at the role and confirm that this is indeed a tuple that Python returns.

02:49 Database connector libraries often consider tuple as the best representation for a database record. So this is just a little support for you to continue thinking of tuples as database records.

03:03 Here’s a quick write-up of the code that I used to create this database, and then also to read from the database. And as I mentioned, you’ll find this code also in the downloadable resources if you’re curious to look at it some more.

Ajay Yada on July 18, 2024

Hi I get the error when I run the code i.e, setup.py. I am using chromebook.

Here is the error

Python 3.9.2 (default, Feb 28 2021, 17:03:44) 
[GCC 10.2.1 20210110] on linux
Type "help", "copyright", "credits" or "license()" for more information.
>>> 
==== RESTART: /mnt/chromeos/MyFiles/Real Python/PB_tuples_and_lists/setup.py ===
Traceback (most recent call last):
  File "/mnt/chromeos/MyFiles/Real Python/PB_tuples_and_lists/setup.py", line 9, in <module>
    with sqlite3.connect("company.db") as connection:
sqlite3.OperationalError: unable to open database file

Ajay Yada on July 18, 2024

Ignore above post. I got it running now.

Become a Member to join the conversation.