Python SQLite Select

SQLite is a C-written embedded database library. Other database systems like PostgreSQL or MySQL, possibly some you are already aware of. These employ a client-server architecture, in which the database is set up as a server and accessed by a client. Because SQLite is embedded into a program as a library, it is different and is referred to as an embedded database. All the data is kept in a file, typically with a .db extension, and using the defined functions we can run/execute SQL statements. Also, using these functions allows us to carry out various database operations. This tutorial covers how to use or run a SELECT query to retrieve data from the SQLite table using the sqlite3 module which is an inbuilt library of python.

What is the SELECT Statement in SQLite Python?

The main statement that we use to query an SQLite database is the SELECT clause. What to select or fetch is specified in the SELECT clause. The FROM clause is usually used with the SELECT clause to specify from where we want to access or retrieve the data.

Prerequisite

You must first connect to the database and a table with data rows and columns should be available. You should be familiar with the name of the SQLite table and the details of its columns before running the following application.

How to Use the SELECT Query in Python?

Python users can use the following steps to select/retrieve data from the database table:

1. To connect to the SQLite database, create a connection object.
2. Next, use the cursor function of the connection object for creating a Cursor object.
3. After that, you can run a SELECT statement using sqlite3.execute() function.

Connecting to SQLite Database

1. Import the sqlite3 module first into the program. The classes and functions provided by the sqlite3 module help us to interact with the database.
2. Then, use the database name in the connect() function of the connector class. The databases to which you wish to connect must be supplied to connect to SQLite. It will create a connection to the SQLite database you provide if it is already existent on the system. However, a new database will be created if the database with the specified name does not exists.
3. Create a cursor using the cursor() function to run SQLite statements and queries from Python.
4. The SQL query is executed by the execute() methods and the output is returned.

Code:

Output:

After establishing the connection, we passed a query to retrieve the version of our SQLite database. The function “cursor.fetchall()” was used to read the query results. After finishing the task, we can use the clsoe() and connection.clsoe() methods to close the cursor and SQLite connections.

We learned how to connect to the SQLite database. Now, we need a table from which we can retrieve data and show you how to use the SELECT clause to achieve our goal.

Creating ag Database Table and Inserting Data Into it

Creating ag Database Table and Inserting Data Into it
We will learn in this section how to use Python to create an SQLite database table in an SQLite database. Creating a table is a Data definition language (DDL) statement. In the program below, we will create a table “student” in the “my_db.db” database. The steps given below can be used to create a table in the sqlite database:

1. Using sqlite3.connect() method to connect to SQLite.
2. Create a table query.
3. Using a cursor.execute(query), run the query.

Code:

Output:

We created a table “students” by using CREATE TABLE clause/query and execute it using the cursor.execute() function. Then, we used the commit function to ensure that the database changes are consistent. Now, to insert data into our table ‘students’, we will repeat the same procedure as we did for creating our table but this time, we will use a different query. The insert query is used to add/enter/insert data into an already existing database table. The VALUES clause is used to specify the value for each row of data inside the table. The insert query might look as follows:

Where col1, col2… are the names of the columns and val1, val2… are the values for each column of the rows in our table.

Let us now add some data to the database table.

Code:

Output:

We passed the insert query inside the cursor.execute() function and called the function multiples time with different data each time to insert data rows in our ‘students’ table.

Now let us see how to retrieve/fetch data from the SQLite table using a SELECT statement in python.

Steps for Retrieving Data Using SELECT

1. Using the sqlite3 module, connect to the SQLite database.
2. To retrieve rows from a table, define a SELECT query. Depending on the requirements, you can extract all or just a few rows.
3. The next step is to create a cursor using the “connection.cursor()” method. The query cannot be run without the Cursor object.
4. Run the SELECT query.
5. To retrieve all rows, use the fetchall() function of a cursor object. It will return a list of table rows.
6. Using a for loop to iterate a list of rows, then access each row separately.
7. Cursor and database connection objects can be closed using the cursor.close() and connection.close() methods, respectively.

Example # 1:

Here, we will use the SELECT query to obtain or retrieve all the data from the ‘students’ table. For this, we will use ‘*’ with the SELECT statement and use the table name ‘students’ with the FROM statement to specify the table from which we want to retrieve data.

Code:

Output:

All the data that we stored when creating the table is retrieved by using the program. The query “SELECT * FROM students;” is used to get all the data of table ‘students’. The cursor.fetchall() function retrieved/fetched all rows of the query and returned a list of tuples.

Example # 2:

Instead of using the cursor.fetchall() function, we can also use the fetchmany() function if we want a certain amount of rows only. Sometimes, if a table has thousands of rows, it can take a long time to fetch all the data from it. We require greater processing power and storage because we must use more resources to fetch all rows. Using the cursor class’s fetchmany(size) method, we can fetch fewer rows and improve efficiency.

Code:

Output:

This program is similar to the program we used in example 1 but the difference is that instead of using the fetchall() function we used fetchmany() which takes an integer value as input to retrieve some/limited rows. We created a function ‘rowLimit’ to pass the parameter size value to the cursor.fetchmany() function. For retrieving three records, the size supplied in the aforementioned script is 3. Fewer rows will be returned if the table has rows that are smaller than the specified size.

Conclusion

In this tutorial, we first saw an introduction to SQLite and how you can access SQLite using python. The goal of this article is to teach how we can use the SELECT query in SQLite python. For retrieving data from a database table. We first discussed how a connection can be established between python and SQLite. Then we explained how to create a database table and insert values into it. Then at the last, we saw how we can use a SELECT query to retrieve all or a certain number of rows with the help of fetchall() and fetchmany() functions.