Skip to content
Related Articles

Related Articles

Improve Article

Python SQLite – Select Data from Table

  • Last Updated : 23 May, 2021

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:

 Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.  

To begin with, your interview preparations Enhance your Data Structures concepts with the Python DS Course. And to begin with your Machine Learning Journey, join the Machine Learning – Basic Level Course




My Personal Notes arrow_drop_up
Recommended Articles
Page :