Lab Manual: Connecting to SQL Database using SQLite

Objective:

To understand how SQLite is used in Python to create, manipulate, and delete records in a database.

Introduction to SQLite:

SQLite is a lightweight, serverless database engine used in applications requiring simple data storage and retrieval. It is built into Python, making it easy to work with databases without additional configuration.

Given Code:

The following Python script interacts with an SQLite database, performing operations such as creating a table, inserting data, and deleting records.

import sqlite3  # Imports the SQLite module

# Establishes a connection to the database (creates 'data.db' if it doesn’t exist)

conn = sqlite3.connect('data.db')

# Creates a cursor object to execute SQL commands

cur = conn.cursor()

# Drops the 'Tracks' table if it already exists to prevent duplication

cur.execute('DROP TABLE IF EXISTS Tracks')

# Creates a new table named 'Tracks' with two columns: title (TEXT) and plays (INTEGER)

cur.execute('CREATE TABLE Tracks (title TEXT, plays INTEGER)')

# Inserts a row into the 'Tracks' table with title 'Thunderstruck' and plays count as 20

cur.execute('INSERT INTO Tracks (title, plays) VALUES (\'Thunderstruck\', 20)')

# Deletes all records from the 'Tracks' table where the plays count is less than 100

cur.execute('DELETE FROM Tracks WHERE plays < 100')

# Closes the database connection

conn.close()

Explanation of Each Line:

1. import sqlite3

   • Imports the sqlite3 module, which allows interaction with SQLite databases.

2. conn = sqlite3.connect('data.db')

   • Creates (or opens if already exists) a database file named data.db.

   • Returns a connection object to interact with the database.

3. cur = conn.cursor()

   • Creates a cursor object to execute SQL commands within the database.

4. cur.execute('DROP TABLE IF EXISTS Tracks')

   • Checks if a table named Tracks exists. If it does, it deletes (drops) the table to prevent duplication.

5. cur.execute('CREATE TABLE Tracks (title TEXT, plays INTEGER)')

   • Creates a new table named Tracks with two columns:

     • title: Stores text data (song title).

     • plays: Stores integer values (number of times a song is played).

6. cur.execute('INSERT INTO Tracks (title, plays) VALUES (\'Thunderstruck\', 20)')

   • Inserts a new row into the Tracks table with:

     • Title: 'Thunderstruck'

     • Plays: 20

7. cur.execute('DELETE FROM Tracks WHERE plays < 100')

   • Deletes all records from the Tracks table where the plays value is less than 100.

8. conn.close()

   • Closes the database connection to free up resources.

Expected Output:

After executing the script, the Tracks table is created and a song entry (Thunderstruck, 20) is added. However, since 20 is less than 100, the DELETE command removes this entry, leaving the table empty.

Tasks:

1. Modify the script to insert multiple records and ensure some have plays greater than 100.

2. Retrieve and print all rows from the table after each operation to observe changes.

3. Modify the DELETE condition to remove only specific songs.

Conclusion:

This lab demonstrates how to use SQLite in Python to create, insert, delete, and manipulate database records. Understanding these fundamental operations is essential for working with relational databases in Python applications.