Python–SQL Connectivity

MySQL is a server. It is a program that keeps running in the background and listens on a port — 3306 unless someone changed it. Your data lives inside that server, not inside your .py file. So Python never opens the database files directly. It becomes a client and asks the server, exactly the way the mysql command-line tool does.

The library that speaks MySQL's protocol on Python's behalf is called a connector. The one CBSE names is mysql-connector-python. Install it once from the command prompt:

pip install mysql-connector-python

Note the trap straight away: the package you install is mysql-connector-python, but the name you import is mysql.connector. Two different strings for the same thing.

Every connectivity program, however large, is these five steps:

1. Import the connector.
2. connect() — open a line to the server. It returns a connection object.
3. cursor() — ask the connection for a cursor object. The cursor is what actually carries SQL to the server and holds whatever comes back.
4. execute() — send one SQL statement.
5. Fetch the rows (for SELECT) or commit() (for INSERT/UPDATE/DELETE), then close.

Why two objects instead of one? Because the connection is the line and the cursor is the worker. One connection can hand out several cursors, so one program can hold more than one result set open at a time. That split also explains which method sits where: execute() and the fetch methods belong to the cursor, while commit(), rollback() and close() finish work on the whole line and so belong to the connection.

Worked example. Every output printed in this chapter came from a real run against MySQL 8.0.41 with mysql-connector-python on Python 3.13. The demo server was on port 3307; on a normal machine leave port out and it defaults to 3306.

import mysql.connector

con = mysql.connector.connect(host="127.0.0.1", port=3307, user="root",
                              password="", database="cs12_python_sql_connectivity")
print("type(con)      :", type(con).__name__)
print("is_connected() :", con.is_connected())
print("server version :", con.server_info)

cur = con.cursor()
print("type(cur)      :", type(cur).__name__)

cur.execute("SELECT DATABASE()")
print("current db     :", cur.fetchone())

cur.execute("SELECT Name, City, Salary FROM EMPLOYEE WHERE Dept='IT'")
print("column names   :", [d[0] for d in cur.description])
for row in cur.fetchall():
    print(row)

cur.close()
con.close()
print("after close    :", con.is_connected())

Real output:

type(con)      : CMySQLConnection
is_connected() : True
server version : 8.0.41
type(cur)      : CMySQLCursor
current db     : ('cs12_python_sql_connectivity',)
column names   : ['Name', 'City', 'Salary']
('Diya Nair', 'Kochi', Decimal('72000.00'))
('Rohan Verma', 'Pune', Decimal('65000.00'))
after close    : False

Four things to read off that output. First, fetchone() returned a tuple, not a plain value — ('cs12_python_sql_connectivity',) — so to print just the value you index it, cur.fetchone()[0]. Second, a DECIMAL column comes back as a Decimal object, not a float, which is why the salaries print as Decimal('72000.00'). Third, is_connected() flips to False the instant you close.

Fourth, the class names begin with C. The pip wheel ships an optional C extension, and when it is present connect() gives you a CMySQLConnection and a CMySQLCursor. Pass use_pure=True and you get the pure-Python MySQLConnection and MySQLCursor instead — the pair that textbooks quote. Everything in this chapter behaves identically on both except one detail, flagged in Section 2.

The EMPLOYEE table used everywhere in this chapter was itself created from Python — no phpMyAdmin, no command line:

import mysql.connector

con = mysql.connector.connect(host="127.0.0.1", port=3307, user="root", password="")
print("connected:", con.is_connected())
cur = con.cursor()
cur.execute("CREATE DATABASE cs12_python_sql_connectivity")
cur.execute("USE cs12_python_sql_connectivity")
cur.execute("""
CREATE TABLE EMPLOYEE (
  EmpID   INT PRIMARY KEY,
  Name    VARCHAR(25),
  City    VARCHAR(20),
  Dept    VARCHAR(15),
  Salary  DECIMAL(10,2)
)
""")
rows = [
 (101, 'Aarav Sharma',   'Delhi',   'Sales',    58000),
 (102, 'Diya Nair',      'Kochi',   'IT',       72000),
 (103, 'Rohan Verma',    'Pune',    'IT',       65000),
 (104, 'Ishita Banerjee','Kolkata', 'HR',       49000),
 (105, 'Kabir Singh',    'Jaipur',  'Sales',    53000),
 (106, 'Meera Iyer',     'Chennai', 'Accounts', 61000),
]
cur.executemany("INSERT INTO EMPLOYEE VALUES (%s,%s,%s,%s,%s)", rows)
con.commit()
print("rows inserted:", cur.rowcount)
cur.execute("SELECT * FROM EMPLOYEE")
for r in cur.fetchall():
    print(r)
cur.close(); con.close()

Real output — these six rows are the seed table, and every later example says whether it starts from them:

connected: True
rows inserted: 6
(101, 'Aarav Sharma', 'Delhi', 'Sales', Decimal('58000.00'))
(102, 'Diya Nair', 'Kochi', 'IT', Decimal('72000.00'))
(103, 'Rohan Verma', 'Pune', 'IT', Decimal('65000.00'))
(104, 'Ishita Banerjee', 'Kolkata', 'HR', Decimal('49000.00'))
(105, 'Kabir Singh', 'Jaipur', 'Sales', Decimal('53000.00'))
(106, 'Meera Iyer', 'Chennai', 'Accounts', Decimal('61000.00'))

When connect() fails. Put it inside try ... except mysql.connector.Error so the user gets a sentence instead of a traceback. Two failures, both produced on purpose:

import mysql.connector

try:
    con = mysql.connector.connect(host="127.0.0.1", port=3307, user="root",
                                  password="wrongpassword",
                                  database="cs12_python_sql_connectivity")
except mysql.connector.Error as e:
    print("Error class :", type(e).__name__)
    print("errno       :", e.errno)
    print("msg         :", e.msg)

Real output:

Error class : ProgrammingError
errno       : 1045
msg         : Access denied for user 'root'@'localhost' (using password: YES)

Asking for a database that does not exist gives, on the same server, ProgrammingError with errno 1049 and the message Unknown database 'no_such_db_xyz'. Learn to recognise those two numbers. Between them, 1045 (wrong user or password) and 1049 (wrong database name) explain most of the "my program won't connect" problems in a school lab.

Wrong-object mistakes cost marks every single year. All four of these were triggered deliberately, and the messages below are exactly what Python printed on the default connection:

On a connection made with use_pure=True the same four mistakes give the same four errors with the un-prefixed class names — 'MySQLCursor' and 'MySQLConnection' — which is how most textbooks print them. The mistake is identical either way, so quote whichever your own machine shows.

Compress all four into one sentence and you will never mix them up: execute and fetch belong to the cursor; connect, commit, rollback and close belong to the connection.

A SELECT does not hand the rows to Python by itself. execute() only sends the query. The rows sit in a result set attached to the cursor, and the cursor keeps a position marker in it — picture a finger pointing at the next unread row. The three fetch methods differ only in how many rows they take and how far they push that finger.

The word remaining is where marks are lost. fetchall() does not rewind to the top. Whatever you already fetched is gone.

Worked example. The EMPLOYEE table holds the six seed rows created in Section 1. This one program demonstrates the whole section:

import mysql.connector

con = mysql.connector.connect(host="127.0.0.1", port=3307, user="root",
                              password="", database="cs12_python_sql_connectivity",
                              use_pure=True)
cur = con.cursor()
print("cursor class :", type(cur).__name__)

print("rowcount before execute() :", cur.rowcount)
cur.execute("SELECT EmpID, Name, Salary FROM EMPLOYEE ORDER BY EmpID")
print("rowcount after  execute() :", cur.rowcount)

print("fetchone() #1 ->", cur.fetchone())
print("fetchone() #2 ->", cur.fetchone())
print("rowcount now              :", cur.rowcount)

rest = cur.fetchall()
print("fetchall() returns", len(rest), "rows:")
for r in rest:
    print("   ", r)
print("rowcount after fetchall   :", cur.rowcount)
print("fetchone() past the end   :", cur.fetchone())
print("fetchall() past the end   :", cur.fetchall())
cur.close()
con.close()

Real output:

cursor class : MySQLCursor
rowcount before execute() : -1
rowcount after  execute() : -1
fetchone() #1 -> (101, 'Aarav Sharma', Decimal('58000.00'))
fetchone() #2 -> (102, 'Diya Nair', Decimal('72000.00'))
rowcount now              : 2
fetchall() returns 4 rows:
    (103, 'Rohan Verma', Decimal('65000.00'))
    (104, 'Ishita Banerjee', Decimal('49000.00'))
    (105, 'Kabir Singh', Decimal('53000.00'))
    (106, 'Meera Iyer', Decimal('61000.00'))
rowcount after fetchall   : 6
fetchone() past the end   : None
fetchall() past the end   : []

Walk through the four lessons buried in that output.

• fetchone() moves the marker. The two calls returned different rows — 101 and then 102. It is not a "get first row" function; it is a "get next row" function.
• fetchall() returns only what is left. The table has six rows but len(rest) is 4, because 101 and 102 had already been taken. This is the single most common trap in this chapter.
• rowcount starts at -1. Straight after execute() it was still -1. It became 2 after two fetchone() calls and 6 after fetchall(). So on a plain cursor, rowcount reports rows fetched so far, not rows matched. Print it before fetching and you get -1, not the answer you wanted.
• Running off the end is not an error. fetchone() gives None and fetchall() gives []. That is why the standard idiom is if rec: after a fetchone and if not rows: after a fetchall.

An honest note on -1, and why the program above sets use_pure=True. This driver ships two cursor implementations, as Section 1 explained. The pure-Python one prints -1 after execute() and is the one every textbook describes; the optional C-extension cursor printed 0 in the same place on the same machine. Both agree on the real point: the number is meaningless until you fetch. In the board exam, -1 is the expected answer. In your own programs, do not depend on either — count with len(cur.fetchall()).

The C-extension cursor has one further quirk worth knowing before your practical: after you mix fetchone() and fetchall() on the same result set, a further fetchone() raises InternalError: No result set available instead of returning None. On its own result set it returns None correctly, as the standard rule says. If you want to demonstrate the past-the-end rule, run it on a fresh query and nothing surprising happens.

If you genuinely need the count first, ask for a buffered cursor. It pulls the entire result set from the server immediately, so rowcount is correct right away:

cur = con.cursor(buffered=True)
cur.execute("SELECT * FROM EMPLOYEE WHERE Dept='Sales'")
print("buffered cursor, rowcount straight after execute():", cur.rowcount)
print("rows:", cur.fetchall())

Real output:

buffered cursor, rowcount straight after execute(): 2
rows: [(101, 'Aarav Sharma', 'Delhi', 'Sales', Decimal('58000.00')), (105, 'Kabir Singh', 'Jaipur', 'Sales', Decimal('53000.00'))]

fetchmany(n) sits between the two and is useful when a table is too large to print at once:

cur.execute("SELECT EmpID, Name FROM EMPLOYEE ORDER BY EmpID")
print(cur.fetchmany(3))
print(cur.fetchone())

Real output — note that the fetchone() afterwards gives row 104, not row 101, because fetchmany(3) already consumed the first three:

[(101, 'Aarav Sharma'), (102, 'Diya Nair'), (103, 'Rohan Verma')]
(104, 'Ishita Banerjee')

Ask for more rows than are left and you simply get fewer, with no error. On this six-row table three successive fetchmany(4) calls returned 4 rows, then 2 rows, then [].

Leave rows unread and the next execute() fails. On a plain (unbuffered) cursor the server is still holding rows for you, so a fresh query on the same cursor is refused:

cur.execute("SELECT * FROM EMPLOYEE")
cur.fetchone()
cur.execute("SELECT COUNT(*) FROM EMPLOYEE")

Real output — identical on both cursor implementations:

InternalError : Unread result found

The fix is to finish the result set — call cur.fetchall() and ignore it, or use buffered=True. If this error ever appears in your practical exam, it means you fetched one row and moved on.

Column headings. After any SELECT, cur.description holds one entry per column and item 0 of each is the column name, so you can print headings without hard-coding them. On the Section 1 query it gave:

column names   : ['Name', 'City', 'Salary']

Before any execute(), and after a statement that returns no rows such as an UPDATE, cur.description is None — both checked on a live cursor.

INSERT, UPDATE and DELETE go through exactly the same cur.execute() as a SELECT. Two things change. There are no rows to fetch, and rowcount is immediately useful — for a write it is the number of rows the statement affected, available at once, with no fetching involved.

Worked example. Starting from the six seed rows of Section 1:

cur.execute("INSERT INTO EMPLOYEE (EmpID,Name,City,Dept,Salary) VALUES (%s,%s,%s,%s,%s)",
            (107, "Ananya Rao", "Hyderabad", "IT", 68000))
print("INSERT rowcount :", cur.rowcount)
con.commit()

cur.execute("UPDATE EMPLOYEE SET Salary = Salary + 5000 WHERE Dept=%s", ("Sales",))
print("UPDATE rowcount :", cur.rowcount)
con.commit()

cur.execute("UPDATE EMPLOYEE SET Salary = 99999 WHERE City=%s", ("Shimla",))
print("UPDATE (no match) rowcount :", cur.rowcount)
con.commit()

cur.execute("DELETE FROM EMPLOYEE WHERE Salary < %s", (50000,))
print("DELETE rowcount :", cur.rowcount)
con.commit()

cur.execute("SELECT * FROM EMPLOYEE ORDER BY EmpID")
for r in cur.fetchall():
    print("   ", r)

Real output:

INSERT rowcount : 1
UPDATE rowcount : 2
UPDATE (no match) rowcount : 0
DELETE rowcount : 1
    (101, 'Aarav Sharma', 'Delhi', 'Sales', Decimal('63000.00'))
    (102, 'Diya Nair', 'Kochi', 'IT', Decimal('72000.00'))
    (103, 'Rohan Verma', 'Pune', 'IT', Decimal('65000.00'))
    (105, 'Kabir Singh', 'Jaipur', 'Sales', Decimal('58000.00'))
    (106, 'Meera Iyer', 'Chennai', 'Accounts', Decimal('61000.00'))
    (107, 'Ananya Rao', 'Hyderabad', 'IT', Decimal('68000.00'))

Read the 0 carefully. The Shimla update did not fail and did not raise anything — it simply matched nothing. That is why a real application must print cur.rowcount after every update and delete. Telling a user "record updated" when rowcount was 0 is a lie your program told on your behalf.

One refinement, checked on the same table: for an UPDATE this driver reports rows changed, not rows matched. Running UPDATE EMPLOYEE SET Salary=Salary WHERE Dept='Sales' matches two rows but alters no value, and rowcount came back 0. So rowcount 0 after an update means "nothing actually changed", which may be because nothing matched or because the new value equalled the old one.

To insert many rows in one call, use executemany() with a list of tuples:

cur.executemany("INSERT INTO EMPLOYEE VALUES (%s,%s,%s,%s,%s)",
                [(108, "Vikram Joshi", "Nagpur",    "HR",       47000),
                 (109, "Sneha Pillai", "Bengaluru", "Accounts", 59000)])
print("executemany rowcount :", cur.rowcount)
con.commit()
cur.execute("SELECT COUNT(*) FROM EMPLOYEE")
print("total rows now       :", cur.fetchone()[0])

executemany rowcount : 2
total rows now       : 8

Now the part that decides marks: commit(). Here is the experiment, starting again from the six seed rows, run twice, changing exactly one line.

import mysql.connector

CFG = dict(host="127.0.0.1", port=3307, user="root", password="",
           database="cs12_python_sql_connectivity", use_pure=True)

def count():
    c = mysql.connector.connect(**CFG)
    k = c.cursor()
    k.execute("SELECT COUNT(*) FROM EMPLOYEE")
    n = k.fetchone()[0]
    k.close(); c.close()
    return n

def row(i):
    c = mysql.connector.connect(**CFG)
    k = c.cursor()
    k.execute("SELECT * FROM EMPLOYEE WHERE EmpID=%s", (i,))
    v = k.fetchall()
    k.close(); c.close()
    return v

print("rows at start :", count())

# RUN 1 : no commit()
con = mysql.connector.connect(**CFG)
cur = con.cursor()
cur.execute("INSERT INTO EMPLOYEE VALUES (107,'Ananya Rao','Hyderabad','IT',68000)")
print("RUN 1 (no commit) -- rowcount after INSERT :", cur.rowcount)
cur.execute("SELECT * FROM EMPLOYEE WHERE EmpID=107")
print("RUN 1 -- same connection sees it :", cur.fetchall())
cur.close()
con.close()                      # closed WITHOUT commit()
print("RUN 1 -- rows after reconnecting :", count())
print("RUN 1 -- row 107 after reconnect :", row(107))

# RUN 2 : identical, plus one line
con = mysql.connector.connect(**CFG)
cur = con.cursor()
cur.execute("INSERT INTO EMPLOYEE VALUES (107,'Ananya Rao','Hyderabad','IT',68000)")
print("RUN 2 (with commit) -- rowcount :", cur.rowcount)
con.commit()
cur.close()
con.close()
print("RUN 2 -- rows after reconnecting :", count())
print("RUN 2 -- row 107 after reconnect :", row(107))

Real output:

rows at start : 6
RUN 1 (no commit) -- rowcount after INSERT : 1
RUN 1 -- same connection sees it : [(107, 'Ananya Rao', 'Hyderabad', 'IT', Decimal('68000.00'))]
RUN 1 -- rows after reconnecting : 6
RUN 1 -- row 107 after reconnect : []
RUN 2 (with commit) -- rowcount : 1
RUN 2 -- rows after reconnecting : 7
RUN 2 -- row 107 after reconnect : [(107, 'Ananya Rao', 'Hyderabad', 'IT', Decimal('68000.00'))]

Look at Run 1 line by line, because this is exactly how students get fooled in the lab. rowcount said 1. A SELECT on the same connection found the row. Every signal said success. Then the connection closed, a new one opened, and the count was back to 6 and row 107 returned []. The insert was thrown away.

Why? Because the table is InnoDB, a transactional engine, and the session the connector opened has autocommit switched off. A separate check confirmed it precisely — the MySQL server's own global setting is 1, but the session is 0:

con = mysql.connector.connect(**CFG)
cur = con.cursor()
cur.execute("SHOW TABLE STATUS LIKE 'EMPLOYEE'")
print("engine :", cur.fetchone()[1])
cur.execute("SELECT @@GLOBAL.autocommit, @@SESSION.autocommit")
print("global / session autocommit on server :", cur.fetchone())
print("con.autocommit (connector property)   :", con.autocommit)

engine : InnoDB
global / session autocommit on server : (1, 0)
con.autocommit (connector property)   : False

So the driver deliberately switches autocommit off for you. Every DML statement you run starts a transaction, and a transaction that is not committed when the connection ends is rolled back. That is the safe default — it means a program that crashes halfway through updating ten records leaves the table untouched rather than half-changed. But it also means con.commit() is not optional decoration. It is the line that saves your work.

One-line rule for the exam: every INSERT, UPDATE or DELETE must be followed by con.commit(); SELECT never needs it.

rollback() is the same power used on purpose. Wrap several writes in a try, and if any one of them fails, undo all of them. Again from the six seed rows:

con = mysql.connector.connect(**CFG)
cur = con.cursor()
print("rows before :", count())
try:
    cur.execute("INSERT INTO EMPLOYEE VALUES (110,'Farhan Qureshi','Lucknow','IT',57000)")
    print("first INSERT rowcount :", cur.rowcount)
    cur.execute("INSERT INTO EMPLOYEE VALUES (101,'Duplicate Id','Delhi','HR',40000)")
    con.commit()
    print("both committed")
except mysql.connector.Error as e:
    con.rollback()
    print("error errno", e.errno, ":", e.msg)
    print("rolled back")
con.close()
print("rows after  :", count())
print("row 110     :", row(110))

Real output:

rows before : 6
first INSERT rowcount : 1
error errno 1062 : Duplicate entry '101' for key 'employee.PRIMARY'
rolled back
rows after  : 6
row 110     : []

The first insert genuinely succeeded, but because the second violated the primary key, rollback() removed both. The table is left exactly as it was. Errno 1062 is the duplicate-primary-key error and is worth memorising alongside 1045 and 1049. Its exception class is IntegrityError, which is a subclass of mysql.connector.Error, so the single except above catches it.

Turning autocommit on is possible but you rarely want it, because you lose rollback:

con = mysql.connector.connect(autocommit=True, **CFG)
cur = con.cursor()
cur.execute("SELECT @@SESSION.autocommit")
print("autocommit=True -> session autocommit :", cur.fetchone()[0])
cur.execute("INSERT INTO EMPLOYEE VALUES (111,'Nikhil Rane','Nashik','HR',45000)")
cur.close(); con.close()          # again NO commit() call
print("row 111 after reconnect               :", row(111))

autocommit=True -> session autocommit : 1
row 111 after reconnect               : [(111, 'Nikhil Rane', 'Nashik', 'HR', Decimal('45000.00'))]

Same code, no commit(), and this time the row survived — proof that the earlier disappearance was the transaction, not a bug. Still, write your board answers the normal way, with autocommit left alone and an explicit con.commit().

The syllabus names two ways of putting a Python value into a query — the %s format specifier, and format(). Both are examinable and both earn marks. They are not equivalent, though, and this section shows exactly what separates them. Every run below starts from the six seed rows plus (107, 'Ananya Rao', 'Hyderabad', 'IT', 68000).

Way one: format(). Here you build the finished SQL as an ordinary Python string, and execute() receives one completed statement:

dept = "IT"
sal = 60000
q = "SELECT Name, Salary FROM EMPLOYEE WHERE Dept='{}' AND Salary>{}".format(dept, sal)
print("query :", q)
cur.execute(q)
print("rows  :", cur.fetchall())

Real output:

query : SELECT Name, Salary FROM EMPLOYEE WHERE Dept='IT' AND Salary>60000
rows  : [('Diya Nair', Decimal('72000.00')), ('Rohan Verma', Decimal('65000.00')), ('Ananya Rao', Decimal('68000.00'))]

Look hard at the quotes, because this is where the marks go. With format() you are writing the SQL, so you must type the quotes around a text value — '{}' for Dept — and leave them off a number — {} for Salary. Forget the quotes on the text one and the server reads Dept=IT, treats IT as a column name, and raises error 1054.

Way two: %s with a tuple. You write %s wherever a value goes and pass the values as a second argument to execute(). The driver, not you, inserts them:

dept = "IT"
limit = 60000
cur.execute("SELECT Name, City, Salary FROM EMPLOYEE WHERE Dept=%s AND Salary>%s",
            (dept, limit))
for r in cur.fetchall():
    print("   ", r)

Real output:

('Diya Nair', 'Kochi', Decimal('72000.00'))
('Rohan Verma', 'Pune', Decimal('65000.00'))
('Ananya Rao', 'Hyderabad', Decimal('68000.00'))

Now the quote rule is the exact opposite, and mixing the two rules up is the commonest error in this chapter. With %s you never type quotes, because the driver adds its own. Note too that %s is used for every data type. Not %d for the salary, not %f — this is not Python's % operator, and the driver works out the correct SQL literal from the Python type. A number stays unquoted, a string gets quoted, and a datetime.date is formatted as a DATE (checked: it also comes back as a datetime.date).

Three mistakes worth failing on purpose. All three messages below are real:

cur.execute("SELECT Name FROM EMPLOYEE WHERE City=%s", ("Kochi"))

ProgrammingError : Could not process parameters: str(Kochi), it must be of type list, tuple or dict

There is no comma, so ("Kochi") is just a string in brackets, not a tuple. A one-value tuple needs the trailing comma: ("Kochi",). That single comma is a classic one-mark question.

cur.execute("SELECT Name FROM EMPLOYEE WHERE City='%s'", ("Kochi",))

ProgrammingError : 1064 (42000): You have an error in your SQL syntax; ... near 'Kochi''' at line 1

Here the placeholder was quoted by hand. The driver adds its own quotes, so the server received City=''Kochi''. Rule: never put quotes around %s. The driver's job is to quote.

cur.execute("SELECT Name FROM EMPLOYEE WHERE City=%s" % "Kochi")

ProgrammingError : 1054 (42S22): Unknown column 'Kochi' in 'where clause'

That third one is Python's own % operator, not the driver's placeholder. It pasted Kochi in with no quotes at all, so MySQL looked for a column of that name.

Where format() actually breaks: the apostrophe. Consider an Indian name that contains one:

cur.execute("INSERT INTO EMPLOYEE VALUES (109,%s,%s,%s,%s)",
            ("Rani D'Souza", "Panaji", "Sales", 51000))
con.commit()
cur.execute("SELECT * FROM EMPLOYEE WHERE EmpID=109")
print("   apostrophe inserted safely:", cur.fetchone())

name = "Rani D'Souza"
bad = "SELECT * FROM EMPLOYEE WHERE Name='{}'".format(name)
print("   built with format():", bad)
cur.execute(bad)

Real output:

   apostrophe inserted safely: (109, "Rani D'Souza", 'Panaji', 'Sales', Decimal('51000.00'))
   built with format(): SELECT * FROM EMPLOYEE WHERE Name='Rani D'Souza'
   -> ProgrammingError : 1064 (42000): You have an error in your SQL syntax; ... near 'Souza'' at line 1

The same value through %s works:

cur.execute("SELECT * FROM EMPLOYEE WHERE Name=%s", (name,))
print(cur.fetchall())

[(109, "Rani D'Souza", 'Panaji', 'Sales', Decimal('51000.00'))]

The apostrophe closed the string early and broke the query built by format(). The %s version escaped it and worked. Hold on to that observation, because it is the important idea in miniature: a character typed by a user changed the structure of the SQL. With format() the values and the SQL keywords are all one piece of text by the time the server sees them; with %s the driver keeps them apart.

Why that matters beyond apostrophes. Here is a login check written both ways against a two-row LOGIN table (aarav/clerk and principal/admin):

def login_unsafe(u, p):
    q = "SELECT UserID, Role FROM LOGIN WHERE UserID='" + u + "' AND Pwd='" + p + "'"
    print("   SQL sent :", q)
    cur.execute(q)
    return cur.fetchall()

def login_safe(u, p):
    cur.execute("SELECT UserID, Role FROM LOGIN WHERE UserID=%s AND Pwd=%s", (u, p))
    return cur.fetchall()

Real output for four attempts:

A) honest login, unsafe version
   SQL sent : SELECT UserID, Role FROM LOGIN WHERE UserID='aarav' AND Pwd='delhi@123'
   result   : [('aarav', 'clerk')]

B) wrong password, unsafe version
   SQL sent : SELECT UserID, Role FROM LOGIN WHERE UserID='aarav' AND Pwd='guess'
   result   : []

C) attacker types password: x' OR '1'='1
   SQL sent : SELECT UserID, Role FROM LOGIN WHERE UserID='aarav' AND Pwd='x' OR '1'='1'
   result   : [('aarav', 'clerk'), ('principal', 'admin')]

D) same attack against the %s version
   result   : []
   rowcount : 0

Read line C. The visitor typed x' OR '1'='1 into a password box. Their apostrophe closed the Pwd literal, their OR '1'='1' became part of the WHERE clause, and the condition is true for every row. The function returned both accounts, including the admin. No password was ever guessed. The quotes happen to balance, which is exactly why no error was raised to warn anybody.

Line D is the same input through execute(..., (u, p)). The driver treated the whole thing as one string value to compare against the Pwd column, found no user whose password is literally x' OR '1'='1, and returned []. Same input, same table, different result — because the value never reached the SQL parser.

The same trick on a DELETE destroys data. This was run on a fresh copy of the two-row table:

typed = "x' OR '1'='1"
q = "DELETE FROM LOGIN WHERE UserID='" + typed + "'"
print("SQL sent     :", q)
cur.execute(q)
print("rowcount     :", cur.rowcount)
con.commit()

Real output:

LOGIN before : [('aarav', 'clerk'), ('principal', 'admin')]
SQL sent     : DELETE FROM LOGIN WHERE UserID='x' OR '1'='1'
rowcount     : 2
LOGIN after  : []

One row was meant to be deleted at most; both were. Run the identical string through a placeholder instead — cur.execute("DELETE FROM LOGIN WHERE UserID=%s", (typed,)) — and rowcount is 0 and both rows survive. Verified both ways.

So state the principle plainly, and write it this way in the exam: a query's structure must come from the programmer and its values must come from the parameters. Use format() when the question asks for it or when the values are your own constants; use %s whenever the value came from input(), a file or a form.

Two practical footnotes. Named placeholders are available if a query has many parameters and you want the code readable — pass a dictionary instead of a tuple:

cur.execute("SELECT Name, Salary FROM EMPLOYEE WHERE Dept=%(d)s AND Salary>=%(s)s",
            {"d": "IT", "s": 60000})
print(cur.fetchall())

[('Diya Nair', Decimal('72000.00')), ('Rohan Verma', Decimal('65000.00')), ('Ananya Rao', Decimal('68000.00'))]

And placeholders substitute values only. A table name or a column name cannot be a %s parameter — cur.execute("SELECT * FROM %s", (tbl,)) raises ProgrammingError 1064 ... near ''EMPLOYEE'', because the driver quoted the table name as though it were text. If your program must choose a column at run time, pick it from a fixed list you wrote yourself — never from whatever the user typed.

Everything so far now becomes one program. The syllabus calls this a database connectivity application, and the board asks for it as a 4- or 5-mark question. The shape never changes: open one connection, loop a menu, call one function per operation, close on exit.

Design rules that earn the marks.

• Open the connection once, outside the loop. Connecting inside every menu choice is slow and pointless — the connection is the expensive part.
• One function per operation. An examiner reading add_student(), view_students(), update_marks() can award marks per function. A single 60-line block is hard to mark and harder to debug.
• Every value from the user goes in as a parameter, never pasted into the query text — for the reasons demonstrated in Section 4.
• commit() after every write, and never after a plain SELECT.
• Report cur.rowcount after update and delete, so the user learns when nothing matched.
• Convert input(). input() always returns a string, so roll numbers need int() and marks need float().

The table. Created once before the program runs:

CREATE TABLE STUDENT (
  RollNo INT PRIMARY KEY,
  Name   VARCHAR(25),
  Class  VARCHAR(6),
  Marks  DECIMAL(5,2)
);

The complete program — this is the exact file that was run:

import mysql.connector

def get_connection():
    return mysql.connector.connect(host="127.0.0.1", port=3307, user="root",
                                   password="", database="cs12_python_sql_connectivity")

def add_student(con):
    cur = con.cursor()
    rno = int(input("Roll no  : "))
    name = input("Name     : ")
    cls = input("Class    : ")
    marks = float(input("Marks    : "))
    cur.execute("INSERT INTO STUDENT VALUES (%s,%s,%s,%s)", (rno, name, cls, marks))
    con.commit()
    print("Added.", cur.rowcount, "record inserted.")
    cur.close()

def view_students(con):
    cur = con.cursor()
    cur.execute("SELECT * FROM STUDENT ORDER BY RollNo")
    rows = cur.fetchall()
    if not rows:
        print("No records.")
    else:
        print("RollNo  Name            Class   Marks")
        for r in rows:
            print(f"{r[0]:<8}{r[1]:<16}{r[2]:<8}{r[3]}")
        print("Total records:", len(rows))
    cur.close()

def search_student(con):
    cur = con.cursor()
    rno = int(input("Roll no to search: "))
    cur.execute("SELECT * FROM STUDENT WHERE RollNo=%s", (rno,))
    rec = cur.fetchone()
    print("Found:", rec) if rec else print("No such roll no.")
    cur.close()

def update_marks(con):
    cur = con.cursor()
    rno = int(input("Roll no  : "))
    marks = float(input("New marks: "))
    cur.execute("UPDATE STUDENT SET Marks=%s WHERE RollNo=%s", (marks, rno))
    con.commit()
    print("Rows updated:", cur.rowcount)
    cur.close()

def delete_student(con):
    cur = con.cursor()
    rno = int(input("Roll no  : "))
    cur.execute("DELETE FROM STUDENT WHERE RollNo=%s", (rno,))
    con.commit()
    print("Rows deleted:", cur.rowcount)
    cur.close()

def menu():
    con = get_connection()
    while True:
        print("\n--- STUDENT RECORD SYSTEM ---")
        print("1. Add   2. View   3. Search   4. Update marks   5. Delete   6. Exit")
        ch = input("Choice: ")
        if ch == "1":   add_student(con)
        elif ch == "2": view_students(con)
        elif ch == "3": search_student(con)
        elif ch == "4": update_marks(con)
        elif ch == "5": delete_student(con)
        elif ch == "6":
            con.close()
            print("Connection closed. Bye.")
            break
        else:
            print("Wrong choice.")

menu()

A real session. The run below was driven from a script so it can be reproduced exactly, and the typed values are echoed after each prompt just as they would appear if you typed them:

--- STUDENT RECORD SYSTEM ---
1. Add   2. View   3. Search   4. Update marks   5. Delete   6. Exit
Choice: 1
Roll no  : 1
Name     : Aarav Sharma
Class    : 12A
Marks    : 88.5
Added. 1 record inserted.

--- STUDENT RECORD SYSTEM ---
1. Add   2. View   3. Search   4. Update marks   5. Delete   6. Exit
Choice: 1
Roll no  : 2
Name     : Diya Nair
Class    : 12A
Marks    : 92
Added. 1 record inserted.

--- STUDENT RECORD SYSTEM ---
1. Add   2. View   3. Search   4. Update marks   5. Delete   6. Exit
Choice: 1
Roll no  : 3
Name     : Kabir Singh
Class    : 12B
Marks    : 67.25
Added. 1 record inserted.

--- STUDENT RECORD SYSTEM ---
1. Add   2. View   3. Search   4. Update marks   5. Delete   6. Exit
Choice: 2
RollNo  Name            Class   Marks
1       Aarav Sharma    12A     88.50
2       Diya Nair       12A     92.00
3       Kabir Singh     12B     67.25
Total records: 3

--- STUDENT RECORD SYSTEM ---
1. Add   2. View   3. Search   4. Update marks   5. Delete   6. Exit
Choice: 3
Roll no to search: 2
Found: (2, 'Diya Nair', '12A', Decimal('92.00'))

--- STUDENT RECORD SYSTEM ---
1. Add   2. View   3. Search   4. Update marks   5. Delete   6. Exit
Choice: 4
Roll no  : 3
New marks: 79
Rows updated: 1

--- STUDENT RECORD SYSTEM ---
1. Add   2. View   3. Search   4. Update marks   5. Delete   6. Exit
Choice: 2
RollNo  Name            Class   Marks
1       Aarav Sharma    12A     88.50
2       Diya Nair       12A     92.00
3       Kabir Singh     12B     79.00
Total records: 3

--- STUDENT RECORD SYSTEM ---
1. Add   2. View   3. Search   4. Update marks   5. Delete   6. Exit
Choice: 5
Roll no  : 1
Rows deleted: 1

--- STUDENT RECORD SYSTEM ---
1. Add   2. View   3. Search   4. Update marks   5. Delete   6. Exit
Choice: 2
RollNo  Name            Class   Marks
2       Diya Nair       12A     92.00
3       Kabir Singh     12B     79.00
Total records: 2

--- STUDENT RECORD SYSTEM ---
1. Add   2. View   3. Search   4. Update marks   5. Delete   6. Exit
Choice: 6
Connection closed. Bye.

Every idea in this chapter is visible in that transcript. Notice that the marks typed as 92 and 79 come back as 92.00 and 79.00, because float() handed a number to a DECIMAL(5,2) column and MySQL stored it to two places. Option 3 used fetchone() and the record printed as a tuple with a Decimal in it. Option 2 used fetchall() and len(rows) for the count rather than trusting rowcount. Options 4 and 5 printed rowcount, so a wrong roll number would have shown Rows updated: 0 instead of a false success message. And behind every one of the writes there is a con.commit() — without which the whole session would have vanished at option 6.

Two upgrades worth knowing. If the table has an AUTO_INCREMENT primary key, cur.lastrowid gives the number the server just generated, which is how a billing screen shows the bill number it created. On a small FEES table this printed lastrowid : 1 and then lastrowid : 2 for consecutive inserts. And in a real product every one of these functions would sit inside try ... except mysql.connector.Error with a con.rollback(), so that a duplicate roll number produces a polite message instead of a traceback.