Python SQLite - Cursor Object

A Cursor is an object used to execute SQL queries on an SQLite database. It acts as a middleware between the SQLite database connection and the SQL commands. It is created after establishing a connection to the SQLite database. Example:

import sqlite3

conn = sqlite3.connect('example.db')
c = conn.cursor() # cursor
print(c)

Output

<sqlite3.Cursor object at 0x000001AB6D9F7C00>

Explanation: In this code, we establish a connection to an SQLite database called example.db using sqlite3.connect('example.db'), creating it if it doesn't exist. Then, we create a cursor object conn.cursor() to execute SQL commands on the database.

Cursor object syntax

cursor_object = connection_object.cursor()

cursor_object.execute("SQL QUERY")

Parameters:

• connection_object: An active connection to the SQLite database, created with sqlite3.connect('your_database.db').
• cursor_object.execute("SQL QUERY", parameters_tuple): Executes an SQL query.

Returns: cursor_object.execute() returns the cursor object itself. After executing a SELECT query, results can be fetched using fetchone(), fetchall() or fetchmany(size).

Important Methods of cursor

Let's explore key cursor methods to understand how they interact with the SQLite database, making it easier to execute queries and fetch data efficiently.

Cursor object Examples

Example 1: In this example, we are creating a new table in the SQLite database called hotel (if it doesn't already exist) and inserting the data into the table.

import sqlite3

conn = sqlite3.connect('hotel_data.db')
c = conn.cursor()

# Create a new table
c.execute('''
    CREATE TABLE IF NOT EXISTS hotel (
        FIND INTEGER PRIMARY KEY NOT NULL,
        FNAME TEXT NOT NULL,
        COST INTEGER NOT NULL,
        WEIGHT INTEGER
    )
''')

# Insert records
c.execute("INSERT INTO hotel (FIND, FNAME, COST, WEIGHT) VALUES (1, 'Cakes', 800, 10)")
c.execute("INSERT INTO hotel (FIND, FNAME, COST, WEIGHT) VALUES (2, 'Biscuits', 100, 20)")
c.execute("INSERT INTO hotel (FIND, FNAME, COST, WEIGHT) VALUES (3, 'Chocos', 1000, 30)")

conn.commit()
print("Data inserted successfully.")
conn.close()

Output

Data inserted successfully.

Explanation: We connect to hotel_data.db, create a cursor and use CREATE TABLE IF NOT EXISTS to create the hotel table. Three records are inserted, changes are committed with conn.commit() and the connection is closed.

Example 2: In this example, we are retrieving data from the hotel table in the SQLite database using the SELECT statement to fetch all the records.

import sqlite3
conn = sqlite3.connect('hotel_data.db')
c = conn.cursor()
c.execute("SELECT * FROM hotel")

# Fetch all records
rows = c.fetchall()

print("All Food Items:\n")
for row in rows:
    print(f"Food ID: {row[0]}, Name: {row[1]}, Cost: {row[2]}, Weight: {row[3]}")

c.close()

Output

Explanation: We connect to hotel_data.db, create a cursor and execute a SELECT * FROM hotel query to fetch all records. The records are printed and the connection is closed.

Example 3: In this example, we are retrieving specific columns (FIND and FNAME) from the hotel table in the SQLite database using the SELECT statement.

import sqlite3
conn = sqlite3.connect('hotel_data.db')
c = conn.cursor()
c.execute("SELECT FIND, FNAME FROM hotel")

# Fetch and print each row
print("Food ID and Name:\n")
for row in c.fetchall():
    print(f"Food ID: {row[0]}, Name: {row[1]}")

conn.close()

Output

Explanation: We connect to hotel_data.db, create a cursor and execute a SELECT FIND, FNAME FROM hotel query to fetch the food ID and name. The results are printed and the connection is closed.

sravankumar_171fa07058

Follow

Improve

Article Tags :

• Python
• Python-SQLite

Practice Tags :

• python