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", "email@example.com"), ("Bob", "firstname.lastname@example.org"), ("Charlie", "email@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 = "firstname.lastname@example.org" 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()
- We import the
sqlite3module 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 TABLEstatement. The
IF NOT EXISTSclause ensures that the table is created only if it doesn’t already exist.
- Data can be inserted into the table using the
INSERT INTOstatement. The
executemany()method allows us to insert multiple rows at once.
- Changes made to the database are committed using
- Data can be retrieved using the
SELECTstatement and the
- Data can be updated using the
- Data can be deleted using the
- The connection to the database is closed using
Now it’s time for a practical task:
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.