Using Fetchall Fetchmany Fetchone()

 

Python cursor’s fetchall, fetchmany(), fetchone() to read records from database table:




what is the use of fetchall, fetchmany, fetchone?
 
 

cursor.fetchall() fetches all the rows of a query result. It returns all the rows as a list of tuples. An empty list is returned if there is no record to fetch.

cursor.fetchmany(size) returns the number of rows specified by size argument. When called repeatedly, this method fetches the next set of rows of a query result and returns a list of tuples. If no more rows are available, it returns an empty list.

cursor.fetchone() method returns a single record or None if no more rows are available
 
 
 
 

Fetch all rows from database table using cursor’s fetchall:

To fetch all rows from a database table, you need to follow these simple steps:

  • Create a database Connection from Python. Refer Python SQLite connection, Python MySQL connection, Python PostgreSQL connection.
  • Define the SELECT query. Here you need to know the table and its column details.
  • Execute the SELECT query using the cursor.execute() method.
  • Get resultSet (all rows) from the cursor object using a cursor.fetchall().
  • Iterate over the ResultSet using for loop and get column values of each row.
  • Close the Python database connection.
  • Catch any SQL exceptions that may come up during the process.
 
 
 
 

Retrieve a few rows from a table using:

cursor.fetchmany(size)

 
 Here size is the number of rows to be retrieved. This method fetches the next set of rows of a query result and returns a list of tuples. If no more rows are available, it returns an empty list.
 
 
 Cursor’s fetchmany() method returns the number of rows specified by size argument. the default value is 1. If the specified size is 100, then it returns 100 rows.
 
 
 

The syntax of the cursor’s fetchmany()

rows = cursor.fetchmany([size=cursor.arraysize])
 
 
 

Retrieve a single row from a table using cursor.fetchone:

 

Python DB API allows us to fetch only a single row. To fetch a single row from a result set we can use cursor.fetchone(). This method returns a single tuple.
 
 
 It can return a none if no rows are available in the resultset. cursor.fetchone() increments the cursor position by one and return the next row.
 
 
 
 
 
 
 
 
 
 

Comments

Post a Comment

Popular posts from this blog

Queue in Java

Image
  Queue Interface In Java :   The Queue interface present in the java.util package and extends the Collection interface is used to hold the elements about to be processed in FIFO(First In First Out) order.  It is an ordered list of objects with its use limited to insert elements at the end of the list and deleting elements from the start of the list, (i.e.), it follows the FIFO or the First-In-First-Out principle.       queue needs a concrete class for the declaration and the most common classes are the PriorityQueue and LinkedList in Java.   Queue is an interface, objects cannot be created of the type queue. We always need a class which extends this list in order to create an object   Example : import java.util.LinkedList ; import java.util.Queue ; public class q { public static void main (String[] args) { Queue<Integer> qu = new LinkedList<>() ; for ( int i = 0 ; i < 5 ; i++) ...
Read more

Using lists , tuples, sets, dictionaries

  DAY 2 :   lists , tuples, sets, dictionaries lists: Lists are one of 4 built-in data types in Python used to store collections of data, the other 3 are tuple,set and dictionary, all with different qualities and usage. Lists are used to store multiple items in a single variable. Lists are created using square brackets     example: my_list = ["apple","banana","orange"]                         list2 = [5,7,9,6,3]   methods in list : append() Adds an element at the end of the list clear() Removes all the elements from the list copy() Returns a copy of the list count() Returns the number of elements with the specified value extend() Add the elements of a list (or any iterable), to the end of the current list index() Returns the index of the first element with the specified value insert() Adds an element at the specified position pop() Removes the element at the ...
Read more

Blog on Pytest

  Testing Python Applications with Pytest     Pytest is a testing framework and test runner for Python. In this guide, we will look at the most useful configuration and usage, including several pytest plugins and external libraries.   Pytest basics  Pytest will automatically scan our codebase for test modules, files following the file name convention test_*.py or *_test.py , and scan them for functions named test_*() . If we want to run tests defined as methods inside classes, we need to prefix the class names with Test . Pytest will also automatically pick up any tests written using unittest module.   Standard test functions and test runs from python_pytest . basics import add def test_add_can_add_numbers ( ) : # given num = 3 num2 = 45 # when result = add ( num , num2 ) # then assert result == 48 By default, fixtures are function-scoped, meaning that a fixture's lifetime is t...
Read more