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:
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:
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
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():