Python SQLite SELECT FROM Table

In this article we take a look at the select statement of the Python SQLite module. This statement is used to retrieve data from an SQLite table and it then returns the data which is in the table.

In SQLite the syntax of Select Statement is:

SELECT * FROM table_name;

To select a specific column you replace the * with the column name or column names.

SELECT column_name FROM table_name;

Steps

To select the rows from table of sqlite3 database

1. Create a connection object to the sqlite database.
2. Create a cursor to the connection.
3. Run the sqlite execute() method with the SELECT FROM query passed to the method.

Examples

Fetch all

The fetchall() method retrieves all the rows in the result set of a query and returns them as list of tuples.

Syntax: cursor.fetchall()

where, cursor is an object of sqlite3 connection with database.

import sqlite3
#Connecting to sqlite
conn = sqlite3.connect('country1.db')

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#insert data
cursor.execute('''SELECT * FROM COUNTRY''')

#fetch all data
print("All the data")
output = cursor.fetchall()
for row in output:
  print(row)

#Commit your changes in the database
conn.commit()

#Closing the connection
conn.close()

This displayed the following

>>> %Run sqlitefetchall.py
All the data
(‘France', ‘Paris', ‘fr', 67413000)
(‘Germany', ‘Berlin', ‘de', 83190556)
(‘Spain', ‘Madrid', ‘es', 47450795)
(‘Italy', ‘Rome', ‘it', 60317116)

Fetch many

The fetchmany() method retrieves the next set of rows in the result set of a query, instead of a single row.

Syntax: cursor.fetchmany(size)

Parameters: size – a limit to fetch records
where, cursor is an object of sqlite3 connection with database.

import sqlite3
#Connecting to sqlite
conn = sqlite3.connect('country1.db')

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#insert data
cursor.execute('''SELECT * FROM COUNTRY''')

#fetch 3 results
print("Limited data")
output = cursor.fetchmany(3)
for row in output:
  print(row)

#Commit your changes in the database
conn.commit()

#Closing the connection
conn.close()

This displayed the following

>>> %Run sqlitefetchmany.py
Limited data
(‘France', ‘Paris', ‘fr', 67413000)
(‘Germany', ‘Berlin', ‘de', 83190556)
(‘Spain', ‘Madrid', ‘es', 47450795)

Fetch one

The fetchone() method fetches the next row in the result of a query and returns it as a tuple.

Syntax: cursor.fetchone()

where, cursor is an object of sqlite3 connection with database.

import sqlite3
#Connecting to sqlite
conn = sqlite3.connect('country1.db')

#Creating a cursor object using the cursor() method
cursor = conn.cursor()

#insert data
cursor.execute('''SELECT * FROM COUNTRY''')

#fetch 1 results
print("One item of a data")
output = cursor.fetchone()
print(output)

#Commit your changes in the database
conn.commit()

#Closing the connection
conn.close()

This displayed the following

>>> %Run sqlitefetchone.py
One item of a data
(‘France', ‘Paris', ‘fr', 67413000)