Database Programming in Python

Learn how Python programs can connect to databases, store data and work with database records.

What is Database Programming?

Database programming means using a programming language to create, read, update and delete data stored in a database.

Python provides modules and libraries that allow programs to connect to databases and execute SQL commands.

Database Programming Flow

Common Databases Used with Python

SQLite with Python

SQLite is a lightweight relational database. Python provides the built-in sqlite3 module for working with SQLite databases.

import sqlite3

connection = sqlite3.connect("students.db")

print("Database connected")

Output:

Database connected

Creating a Table

SQL can be used to create tables inside the database.

import sqlite3

connection = sqlite3.connect("students.db")

cursor = connection.cursor()

cursor.execute("""
CREATE TABLE IF NOT EXISTS students
(
    id INTEGER PRIMARY KEY,
    name TEXT,
    course TEXT
)
""")

connection.commit()

connection.close()

print("Table created")

Output:

Table created

Inserting Data

The SQL INSERT statement is used to add records to a table.

import sqlite3

connection = sqlite3.connect("students.db")

cursor = connection.cursor()

cursor.execute(
    "INSERT INTO students (name, course) VALUES (?, ?)",
    ("Rahul", "Python")
)

connection.commit()

connection.close()

print("Student inserted")

Output:

Student inserted

Reading Data

The SQL SELECT statement is used to retrieve records from a table.

import sqlite3

connection = sqlite3.connect("students.db")

cursor = connection.cursor()

cursor.execute("SELECT * FROM students")

rows = cursor.fetchall()

for row in rows:

    print(row)

connection.close()

Example Output:

(1, 'Rahul', 'Python')
(2, 'Priya', '.NET')

Updating Data

The SQL UPDATE statement is used to modify existing records.

import sqlite3

connection = sqlite3.connect("students.db")

cursor = connection.cursor()

cursor.execute(
    "UPDATE students SET course = ? WHERE id = ?",
    ("Python Full Stack", 1)
)

connection.commit()

connection.close()

print("Student updated")

Output:

Student updated

Deleting Data

The SQL DELETE statement is used to remove records from a table.

import sqlite3

connection = sqlite3.connect("students.db")

cursor = connection.cursor()

cursor.execute(
    "DELETE FROM students WHERE id = ?",
    (1,)
)

connection.commit()

connection.close()

print("Student deleted")

Output:

Student deleted

CRUD Operations

Most database applications perform four basic operations.

Parameterized Queries

Parameterized queries allow values to be safely passed to SQL statements.

name = "Rahul"
course = "Python"

cursor.execute(
    "INSERT INTO students (name, course) VALUES (?, ?)",
    (name, course)
)

This approach is preferred when inserting user-provided values into SQL statements.

Example 👀📩

The following example creates a database, inserts a student and reads the stored data.

import sqlite3

connection = sqlite3.connect("college.db")

cursor = connection.cursor()

cursor.execute("""
CREATE TABLE IF NOT EXISTS students
(
    id INTEGER PRIMARY KEY,
    name TEXT,
    course TEXT
)
""")

cursor.execute(
    "INSERT INTO students (name, course) VALUES (?, ?)",
    ("Amit", "Python")
)

connection.commit()

cursor.execute("SELECT * FROM students")

students = cursor.fetchall()

for student in students:

    print(student)

connection.close()

Advantages of Database Programming

Best Practices

Common Mistakes

• Forgetting to close the database connection.
• Forgetting to call commit() after modifying data.
• Writing SQL queries incorrectly.
• Building SQL statements directly from user input.

Practice Programs

1. Create an SQLite database using Python.
2. Create a students table.
3. Insert five student records.
4. Display all student records.
5. Update a student record.
6. Delete a student record.