Class 12 Python-SQL Connectivity: Complete Concepts and Practice

Big picture

SQL is great for storing and querying data, and Python is great for building programs. Interface (connectivity) programs combine them: a Python program sends SQL commands to a MySQL database and works with the results, for example a school app that adds students and shows results.

The board paper usually asks you to complete or write such a program, so you need the exact sequence of steps and method names. Remember the golden rule: changes (INSERT, UPDATE, DELETE) are saved only after `commit()`.

Learning goals

Install and import the mysql.connector module.
Create a connection and a cursor.
Execute SQL queries with execute().
Fetch results with fetchone(), fetchmany() and fetchall(), and use rowcount.
Save changes with commit().
Write parameterised queries using %s placeholders and format().
Write complete programs for insert, search, update and delete.

Core ideas

1. Steps in a Python–MySQL program

Import the connector: import mysql.connector (install it once with pip install mysql-connector-python).
Open a connection with mysql.connector.connect(), giving host, user, password and database.
Create a cursor object with connection.cursor(). The cursor executes queries and holds results.
Execute a query with cursor.execute(query).
Fetch results (for SELECT) or commit changes (for INSERT, UPDATE, DELETE).
Close the cursor and connection.

import mysql.connector

con = mysql.connector.connect(host='localhost', user='root',
                              passwd='school123', database='school')
if con.is_connected():
    print('Connected')
cur = con.cursor()

is_connected() returns True if the connection succeeded.

2. Fetching results

After executing a SELECT query, the result is held by the cursor (the result set).

Method / attribute	Returns
`fetchone()`	The next row as a tuple, or `None` if no rows are left
`fetchmany(n)`	The next n rows as a list of tuples
`fetchall()`	All remaining rows as a list of tuples (empty list if none)
`rowcount`	Number of rows fetched so far (for SELECT), or rows affected (for INSERT, UPDATE, DELETE)

Each fetch continues from where the previous one stopped.

cur.execute('SELECT * FROM student')   # suppose the table has 10 rows
r1 = cur.fetchone()       # 1st row (a tuple)
r2 = cur.fetchmany(3)     # rows 2 to 4 (a list of 3 tuples)
rest = cur.fetchall()     # rows 5 to 10 (a list of 6 tuples)
print(cur.rowcount)       # 10, the total rows fetched so far

Displaying all records:

cur.execute('SELECT roll, name, marks FROM student')
for row in cur.fetchall():
    print(row[0], row[1], row[2])

3. Saving changes with commit()

INSERT, UPDATE and DELETE change the database only in a temporary state until `con.commit()` is called. Without it, the changes are lost when the connection closes.

cur.execute("INSERT INTO student VALUES (11, 'Riya', 87)")
con.commit()
print(cur.rowcount, 'record inserted')   # 1 record inserted

4. Parameterised queries

Values typed by the user should be inserted into queries safely.

Using `%s` placeholders (recommended; the values are passed as a tuple):

roll = int(input('Roll: '))
name = input('Name: ')
marks = float(input('Marks: '))
query = 'INSERT INTO student VALUES (%s, %s, %s)'
cur.execute(query, (roll, name, marks))
con.commit()

Using `format()` (string formatting; quotes must be added around text values):

query = "INSERT INTO student VALUES ({}, '{}', {})".format(roll, name, marks)
cur.execute(query)
con.commit()

Using f-strings is similar: f"SELECT * FROM student WHERE marks > {m}".

Placeholders with %s are safer because the connector handles quoting and protects against malicious input (SQL injection).

5. Complete programs

Search by roll number:

r = int(input('Roll to search: '))
cur.execute('SELECT * FROM student WHERE roll = %s', (r,))
row = cur.fetchone()
if row:
    print(row)
else:
    print('Not found')

Note the comma in (r,): a single value must be passed as a one-element tuple.

Update marks:

r = int(input('Roll: '))
m = float(input('New marks: '))
cur.execute('UPDATE student SET marks = %s WHERE roll = %s', (m, r))
con.commit()
if cur.rowcount == 0:
    print('No such student')
else:
    print('Updated')

Delete a record:

cur.execute('DELETE FROM student WHERE roll = %s', (r,))
con.commit()
print(cur.rowcount, 'record(s) deleted')

Create a table from Python:

cur.execute('''CREATE TABLE IF NOT EXISTS student (
                 roll INT PRIMARY KEY,
                 name VARCHAR(20),
                 marks FLOAT)''')

DDL commands like CREATE take effect immediately; commit is needed for data changes.

Closing:

cur.close()
con.close()

6. Handling errors

Wrap database code in try–except to handle errors such as a wrong password, a missing table or a duplicate primary key:

try:
    cur.execute('INSERT INTO student VALUES (%s, %s, %s)', (11, 'Riya', 87))
    con.commit()
except mysql.connector.Error as e:
    print('Database error:', e)
finally:
    con.close()

A reliable routine for connectivity programs

import mysql.connector → connect() → cursor().
Build the query, using %s placeholders for user values.
execute(query, values).
For SELECT, use fetchone(), fetchmany() or fetchall(); for changes, call commit().
Use rowcount to report how many rows were affected or fetched.
Close the cursor and connection.

Connections that matter

Relational databases and SQL provides the queries used here.
Exception handling catches database errors.
File handling is the other way of storing data permanently; databases are better for large, shared, structured data.

Common mistakes

Forgetting `commit()` after INSERT, UPDATE or DELETE, so changes are lost.
Passing a single value as `(r)` instead of `(r,)`.
Forgetting quotes around text values when using format().
Calling `fetchall()` after INSERT. Fetch methods are for SELECT results.
Expecting `fetchone()` to return a list. It returns a tuple (or None).

Revision checklist

I can write the connection and cursor statements.
I can execute queries and fetch results with each fetch method.
I can explain rowcount and commit().
I can write parameterised queries using %s and format().
I can write complete insert, search, update and delete programs.