Class 9: Database Programming

In this class, we will introduce you to database programming with Python, which is an essential skill for many software development jobs. We will cover the basics of how to connect to a database, perform CRUD operations (create, read, update, delete), and implement a practical project.

Photo by Tobias Fischer on Unsplash

Introduction to database programming with Python:

Databases are software systems designed to store and manage large amounts of structured data efficiently. They are used in many applications, including web development, finance, healthcare, and more. Python provides several libraries to interact with databases, such as SQLite, MySQL, PostgreSQL, and Oracle.

Connecting to databases:

To connect to a database, we need to install and import the appropriate library and provide the database credentials, such as the hostname, username, password, and database name. We can use the library’s connection object to execute SQL queries and manipulate the data.

Performing CRUD operations:

The four basic database operations are create, read, update, and delete (CRUD). These operations allow us to create new records, read existing ones, modify them, and delete them from the database.

Practice:

To put our database programming skills into practice, we will create a program that interacts with a database and performs some data manipulation based on user input. For example, we could create a simple customer management system that allows users to add, edit, and delete customer information, such as name, email, phone number, and address. We can use Python’s database libraries to connect to a database, create tables, insert data, update records, delete data, and retrieve data based on user input.

Here is a simple example of a program that creates a database table, inserts some data, updates a record, and retrieves data based on user input:

import sqlite3

# Connect to the database
conn = sqlite3.connect('customer.db')
# Create a table
conn.execute('''CREATE TABLE customers
             (id INT PRIMARY KEY NOT NULL,
             name TEXT NOT NULL,
             email TEXT NOT NULL,
             phone TEXT NOT NULL);''')
# Insert data into the table
conn.execute("INSERT INTO customers (id, name, email, phone) VALUES (1, 'John Smith', 'johnsmith@email.com', '555-555-5555')")
conn.execute("INSERT INTO customers (id, name, email, phone) VALUES (2, 'Jane Doe', 'janedoe@email.com', '555-123-4567')")
# Update a record
conn.execute("UPDATE customers SET phone = '555-987-6543' WHERE id = 1")
# Retrieve data based on user input
user_input = input("Enter the customer ID to retrieve: ")
result = conn.execute("SELECT * FROM customers WHERE id = ?", (user_input,))
print(result.fetchone())
# Close the connection
conn.close()

In this example, we use SQLite as the database system, create a table called customers, insert two records, update the phone number of the first record, retrieve data based on user input, and close the connection.

Note that this is a simple example, and there are many ways to improve this program’s functionality and security, such as input validation, error handling, and authentication. Nonetheless, it provides a good starting point for learning about database programming with Python.