Database Connectivity in Python

Learn how Python applications connect to a database and execute database operations.

What is Database Connectivity?

Database connectivity means creating a connection between a Python application and a database.

Once the connection is created, Python can execute SQL statements and work with data stored in the database.

Database Connectivity Flow

SQLite Database Connection

Python provides the built-in sqlite3 module for connecting to SQLite databases.

import sqlite3

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

print("Database connected")

Output:

Database connected

Creating a Cursor

A cursor is used to execute SQL statements on the database.

import sqlite3

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

cursor = connection.cursor()

print("Cursor created")

Output:

Cursor created

Executing SQL Commands

The execute() method is used to execute SQL statements.

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()

print("Table created")

Output:

Table created

Inserting Data Through Connection

import sqlite3

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

cursor = connection.cursor()

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

connection.commit()

print("Data inserted")

Output:

Data inserted

Reading Data Through Connection

cursor.execute("SELECT * FROM students")

rows = cursor.fetchall()

for row in rows:

    print(row)

Example Output:

(1, 'Rahul', 'Python')

Understanding commit()

The commit() method saves INSERT, UPDATE and DELETE changes to the database.

connection.commit()

Without committing the changes, modified data may not be permanently saved.

Closing the Connection

After database operations are complete, the connection should be closed.

connection.close()

print("Connection closed")

Output:

Connection closed

Parameterized Database Queries

Parameterized queries allow values to be passed separately from the SQL statement.

name = "Priya"
course = ".NET"

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

connection.commit()

Parameterized queries are preferred when working with values received from users or other external sources.

Database Connection with Exception Handling

Exception handling can be used to handle database errors.

import sqlite3

try:

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

    cursor = connection.cursor()

    cursor.execute("SELECT * FROM students")

    rows = cursor.fetchall()

    for row in rows:

        print(row)

except sqlite3.Error as error:

    print("Database error:", error)

finally:

    if "connection" in locals():

        connection.close()

This allows the program to handle database errors and close the connection properly.

Connection and Cursor

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

cursor =
connection.cursor()

Example 📩🤓

The following example connects to an SQLite database, creates a table, inserts a record and reads the data.

import sqlite3

try:

    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 (?, ?)",
        ("Samadhan", "Python")
    )

    connection.commit()

    cursor.execute("SELECT * FROM students")

    students = cursor.fetchall()

    for student in students:

        print(student)

except sqlite3.Error as error:

    print("Database error:", error)

finally:

    connection.close()

Example Output:

(1, 'Samadhan', 'Python')

Advantages of Database Connectivity

Best Practices

Common Mistakes

• Forgetting to create the cursor.
• Forgetting to call commit() after modifying data.
• Forgetting to close the database connection.
• Writing SQL statements incorrectly.
• Not handling possible database errors.

Practice Programs

1. Connect Python to an SQLite database.
2. Create a students table.
3. Insert three student records.
4. Retrieve and display all students.
5. Update a student record.
6. Handle a database error using sqlite3.Error.