Python Select Data Table in SQLite Database

In this tutorial, we’re going to learn how to implement a Python program to select data rows from a SQLite data table.

You can read these posts to learn other actions related to SQLite database:

For example, we have a SQLite database located at D:\ToriCode\db\contacts.db with the contacts table as below data.

Step 1 - Create connection to an existing SQLite database

In order to execute a select query we need to create a connection object as below Python code.

import sqlite3 
from sqlite3 import Error

connection = None
try:
    connection = sqlite3.connect('D:\ToriCode\db\contacts.db')
except Error as e:
    print(e)
finally:
    if connection:
        print('Connect to database successfully.')

Step 2 - Select a row in SQLite data table by id column

Implement the Python function to select contacts record by id.

def select_by_id(conn, id):
    sql_select_by_id = 'SELECT * FROM contacts WHERE id=?'
    cursor = conn.cursor()
    cursor.execute(sql_select_by_id, (id,))
    rows = cursor.fetchall()
    for row in rows:
        print(row)

Step 3 - Select all data rows in SQLite data table

Implement the Python function to select all data in the contact table.

def select_all(conn):
    sql_select_all = 'SELECT * FROM contacts'
    cursor = conn.cursor()
    cursor.execute(sql_select_all)
    rows = cursor.fetchall()
    for row in rows:
        print(row)

Below example code to execute function above to select data.

print('Select by id = 2, result:')
select_by_id(connection, 2)

print('\nSelect all rows in contacts table, result:')
select_all(connection)

connection.close()

Complete Python program code.

import sqlite3 
from sqlite3 import Error

connection = None
try:
    connection = sqlite3.connect('D:\ToriCode\db\contacts.db')
except Error as e:
    print(e)
finally:
    if connection:
        print('Connect to database successfully.')

def select_by_id(conn, id):
    sql_select_by_id = 'SELECT * FROM contacts WHERE id=?'
    cursor = conn.cursor()
    cursor.execute(sql_select_by_id, (id,))
    rows = cursor.fetchall()
    for row in rows:
        print(row)

def select_all(conn):
    sql_select_all = 'SELECT * FROM contacts'
    cursor = conn.cursor()
    cursor.execute(sql_select_all)
    rows = cursor.fetchall()
    for row in rows:
        print(row)

print('Select by id = 2, result:')
select_by_id(connection, 2)

print('\nSelect all rows in contacts table, result:')
select_all(connection)

connection.close()

Output:

Connect to database successfully.
Select by id = 2, result:
(2, 'Alan', 'Whitaker')

Select all rows in contacts table, result:
(1, 'Nancy', 'Harwood')
(2, 'Alan', 'Whitaker')
(3, 'Kealan', 'Parker')
(4, 'Marion', 'Paul')

Python Select Data Table in SQLite Database

Step 1 - Create connection to an existing SQLite database

Step 2 - Select a row in SQLite data table by id column

Step 3 - Select all data rows in SQLite data table

Tags: