Python SQLite - Select Data from Table
SELECT statement in SQLite is used to query and retrieve data from one or more tables in a database. It allows you to choose which columns you want to see, filter rows, sort results, and even perform calculations.
Syntax
SELECT * FROM table_name;
- *: Retrieves all columns from the table.
- If you want to select specific columns, replace * with the column names, e.g., SELECT column1, column2 FROM table_name.
Let's discuss it in detail, with examples:
Below is the snapshot of the table named "Geek" we are going to work around in this article:
Creating a Table in SQLite
Let's create a table called GEEK and insert some sample data into it using the Python sqlite3 module.
import sqlite3
# Connecting to sqlite database
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
To fetch all records from the table, we use the fetchall() method. This will retrieve all the rows.
Syntax:
cursor.fetchall()
- cursor is an object of sqlite3 connection with database.
Code Example:
import sqlite3
# Connecting to sqlite databse
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)
- size: a limit to fetch records
- cursor: an object of sqlite3 connection with database.
Code Example:
import sqlite3
# Connecting to sqlite database
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
To retrieve a single row from the result set, we use the fetchone() method. This is useful when you expect only one record to be returned.
Syntax:
cursor.fetchone()
- cursor: an object of sqlite3 connection with database.
Code Example:
import sqlite3
# Connecting to sqlite database
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:
