Convert SQL Result to List in Python (Example)

Hi! This tutorial will show you how to turn SQL results into a list in the Python programming language.

First, though, here is an overview:

Let’s dive right in!

Import sqlite3 Module

In this tutorial, we will demonstrate how to use the sqlite3 Python module to connect to a SQLite database, which is a lightweight disk-based database that does not require being hosted on a separate remote server.

Please note that this tutorial will not walk you through the steps of creating a database in SQLite; it will rather only demonstrate the steps for fetching the data from the database, and transforming that data into a Python list. It is also assumed that you already have some knowledge of SQL and how to query a relational database.

The sqlite3 module is a built-in module in Python. Therefore, to import the module, run the line of code below in your preferred Python IDE as follows:

import sqlite3

Connect to Database

The first step is to connect to an existing SQLite database. To do so, you can run your code like this:

conn = sqlite3.connect("demo.db")

Where “demo.db” is the name of your SQLite database. Once connected, you can then proceed to the next step.
 

Create a Cursor Object

Next, we will create a cursor object:

cur = conn.cursor()

The cursor object is what will enable us to execute SQL queries.
 

Execute a Statement

Having created the cursor object, we will now execute a statement that will enable us to select all the columns in the table inside our database:

cur.execute("SELECT * FROM my_table")

It is assumed that you already have a table inside your database, from which you can either select individual columns by naming them separated by commas or parsing all columns by the asterisk * just as demonstrated above.

Fetch all Rows as List of Tuples

The next step is to fetch all the rows as a list of tuples via fetchall():

rows = cur.fetchall()

Turn List of Tuples to List of Lists

We will now turn the list of tuples to a list of lists:

result_list = [list(row) for row in rows]

In the above code, we used list comprehension method to iterate through the list of tuples, and convert each tuple to a list with the list() function, and then stored them inside of the list named “result_list” to create a list of lists.
 

Close Cursor & Connection

Now that we have successfully fetched the data from the database, we will now close the cursor and the database connection:

cur.clode()
conn.close()

Print out the Result

Finally, we will print out the resulting list of lists like so:

print(result_list)

With that, we have demonstrated how to transform the result of a SQL query to a list in Python. I hope you found this tutorial helpful!

Video, Further Resources & Summary

Do you need more explanations on how to transform the result of a SQL query to a list in Python? Then you should have a look at the following YouTube video of the Statistics Globe YouTube channel.

In the video, we explain in some more detail how to transform the result of a SQL query to a list in Python.

The YouTube video will be added soon.

Furthermore, I encourage you to check out other interesting Python list tutorials on Statistics Globe, starting with these ones:

• Convert List to Matrix and Vice-Versa in Python (Examples)
• Access Element in List within Dictionary in Python (2 Examples)
• Check if List of Lists is Empty in Python (2 Examples)
• Convert List from Boolean to Float in Python (2 Examples)
• Learn Python Programming

This post has shown how to transform the result of a SQL query to a list in Python. In case you have further questions, you may leave a comment below.

This page was created in collaboration with Ifeanyi Idiaye. You might check out Ifeanyi’s personal author page to read more about his academic background and the other articles he has written for the Statistics Globe website.