Insert, Update & Delete from PythonOptional for Informatics Practices

1Putting values in with %s

The values a program inserts come from variables, not from you typing them. Rather than gluing strings together, leave a %s for each value and pass them as a tuple second argument:

cur.execute("insert into students values (%s, %s, %s, %s, %s, %s)",
            (5001, "Asha", 11, 77.5, "2009-04-10", "Namchi"))

Six %s and six values, in the same order. The connector puts them in for you — and, importantly, it quotes the text and dates correctly without you having to think about it.

Building the query by joining strings instead — "… values (" + str(id) + ", '" + name + "')" — works right up until a name contains an apostrophe, at which point the SQL breaks. Placeholders avoid the whole problem, so use them from the start.

2commit() — the line everyone forgets

Here is the demonstration. One program opens two connections: A inserts a row and does not commit, then B — a completely separate connection — counts the rows.

a = open_con()
ca = a.cursor()
ca.execute("insert into students values (%s,%s,%s,%s,%s,%s)",
           (5002, "Ghost", 10, 50.0, "2010-01-01", "Namchi"))
print("inserted, rowcount:", ca.rowcount)

b = open_con()
cb = b.cursor()
cb.execute("select count(*) from students")
print("what another connection sees:", cb.fetchone())

a.rollback()
print("after rollback on A")
cb.execute("select count(*) from students")
print("what another connection sees:", cb.fetchone())

inserted, rowcount: 1
what another connection sees: (12,)
after rollback on A
what another connection sees: (12,)

The insert reported rowcount: 1 — it worked. And yet the other connection still counts 12. The new row exists only inside connection A, invisible to everybody else, waiting to be confirmed or thrown away.

rollback() is the opposite of commit: it throws the unsaved changes away deliberately. That is why the count is still 12 at the end, and why the table is unharmed by this demonstration.

3All three operations, done properly

Insert a student, change their marks, then remove them — each followed by a commit(), and each checked:

import mysql.connector

con = mysql.connector.connect(
    host="localhost", port=3307,
    user="student", passwd="kv@1234", database="lambdalab")
cur = con.cursor()

cur.execute("insert into students values (%s, %s, %s, %s, %s, %s)",
            (5001, "Asha", 11, 77.5, "2009-04-10", "Namchi"))
print("insert rowcount:", cur.rowcount)
con.commit()

cur.execute("select count(*) from students")
print("after insert:", cur.fetchone())

cur.execute("update students set percent = %s where id = %s", (81.0, 5001))
print("update rowcount:", cur.rowcount)
con.commit()

cur.execute("select id, name, percent from students where id = 5001")
print("the updated row:", cur.fetchone())

cur.execute("delete from students where id = %s", (5001,))
print("delete rowcount:", cur.rowcount)
con.commit()

cur.execute("select count(*) from students")
print("after delete:", cur.fetchone())
con.close()

insert rowcount: 1
after insert: (13,)
update rowcount: 1
the updated row: (5001, 'Asha', 81.0)
delete rowcount: 1
after delete: (12,)

Twelve students became thirteen and then twelve again. Every step reported rowcount: 1, and the updated row shows 81.0 where 77.5 had been.

4rowcount tells you what really happened

After a change, cur.rowcount is the number of rows affected — the same number MySQL prints at the prompt. It is the natural thing to test:

cur.execute("delete from students where id = %s", (rollno,))
con.commit()

if cur.rowcount == 0:
    print("No student with that roll number.")
else:
    print(cur.rowcount, "record(s) deleted.")

A delete that matches nothing is not an error in SQL, so without this check the program would cheerfully report success having done nothing.

5The skeleton to remember

Step 4 is the only one that is conditional: a select changes nothing, so it needs no commit. Everything else does.

6Recap