Python Data Persistence - ResultSet Object

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)

Python Data Persistence – ResultSet Object

Related