Python and SQL: A Comprehensive Guide to Working with Databases

Learn Python @

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.


Before we begin, make sure you have the following prerequisites:

  1. A working knowledge of Python programming.
  2. A database system installed (e.g., SQLite, MySQL, PostgreSQL).
  3. 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

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

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

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:


(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
Author: user