SELECT rows FROM Table - sqlite3 - Python Examples

Select Rows from sqlite3 Table

In this tutorial, we will learn how to select rows from a table in an sqlite3 database using Python.

Selecting rows from a table is a fundamental task when working with databases. You can select all rows or filter specific rows based on conditions.

Steps to Select Rows from sqlite3 Table

To select rows from a table in an sqlite3 database, follow these steps:

  1. Create a connection object to the sqlite database.
  2. Create a cursor object to interact with the database.
  3. Use the sqlite3.execute() method with a SELECT FROM query to retrieve data from the table.

Examples

1. Select All Rows from sqlite3 Table

This program fetches all the rows from the students table in the mysqlite.db database.

Python Program

import sqlite3

# Create a connection to the database
conn = sqlite3.connect('mysqlite.db')

# Create a cursor object
c = conn.cursor()

# Execute the SELECT query to fetch all rows
c.execute('''SELECT * FROM students;''')

# Fetch all rows from the query result
rows = c.fetchall()

# Print each row
for row in rows:
    print(row)

# Commit the changes (if any) and close the connection
conn.commit()
conn.close()

Explanation:

  1. sqlite3.connect('mysqlite.db'): Connects to the mysqlite.db database.
  2. cursor(): Creates a cursor object for executing SQL commands.
  3. SELECT * FROM students: Executes the SQL query to retrieve all rows from the students table.
  4. fetchall(): Fetches all the rows returned by the query.
  5. conn.commit() and conn.close(): Saves changes (if any) and closes the connection.

Output

(1.0, 'Glen', 8.0)
(2.0, 'Elliot', 7.0)
(3.0, 'Gene', 7.0)

This output shows the rows in the students table, where each tuple represents a row in the table.

2. Select Rows with WHERE Clause

In this example, we will filter the rows using a WHERE clause to fetch only those records where the student's name is "Elliot".

Python Program

import sqlite3

# Create a connection to the database
conn = sqlite3.connect('mysqlite.db')

# Create a cursor object
c = conn.cursor()

# Execute SELECT query with WHERE clause
c.execute('''SELECT * FROM students WHERE name="Elliot";''')

# Fetch the matching rows
rows = c.fetchall()

# Print the results
for row in rows:
    print(row)

# Commit changes and close the connection
conn.commit()
conn.close()

Explanation:

  1. WHERE name="Elliot": Filters the rows to include only those where the name is "Elliot".
  2. The output will display the row for the student named Elliot.

Output

(2.0, 'Elliot', 7.0)

This output shows the row where the student's name is "Elliot".

3. Select with Sorting (ORDER BY)

You can sort the rows by one or more columns using the ORDER BY clause. This example sorts the rows by the class column in ascending order.

Python Program

import sqlite3

# Create a connection to the database
conn = sqlite3.connect('mysqlite.db')

# Create a cursor object
c = conn.cursor()

# Execute SELECT query with ORDER BY clause
c.execute('''SELECT * FROM students ORDER BY class ASC;''')

# Fetch the sorted rows
rows = c.fetchall()

# Print the sorted results
for row in rows:
    print(row)

# Commit changes and close the connection
conn.commit()
conn.close()

Explanation:

  1. ORDER BY class ASC: Sorts the rows by the class column in ascending order.
  2. Ascending sorting is the default; use DESC for descending order if needed.

Output

(2.0, 'Elliot', 7.0)
(3.0, 'Gene', 7.0)
(1.0, 'Glen', 8.0)

The output shows the rows sorted by class in ascending order.

4. Select Specific Columns

If you want to retrieve specific columns instead of all columns, you can specify the column names in the SELECT query.

Python Program

import sqlite3

# Create a connection to the database
conn = sqlite3.connect('mysqlite.db')

# Create a cursor object
c = conn.cursor()

# Execute SELECT query to fetch only name and class columns
c.execute('''SELECT name, class FROM students;''')

# Fetch the rows
rows = c.fetchall()

# Print the results
for row in rows:
    print(row)

# Commit changes and close the connection
conn.commit()
conn.close()

Explanation:

  1. SELECT name, class FROM students: Fetches only the name and class columns from the students table.
  2. This allows for more efficient data retrieval when only specific columns are needed.

Output

('Glen', 8.0)
('Elliot', 7.0)
('Gene', 7.0)

This output shows only the name and class columns for each student.

Summary

In this tutorial, we learned how to use the SELECT FROM query to retrieve data from an sqlite3 table. We covered examples on selecting all rows, filtering with a WHERE clause, sorting with ORDER BY, and selecting specific columns. These are the foundational operations for working with data in an sqlite3 database using Python.



Python Libraries