How to use Python cursor’s fetchall, fetchmany(), fetchone() to read records from SQL

  • MySQL — — — — MySQL Connector Pytho
  • PostgreSQL — — — — Psycopg2
  • SQLite — — — — sqlite3

Fetch all rows from the database table using cursor’s fetchall()

  1. Create a database Connection from Python.
  2. Define the SELECT query. Here you need to know the table, and it’s column details.
  3. Execute the SELECT query using the cursor.execute() method.
  4. Get resultSet (all rows) from the cursor object using a cursor.fetchall().
  5. Iterate over the ResultSet using for loop and get columns values of each row.
  6. Close the Python database connection.
  7. Catch any SQL exceptions that may come up during the process.
import sqlite3

def getAllRecords():
try:
connection = sqlite3.connect('SqlTest.db')
cursor = connection.cursor()
print("Connected to SQLite")

sqlite_select_query = """SELECT * from database_score"""
cursor.execute(sqlite_select_query)
records = cursor.fetchall()
print("Total rows are: ", len(records))
print("Printing each row")
for row in records:
print("Name: ", row[0])
print("Email: ", row[1])
print("TestDate: ", row[2])
print("Score: ", row[3])
print("\n")
cursor.close()

except sqlite3.Error as error:
print("Failed to read data from table", error)
finally:
if (connection):
connection.close()
print("The Sqlite connection is closed")

getAllRecords()
Connected to SQLite
Total rows are: 3
Printing each row
Name: Jacob
Email: Jacob@gmail.com
TestDate: 2020/12/04
Score: 86


Name: Tim
Email: Tim@gmail.com
TestDate: 2020/12/06
Score: 84


Name: Jack
Email: Jack@gmail.com
TestDate: 2020/12/04
Score: 81


The Sqlite connection is closed

Retrieve a few rows from a table using cursor.fetchmany(size)

rows = cursor.fetchmany([size=cursor.arraysize])
  • 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.
import sqlite3

def getlimitedRows(size):
try:
connection = sqlite3.connect('SqlTest.db')
cursor = connection.cursor()
print("Connected to database")

sqlite_select_query = """SELECT * from database_score"""
cursor.execute(sqlite_select_query)
records = cursor.fetchmany(size)
print("Fetching Total ", size," rows")
print("Printing each row")
for row in records:
print("Name: ", row[0])
print("Email: ", row[1])
print("TestDate: ", row[2])
print("Score: ", row[3])
print("\n")

cursor.close()

except sqlite3.Error as error:
print("Failed to read data from table", error)
finally:
if (connection):
connection.close()
print("The Sqlite connection is closed")

getlimitedRows(2)
Connected to SQLite
Total rows are: 2
Printing each row
Name: Jacob
Email: Jacob@gmail.com
TestDate: 2020/12/04
Score: 86


Name: Tim
Email: Tim@gmail.com
TestDate: 2020/12/06
Score: 84


The Sqlite connection is closed
  • fetchmany() returns an empty list when no more rows are available in the table.
  • A ProgrammingError raised if the previous call to execute*() did not produce any result set or no call issued yet.
  • fetchmany() returns fewer rows if the table contains the less number of rows specified by SIZE argument.

What will happen if the cursor’s fetchmany(size) called repeatedly

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.
import sqlite3def getSingleRows():
try:
connection = sqlite3.connect('SqlTest.db')
cursor = connection.cursor()
print("Connected to database")
sqlite_select_query = """SELECT * from database_score"""
cursor.execute(sqlite_select_query)
print("Fetching single row")
record = cursor.fetchone()
print(record)
print("Fetching next row")
record = cursor.fetchone()
print(record)
cursor.close() except sqlite3.Error as error:
print("Failed to read data from table", error)
finally:
if (connection):
connection.close()
print("The Sqlite connection is closed")
getSingleRows()
Connected to database

Fetching single row
('Jacob', 'Jacob@gmail.com', '2020/12/04', 86)

Fetching next row
('Tim', 'Tim@gmail.com', '2020/12/06', 84)
The Sqlite connection is closed

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store