Python Connectivity with SQL Notes Class 12 CS (083) | High Scoring Revision

Explore Python Connectivity with SQL Notes featuring clear explanations, practical examples, and CBSE-oriented content for effective exam and practical preparation.

⚠️ Before learning Python Connectivity with MySQL, it is essential to understand Database Concepts and SQL, as they form the foundation of Unit 3: Database Management. If you haven’t covered them yet, refer to the links below first.

Database Concepts Notes Class 12 CS

Structure Query Language Notes Class 12 CS

Interface of python with an SQL database

• Python can be connected to an SQL database to perform database operations like insert, update, delete, and retrieve data.
• This connection helps in creating database-driven applications using Python.
• Python uses a database connector (like MySQL connector) to establish connection with the database.

Connecting SQL with Python

There are 6 steps that must be followed in sequence to connect Python with an SQL database:

1. Import the MySQL Connector Module
2. Establish Connection with Database
3. Create Cursor Object
4. Execute the Query
5. Fetch Result / Commit Changes
6. Close the Connection

import mysql.connector

2. Establish Connection with Database
Next, we need to establish a connection between Python and the MySQL database using the connect() method.

conn = mysql.connector.connect(
    host=”localhost”,
    user=”root”,
    password=”1234″,
    database=”School”
)

Connection-object → The connection object stores and manage the connection between Python and the MySQL database and is used to perform database operations.
host → Specifies the address of the MySQL server. localhost means the database is running on the same computer.
user → Specifies the MySQL username.
password → Specifies the password of the MySQL account.
database → Specifies the name of the database to connect with.

Note: the user and password you specify should be valid user and password for your local MySQL

3. Create Cursor Object
A cursor is an object used to execute SQL queries and fetch data from the database in Python.

cursor = conn.cursor()

4. Execute SQL Query
After creating the cursor, SQL queries can be executed using the execute() method.

cursor.execute(“Select * from Student”)

This code executes the query and stores the retrieved records (recordset) in the cursor object.

Recordset – A recordset is the collection of records (rows of data) fetched after executing a SQL query.

5. Fetch Result / Commit Changes

• After executing the SQL query, the retrieved records are stored in the recordset.
• Records can be fetched from the recordset using the following methods:
  • fetchone() → Fetches one record at a time in form of tuple
  • fetchmany(n) → Fetches specified number of records
  • fetchall() → Fetches all records in form of tuple
• rowcount is used to get the number of rows affected or retrieved by the query.
• For INSERT, UPDATE, and DELETE queries, commit() is used to save changes permanently in the database.

fetchall()

result = cursor.fetchall()
count = cursor.rowcount
print(“Total Records =”,count)
for row in result:
    print(row)

fetchone()

result = cursor.fetchone()                          #fetch one record in the resultset. Fetch first record first time

count = cursor.rowcount                           #counts 1 record

print(“Total no of records =”,count)
print(result)

6. Close the Connection

This is final step where we need to close the connection established.

conn.close()

example: creating database connectivity applications

Example:

Write a Python program to connect with MySQL database and display records from the STUDENT table.

Adno	Name	Class
101	Rahul	12
102	Priya	11
103	Aman	12

Solution:

import mysql.connector

# Step 1: Establish connection with database
conn = mysql.connector.connect(
    host=”localhost”,
    user=”root”,
    password=”1234″,
    database=”School”
)

# Step 2: Create cursor object
cursor = conn.cursor()

# Step 3: Write SQL query
query = “SELECT * FROM Student”

# Step 4: Execute query
cursor.execute(query)

# Step 5: Fetch all records
records = cursor.fetchall()

# Step 6: Display records
for row in records:
    print(row)

# Step 7: Display total number of records
print(“Total Records:”, cursor.rowcount)

# Step 8: Close connection
cursor.close()
conn.close()

Output:

(101, ‘Rahul’, 12)
(102, ‘Priya’, 11)
(103, ‘Aman’, 12)

Total Records: 3

Example:

Write a Python program to display all records from the STUDENT table using fetchone().

Adno	Name	Class
101	Rahul	12
102	Priya	11
103	Aman	12

Solution:

import mysql.connector

# Step 1: Establish connection with database
conn = mysql.connector.connect(
    host=”localhost”,
    user=”root”,
    password=”1234″,
    database=”School”
)

# Step 2: Create cursor object
cursor = conn.cursor()

# Step 3: Execute query
cursor.execute(“SELECT * FROM Student”)

# Step 4: Fetch and display records one by one
record = cursor.fetchone()

while record is not None:
    print(record)
    record = cursor.fetchone()

# Step 5: Close connection
cursor.close()
conn.close()

Output:

(101, ‘Rahul’, 12)
(102, ‘Priya’, 11)
(103, ‘Aman’, 12)