PYnative

Python Programming

  • Learn Python
  • Exercises
  • Quizzes
  • Code Editor
  • Tricks
Home » Python » Databases » Python Select from SQLite Table

Python Select from SQLite Table

Updated on: March 9, 2021 | 9 Comments

This lesson demonstrates how to execute SQLite SELECT Query from Python to retrieve rows from the SQLite table using the built-in module sqlite3.

Goals of this lesson

  • Fetch all rows using a cursor.fetchall()
  • Use cursor.fetchmany(size) to fetch limited rows, and fetch only a single row using cursor.fetchone()
  • Use the Python variables in the SQLite Select query to pass dynamic values.

Also Read:

  • Solve Python SQLite Exercise
  • Read Python SQLite Tutorial (Complete Guide)

Table of contents

  • Prerequisite
  • Steps to select rows from SQLite table
    • Example to read all rows from SQLite table
  • Use Python variables as parameters in SQLite Select Query
  • Select limited rows from SQLite table using cursor.fetchmany()
  • Select a single row from SQLite table
  • Next Steps:

Prerequisite

Before executing the following program, please make sure you know the SQLite table name and its column details.

For this lesson, I am using the ‘SqliteDb_developers’ table present in my SQLite database.

sqlitedb_developers table with data
sqlitedb_developers table with data

If a table is not present in your SQLite database, then please refer to the following articles: –

  • Create SQLite table from Python.
  • Insert data into SQLite Table from Python

Steps to select rows from SQLite table

How to Select from a SQLite table using Python

  1. Connect to SQLite from Python

    Refer to Python SQLite database connection to connect to SQLite database.

  2. Define a SQLite SELECT Query

    Next, prepare a SQLite SELECT query to fetch rows from a table. You can select all or limited rows based on your requirement.
    For example, SELECT column1, column2, columnN FROM table_name;

  3. Get Cursor Object from Connection

    Next, use a connection.cursor() method to create a cursor object. This method returns a cursor object. The Cursor object is required to execute the query.

  4. Execute the SELECT query

    Execute the select query using the cursor.execute(query) method.

  5. Extract all rows from a result

    After successfully executing a select operation, Use the fetchall() method of a cursor object to get all rows from a query result. it returns a list of rows.

  6. Iterate each row

    Iterate a row list using a for loop and access each row individually (Access each row’s column data using a column name or index number.)

  7. Close the cursor object and database connection object

    use cursor.clsoe() and connection.clsoe() method to close the SQLite connection after your work completes.

Example to read all rows from SQLite table

import sqlite3

def readSqliteTable():
    try:
        sqliteConnection = sqlite3.connect('SQLite_Python.db')
        cursor = sqliteConnection.cursor()
        print("Connected to SQLite")

        sqlite_select_query = """SELECT * from SqliteDb_developers"""
        cursor.execute(sqlite_select_query)
        records = cursor.fetchall()
        print("Total rows are:  ", len(records))
        print("Printing each row")
        for row in records:
            print("Id: ", row[0])
            print("Name: ", row[1])
            print("Email: ", row[2])
            print("JoiningDate: ", row[3])
            print("Salary: ", row[4])
            print("\n")

        cursor.close()

    except sqlite3.Error as error:
        print("Failed to read data from sqlite table", error)
    finally:
        if sqliteConnection:
            sqliteConnection.close()
            print("The SQLite connection is closed")

readSqliteTable()

Output:

Connected to SQLite
Total rows are:   6

Printing each row
Id:  1
Name:  James
Email:  james@pynative.com
JoiningDate:  2019-03-17
Salary:  8000.0

Id:  2
Name:  Joe
Email:  joe@pynative.com
JoiningDate:  2019-05-19
Salary:  9000.0

Id:  3
Name:  Ben
Email:  ben@pynative.com
JoiningDate:  2019-02-23
Salary:  9500.0

Id:  4
Name:  Jos
Email:  jos@gmail.com
JoiningDate:  2019-01-14
Salary:  9500.0

Id:  5
Name:  Chris
Email:  chris@gmail.com
JoiningDate:  2019-05-15
Salary:  7600.0

Id:  6
Name:  Jonny
Email:  jonny@gmail.com
JoiningDate:  2019-03-27
Salary:  8400.0

The SQLite connection is closed

Note: I am directly displaying each row and its column values. If you want to use column values in your program, you can copy them into python variables to use it. For example, name = row[1]

Use Python variables as parameters in SQLite Select Query

We often need to pass a variable to SQLite select query in where clause to check some condition.

Let’s say the application wants to fetch person details by giving any id at runtime. To handle such a requirement, we need to use a parameterized query.

A parameterized query is a query in which placeholders (?) are used for parameters and the parameter values supplied at execution time.

cursor.execute("SELECT salary FROM SqliteDb_developers WHERE id = "ID from application")

Example

import sqlite3

def getDeveloperInfo(id):
    try:
        sqliteConnection = sqlite3.connect('SQLite_Python.db')
        cursor = sqliteConnection.cursor()
        print("Connected to SQLite")

        sql_select_query = """select * from SqliteDb_developers where id = ?"""
        cursor.execute(sql_select_query, (id,))
        records = cursor.fetchall()
        print("Printing ID ", id)
        for row in records:
            print("Name = ", row[1])
            print("Email  = ", row[2])
            print("JoiningDate  = ", row[3])
            print("Salary  = ", row[4])
        cursor.close()

    except sqlite3.Error as error:
        print("Failed to read data from sqlite table", error)
    finally:
        if sqliteConnection:
            sqliteConnection.close()
            print("The SQLite connection is closed")

getDeveloperInfo(2)

Output:

Connected to SQLite
Printing ID  2
Name =  Joe
Email  =  joe@pynative.com
JoiningDate  =  2019-05-19
Salary  =  9000.0
The SQLite connection is closed

Select limited rows from SQLite table using cursor.fetchmany()

In some circumstances, fetching all the data rows from a table is a time-consuming task if a table contains thousands of rows.

To fetch all rows, we have to use more resources, so we need more space and processing time. To enhance performance, use the fetchmany(SIZE) method of a cursor class to fetch fewer rows.

import sqlite3

def readLimitedRows(rowSize):
    try:
        sqliteConnection = sqlite3.connect('SQLite_Python.db')
        cursor = sqliteConnection.cursor()
        print("Connected to SQLite")

        sqlite_select_query = """SELECT * from SqliteDb_developers"""
        cursor.execute(sqlite_select_query)
        print("Reading ", rowSize, " rows")
        records = cursor.fetchmany(rowSize)
        print("Printing each row \n")
        for row in records:
            print("Id: ", row[0])
            print("Name: ", row[1])
            print("Email: ", row[2])
            print("JoiningDate: ", row[3])
            print("Salary: ", row[4])
            print("\n")

        cursor.close()

    except sqlite3.Error as error:
        print("Failed to read data from sqlite table", error)
    finally:
        if sqliteConnection:
            sqliteConnection.close()
            print("The SQLite connection is closed")

readLimitedRows(2)

Output:

Connected to SQLite
Reading 2  rows
Printing each row 

Id:  1
Name:  James
Email:  james@pynative.com
JoiningDate:  2019-03-17
Salary:  8000.0

Id:  2
Name:  Joe
Email:  joe@pynative.com
JoiningDate:  2019-05-19
Salary:  9000.0

The SQLite connection is closed

Note: In the above program, the specified size is 2 to fetch two records. If the SQLite table contains rows lesser than the specified size, then fewer rows will return.

Select a single row from SQLite table

When you want to read only one row from the SQLite table, then you should use fetchone() method of a cursor class. You can also use this method in situations when you know the query is going to return only one row.

The cursor.fetchone() method retrieves the next row from the result set.

import sqlite3

def readSingleRow(developerId):
    try:
        sqliteConnection = sqlite3.connect('SQLite_Python.db')
        cursor = sqliteConnection.cursor()
        print("Connected to SQLite")

        sqlite_select_query = """SELECT * from SqliteDb_developers where id = ?"""
        cursor.execute(sqlite_select_query, (developerId,))
        print("Reading single row \n")
        record = cursor.fetchone()
        print("Id: ", record[0])
        print("Name: ", record[1])
        print("Email: ", record[2])
        print("JoiningDate: ", record[3])
        print("Salary: ", record[4])

        cursor.close()

    except sqlite3.Error as error:
        print("Failed to read single row from sqlite table", error)
    finally:
        if sqliteConnection:
            sqliteConnection.close()
            print("The SQLite connection is closed")

readSingleRow(3)

Output:

Connected to SQLite
Reading single row 

Id:  3
Name:  Ben
Email:  ben@pynative.com
JoiningDate:  2019-02-23
Salary:  9500.0
The SQLite connection is closed

Next Steps:

To practice what you learned in this article, Please solve a Python Database Exercise project to Practice and master the Python Database operations.

Filed Under: Python, Python Databases

Did you find this page helpful? Let others know about it. Sharing helps me continue to create free Python resources.

TweetF  sharein  shareP  Pin

About Vishal

Founder of PYnative.com I am a Python developer and I love to write articles to help developers. Follow me on Twitter. All the best for your future Python endeavors!

Related Tutorial Topics:

Python Python Databases

Python Exercises and Quizzes

Free coding exercises and quizzes cover Python basics, data structure, data analytics, and more.

  • 15+ Topic-specific Exercises and Quizzes
  • Each Exercise contains 10 questions
  • Each Quiz contains 12-15 MCQ
Exercises
Quizzes

Posted In

Python Python Databases
TweetF  sharein  shareP  Pin

  Python SQLite

  • Python SQLite Guide
  • Python SQLite Insert
  • Python SQLite Select
  • Python SQLite Update
  • Python SQLite Delete
  • Python SQLite Create Functions
  • Python Parameterized Query
  • Python SQLite BLOB
  • Python SQLite DateTime
  • Python Database Exercise

All Python Topics

Python Basics Python Exercises Python Quizzes Python File Handling Python OOP Python Date and Time Python Random Python Regex Python Pandas Python Databases Python MySQL Python PostgreSQL Python SQLite Python JSON

About PYnative

PYnative.com is for Python lovers. Here, You can get Tutorials, Exercises, and Quizzes to practice and improve your Python skills.

Explore Python

  • Learn Python
  • Python Basics
  • Python Databases
  • Python Exercises
  • Python Quizzes
  • Online Python Code Editor
  • Python Tricks

Follow Us

To get New Python Tutorials, Exercises, and Quizzes

  • Twitter
  • Facebook
  • Sitemap

Legal Stuff

  • About Us
  • Contact Us

We use cookies to improve your experience. While using PYnative, you agree to have read and accepted our Terms Of Use, Cookie Policy, and Privacy Policy.

Copyright © 2018–2023 pynative.com