Python in Practice: Mastering Database Management with SQLite

Databases play a pivotal role in modern applications, and Python’s versatility makes it an excellent choice for working with databases. In this article, we’ll focus on using SQLite, a lightweight and self-contained database engine, with Python. Whether you’re a beginner or an experienced developer, understanding how to integrate Python with SQLite can greatly enhance your data handling capabilities. We’ll provide detailed examples, practical use cases, and expected outputs to help you become proficient in working with SQLite databases using Python.

Prerequisites:

Before we dive into working with SQLite and Python, ensure you have the following prerequisites:

1. A basic understanding of Python programming.
2. Python installed on your system.
3. Familiarity with SQL (Structured Query Language) concepts is helpful but not mandatory.

Getting Started with SQLite:

SQLite is a self-contained, serverless, and zero-configuration SQL database engine. It’s an ideal choice for small to medium-sized projects or when you need a lightweight database solution.

Installation:

Python comes with SQLite built-in, so there’s no need for a separate installation.

Connecting to an SQLite Database:

To begin working with an SQLite database in Python, you need to establish a connection. Here’s how to do it:

import sqlite3
# Connect to the SQLite database (creates it if it doesn't exist)
connection = sqlite3.connect("mydatabase.db")
# Create a cursor object for database interaction
cursor = connection.cursor()

# Create a table
cursor.execute('''CREATE TABLE employees
                  (id INTEGER PRIMARY KEY,
                   name TEXT,
                   age INTEGER,
                   position TEXT)''')

# Commit the changes
connection.commit()

# Create a table
cursor.execute('''CREATE TABLE employees
                  (id INTEGER PRIMARY KEY,
                   name TEXT,
                   age INTEGER,
                   position TEXT)''')

# Commit the changes
connection.commit()

# Insert data
cursor.execute("INSERT INTO employees (name, age, position) VALUES (?, ?, ?)", ("Sachin", 25, "Manager"))
cursor.execute("INSERT INTO employees (name, age, position) VALUES (?, ?, ?)", ("Raju", 22, "Developer"))
# Commit the changes
connection.commit()

# Query data
cursor.execute("SELECT * FROM employees")
employees = cursor.fetchall()
# Print the results
for employee in employees:
    print(employee)

(1, 'Sachin', 25, 'Manager')
(2, 'Raju', 22, 'Developer')

# Update data
cursor.execute("UPDATE employees SET age=?, position=? WHERE name=?", (26, "Senior Developer", "Alice"))
# Delete data
cursor.execute("DELETE FROM employees WHERE name=?", ("Bob",))
# Commit the changes
connection.commit()