Loading notes...
Loading notes...
BCA • Chapter 11
Chapter 11 teaches how to connect Python to relational databases using sqlite3, covering table creation, parameterized CRUD operations (Create, Read, Update, Delete), and SQL injection prevention.
While text files are great for simple data persistence, they fail miserably when dealing with massive datasets, complex relationships, concurrent user access, or advanced querying. Relational Databases solve this problem. They organize data into tables (rows and columns) and use Structured Query Language (SQL) for manipulation. This chapter covers how to connect your Python applications to a database to perform CRUD operations: Create, Read, Update, and Delete.
11.1 The sqlite3 Module
While large-scale applications use dedicated database servers like MySQL, PostgreSQL, or Oracle, Python includes a built-in module called `sqlite3`. SQLite is a C-language library that implements a small, fast, self-contained, high-reliability SQL database engine. It doesn't require a separate server process to run. The entire database is stored in a single file on your disk, making it perfect for learning and small applications.
import sqlite3
# 1. Connect to the database (creates the file if it doesn't exist)
# You can also use ':memory:' to create a temporary RAM database
connection = sqlite3.connect('college.db')
# 2. Create a Cursor object
# The cursor is the object that actually executes SQL commands
cursor = connection.cursor()
print("Database opened successfully!")
# 3. Always close the connection when done
connection.close()11.2 Creating Tables and Inserting Data
To create a table, we use the `CREATE TABLE` SQL statement. To add data, we use the `INSERT INTO` statement. A critical concept in database programming is the Transaction. When you insert data, it is not permanently saved to the hard drive immediately. You MUST call `connection.commit()` to finalize the transaction.
import sqlite3
conn = sqlite3.connect('college.db')
cursor = conn.cursor()
# Execute a single SQL command
cursor.execute('''
CREATE TABLE IF NOT EXISTS Students (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
course TEXT,
marks REAL
)
''')
# Inserting a single record
cursor.execute("INSERT INTO Students (name, course, marks) VALUES ('Basant', 'BCA', 95.5)")
# Commit the transaction to permanently save the changes
conn.commit()
conn.close()11.3 Preventing SQL Injection (Parameterization)
If you need to insert data provided by a user, NEVER use standard string concatenation or f-strings to build the SQL query. Malicious users can input raw SQL commands (like `DROP TABLE`) which will destroy your database—this is called SQL Injection. Instead, always use parameterized queries with `?` placeholders. The database engine will automatically sanitize the inputs.
import sqlite3
conn = sqlite3.connect('college.db')
cursor = conn.cursor()
# The Safe Way: Use ? as placeholders, and pass a Tuple of values
student_name = "Ravi"
student_course = "MCA"
student_marks = 88.0
# Notice the second argument is a tuple
cursor.execute("INSERT INTO Students (name, course, marks) VALUES (?, ?, ?)",
(student_name, student_course, student_marks))
# Inserting multiple records efficiently using executemany()
cohort = [
('Amit', 'BCA', 75.0),
('Neha', 'BCA', 91.0),
('Pooja', 'MCA', 85.5)
]
cursor.executemany("INSERT INTO Students (name, course, marks) VALUES (?, ?, ?)", cohort)
conn.commit()
conn.close()11.4 Reading Data (SELECT)
To retrieve data, we execute a `SELECT` statement. Once executed, the results are held by the cursor. We can retrieve the data using `fetchone()` (returns a single tuple), `fetchmany(size)` (returns a list of tuples), or `fetchall()` (returns a list containing all remaining tuples).
import sqlite3
conn = sqlite3.connect('college.db')
cursor = conn.cursor()
# Execute a SELECT query
cursor.execute("SELECT id, name, marks FROM Students WHERE course = 'BCA'")
# Fetch all matching rows
results = cursor.fetchall()
print("BCA Students:")
for row in results:
# row is a Tuple: (id, name, marks)
print(f"ID: {row[0]}, Name: {row[1]}, Marks: {row[2]}")
conn.close()11.5 Updating and Deleting Records
The `UPDATE` statement modifies existing records, while the `DELETE` statement removes them. Both statements should almost always include a `WHERE` clause; failing to do so will update or delete every single record in the table!
import sqlite3
conn = sqlite3.connect('college.db')
cursor = conn.cursor()
# Updating records
cursor.execute("UPDATE Students SET marks = ? WHERE name = ?", (98.0, 'Basant'))
print(f"{cursor.rowcount} row(s) updated.")
# Deleting records
cursor.execute("DELETE FROM Students WHERE marks < ?", (80.0,))
print(f"{cursor.rowcount} row(s) deleted.")
conn.commit()
conn.close()