A cursor acts as an interface between Python code and an SQLite database. It executes SQL statements and manages access to the rows returned by queries.
Creating Cursor Object
cursor() method of a database connection creates a cursor object.
Syntax
cursor = connection.cursor()
- connection: An active SQLite database connection.
- cursor(): Creates a cursor object used to execute SQL statements.
Example:
Python
import sqlite3
connection = sqlite3.connect(":memory:")
cursor = connection.cursor()
print(type(cursor).__name__)
cursor.close()
connection.close()
Output
Cursor
Explanation:
- sqlite3.connect(":memory:") creates a temporary, in-memory database.
- connection.cursor() creates a cursor object.
- type(cursor).name displays the object's class name.
- close() releases the cursor and database connection resources.
Executing SQL Queries Using execute()
execute() method runs a single SQL statement. It can execute commands such as CREATE TABLE, INSERT, UPDATE, DELETE, and SELECT.
Syntax
cursor.execute(sql_query, parameters)
Example:
Python
cursor.execute(
"SELECT * FROM students WHERE marks >= ?",
(80,)
)
Explanation:
- SQL statement retrieves students with marks of at least 80.
- Question mark acts as a parameter placeholder.
- tuple (80,) supplies the value safely to the query.
Executing Multiple Queries with executemany()
executemany() method executes the same data-manipulation statement for multiple sets of parameters. It is useful for inserting or updating several records.
Syntax
cursor.executemany(sql_query, parameters)
Example:
Python
students = [
("Asha", 85),
("Rohan", 92),
("Meera", 78)
]
cursor.executemany(
"INSERT INTO students (name, marks) VALUES (?, ?)",
students
)
Explanation:
- students contains multiple sets of values.
- Each tuple supplies the name and marks for one record.
- executemany() applies the INSERT statement to each set of parameters.
Fetching Data from Cursor
After executing a SELECT query, the cursor can retrieve the resulting rows using different methods.
1. fetchone()
Returns the next available row as a tuple, or None when no rows remain.
Python
cursor.execute("SELECT name FROM students")
row = cursor.fetchone()
print(row)
2. fetchmany()
Returns the next specified number fo rows as a list. The number of rows can be supplied through the size argument.
Python
cursor.execute("SELECT name FROM students")
rows = cursor.fetchmany(2)
print(rows)
3. fecthall()
Returns all remaining rows as a list. An empty list is returned when no rows remain.
Python
cursor.execute("SELECT name FROM students")
rows = cursor.fetchall()
print(rows)
4. Iterating over a Cursor
A cursor is iterable, so query results can be processed one row at a time without explicitly calling a fetching method.
Python
cursor.execute("SELECT name, marks FROM students")
for row in cursor:
print(row)
Attributes
It provides attributes that help inspect query results and database operations.
- description: Provides metadata about the columns returned by the last query, including their names.
- rowcount: Reports the number of rows modified by supported INSERT, UPDATE, DELETE, or REPLACE statements once execution is complete. For SELECT statements, it generally returns -1.
- lastrowid: Provides the row ID associated with the last successful INSERT or REPLACE executed through execute(), where applicable.
Example:
Python
cursor.execute(
"INSERT INTO students (name, marks) VALUES (?, ?)",
("Kabir", 88)
)
print(cursor.lastrowid)
print(cursor.rowcount)
Output
4
1
Explanation:
- lastrowid returns 4 because this example follows the insertion of three earlier records.
- rowcount returns 1 because one record was inserted.
Complete Example
Following program demonstrate creating a table, inserting records, retrieving matching rows, and closing the database connection.
1. Importing module and creating connection
Python
import sqlite3
connection = sqlite3.connect(":memory:")
cursor = connection.cursor()
Explanation:
- sqlite3 provides Python's built-in interface to SQLite.
- Connection creates an in-memory database.
- cursor() creates an object for executing SQL statements.
2. Creating table and insert records
Python
cursor.execute("""
CREATE TABLE students (
id INTEGER PRIMARY KEY,
name TEXT,
marks INTEGER
)
""")
students = [
("Asha", 85),
("Rohan", 92),
("Meera", 78)
]
cursor.executemany(
"INSERT INTO students (name, marks) VALUES (?, ?)",
students
)
connection.commit()
Explanation:
- CREATE TABLE defines the columns and their data types.
- executemany() inserts the supplied student records.
- commit() saves the changes to the database transaction.
3. Executing query and display results
Python
cursor.execute(
"SELECT id, name, marks FROM students WHERE marks >= ?",
(80,)
)
for row in cursor.fetchall():
print(row)
Output
(1, 'Asha', 85)
(2, 'Rohan', 92)
Explanation:
- execute() runs the SELECT query with a parameterized condition.
- fetchall() retrieves the matching rows.
- Loop displays each result as a tuple containing the student's ID, name, and marks.
4. Closing cursor and connection
Python
cursor.close()
connection.close()
