SQLite is a lightweight, embedded database that comes pre-installed with Python. It is widely used for applications that require simple, fast, and reliable database solutions without the need for a separate server. In this lesson, we’ll learn how to interact with SQLite databases using Python’s built-in sqlite3 module.
Lesson Outline:
- Connecting to an SQLite Database
- Creating Tables and Inserting Data
- Querying and Updating Records
1. Connecting to an SQLite Database
Introduction to SQLite:
- SQLiteis a self-contained, serverless SQL database engine.
- Unlike other SQL databases, it stores the entire database as a single file on disk.
Connecting to a Database:
To interact with an SQLite database in Python, we use the sqlite3 module.
# Connect to a database (or create it if it doesn’t exist)
conn = sqlite3.connect(‘my_database.db’)
# Create a cursor object to execute SQL commands
cursor = conn.cursor()
- connect(): Creates a connection to the database file. If the file doesn’t exist, it will be created automatically.
- Cursor: Acts as a control structure to interact with the database (execute SQL commands).
2. Creating Tables and Inserting Data
Creating a Table:
You can create tables using SQL CREATE TABLE statements.
cursor.execute(“””
CREATE TABLE IF NOT EXISTS students (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age INTEGER,
grade TEXT
)
“””)
# Save (commit) the changes
conn.commit()
- SQL Syntax:Standard SQL is used to define table structure.
- IF NOT EXISTS: Prevents errors if the table already exists.
- commit(): Saves changes to the database.
Inserting Data:
cursor.execute(“INSERT INTO students (name, age, grade) VALUES (?, ?, ?)”,
(‘Alice’, 20, ‘A’))
# Commit the transaction
conn.commit()
- Parameterized Queries (?placeholders): Help prevent SQL injection attacks.
- VALUES (?, ?, ?): Maps the data values to the table columns.
3. Querying and Updating Records
Querying Data (SELECT Statement):
# Retrieve all records from the ‘students’ table
cursor.execute(“SELECT * FROM students”)
rows = cursor.fetchall()
for row in rows:
print(row)
- SELECT *: Retrieves all columns from the table.
- fetchall(): Returns all results from the query as a list of tuples.
Filtering Results:
cursor.execute(“SELECT name, grade FROM students WHERE grade = ‘A’”)
for row in cursor.fetchall():
print(row)
- WHEREClause: Filters records based on specific conditions.
Updating Records:
cursor.execute(“UPDATE students SET grade = ? WHERE name = ?”, (‘B’, ‘Alice’))
conn.commit()
- UPDATEStatement: Modifies existing records.
- Condition:Ensures only the intended records are updated.
Deleting Records:
cursor.execute(“DELETE FROM students WHERE name = ‘Alice’”)
conn.commit()
- DELETEStatement: Removes records that meet specified conditions.
Closing the Connection
Always close the connection after completing database operations to free up resources.
Key Takeaways:
- SQLiteis ideal for lightweight database applications.
- Use sqlite3in Python to connect, create tables, insert, query, update, and delete records.
- Always commit changesafter insert/update/delete operations.
- Use parameterized queriesto prevent SQL injection.
- Close the connection with close()after database interactions.
This foundational knowledge of SQLite will help you work efficiently with databases in Python applications.
Leave a Reply