Interface of Python with an SQL Database: CBSE Class 12 Computer Science

What Interface of Python with an SQL Database is about

Weightage: This chapter is part of Unit 3, Database Management, which carries 20 of the 70 theory marks together with Database Concepts and Structured Query Language (SQL).Source: CBSE Computer Science (083) syllabus 2026-27, distribution of marks, Unit 3 Database Management

Key concepts

Connecting Python with MySQL

NCERT section Not in NCERT; CBSE syllabus (connecting SQL with Python)

The mysql.connector module (installed with pip install mysql-connector-python) lets Python talk to a MySQL server. mysql.connector.connect() takes the server's host (usually 'localhost'), the MySQL user, the password (passwd or password) and, optionally, the database to use, and returns a connection object. con.is_connected() returns True if the connection succeeded. Always close the connection with con.close() at the end.

Worked example

Write code to connect to the database school on the local MySQL server with user root and password tiger, and print a message if the connection is open.

Try it first, then show the solutionSolution

1. Import the module, call connect() with the four arguments, test is_connected().

2. import mysql.connector as m
   con = m.connect(host='localhost', user='root', passwd='tiger', database='school')
   if con.is_connected():
       print('Connected to school')
   con.close()

Answer: Connected to school

Cursor and execute()

NCERT section Not in NCERT; CBSE syllabus (cursor(), execute())

A cursor is an object that runs SQL statements on the connection and holds the result of a SELECT so you can read it row by row. Create it with cur = con.cursor(). cur.execute(sql) sends one SQL statement as a string; cur.execute(sql, values) fills %s placeholders in the string with the values in a tuple (or list). One connection can have several cursors.

Worked example

Write the statements that create a cursor and select the names of students of class 12A.

Try it first, then show the solutionSolution

1. Text in SQL needs quotes; the easiest safe way is a %s placeholder.

2. import mysql.connector as m
   con = m.connect(host='localhost', user='root', passwd='tiger', database='school')
   cur = con.cursor()
   cur.execute('SELECT Name FROM STUDENT WHERE Class = %s', ('12A',))
   print(cur.fetchall())
   con.close()

3. Note the comma in ('12A',): a one-item tuple.

Answer: [('Asha',), ('Meena',)]

Reading results: fetchone(), fetchall() and rowcount

NCERT section Not in NCERT; CBSE syllabus (fetchone(), fetchall(), rowcount)

After a SELECT, cur.fetchone() returns the next row as a tuple (or None when no rows are left); cur.fetchall() returns a list of tuples of all remaining rows (an empty list if none). Each fetch continues from where the last one stopped. cur.rowcount gives the number of rows fetched so far for a SELECT, and the number of rows affected for INSERT, UPDATE or DELETE. A single selected column still comes back as a tuple, e.g. ('Asha',).

Worked example

Predict the output:

import mysql.connector as m
con = m.connect(host='localhost', user='root', passwd='tiger', database='school')
cur = con.cursor()
cur.execute('SELECT * FROM STUDENT WHERE Marks > %s', (70,))
r = cur.fetchone()
print(r, cur.rowcount)
rest = cur.fetchall()
print(rest, cur.rowcount)
con.close()

Try it first, then show the solutionSolution

1. Rows with Marks > 70: Asha (88), Meena (79), Joy (91).
2. fetchone() gives Asha's row; one row fetched so far.
3. fetchall() gives the remaining two rows; three fetched in total.

Answer: (1, 'Asha', '12A', 88, 'Delhi') 1
[(3, 'Meena', '12A', 79, 'Chennai'), (4, 'Joy', '12C', 91, 'Delhi')] 3

INSERT, UPDATE and DELETE with commit()

NCERT section Not in NCERT; CBSE syllabus (insert, update, delete queries using cursor; commit())

Statements that change data are run with execute() like any other, but the change is only made permanent when you call **con.commit()** on the connection. Without commit(), the change is lost when the program ends (MySQL's tables use transactions). After the execute, cur.rowcount tells how many rows were inserted, updated or deleted, which is a quick way to report 'Record not found' when it is 0.

Worked example

Write code to add 5 marks to every student of class 12A, save the change and print how many records were updated.

Try it first, then show the solutionSolution

1. import mysql.connector as m
   con = m.connect(host='localhost', user='root', passwd='tiger', database='school')
   cur = con.cursor()
   cur.execute('UPDATE STUDENT SET Marks = Marks + 5 WHERE Class = %s', ('12A',))
   con.commit()
   print(cur.rowcount, 'record(s) updated')
   con.close()

2. Class 12A has Asha and Meena, so 2 rows change (88 → 93, 79 → 84).

Answer: 2 record(s) updated

Building queries with %s or format()

NCERT section Not in NCERT; CBSE syllabus (%s format specifier or format())

Values from variables can be put into a query in two ways. %s placeholders: write %s where each value goes (no quotes around it, even for text) and pass a tuple: cur.execute('INSERT INTO STUDENT VALUES (%s, %s, %s, %s, %s)', (6, 'Neha', '12C', 72, 'Agra')). The connector adds quotes where needed. format(): build the full string yourself, putting quotes around text values: "INSERT INTO STUDENT VALUES ({}, '{}', '{}', {}, '{}')".format(6, 'Neha', '12C', 72, 'Agra'). Both are in the syllabus; %s is safer because the connector handles quoting.

Worked example

Insert the record (6, 'Neha', '12C', 72, 'Agra') using format(), commit it, and print the rowcount.

Try it first, then show the solutionSolution

1. With format() the text values need their own single quotes inside the SQL string.

2. import mysql.connector as m
   con = m.connect(host='localhost', user='root', passwd='tiger', database='school')
   cur = con.cursor()
   q = "INSERT INTO STUDENT VALUES ({}, '{}', '{}', {}, '{}')".format(6, 'Neha', '12C', 72, 'Agra')
   cur.execute(q)
   con.commit()
   print(cur.rowcount)
   con.close()

Answer: 1

A complete database connectivity application

NCERT section Not in NCERT; CBSE syllabus (creating database connectivity applications)

A connectivity application wraps the six steps in functions: one to add a record, one to search, one to update, one to delete, one to display, often driven by a menu. Each function can open its own connection (simplest in exam answers) or share one. Read user input with input(), convert numbers with int(), pass values through %s, commit after changes, and report 'No such record' when rowcount is 0 or fetchone() returns None.

Worked example

Write a function search(roll) that prints the name and marks of the student with that roll number, or 'No such student'. Show search(3) and search(9).

Try it first, then show the solutionSolution

1. import mysql.connector as m
   def search(roll):
       con = m.connect(host='localhost', user='root', passwd='tiger', database='school')
       cur = con.cursor()
       cur.execute('SELECT Name, Marks FROM STUDENT WHERE RollNo = %s', (roll,))
       row = cur.fetchone()
       if row is None:
           print('No such student')
       else:
           print(row[0], row[1])
       con.close()
   search(3)
   search(9)

2. RollNo 3 is Meena with 79 marks; RollNo 9 does not exist, so fetchone() gives None.

Answer: Meena 79
No such student

Interface of Python with an SQL Database in real life

Each concept, met in an everyday situation. Read the situation, answer the question in your own words, then compare with the model answer. The one-line hook is what to remember.

Topics in this chapter

• Connecting Python with MySQLComing soon
• Insert, Update and Delete with commit()Coming soon
• fetchone(), fetchall() and rowcountComing soon
• Connecting Python to MySQL: connect(), cursor() and execute()
• Fetching Query Results: fetchone(), fetchall() and rowcount
• INSERT, UPDATE and DELETE from Python: commit(), %s and format()

Interface of Python with an SQL Database: syntax and key facts

Six steps

1. 1.import mysql.connector
2. 2.con = connect(host, user, passwd, database)
3. 3.cur = con.cursor()
4. 4.cur.execute(sql[, values])
5. 5.fetch (SELECT) or con.commit() (INSERT/UPDATE/DELETE)
6. 6.con.close()

commit() belongs to the connection, not the cursor.

Fetch methods

fetchone(): next row as a tuple or None; fetchall(): list of tuples of the remaining rows ([] if none)

Each fetch continues from where the previous one stopped.

rowcount

After SELECT: rows fetched so far. After INSERT/UPDATE/DELETE: rows affected.

It is an attribute: cur.rowcount, no brackets.

%s placeholders

cur.execute('... WHERE RollNo = %s AND City = %s', (roll, city))

No quotes around %s; a single value still needs a tuple: (roll,).

format()

"... VALUES ({}, '{}')".format(6, 'Neha')

Put quotes around text placeholders yourself.

How the board asks Interface of Python with an SQL Database

The question patterns to expect, and the method that works for each.

Tricks to remember Interface of Python with an SQL Database

Short memory aids. Say each one aloud once, then use it on a question the same day.

• "I Can Cook Every Fine Curry"

  Import, Connect, Cursor, Execute, Fetch/commit, Close.

  Use it when: Writing all six steps in order.

• Change → commit

  INSERT, UPDATE, DELETE change data, so they need commit(); SELECT only reads, so it needs a fetch.

  Use it when: Deciding between commit() and fetch.

• One is a tuple, all is a list

  fetchone() → (row); fetchall() → [(row), (row)].

  Use it when: Output questions on fetch methods.

• %s needs no quotes, {} needs quotes

  The connector quotes %s values for you; with format() you must quote text yourself.

  Use it when: Building queries without syntax errors.

Interface of Python with an SQL Database: board-level solved questions

Ordered from board level to HOTS. Cover the solution, write your own, then compare step by step.

import mysql.connector as m
con = m.connect(host='localhost', user='root', passwd='tiger', database='school')
cur = con.cursor()
cur.execute('SELECT Name FROM STUDENT')
a = cur.fetchone()
b = cur.fetchone()
c = cur.fetchall()
print(a, b, len(c), cur.rowcount)
con.close()

import ______ as m                              # (i)
con = m.______(host='localhost', user='root', passwd='tiger', database='school')   # (ii)
cur = con.cursor()
cur.execute('DELETE FROM STUDENT WHERE City = %s', ('Pune',))
con.______                                      # (iii)
print(cur.______, 'deleted')                     # (iv)

import mysql.connector as m
con = m.connect(host='localhost', user='root', passwd='tiger', database='school')
cur = con.cursor
cur.execute('INSERT INTO STUDENT VALUES (%s, %s, %s, %s, %s)', (7, Tara, '12A', 81, 'Goa'))
cur.commit()
print(cur.rowcount())

Case-study practice (Section E style)

Read the situation, then answer every part. In the board paper this is a 4-mark question split into 1 + 1 + 2 marks.

Where the marks are

How a typical answer is split into step marks. Show every step: a right final answer alone rarely earns full marks.

Where students lose marks in Interface of Python with an SQL Database

The slips examiners see most often, and what to write instead.

• ✗Mistake: Forgetting con.commit() after INSERT, UPDATE or DELETE

  The change is rolled back when the connection closes.

  ✓Do this: Write con.commit() immediately after every execute() that changes data.

  Usually costs: 1 mark

• ✗Mistake: Calling commit() on the cursor

  commit() is a method of the connection object.

  Wrong working

  cur.commit()

  Right working

  con.commit()

  ✓Do this: Remember: the connection saves; the cursor executes and fetches.

  Usually costs: Half to 1 mark

• ✗Mistake: Passing a single value without a tuple comma

  (70) is just the integer 70; execute() needs a sequence of values.

  Wrong working

  cur.execute('... WHERE Marks > %s', (70))

  Right working

  cur.execute('... WHERE Marks > %s', (70,))

  ✓Do this: Write (value,) with the comma, or [value].

  Usually costs: Half a mark

• ✗Mistake: Putting quotes around %s or leaving them out with format()

  '%s' in quotes is inserted literally; format() does not add quotes for text.

  Wrong working

  "... WHERE Name = '%s'" with a values tuple, or "... WHERE Name = {}".format('Asha')

  Right working

  "... WHERE Name = %s" with (name,), or "... WHERE Name = '{}'".format('Asha')

  ✓Do this: %s: never quote. format(): quote text placeholders.

  Usually costs: Half to 1 mark

• ✗Mistake: Printing the result of execute() instead of fetching

  execute() runs the query but does not return the rows; the rows are read with fetchone() or fetchall().

  ✓Do this: After a SELECT, always call a fetch method and loop over its result.

  Usually costs: 1 mark

NCERT exercise guide: Interface of Python with an SQL Database

What each exercise in the NCERT chapter tests, how to approach it, and a question of the same kind solved step by step. Questions here are written in our own words.

Practice questions on Interface of Python with an SQL Database

Write each answer on paper first. Open the hint only if you are stuck, and the answer only when you have finished.

1. Q1 · 1 mark · MCQBoard level

   Which method returns all the remaining rows of a query result? (a) fetchone() (b) fetchall() (c) execute() (d) commit()

   Hint

   List of tuples.

   Answer

   (b) fetchall()

2. Q2 · 1 mark · Very short answerBoard level

   What is a cursor in Python-MySQL connectivity?

   Hint

   It executes and holds results.

   Answer

   An object created from the connection that executes SQL statements and holds the result set so rows can be fetched.

3. Q3 · 2 marks · Short answerHarder board level

   Predict the output:

   import mysql.connector as m
   con = m.connect(host='localhost', user='root', passwd='tiger', database='school')
   cur = con.cursor()
   cur.execute('SELECT Name, Marks FROM STUDENT WHERE Class = %s', ('12A',))
   for n, mk in cur.fetchall():
       print(n, mk + 2)
   print(cur.rowcount)
   con.close()

   Hint

   Two students in 12A.

   Answer

   Asha 90
   Meena 81
   2

4. Q4 · 2 marks · Short answerBoard level

   Write the statement that inserts the values in the variables r, n, c, mk and ct into STUDENT using %s, and the statement that saves it.

   Hint

   Five placeholders, one tuple.

   Answer

   cur.execute('INSERT INTO STUDENT VALUES (%s, %s, %s, %s, %s)', (r, n, c, mk, ct))
   con.commit()

5. Q5 · 1 mark · Assertion-reasonHOTS / competency

   Assertion (A): cur.execute('SELECT * FROM STUDENT WHERE RollNo = %s', (3)) raises an error.
   Reason (R): The values for %s placeholders must be given as a sequence such as a tuple, and (3) is an int.

   Hint

   What is (3)?

   Answer

   (a) Both A and R are true and R is the correct explanation of A; write (3,).

6. Q6 · 3 marks · Short answerHarder board level

   Write a function delete_student(roll) that deletes the student with the given roll number and prints 'Deleted' or 'No such roll number'. Show the output of delete_student(5) followed by delete_student(5).

   Hint

   Use rowcount after the DELETE.

   Answer

   import mysql.connector as m
   def delete_student(roll):
       con = m.connect(host='localhost', user='root', passwd='tiger', database='school')
       cur = con.cursor()
       cur.execute('DELETE FROM STUDENT WHERE RollNo = %s', (roll,))
       con.commit()
       if cur.rowcount == 0:
           print('No such roll number')
       else:
           print('Deleted')
       con.close()
   delete_student(5)
   delete_student(5)

   Output:
   Deleted
   No such roll number

7. Q7 · 3 marks · Short answerHOTS / competency

   Write a program to display the average marks of each class using GROUP BY from Python, one class per line.

   Hint

   The SQL does the grouping; Python only prints.

   Answer

   import mysql.connector as m
   con = m.connect(host='localhost', user='root', passwd='tiger', database='school')
   cur = con.cursor()
   cur.execute('SELECT Class, AVG(Marks) FROM STUDENT GROUP BY Class ORDER BY Class')
   for cls, avg in cur.fetchall():
       print(cls, round(avg, 1))
   con.close()

   Output:
   12A 83.5
   12B 59.5
   12C 91.0
   (MySQL returns AVG as a Decimal; round() still works.)

8. Q8 · 2 marks · Short answerHOTS / competency

   Why is it better to pass values with %s than to join strings with +, for example 'SELECT * FROM STUDENT WHERE Name = ' + name?

   Hint

   Quotes and safety.

   Answer

   With + you must add the quotes yourself, and a name containing a quote (e.g. O'Neil) breaks the query; a user could also type SQL that changes the meaning of the query. With %s the connector quotes and escapes the value correctly.

9. Q9 · 1 mark · Very short answerHarder board level

   What will print(con.is_connected()) show after con.close()?

   Hint

   The connection is closed.

   Answer

   False

Test your recall

Answer in your head or on paper first, then open the card. Pulling an answer out of memory makes it stick far better than re-reading.

1. 1.Which module connects Python with MySQL in the syllabus?Show answer

   mysql.connector.

2. 2.Which method creates the object that executes SQL statements?Show answer

   cursor() of the connection object.

3. 3.What does fetchone() return when no rows are left?Show answer

   None.

4. 4.What type does fetchall() return?Show answer

   A list of tuples.

5. 5.Why is commit() needed?Show answer

   To make INSERT, UPDATE and DELETE changes permanent in the database.

6. 6.What does cur.rowcount give after an UPDATE?Show answer

   The number of rows updated (affected).

7. 7.Which method checks whether a connection was made?Show answer

   is_connected() of the connection object.

8. 8.Name the two ways to put Python values into a query in the syllabus.Show answer

   The %s format specifier (with a tuple of values) and str.format().

Quick revision checklist

The night before the exam: can you do each of these without looking?

• ☐import mysql.connector; con = mysql.connector.connect(host=..., user=..., passwd=..., database=...).
• ☐cur = con.cursor(); cur.execute(sql) or cur.execute(sql, values).
• ☐fetchone(): next row as a tuple or None; fetchall(): list of tuples.
• ☐rowcount: rows fetched so far (SELECT) or rows affected (INSERT/UPDATE/DELETE).
• ☐con.commit() after any change; con.close() at the end.
• ☐%s placeholders take a tuple and need no quotes; (x,) for one value.
• ☐format() needs quotes around text values in the SQL string.
• ☐A one-column row is still a tuple: ('Asha',).

Interface of Python with an SQL Database: frequently asked questions