Python Database Connectivity: SQLite, MySQL & CRUD Operations

Introduction

Database connectivity allows Python applications to store, retrieve, update, and delete data in a database. It is an essential skill for developers building websites, desktop applications, reporting tools, and data-driven systems.

Python supports several databases, including SQLite, MySQL, PostgreSQL, and others.

In this tutorial, you will learn:

• What database connectivity means

• Connecting Python to SQLite

• Creating database tables

• Inserting and retrieving records

• Updating and deleting records

• Understanding CRUD operations

• Connecting Python to MySQL

• Using parameterized queries

• Handling database errors

• Building a simple student management example

1. What Is Database Connectivity?

Database connectivity is the process of connecting an application to a database so it can perform operations on stored information.

For example, a student management application may store student names, email addresses, and marks in a database.

Python sends SQL queries to the database through a database library or driver.

Common Python database options include:

• SQLite: A lightweight database included with Python's standard library.

• MySQL: A popular relational database server.

• PostgreSQL: An advanced open-source relational database.

• SQL Server: A relational database management system from Microsoft.

SQLite is a convenient starting point because it does not require a separate database server.

2. Connecting Python to SQLite

Python provides the built-in sqlite3 module for working with SQLite databases.

Example

import sqlite3

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

print("Database connected successfully!")

connection.close()

Output

Database connected successfully!

This code creates a database file named school.db if it does not already exist. If the file exists, Python opens the existing database.

The close() method releases the database connection.

3. Creating a Database Table

After connecting to a database, you can execute SQL commands to create tables.

Example

import sqlite3

connection = sqlite3.connect("school.db")
cursor = connection.cursor()

cursor.execute("""
    CREATE TABLE IF NOT EXISTS students (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE,
        marks REAL
    )
""")

connection.commit()
connection.close()

print("Students table created successfully!")

Explanation

• CREATE TABLE IF NOT EXISTS creates the table only if it does not already exist.

• id is the primary key.

• AUTOINCREMENT generates increasing integer IDs.

• NOT NULL requires a name value.

• UNIQUE prevents duplicate non-NULL email values.

• REAL stores numeric values that may include decimal places.

• commit() saves the transaction.

4. Inserting Data into SQLite

The INSERT statement adds new records to a table.

Always use parameterized queries for values rather than combining user input directly into SQL strings.

Example

import sqlite3

connection = sqlite3.connect("school.db")
cursor = connection.cursor()

student = ("Rahul", "rahul@example.com", 85.5)

cursor.execute("""
    INSERT INTO students (name, email, marks)
    VALUES (?, ?, ?)
""", student)

connection.commit()
connection.close()

print("Student inserted successfully!")

Output

Student inserted successfully!

The ? placeholders are used by SQLite to safely bind values to the SQL query.

If you run this example multiple times with the same email address, the UNIQUE constraint will reject duplicate entries.

5. Retrieving Data from SQLite

Use the SELECT statement to retrieve records.

Example

import sqlite3

connection = sqlite3.connect("school.db")
cursor = connection.cursor()

cursor.execute("SELECT id, name, email, marks FROM students")

students = cursor.fetchall()

for student in students:
    print(student)

connection.close()

Example Output

(1, 'Rahul', 'rahul@example.com', 85.5)

The exact output depends on the records in your database.

Useful fetching methods include:

• fetchone() — retrieves one row.

• fetchmany(size) — retrieves a specified number of rows.

• fetchall() — retrieves all remaining rows.

6. Updating Data in SQLite

The UPDATE statement modifies existing records.

Example

import sqlite3

connection = sqlite3.connect("school.db")
cursor = connection.cursor()

cursor.execute("""
    UPDATE students
    SET marks = ?
    WHERE id = ?
""", (90, 1))

connection.commit()

print("Rows updated:", cursor.rowcount)

connection.close()

This example changes the marks of the student whose ID is 1.

Important: Always check the WHERE condition. Without it, an update may modify every row in the table.

7. Deleting Data from SQLite

The DELETE statement removes records from a table.

Example

import sqlite3

connection = sqlite3.connect("school.db")
cursor = connection.cursor()

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

connection.commit()

print("Rows deleted:", cursor.rowcount)

connection.close()

This deletes the record whose ID is 1.

The comma in (1,) makes it a one-element tuple, which is the parameter format expected by the SQLite API.

Use delete operations carefully, especially when working with important production data.

8. Understanding CRUD Operations

CRUD represents the four basic operations used to manage database records.

Operation	SQL Command	Purpose
Create	INSERT	Add new records
Read	SELECT	Retrieve records
Update	UPDATE	Modify existing records
Delete	DELETE	Remove records

These operations are fundamental to applications such as student management systems, inventory applications, booking platforms, and customer management software.

9. Using with to Manage SQLite Connections

Python's with statement helps manage resources more safely. With a SQLite connection, it automatically commits a successful transaction or rolls it back when an exception occurs. You should still close the connection explicitly or use contextlib.closing.

Example

import sqlite3
from contextlib import closing

with closing(sqlite3.connect("school.db")) as connection:
    with connection:
        connection.execute(
            "INSERT INTO students (name, email, marks) VALUES (?, ?, ?)",
            ("Priya", "priya@example.com", 92)
        )

print("Operation completed.")

The inner with connection: manages the transaction. The outer closing() ensures the connection is closed when the block finishes.

10. Connecting Python to MySQL

MySQL is a database server commonly used in web applications.

To connect Python to MySQL, install the official MySQL Connector/Python package in your Python environment.

Installation

Run this command in your terminal:

python -m pip install mysql-connector-python

Make sure MySQL Server is installed and running, and that you have a database and a MySQL user with appropriate permissions.

Example Connection

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="school"
)

if connection.is_connected():
    print("Connected to MySQL successfully!")

connection.close()

Replace your_username and your_password with your own database credentials.

Avoid hardcoding real passwords in production code. Use environment variables or a secure secrets-management system.

11. Creating a Table in MySQL

After establishing the connection, you can execute SQL statements using a cursor.

Example

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="school"
)

cursor = connection.cursor()

cursor.execute("""
    CREATE TABLE IF NOT EXISTS students (
        id INT PRIMARY KEY AUTO_INCREMENT,
        name VARCHAR(100) NOT NULL,
        email VARCHAR(150) UNIQUE,
        marks DECIMAL(5, 2)
    )
""")

connection.commit()

cursor.close()
connection.close()

print("Students table is ready!")

The database school must already exist for this connection example to work.

12. Inserting and Fetching MySQL Records

MySQL Connector/Python uses %s placeholders for parameterized values.

Example: Insert a Student

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="school"
)

cursor = connection.cursor()

query = """
    INSERT INTO students (name, email, marks)
    VALUES (%s, %s, %s)
"""

values = ("Amit", "amit@example.com", 88.5)

cursor.execute(query, values)
connection.commit()

print("Student inserted successfully!")

cursor.close()
connection.close()

Example: Fetch Students

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="school"
)

cursor = connection.cursor()

cursor.execute("SELECT id, name, email, marks FROM students")

for student in cursor.fetchall():
    print(student)

cursor.close()
connection.close()

Use %s placeholders for values even when the values are integers or decimals. Do not place user-provided values directly into SQL query strings.

13. Handling Database Errors

Database operations can fail because of connection problems, invalid SQL, duplicate values, or constraint violations.

Python's try, except, and finally statements help handle errors and clean up resources.

SQLite Example

import sqlite3

connection = None

try:
    connection = sqlite3.connect("school.db")
    cursor = connection.cursor()

    cursor.execute("""
        SELECT id, name
        FROM students
    """)

    for student in cursor.fetchall():
        print(student)

except sqlite3.Error as error:
    print("Database error:", error)

finally:
    if connection is not None:
        connection.close()

This example reports SQLite errors and closes the connection when it has been created.

For production applications, log technical errors securely and display appropriate messages to users rather than exposing sensitive database details.

14. Practical Project: Student Management Using SQLite

The following program demonstrates a simple student management application with create, read, update, and delete functionality.

Complete Code

import sqlite3

DATABASE = "school.db"


def create_table():
    with sqlite3.connect(DATABASE) as connection:
        connection.execute("""
            CREATE TABLE IF NOT EXISTS students (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                email TEXT UNIQUE,
                marks REAL
            )
        """)


def add_student(name, email, marks):
    with sqlite3.connect(DATABASE) as connection:
        connection.execute("""
            INSERT INTO students (name, email, marks)
            VALUES (?, ?, ?)
        """, (name, email, marks))

    print("Student added successfully!")


def show_students():
    with sqlite3.connect(DATABASE) as connection:
        students = connection.execute("""
            SELECT id, name, email, marks
            FROM students
            ORDER BY id
        """).fetchall()

    if not students:
        print("No students found.")
        return

    for student in students:
        print(student)


def update_marks(student_id, marks):
    with sqlite3.connect(DATABASE) as connection:
        cursor = connection.execute("""
            UPDATE students
            SET marks = ?
            WHERE id = ?
        """, (marks, student_id))

    if cursor.rowcount:
        print("Marks updated successfully!")
    else:
        print("Student not found.")


def delete_student(student_id):
    with sqlite3.connect(DATABASE) as connection:
        cursor = connection.execute("""
            DELETE FROM students
            WHERE id = ?
        """, (student_id,))

    if cursor.rowcount:
        print("Student deleted successfully!")
    else:
        print("Student not found.")


create_table()

add_student("Rahul", "rahul@example.com", 85)
show_students()

update_marks(1, 95)
show_students()

# Uncomment the following line to delete student ID 1:
# delete_student(1)

What This Project Demonstrates

• Creating a database table.

• Inserting a new student.

• Retrieving student records.

• Updating marks using a student ID.

• Deleting a student record.

• Using parameterized SQL queries.

• Managing database transactions with connection context managers.

The example assumes that rahul@example.com has not already been inserted. Because the email column is unique, running the program repeatedly may raise an integrity error for a duplicate email. A production application should handle that case appropriately.

15. Best Practices for Python Database Connectivity

1. Use parameterized queries to prevent SQL injection.

2. Close connections and cursors when they are no longer needed.

3. Use transactions for operations that must succeed or fail together.

4. Store credentials securely.

5. Handle database errors appropriately.

6. Add constraints such as primary keys, unique values, and required fields.

7. Avoid deleting or updating records without checking the intended conditions.

8. Use connection pooling when appropriate for applications with many database requests.

9. Keep database operations separate from application presentation logic.

10. Back up important databases and test recovery procedures.

16. Common Mistakes to Avoid

• Forgetting to call commit() when required.

• Connecting to a database that does not exist.

• Using the wrong parameter placeholder for the database driver.

• Concatenating user input into SQL queries.

• Forgetting the WHERE clause in an update or delete statement.

• Reusing a closed connection.

• Hardcoding production credentials in source code.

• Assuming SQLite and MySQL use identical SQL syntax and connection settings.

17. Python Database Connectivity Practice Questions

1. Connect Python to an SQLite database.

2. Create a table named employees.

3. Insert five employee records.

4. Retrieve all employees using SELECT.

5. Display employees whose salary is greater than a specified amount.

6. Update an employee's salary using their ID.

7. Delete an employee using their ID.

8. Handle database exceptions using try and except.

9. Connect Python to MySQL using MySQL Connector/Python.

10. Build a command-line student management application using CRUD operations.

18. Python Database Interview Questions and Answers

Q1. What is database connectivity?

Database connectivity allows an application to communicate with a database to store and manage information.

Q2. Which Python module is used for SQLite?

Python provides the built-in sqlite3 module.

Q3. What is MySQL Connector/Python?

It is a driver that allows Python applications to connect to MySQL Server.

Q4. What are CRUD operations?

CRUD stands for Create, Read, Update, and Delete.

Q5. What does commit() do?

It commits the current transaction, making its changes permanent in the database.

Q6. What is a cursor?

A cursor is an object used to execute SQL statements and retrieve query results through a database connection.

Q7. Why are parameterized queries important?

They separate SQL instructions from input values, helping prevent SQL injection and ensuring values are handled correctly.

Q8. What is the difference between SQLite and MySQL?

SQLite is an embedded, file-based database. MySQL is a database server that applications connect to over a local or remote connection.

Q9. What does fetchall() do?

It retrieves all remaining rows from a query result.

Q10. Why should database connections be closed?

Closing connections releases resources and helps avoid unnecessary open connections.

Conclusion

Python database connectivity is an essential skill for developing data-driven applications. SQLite provides a simple starting point, while MySQL supports applications that need a database server.

By learning SQL queries, CRUD operations, transactions, parameterized queries, and error handling, you can build practical Python applications that store and manage data reliably.

Continue practising by creating your own student, employee, or inventory management project.