DATABASE OPERATIONS IN MYSQL

USING PYTHON

1. Introduction

Python sends SQL queries to the MySQL Server through a connector library. The MySQL
server executes the query and returns the result back to Python.

Real-Life Example

Python acts as a bridge between the user and the MySQL database.

2. MySQL Connector

Python cannot communicate with MySQL directly. It needs a special library called mysql-
connector-python. This library establishes the connection between Python and MySQL.

Functions of MySQL Connector

Connect Python with MySQL

Execute SQL queries
Insert records
Select records
Update records
Delete records
Return query results

Installing MySQL Connector

The connector is installed using the pip command.

pip install mysql-connector-python

3. Importing the Module

Before connecting to MySQL, we must import the connector library.

import [Link]

4. Connecting Python to MySQL

To connect Python with MySQL, we use the connect() function.

Syntax

import [Link]

connection = [Link](
host="localhost",
user="root",
password="your_password"
)

Explanation of Parameters

Parameter Description

host Address of MySQL Server.

user MySQL username.

 password Password of the MySQL user.

5. Complete Connection Program

import [Link]

connection = [Link](
host="localhost",
user="root",
password="1234"
)

print("Connected Successfully")

Output

Connected Successfully

Connecting to a Particular Database

If we want Python to work with a specific database, we must provide the database name.

import [Link]

connection = [Link](

host="localhost",

user="root",
password="1234",

database="college"

print("Database Connected")

The database must already exist in MySQL. Otherwise Python will generate an error.

6. Connection Object

The object returned by [Link]() is called the Connection Object.

Using this object, Python communicates with MySQL.

Example

connection = [Link](
host="localhost",
user="root",
password="1234",
database="college"
)

Here, connection is the Connection Object.

7. Checking the Connection

Sometimes we need to verify whether the connection is successful. For this, MySQL
Connector provides the is_connected() function.

import [Link]
connection = [Link](
host="localhost",
user="root",
password="1234"
)
if connection.is_connected():
print("Connection Successful")
else:
print("Connection Failed")

Output

Connection Successful

8. Closing the Connection

After completing all database operations, the connection should be closed. Closing the
connection frees system resources.

[Link]()

12. Summary

Function Purpose

import [Link] Imports MySQL Connector

connect() Creates connection with MySQL

is_connected() Checks whether connection is successful

close() Closes the database connection

 BASIC CRUD OPERATIONS IN MYSQL
USING PYTHON

CRUD is one of the most important concepts in database programming. Almost every
application that uses a database performs these four operations.

Operation Meaning SQL Command

C Create (Insert New Record) INSERT

R Read (View Records) SELECT

U Update Existing Record UPDATE

D Delete Record DELETE

1. Cursor Object

After creating a connection with MySQL, Python cannot execute SQL queries directly. It
needs another object called the Cursor Object. The cursor acts as a bridge between the
Python program and the MySQL database.

Connection Object → Connects Python with MySQL. Cursor Object → Executes SQL
commands.

Syntax

cursor = [Link]()

Explanation

Statement Meaning
 connection Connection object created using connect()

cursor() Creates a Cursor Object

cursor Variable storing the Cursor Object

2. Complete Example of Creating Cursor

import [Link]
connection = [Link](
host="localhost",
user="root",
password="1234",
database="college"
)
cursor = [Link]()
print("Cursor Created Successfully")

Cursor Created Successfully

Every SQL query is executed through the Cursor Object.

3. execute() Method

The execute() method is used to execute SQL queries from Python.

Syntax

[Link](sql_query)

Example
[Link]("SELECT * FROM student")

Every INSERT, SELECT, UPDATE and DELETE statement is executed using

execute().

4. INSERT Operation

The INSERT statement is used to add new records into a table. Python sends the INSERT
query to MySQL using execute().

import [Link]
connection=[Link](
host="localhost",
user="root",
password="1234",
database="college"
)
cursor=[Link]()
query="INSERT INTO student VALUES(102,'Akhil','IT')"
[Link](query)
[Link]()
print("Record Inserted Successfully")
[Link]()

Output

Record Inserted Successfully

5. commit() Method

Whenever data is inserted, updated or deleted, the changes are first stored temporarily.
The commit() method permanently saves those changes into the database.
Syntax

[Link]()

Why commit() is Important

With commit() Without commit()

Record saved permanently. Record may not be saved.

Visible after reopening MySQL. May disappear after closing connection.

6. Inserting Multiple Records

Multiple INSERT statements can be executed one after another. After all INSERT
operations are completed, call commit() once.

[Link]("INSERT INTO student VALUES(103,'Anu','BCA')")

[Link]("INSERT INTO student VALUES(104,'Nikhil','BCom')")
[Link]("INSERT INTO student VALUES(105,'Fathima','BSc')")
[Link]()

7. SELECT Operation (Read)

The SELECT statement is used to retrieve (read) records from a table.

SQL Query

SELECT * FROM student;

This query displays all records from the student table.

 8. Program to Display All Records

import [Link]
connection = [Link](
host="localhost",
user="root",
password="1234",
database="college"
)
cursor = [Link]()
query = "SELECT * FROM student"
[Link](query)
records = [Link]()
for row in records:
print(row)
[Link]()

Output

(101, 'Rahul', 'CS') (102, 'Akhil', 'IT') (103, 'Anu', 'BCA')

9. Fetch Methods

After executing a SELECT query, Python stores the result in the cursor. To read these
results, MySQL Connector provides three fetch methods.

Method Purpose

fetchone() Returns only one record.

fetchmany(n) Returns the specified number of records.

fetchall() Returns all records.

 10. fetchone()

The fetchone() method returns only the first available row. If called again, it returns the
next row.

Syntax

row = [Link]()

Example

[Link]("SELECT * FROM student")

row = [Link]()
print(row)

Output

(101, 'Rahul', 'CS')

Each call to fetchone() returns the next row.

11. fetchmany()

The fetchmany() method returns a specified number of rows.

Syntax

rows = [Link](2)

Example

[Link]("SELECT * FROM student")

rows = [Link](2)
print(rows)

Output

[(101,'Rahul','CS'), (102,'Akhil','IT')]

12. fetchall()

The fetchall() method returns all remaining records from the query result.

Syntax

rows = [Link]()

Example

[Link]("SELECT * FROM student")

rows = [Link]()
print(rows)

Output

[(101,'Rahul','CS'), (102,'Akhil','IT'), (103,'Anu','BCA')]

13. Quick Revision

Method Purpose

execute() Executes SQL query

fetchone() Returns one row

 fetchmany() Returns specified rows

fetchall() Returns all rows

14. UPDATE Operation

The UPDATE statement is used to modify existing records in a table. Unlike INSERT,
which adds new records, UPDATE changes the values of existing records.

SQL Syntax

UPDATE table_name
SET column_name = value
WHERE condition;

The WHERE clause specifies which record should be updated.

15. Example SQL Query

Suppose the student table contains the following records.

Roll No Name Department

101 Rahul CS

102 Akhil IT

Now we want to change Akhil's department from IT to MCA.

import [Link]
connection=[Link](
host="localhost",
user="root",
password="1234",
database="college"
)
cursor=[Link]()
query="UPDATE student SET Department='MCA' WHERE RollNo=102"
[Link](query)
[Link]()
print("Record Updated Successfully")
[Link]()

Output

Record Updated Successfully

Always call commit() after UPDATE.

16. DELETE Operation

The DELETE statement removes records from a table. Only the matching rows are
deleted.

SQL Syntax

DELETE FROM table_name

WHERE condition;

DELETE using Python

import [Link]

connection=[Link](
host="localhost",

user="root",

password="1234",

database="college"

cursor=[Link]()

query="DELETE FROM student WHERE RollNo=103"

[Link](query)

[Link]()

print("Record Deleted Successfully")

[Link]()

Output

Record Deleted Successfully

17. rowcount Property

The rowcount property returns the number of rows affected by the last SQL query.

Example

[Link]("DELETE FROM student WHERE RollNo=103")

[Link]()

print([Link])

Output

Output 1 means one record was affected.

18. Complete CRUD Program

import [Link]

connection=[Link](

host="localhost",

user="root",

password="1234",

database="college"

cursor=[Link]()

# INSERT

[Link]("INSERT INTO student VALUES(110,'John','BCA')")

[Link]()
print("Insert Completed")

# SELECT

[Link]("SELECT * FROM student")

records=[Link]()

print("\nStudent Records")

for row in records:

print(row)

# UPDATE

[Link]("UPDATE student SET Department='MCA' WHERE RollNo=110")

[Link]()

print("\nUpdate Completed")

# DELETE

[Link]("DELETE FROM student WHERE RollNo=110")

[Link]()

print("Delete Completed")

[Link]()

Sample Output

Insert Completed Student Records (101,'Rahul','CS') (102,'Akhil','IT')

(110,'John','BCA') Update Completed Delete Completed

19. Chapter Summary

Method / Property Purpose

connect() Creates database connection

cursor() Creates Cursor Object

execute() Executes SQL query

commit() Saves changes permanently

fetchone() Returns one record

fetchmany() Returns specified number of records

fetchall() Returns all records

rowcount Returns affected rows

close() Closes database connection

20. Key Points to Remember

Use [Link] to connect Python with MySQL.

Create a Connection Object using connect().
Create a Cursor Object using cursor().
Use execute() to execute SQL queries.
Use commit() only for INSERT, UPDATE and DELETE.
Use fetchone(), fetchmany() and fetchall() only with SELECT queries.
Always close the connection after completing database operations.