conn.cursor objects in sqlite3 - python

In an sqlite3 connection, cursor objects acts as an intermediary between the connected SQLite database and Python code. Cursors are especially useful when executing queries that may yield data from the database such as those involving the SELECT sql command.

Consider the following example:

import sqlite3

#create a database called 'points.db'
with sqlite3.connect('points.db') as conn:
  try:
    #create a table called 'point'.
    conn.execute(
      '''CREATE TABLE point(
       id INTEGER PRIMARY KEY AUTOINCREMENT,
         x REAL,
         y REAL);''')

    print('Table created successfully.')

     #insert rows into the point table
    conn.executescript(
     '''
       INSERT INTO point (x, y) VALUES (3, 4);
       INSERT INTO point (x, y) VALUES (2, 5);
       INSERT INTO point (x, y) VALUES (4, 6);''')
    print('Rows inserted successfully.')

  except:
    print('Something went wrong.')

In the above example, we created an SQLite database called points.db. We then created a table in the database called point with columns x and y, to represent the x and y coordinates of a point object. And finally we  inserted three rows into the point table.

Consider what we would do if we wanted to retrieve rows from the database. The conn object created by sqlite3.connect() function contain the methods for executing sql queries such as execute(), executemany and executescript. However, these methods do not store the results of the query if any. In order to store and access the results, we need to execute the query through a cursor object.

conn.cursor() objects

Cursor objects contains the three query executing methods i.e execute(), executemany() and executescript(). The three methods have the following syntax and usage.

In addition to the querying methods shown above, cursor objects also contains methods for retrieving the results of an executed query, the fetching methods are fetchone(), fetchmany() and fetchall(). 

Consider the following example:

import sqlite3

with sqlite3.connect('points.db') as conn:
  #create a cursor
  cursor = conn.cursor()
  cursor.execute('SELECT x, y FROM point;')

  #get the results
  print(cursor.fetchone())
  
  cursor.close()

In the above example, we used the fetchone() method to retrieve a single row from the results yielded by the SELECT query. The fetchone() method returns one row from the query results, it remembers where it left off. This means that subsequent fetchone() call will retrieve the next row in the result, as shown in the following example:

import sqlite3

with sqlite3.connect('points.db') as conn:
  #create a cursor
  cursor = conn.cursor()
  cursor.execute('SELECT x, y FROM point;')

  #get the results
  print(cursor.fetchone())
  print(cursor.fetchone())
  print(cursor.fetchone())
  
  cursor.close()

Calling the cursor.close() method is vital for ensuring that the cursor is closed and its resources are released. 

Fetch multiple rows

The cursor.fetchmany() method takes an integer argument that specifies the number of rows to be retrieved from the query result. It has the following syntax:

cursor.fetchmany(n = 1)

n is the number of rows to be retrieved from the result, it defaults to 1.

It returns a list of n tuples where each tuple represents a fetched row.

import sqlite3

with sqlite3.connect('points.db') as conn:

  #create a cursor
  cursor = conn.cursor()
  cursor.execute('SELECT x, y FROM point;')

  print(cursor.fetchmany(2)) # fetch two rows
  
  cursor.close()

Fetch all rows

To fetch all returned rows, use the cursor.fetchall() method.

import sqlite3

#connect to the database
with sqlite3.connect('points.db') as conn:

  #create a cursor
  cursor = conn.cursor()
  cursor.execute('SELECT x, y FROM point;')

  print(cursor.fetchall()) # fetch all rows
  
  cursor.close()

Cursor objects are also iterable meaning that we can for example use them directly in for loops to iterate through the returned rows without directly calling any of the three fetch methods.

import sqlite3

#connect to the database
with sqlite3.connect('points.db') as conn:

  #create a cursor
  cursor = conn.cursor()
  cursor.execute('SELECT x, y FROM point;')
   
  for row in cursor:
    print(row)

  cursor.close()

Summary

The cursor methods are summarized in the following table: