Python Data Persistence – ResultSet Object
We need to call execute ( ) method on cursor object with a SELECT query string as its argument and a query result set is built which is similar to an iterator of rows returned in response to the query. The module provides the following methods to traverse the result set:
fenchone (): Next row in the query result set is retrieved and returned in the form of a tuple. The method returns None if there are no more rows to be fetched,
fetchall (): This method returns a list of all available rows in the result set. It contains a sequence corresponding to each row. You can employ a regular for loop to traverse the rows, as follows:
Example
import sqlite3
conn=sqlite3.connect('mydb.sqlite')
cur=conn.cursor()
qry="select * from Products;"
cur.execute(qry)
rows=cur.fetchall()
for row in rows:
print (row)
conn.close()
Run the above code (‘ selectqry. py’) from the command prompt.
E:\python37>python selectqry.py (1, 'Laptop', 25000) (2, ’TV', 40000) (3, 'Router', 2000) (4, 'Scanner' , 5000) (5, 'Printer' , 9000) (6, 'Mobile', 15000)
The fact that execute () method runs a parameterized query can be used to good effect to search for a certain condition in the table. The following code snippet accepts the product’s name as input and displays its price.
Example
import sqlite3
conn=sqlite3.connect('mydb.sqlite') nm=input ('Enter name of product:') cur=conn.cursor( )
qry="select * from Products where name=?";
cur.execute(qry, (nm,))
row=cur.fetchone( )
print (row)
conn.close( )
When the above script is run from the command prompt, then it above script shows the following output:
E:\python37>python selecttqry.py Enter name of product:TV (2, 'TV', 40000)
Individual items in the ‘row’ tuple can be accessed by index. The row can also be unpacked in separate variables as under:
Example
row=cur fetchone( ) print ( ' ID: ', row [ 0] , 'Name: 1, row[1], 'price:', row [2 ] ) id, nm, p=row print ( ID:', id, 'Name:', nm, 1 price: ' , p)