Python SQLite – Select Data from Table

In this article, we will discuss, select statement of the Python SQLite module. This statement is used to retrieve data from an SQLite table and this returns the data contained in the table.

In SQLite the syntax of Select Statement is:

SELECT * FROM table_name;

*  : means all the column from the table

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

Now we will use the Select statement in the Python program and see the results:

Demonstration of our GEEK table:

Creating the above table:

Here we are going to create the table using the above approach.

Python3

import sqlite3

# Connecting to sqlite
# connection object
connection_obj = sqlite3.connect('geek.db')

# cursor object
cursor_obj = connection_obj.cursor()

connection_obj.execute("""CREATE TABLE GEEK(
  Email varchar(255),
  Name varchar(50),
  Score int
  );""")

connection_obj.execute(
    """INSERT INTO GEEK (Email,Name,Score) VALUES ("geekk1@gmail.com","Geek1",25)""")
connection_obj.execute(
    """INSERT INTO GEEK (Email,Name,Score) VALUES ("geekk2@gmail.com","Geek2",15)""")
connection_obj.execute(
    """INSERT INTO GEEK (Email,Name,Score) VALUES ("geekk3@gmail.com","Geek3",36)""")
connection_obj.execute(
    """INSERT INTO GEEK (Email,Name,Score) VALUES ("geekk4@gmail.com","Geek4",27)""")
connection_obj.execute(
    """INSERT INTO GEEK (Email,Name,Score) VALUES ("geekk5@gmail.com","Geek5",40)""")
connection_obj.execute(
    """INSERT INTO GEEK (Email,Name,Score) VALUES ("geekk6@gmail.com","Geek6",36)""")
connection_obj.execute(
    """INSERT INTO GEEK (Email,Name,Score) VALUES ("geekk7@gmail.com","Geek7",27)""")

connection_obj.commit()

# Close the connection
connection_obj.close()

Read All Rows:

Now we will use the Select statement to retrieve data from the table and fetch all records. To fetch all records we will use fetchall() method.

Syntax: cursor.fetchall()

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

Code:

Python3

import sqlite3

# Connecting to sqlite
# connection object
connection_obj = sqlite3.connect('geek.db')

# cursor object
cursor_obj = connection_obj.cursor()

# to select all column we will use
statement = '''SELECT * FROM GEEK'''

cursor_obj.execute(statement)

print("All the data")
output = cursor_obj.fetchall()
for row in output:
  print(row)

connection_obj.commit()

# Close the connection
connection_obj.close()

Output:

Read Some Rows:

Now we will use the Select statement to retrieve data from the table and fetch many records not all. To fetch many records we will use fetchmany() method.

Syntax: cursor.fetchmany(size)

Parameters: size – a limit to fetch records

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

Code:

Python3

import sqlite3

# Connecting to sqlite
# connection object
connection_obj = sqlite3.connect('geek.db')

# cursor object
cursor_obj = connection_obj.cursor()

# to select all column we will use
statement = '''SELECT * FROM GEEK'''

cursor_obj.execute(statement)

print("Limited data")
output = cursor_obj.fetchmany(5)
for row in output:
  print(row)

connection_obj.commit()

# Close the connection
connection_obj.close()

Output:

Read Only one Row:

Now e will use the Select statement to retrieve data from the table and fetch only one record. To fetch only one record, we will use fetchone() method.

Syntax: cursor.fetchone()

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

Python3

import sqlite3

# Connecting to sqlite
# connection object
connection_obj = sqlite3.connect('geek.db')

# cursor object
cursor_obj = connection_obj.cursor()

# to select all column we will use
statement = '''SELECT * FROM GEEK'''

cursor_obj.execute(statement)

print("Only one data")
output = cursor_obj.fetchone()
print(output)

connection_obj.commit()

# Close the connection
connection_obj.close()

Output: