Connecting Python to a database allows a Python application to store, retrieve, update, and delete data permanently. Python can work with different types of databases, including SQLite, MySQL, and PostgreSQL. The connection process generally involves importing a database library, establishing a connection, creating a cursor, executing SQL queries, processing the results, and closing the connection.

Table of Contents

---

What is Database Connectivity?

Database connectivity is the process of establishing communication between a Python application and a database.

1. Once the connection is established, Python can send SQL commands to the database and receive the requested results.
2. For example, a student management application can connect Python to a database to store student names, ages, courses, and other information.

Why Connect Python to a Database?

Python programs can use variables, lists, and dictionaries to store data temporarily, but this data is usually lost when the program ends. A database provides permanent storage for application data.

1. Permanent Storage: A database allows application data to remain available after the Python program closes.
2. Data Management: Python can insert, retrieve, update, and delete records using SQL.
3. Organized Data: Databases store information in structured tables.
4. Application Development: Database connectivity is useful when building web applications, management systems, and automation tools.
5. Data Retrieval: Python can retrieve only the records required by an application.

Types of Databases Used with Python

Python can connect to several types of databases.

1. SQLite: SQLite is a lightweight, file-based database that is useful for small applications and learning projects.
2. MySQL: MySQL is a server-based relational database commonly used in web and business applications.
3. PostgreSQL: PostgreSQL is an advanced relational database that provides features for larger and more complex applications.

The connection method and Python library can differ depending on the database being used.

Connecting Python to SQLite

Python provides the built-in sqlite3 module for working with SQLite databases. No additional Python package is normally required.

# Python program to connect Python to a SQLite database
import sqlite3
connection = sqlite3.connect("school.db")
print("Connected to SQLite successfully.")
connection.close()

Output:
Connected to SQLite successfully.

The sqlite3.connect() function creates a connection to the specified database file. If the file does not exist, SQLite can create it automatically.

Connecting Python to MySQL

Python can connect to MySQL using a connector library such as mysql-connector-python.

Install the connector using:

pip install mysql-connector-python

After installation, the connector can be imported into the Python program.

# Python program to connect Python to a MySQL database
import mysql.connector
connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="YOUR_PASSWORD",
    database="school"
)
print("Connected to MySQL successfully.")
connection.close()

Output:
Connected to MySQL successfully.

The connection requires details such as the MySQL server address, username, password, and database name.

Creating a Database Connection

A database connection is usually represented by a connection object. This object allows the Python program to communicate with the database.

For SQLite, a connection can be created using:

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

For MySQL, a connection can be created using:

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

The exact connection method depends on the database and connector being used.

Creating a Cursor

After establishing a database connection, a cursor is generally created to execute SQL statements.

For example:

cursor = connection.cursor()

The cursor acts as an interface between the Python program and the database. It can execute SQL commands such as CREATE, INSERT, SELECT, UPDATE, and DELETE.

Creating a Table

After connecting to a database, Python can execute an SQL statement to create a table.

# Python program to create a student table in SQLite
import sqlite3
connection = sqlite3.connect("school.db")
cursor = connection.cursor()
cursor.execute("""
CREATE TABLE IF NOT EXISTS students (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER,
    course TEXT
)
""")
connection.commit()
print("Table created successfully.")
connection.close()

Output:
Table created successfully.

The students table contains columns for the student's ID, name, age, and course.

Inserting Data

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

# Python program to insert data into a SQLite database
import sqlite3
connection = sqlite3.connect("school.db")
cursor = connection.cursor()
cursor.execute(
    "INSERT INTO students (id, name, age, course) VALUES (?, ?, ?, ?)",
    (1, "Tanvi", 20, "BSc IT")
)
connection.commit()
print("Data inserted successfully.")
connection.close()

Output:
Data inserted successfully.

The values are passed separately from the SQL statement. This approach is safer than directly adding user input to an SQL query.

Reading Data

The SELECT statement is used to retrieve records from a database.

# Python program to read data from a SQLite database
import sqlite3
connection = sqlite3.connect("school.db")
cursor = connection.cursor()
cursor.execute("SELECT * FROM students")
records = cursor.fetchall()
for record in records:
    print(record)
connection.close()

Output:
(1, 'Tanvi', 20, 'BSc IT')

The fetchall() method retrieves all rows returned by the query.

Updating Data

The UPDATE statement is used to modify existing records.

# Python program to update data in a SQLite database
import sqlite3
connection = sqlite3.connect("school.db")
cursor = connection.cursor()
cursor.execute(
    "UPDATE students SET age = ? WHERE id = ?",
    (21, 1)
)
connection.commit()
print("Data updated successfully.")
connection.close()

Output:
Data updated successfully.

The WHERE clause identifies the specific record that should be updated.

Deleting Data

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

# Python program to delete data from a SQLite database
import sqlite3
connection = sqlite3.connect("school.db")
cursor = connection.cursor()
cursor.execute(
    "DELETE FROM students WHERE id = ?",
    (1,)
)
connection.commit()
print("Data deleted successfully.")
connection.close()

Output:
Data deleted successfully.

The WHERE clause is important because it specifies which record should be deleted.

Using Parameterized Queries

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

For SQLite, a parameterized query can be written as:

cursor.execute("SELECT * FROM students WHERE age = ?", (20,))

For MySQL Connector/Python, placeholders are written using %s:

cursor.execute("SELECT * FROM students WHERE age = %s", (20,))

Parameterized queries help protect applications from SQL injection and reduce problems caused by directly inserting values into SQL statements.

Committing Changes

The commit() method saves changes made to the database.

connection.commit()

It is generally used after operations such as INSERT, UPDATE, and DELETE.
If changes are made without committing the transaction, they may not be saved as expected.

Closing the Database Connection

After completing database operations, the connection should be closed.

connection.close()

Closing the connection releases the resources being used by the database connection.
A cursor can also be closed when it is no longer needed:

cursor.close()

Handling Database Errors

Database operations can fail because of incorrect connection details, invalid SQL statements, unavailable database servers, or other problems. Python can handle these errors using try-except.

# Python program to handle SQLite database errors
import sqlite3
try:
    connection = sqlite3.connect("school.db")
    cursor = connection.cursor()
    cursor.execute("SELECT * FROM students")
    records = cursor.fetchall()
    for record in records:
        print(record)
except sqlite3.Error as error:
    print("Database error:", error)
finally:
    if "connection" in locals():
        connection.close()
        print("Database connection closed.")

Output:
(1, 'Tanvi', 20, 'BSc IT')
Database connection closed.

The finally block ensures that the database connection is closed after the operation.

How Does Python Database Connectivity Work?

The database connection process follows a simple sequence.

1. Import the Database Library: Python first imports the library required to communicate with the selected database.
2. Establish the Connection: The program connects to the database using the required connection details.
3. Create a Cursor: A cursor is created to execute SQL statements.
4. Execute SQL Queries: The cursor executes SQL commands to create, insert, retrieve, update, or delete data.
5. Process the Results: Python retrieves and processes the results returned by the database.
6. Commit Changes: Changes made by database modification operations are committed when required.
7. Close the Connection: The cursor and database connection are closed after the database operations are completed.

Complete Database Connection Program

The following program demonstrates a complete SQLite database operation using Python.

# Python program to connect Python to a database and perform CRUD operations
import sqlite3
connection = sqlite3.connect("school.db")
cursor = connection.cursor()
cursor.execute("""
CREATE TABLE IF NOT EXISTS students (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER,
    course TEXT
)
""")
cursor.execute(
    "INSERT OR REPLACE INTO students (id, name, age, course) VALUES (?, ?, ?, ?)",
    (1, "Tanvi", 20, "BSc IT")
)
connection.commit()
cursor.execute("SELECT * FROM students")
records = cursor.fetchall()
print("Student Records:")
for record in records:
    print(record)
cursor.execute(
    "UPDATE students SET age = ? WHERE id = ?",
    (21, 1)
)
connection.commit()
print("Student record updated successfully.")
cursor.execute(
    "DELETE FROM students WHERE id = ?",
    (1,)
)
connection.commit()
print("Student record deleted successfully.")
cursor.close()
connection.close()
print("Database connection closed.")

Output:
Student Records:
(1, 'Tanvi', 20, 'BSc IT')
Student record updated successfully.
Student record deleted successfully.
Database connection closed.

Advantages of Database Connectivity in Python

1. Permanent Data Storage: Database connectivity allows Python applications to store data beyond the lifetime of the program.
2. Easy Data Management: Python can use SQL commands to manage database records.
3. Structured Information: Databases organize application data into tables and related records.
4. Application Integration: Python can connect databases with web applications, desktop applications, and automation systems.
5. Multiple Database Options: Python supports different databases, allowing developers to choose a database based on application requirements.

Common Beginner Mistakes

1. Using the Wrong Database Credentials: Incorrect usernames, passwords, database names, or server addresses can prevent a connection from being established.
2. Forgetting to Create a Cursor: A cursor is generally required to execute SQL statements through the database connection.
3. Forgetting to Commit Changes: Database modifications may not be saved if the required transaction is not committed.
4. Using Unsafe SQL Queries: Directly adding user input to SQL statements can create SQL injection vulnerabilities, so parameterized queries should be used.
5. Forgetting to Close the Connection: Leaving database connections open can unnecessarily consume system and database resources.

Conclusion

Connecting Python to a database allows applications to store and manage data efficiently. Python provides different libraries and modules for connecting to databases such as SQLite, MySQL, and PostgreSQL. The basic process involves establishing a connection, creating a cursor, executing SQL queries, processing results, committing changes, and closing the connection. Understanding these steps provides a strong foundation for building Python applications that work with databases.

Frequently Asked Questions

1. What is database connectivity in Python?

Database connectivity is the process of connecting a Python application to a database so that it can store, retrieve, update, and delete data.

2. Which databases can Python connect to?

Python can connect to databases such as SQLite, MySQL, and PostgreSQL using their respective modules or connector libraries.

3. What is the purpose of a cursor?

A cursor is used to execute SQL statements and retrieve results from a database.

4. Why is commit() used in database programming?

The commit() method saves changes made by operations such as inserting, updating, or deleting database records.

5. Why should database connections be closed?

Database connections should be closed after use to release resources and prevent unnecessary open connections.