Python Database Connectivity (CBSE Class 12 Computer Science - 083)

Modern applications store data in databases instead of text files because databases provide better security, faster retrieval, data integrity, and support multiple users simultaneously. Python can communicate with MySQL databases using the mysql.connector module. This communication is known as Database Connectivity.

Learning Objectives

• Understand database connectivity.
• Learn about MySQL Connector.
• Install mysql-connector-python.
• Create a connection using connect().
• Create a cursor using cursor().
• Close database connections properly.

---

What is Database Connectivity?

Database Connectivity is the process of establishing communication between a Python program and a database management system (DBMS). After establishing the connection, Python programs can execute SQL queries to insert, update, delete, and retrieve data.

---

Why is Database Connectivity Required?

• Store large amounts of data.
• Retrieve information quickly.
• Perform database operations using Python.
• Create real-world applications.
• Reduce data redundancy.
• Maintain data security.

---

Python and MySQL

Python cannot communicate directly with MySQL. A connector library acts as a bridge between Python and the MySQL Server.

Python Program
      │
      ▼
mysql.connector Module
      │
      ▼
MySQL Server
      │
      ▼
Database

---

MySQL Connector

The mysql.connector module is an official Python library that allows Python programs to communicate with MySQL databases.

Features

• Simple to use.
• Supports all SQL commands.
• Provides secure database communication.
• Supports transactions.
• Works on Windows, Linux, and macOS.

---

Installing MySQL Connector

Before connecting Python to MySQL, the connector package must be installed.

Command

pip install mysql-connector-python

If the package is already installed, Python can directly import it.

---

Importing the Module

import mysql.connector

This statement imports the MySQL Connector library into the Python program.

---

The connect() Function

The connect() function establishes a connection between Python and the MySQL Server.

Syntax

mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="School"
)

---

Parameters of connect()

Parameter	Description
host	Name or IP address of the MySQL Server.
user	MySQL username.
password	Password of the MySQL user.
database	Name of the database to connect.

---

Example : Connecting to MySQL

import mysql.connector

con = mysql.connector.connect(
    host="localhost",
    user="root",
    password="root",
    database="School"
)

print("Connection Successful")

Output

Connection Successful

---

Connection Object

The object returned by connect() is called the Connection Object. It represents the active connection with the MySQL Server.

con = mysql.connector.connect(...)

---

The cursor() Method

The cursor() method creates a Cursor Object. The cursor executes SQL queries and retrieves results from the database.

Syntax

cur = con.cursor()

---

Cursor Object

A cursor acts like an intermediary between the Python program and the database. All SQL statements are executed through the cursor.

cur = con.cursor()

---

Complete Connection Program

import mysql.connector

con = mysql.connector.connect(
    host="localhost",
    user="root",
    password="root",
    database="School"
)

cur = con.cursor()

print("Database Connected Successfully")

---

Checking the Connection

The is_connected() method checks whether the connection has been established successfully.

import mysql.connector

con = mysql.connector.connect(
    host="localhost",
    user="root",
    password="root",
    database="School"
)

if con.is_connected():
    print("Connected Successfully")

---

Closing the Connection

After completing all database operations, the connection should always be closed.

Syntax

con.close()

---

Complete Program

import mysql.connector

con = mysql.connector.connect(
    host="localhost",
    user="root",
    password="root",
    database="School"
)

cur = con.cursor()

print("Connected Successfully")

con.close()

print("Connection Closed")

Output

Connected Successfully
Connection Closed

---

Flow of Database Connectivity

Import mysql.connector
          │
          ▼
connect()
          │
          ▼
Connection Object
          │
          ▼
cursor()
          │
          ▼
Execute SQL Queries
          │
          ▼
Close Connection

---

Advantages of Database Connectivity

• Fast data retrieval.
• Improved security.
• Supports multi-user applications.
• Easy data management.
• Suitable for real-world software development.

---

Common Errors

Error	Reason
ModuleNotFoundError	mysql-connector-python is not installed.
Access denied	Incorrect username or password.
Unknown database	The specified database does not exist.
Can't connect to MySQL server	MySQL Server is not running.

---

Exam Tips

---

Frequently Asked Questions (FAQs)

1. Why is mysql.connector required?

It provides communication between Python and the MySQL database.

2. Which function establishes a database connection?

The connect() function.

3. What is a cursor?

A cursor is an object used to execute SQL statements and retrieve results.

4. Why should we close the database connection?

Closing the connection releases system resources and improves application performance.

5. Which method checks whether the connection is successful?

The is_connected() method.

---

Summary

• Python communicates with MySQL using the mysql.connector module.
• The connect() function establishes a connection.
• The connection object represents the active database connection.
• The cursor() method creates a cursor for executing SQL queries.
• The close() method terminates the connection.
• Database connectivity is the foundation for creating Python-MySQL applications.