Python + MySQL connectivity

Part of the free LogicWiz Python course, Pack A: CBSE Class 12 Board Pack. The examples and practice run in the app.

A 4-mark board question: "Change the quantity of item 208 to 91 in the product_inventory table." The code is only 8 lines, and the mark students drop most often is the one for commit().

You'll learn to

  • Connect to a database with mysql.connector and get a cursor
  • Run INSERT, UPDATE and DELETE safely with %s placeholders, then commit
  • Read rows with fetchone() and fetchall(), and use rowcount

Learn

Six steps, every time

  1. import mysql.connector
  2. con = mysql.connector.connect(host=..., user=..., password=..., database=...)
  3. cur = con.cursor()
  4. cur.execute(query) or cur.execute(query, values)
  5. Changed data (INSERT/UPDATE/DELETE)? → con.commit(). Reading (SELECT)? → cur.fetchone() / cur.fetchall()
  6. con.close()

The connection is the line to the database; the cursor is the pen that runs queries and holds the results.

Setting up real MySQL (school lab or your laptop). Install MySQL Server, then run pip install mysql-connector-python. In the MySQL prompt: CREATE DATABASE school;. In Python connect with your own details: mysql.connector.connect(host="localhost", user="root", password="<your MySQL password>", database="school"). Check with con.is_connected().

Here in the browser there is no MySQL server, so mysql.connector is a LogicWiz stand-in that keeps the data in SQLite. The code is identical to real MySQL: same connect, %s, commit, fetchall and rowcount. The host, user and password are accepted but not checked.

Create, insert with %s, commit

import mysql.connector
con = mysql.connector.connect(host="localhost", user="root", password="tiger", database="school")
cur = con.cursor()
cur.execute("DROP TABLE IF EXISTS student")
cur.execute("CREATE TABLE student (roll INT PRIMARY KEY, name VARCHAR(20), marks INT)")

data = [(101, "Aarav", 91), (102, "Meher", 78), (103, "Kabir", 85)]
for rec in data:
    cur.execute("INSERT INTO student VALUES (%s, %s, %s)", rec)
con.commit()                       # make the inserts permanent
print("Inserted 3 rows")

cur.execute("SELECT * FROM student")
rows = cur.fetchall()
print(rows)
print("rowcount:", cur.rowcount)
con.close()

%s is a placeholder: the connector puts each value in safely, with quotes for strings. Write %s even for numbers, and never put quotes around it.

fetchone, fetchall and rowcount

import mysql.connector
setup = mysql.connector.connect(host="localhost", user="root", password="tiger", database="school")
k = setup.cursor()
k.execute("DROP TABLE IF EXISTS student")
k.execute("CREATE TABLE student (roll INT PRIMARY KEY, name VARCHAR(20), marks INT)")
k.executemany("INSERT INTO student VALUES (%s, %s, %s)", [(101, "Aarav", 91), (102, "Meher", 78), (103, "Kabir", 85)])
setup.commit()
setup.close()

con = mysql.connector.connect(host="localhost", user="root", password="tiger", database="school")
cur = con.cursor()
cur.execute("SELECT name, marks FROM student WHERE marks > %s ORDER BY marks DESC", (80,))
first = cur.fetchone()
print(first, "| rowcount:", cur.rowcount)
rest = cur.fetchall()
print(rest, "| rowcount:", cur.rowcount)
print(cur.fetchone())
con.close()

fetchone() gives the next row as a tuple, or None when no rows are left. fetchall() gives a list of the rows not yet fetched. For a SELECT, rowcount is the number of rows fetched so far.

Two mistakes that cost marks.

  1. No con.commit() after INSERT/UPDATE/DELETE: the change looks fine inside your program, but it is thrown away when the connection closes.
  2. (80) is not a tuple. The values must be a tuple or list, so a single value needs a comma: (80,).

The 4-mark program: update by input

import mysql.connector
con = mysql.connector.connect(host="localhost", user="admin_user", password="shop2026", database="kirana")
cur = con.cursor()
cur.execute("DROP TABLE IF EXISTS stock")
cur.execute("CREATE TABLE stock (item_code INT PRIMARY KEY, item VARCHAR(20), qty INT, price INT)")
cur.executemany("INSERT INTO stock VALUES (%s, %s, %s, %s)", [(207, "Atta 5kg", 12, 240), (208, "Toor dal", 40, 160)])
con.commit()

# ---- the board answer starts here ----
code = int(input("Item code: "))
qty = int(input("New quantity: "))
cur.execute("UPDATE stock SET qty = %s WHERE item_code = %s", (qty, code))
con.commit()
if cur.rowcount == 0:
    print("No such item")
else:
    print(cur.rowcount, "record updated")
cur.execute("SELECT * FROM stock WHERE item_code = %s", (code,))
print(cur.fetchone())
con.close()

The format() way, and why %s is better

import mysql.connector
setup = mysql.connector.connect(host="localhost", user="root", password="tiger", database="school")
k = setup.cursor()
k.execute("DROP TABLE IF EXISTS student")
k.execute("CREATE TABLE student (roll INT PRIMARY KEY, name VARCHAR(20), marks INT)")
k.executemany("INSERT INTO student VALUES (%s, %s, %s)", [(101, "Aarav", 91), (102, "Meher", 78), (103, "Kabir", 85)])
setup.commit()
setup.close()

con = mysql.connector.connect(host="localhost", user="root", password="tiger", database="school")
cur = con.cursor()
cutoff = 85
q = "SELECT name FROM student WHERE marks >= {}".format(cutoff)
cur.execute(q)
print(cur.fetchall())

name = "Meher"
q = "SELECT marks FROM student WHERE name = '{}'".format(name)   # quotes needed
cur.execute(q)
print(cur.fetchone())
con.close()

The syllabus allows format(), but you must add quotes around strings yourself, and if a user types x' OR '1'='1 they can change your query (SQL injection). With %s the connector handles quoting, so prefer %s.

4-mark answer checklist: import mysql.connector (as mycon is fine) · connect() with host, user, password, database from the question · cursor() · the correct SQL in execute() · commit() for changes · fetchall() + a loop for display · close(). Copy the user name, password and table name exactly as the question gives them.

In one line

connect → cursor → execute (with %s and a tuple) → commit() for changes or fetchone()/fetchall() for reading → close; rowcount counts affected or fetched rows.