Executing SQL Queries using Python (CBSE Class 12 Computer Science - 083)

After establishing a connection between Python and MySQL, the next step is to execute SQL queries. Python provides the execute() method to run SQL statements. Data modification operations such as INSERT, UPDATE, and DELETE must be saved permanently using the commit() method.

Learning Objectives

• Understand execute().
• Understand commit().
• Understand rowcount.
• Insert records using Python.
• Update records using Python.
• Delete records using Python.

---

Sample Student Table

Student
----------------------------------------------
StudentID | Name   | City    | Marks
----------------------------------------------
101       | Riya   | Jaipur  | 95
102       | Aman   | Delhi   | 91
103       | Priya  | Jaipur  | 88

---

The execute() Method

The execute() method sends an SQL statement from Python to the MySQL server for execution.

Syntax

cursor.execute(sql_query)

---

Example

sql = "CREATE TABLE Test(ID INT)"
cursor.execute(sql)

---

The commit() Method

INSERT, UPDATE and DELETE operations change the database. These changes are temporary until they are saved permanently using commit().

Syntax

connection.commit()

---

The rowcount Property

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

Syntax

print(cursor.rowcount)

---

INSERT Operation

The INSERT statement adds new records to a table.

SQL Query

INSERT INTO Student
VALUES(104,'Rahul','Mumbai',76)

---

Python Program

import mysql.connector

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

cur=con.cursor()

sql="""
INSERT INTO Student
VALUES(104,'Rahul','Mumbai',76)
"""

cur.execute(sql)

con.commit()

print(cur.rowcount,"Record Inserted")

con.close()

Output

1 Record Inserted

---

UPDATE Operation

The UPDATE statement modifies existing records.

SQL Query

UPDATE Student
SET Marks=98
WHERE StudentID=101;

---

Python Program

import mysql.connector

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

cur=con.cursor()

sql="""
UPDATE Student
SET Marks=98
WHERE StudentID=101
"""

cur.execute(sql)

con.commit()

print(cur.rowcount,"Record Updated")

con.close()

Output

1 Record Updated

---

DELETE Operation

The DELETE statement removes records from a table.

SQL Query

DELETE FROM Student
WHERE StudentID=104;

---

Python Program

import mysql.connector

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

cur=con.cursor()

sql="""
DELETE FROM Student
WHERE StudentID=104
"""

cur.execute(sql)

con.commit()

print(cur.rowcount,"Record Deleted")

con.close()

Output

1 Record Deleted

---

Sequence of Database Operations

Import Module
      │
      ▼
Connect Database
      │
      ▼
Create Cursor
      │
      ▼
execute()
      │
      ▼
commit()
      │
      ▼
Display rowcount
      │
      ▼
Close Connection

---

Using Multiple Queries

cur.execute("UPDATE Student SET Marks=90 WHERE StudentID=102")

cur.execute("DELETE FROM Student WHERE StudentID=108")

con.commit()

---

Using execute() for DDL Commands

cur.execute("""
CREATE TABLE Employee
(
EmpID INT,
Name VARCHAR(30)
)
""")

DDL statements such as CREATE TABLE and DROP TABLE can also be executed using execute().

---

Difference Between execute(), commit() and rowcount

Method	Purpose
execute()	Executes an SQL statement.
commit()	Permanently saves database changes.
rowcount	Returns the number of affected rows.

---

Execution Flow

execute()
     │
     ▼
SQL Executed
     │
     ▼
commit()
     │
     ▼
Changes Saved
     │
     ▼
rowcount

---

Common Errors

Error	Reason
Changes not saved	commit() not called.
0 rows affected	WHERE condition did not match any record.
Syntax Error	Incorrect SQL statement.
ProgrammingError	Table or column name is incorrect.

---

Exam Tips

---

Frequently Asked Questions (FAQs)

1. Why is execute() used?

It executes SQL commands from Python.

2. Why is commit() required?

It permanently saves database changes.

3. What does rowcount return?

It returns the number of rows affected by the last SQL statement.

4. Is commit() required after a SELECT query?

No. SELECT only retrieves data and does not modify the database.

5. Which SQL statements require commit()?

INSERT, UPDATE and DELETE require commit() because they modify data.

---

Summary

• execute() executes SQL statements from Python.
• commit() permanently saves changes made to the database.
• rowcount returns the number of affected rows.
• INSERT adds new records.
• UPDATE modifies existing records.
• DELETE removes records.
• These methods are essential for building Python-MySQL applications.