Python’s versatility extends beyond traditional programming tasks, making it a powerful tool for working with databases. In this article, we will delve into the practical aspects of using Python with SQL to interact with databases. Whether you’re a beginner or an experienced programmer, understanding how Python and SQL complement each other can enhance your data manipulation capabilities. We’ll provide step-by-step examples, real-world scenarios, and expected outputs to help you master the art of working with databases in Python.
Prerequisites:
Before we begin, make sure you have the following prerequisites:
- A working knowledge of Python programming.
- A database system installed (e.g., SQLite, MySQL, PostgreSQL).
- A Python library for database connectivity (e.g.,
sqlite3,mysql-connector,psycopg2).
Connecting to a Database:
To work with databases in Python, you first need to establish a connection. We’ll demonstrate using SQLite, a lightweight database engine.
import sqlite3
# Connect to the database (creates the database if it doesn't exist)
connection = sqlite3.connect("mydatabase.db")
# Create a cursor object for database interaction
cursor = connection.cursor()
# Close the connection when done
connection.close()Creating a Table:
Let’s create a simple table named “students” to store student information.
# Create a table
cursor.execute('''CREATE TABLE students
(id INTEGER PRIMARY KEY,
name TEXT,
age INTEGER)''')
# Commit the changes
connection.commit()Inserting Data:
Now, we’ll insert some data into the “students” table.
# Insert data
cursor.execute("INSERT INTO students (name, age) VALUES (?, ?)", ("Alice", 25))
cursor.execute("INSERT INTO students (name, age) VALUES (?, ?)", ("Bob", 22))
# Commit the changes
connection.commit()Querying Data:
You can retrieve data from the database using SQL queries.
# Query data
cursor.execute("SELECT * FROM students")
students = cursor.fetchall()
# Print the results
for student in students:
print(student)Output:
(1, 'Alice', 25)
(2, 'Bob', 22)
Updating and Deleting Data:
You can update and delete data as needed.
# Update data
cursor.execute("UPDATE students SET age=? WHERE name=?", (26, "Alice"))
# Delete data
cursor.execute("DELETE FROM students WHERE name=?", ("Bob",))
# Commit the changes
connection.commit()
