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.