Lesson 20: Working with Databases (SQL)

Databases are a fundamental component of many applications, and Python provides powerful libraries for working with databases. In this lesson, we will focus on working with relational databases using the Structured Query Language (SQL) and the SQLite database engine.

Let’s explore working with databases in Python:

# Importing the required modules
import sqlite3

# Creating a connection to the database
conn = sqlite3.connect("mydatabase.db")

# Creating a cursor object
cursor = conn.cursor()

# Creating a table
create_table_query = """
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT
)
"""
cursor.execute(create_table_query)

# Inserting data into the table
insert_query = """
INSERT INTO users (name, email) VALUES (?, ?)
"""
data = [
    ("Alice", "alice@example.com"),
    ("Bob", "bob@example.com"),
    ("Charlie", "charlie@example.com")
]
cursor.executemany(insert_query, data)

# Committing the changes
conn.commit()

# Retrieving data from the table
select_query = """
SELECT * FROM users
"""
cursor.execute(select_query)
rows = cursor.fetchall()
for row in rows:
    print(row)

# Updating data in the table
update_query = """
UPDATE users SET email = ? WHERE name = ?
"""
new_email = "alice.new@example.com"
user_name = "Alice"
cursor.execute(update_query, (new_email, user_name))
conn.commit()

# Deleting data from the table
delete_query = """
DELETE FROM users WHERE name = ?
"""
user_name = "Charlie"
cursor.execute(delete_query, (user_name,))
conn.commit()

# Closing the connection
conn.close()

Explanation:

• We import the sqlite3 module to work with SQLite databases.
• We establish a connection to the database using sqlite3.connect() and create a cursor object to execute SQL queries.
• We can create tables using the CREATE TABLE statement. The IF NOT EXISTS clause ensures that the table is created only if it doesn’t already exist.
• Data can be inserted into the table using the INSERT INTO statement. The executemany() method allows us to insert multiple rows at once.
• Changes made to the database are committed using conn.commit().
• Data can be retrieved using the SELECT statement and the fetchall() method.
• Data can be updated using the UPDATE statement.
• Data can be deleted using the DELETE FROM statement.
• The connection to the database is closed using conn.close().

Now it’s time for a practical task:

Task 20:

Create a new SQLite database and a table called “students” with columns “id” (integer), “name” (text), and “grade” (real). Insert a few rows of data into the table. Then, write a function called get_highest_grade() that retrieves the student with the highest grade from the table. Call the function and print the result.

Once you’ve completed the task, you can proceed to the next lesson.