Interface of Python with an SQL Database — Notes

A database stores data safely and lets many users query it; a Python program adds menus, checks and calculations. The program is a client; the MySQL server holds the database; the driver mysql.connector carries SQL to the server and rows back.

Install once: pip install mysql-connector-python. Import in every program: import mysql.connector (or import mysql.connector as sq).

import mysql.connector
mycon = mysql.connector.connect(host="localhost", user="root",
                                passwd="your_password", database="school")
if mycon.is_connected():
    print("Connected")
cur = mycon.cursor()

• connect() opens the session and returns a connection object.
• is_connected() returns True if it is open.
• cursor() (a method of the connection) returns a cursor, which runs statements and keeps the result.
• close() ends the session at the end of the program.

All programs in the slides, notes and questions use these imagined tables. Every output shown was produced by running the program on exactly these rows.

After cur.execute("SELECT …"):

• fetchone() — the next row as a tuple; None when no row is left.
• fetchall() — all remaining rows as a list of tuples; [] when none are left.
• fetchmany(n) — the next n rows as a list.

The cursor keeps one pointer that only moves forward; to read again, execute again. Each tuple holds the selected columns in order, so row[0] is the first selected column.

import mysql.connector
mycon = mysql.connector.connect(host="localhost", user="root",
                                passwd="your_password", database="school")
cur = mycon.cursor()
cur.execute("SELECT roll FROM student ORDER BY roll")
a = cur.fetchmany(2)
b = cur.fetchone()
c = cur.fetchall()
print(a)
print(b)
print(c)

Output:

[(1,), (2,)]
(3,)
[(4,), (5,)]

INSERT, UPDATE and DELETE are run with execute() like any query, but mysql.connector does not autocommit: the change stays in an open transaction until mycon.commit(). Closing without commit discards it.

cur.rowcount (an attribute, no brackets) is the number of rows the last INSERT, UPDATE or DELETE affected.

import mysql.connector
mycon = mysql.connector.connect(host="localhost", user="root",
                                passwd="your_password", database="shop")
cur = mycon.cursor()
cur.execute("UPDATE item SET qty = qty + 25 WHERE qty < 15")
mycon.commit()
print("Restocked:", cur.rowcount)
cur.execute("DELETE FROM item WHERE price > 100")
mycon.commit()
print("Removed:", cur.rowcount)

Output:

Restocked: 3
Removed: 1

Placeholders: write %s for each value and pass a tuple: cur.execute("SELECT * FROM student WHERE roll = %s", (r,)). Text is quoted by the driver; the comma in (r,) makes a one-value tuple.

format(): build the string first: q = "SELECT * FROM student WHERE house = '{}'".format(h); text values need quotes written around {}.

Placeholders are safer with user input (they prevent SQL injection).

Complete program with input:

import mysql.connector
con = mysql.connector.connect(host="localhost", user="root",
                              passwd="your_password", database="shop")
cur = con.cursor()
code_no = int(input("Item code: "))
name = input("Item name: ")
price = int(input("Price: "))
qty = int(input("Quantity: "))
cur.execute("INSERT INTO item VALUES (%s, %s, %s, %s)", (code_no, name, price, qty))
con.commit()
cur.execute("SELECT * FROM item WHERE icode = %s", (code_no,))
print()
print("Saved:", cur.fetchone())
con.close()

With inputs 106, Eraser, 10 and 50 the last line printed is Saved: (106, 'Eraser', 10, 50).

Catching errors: except mysql.connector.Error: catches errors reported by the database (e.g. a duplicate primary key).