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 objectconnection_obj = sqlite3.connect('geek.db') # cursor objectcursor_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 connectionconnection_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 objectconnection_obj = sqlite3.connect('geek.db') # cursor objectcursor_obj = connection_obj.cursor() # to select all column we will usestatement = '''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 connectionconnection_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 objectconnection_obj = sqlite3.connect('geek.db') # cursor objectcursor_obj = connection_obj.cursor() # to select all column we will usestatement = '''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 connectionconnection_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 objectconnection_obj = sqlite3.connect('geek.db') # cursor objectcursor_obj = connection_obj.cursor() # to select all column we will usestatement = '''SELECT * FROM GEEK''' cursor_obj.execute(statement) print("Only one data")output = cursor_obj.fetchone()print(output) connection_obj.commit() # Close the connectionconnection_obj.close() |
Output:
