INSERT, UPDATE and DELETE from Python: commit(), %s and format() (Class 12 Computer Science)

Understand INSERT, UPDATE and DELETE from Python: commit(), %s and format()

q = "INSERT INTO STUDENT VALUES (%s, %s, %s, %s, %s)"
cur.execute(q, (6, "Tara", "12A", 82, "Pune"))
con.commit()
print(cur.rowcount, "record inserted")

rno, name, cls, marks, city = 6, "Tara", "12A", 82, "Pune"
q = "INSERT INTO STUDENT VALUES ({}, '{}', '{}', {}, '{}')".format(rno, name, cls, marks, city)

rno = int(input("Roll number: "))
new = int(input("New marks: "))
cur.execute("UPDATE STUDENT SET Marks = %s WHERE RollNo = %s", (new, rno))
con.commit()
print(cur.rowcount, "record updated")

cur.execute("DELETE FROM STUDENT WHERE City = %s", ("Pune",))
con.commit()
print(cur.rowcount, "record deleted")

INSERT, UPDATE and DELETE from Python: commit(), %s and format(): key facts

Saving changes

• •INSERT, UPDATE, DELETE need con.commit()
• •SELECT does not need commit
• •Without commit the change is lost

commit belongs to the connection, not the cursor.

The %s method

• •query = "... VALUES (%s, %s)"
• •cur.execute(query, values_tuple)
• •No quotes around %s
• •One value needs a trailing comma: (x,)

Values go in column order.

The format method

• •"... VALUES ({}, '{}')".format(a, b)
• •Quote text and dates inside the string
• •Print the final string to check it

A missing quote gives a syntax error.

rowcount

After INSERT	rows inserted
After UPDATE	rows changed
After DELETE	rows removed
	0 means no row matched

Print it for a confirmation message.

How to answer INSERT, UPDATE and DELETE from Python: commit(), %s and format() questions

1. 1Connect and create the cursor as usual.
2. 2Read the values with input(); convert numbers with int() or float().
3. 3Write the query with %s placeholders (or {} with format) and the WHERE condition for UPDATE and DELETE.
4. 4Call cur.execute(query, tuple) and check that the tuple has one value per %s.
5. 5Call con.commit(), then print cur.rowcount with a message.
6. 6Close the connection; keep text values quoted when using format().

INSERT, UPDATE and DELETE from Python: commit(), %s and format() in real life

The idea, met in an everyday situation. Answer the question in your own words, then compare with the model answer. The one-line hook is what to remember.

Tricks to remember INSERT, UPDATE and DELETE from Python: commit(), %s and format()

Short memory aids. Say each one aloud once, then use it on a question the same day.

• Change? Commit!

  Every change in data needs a commit; a SELECT does not.

  Use it when: When to call commit.

• %s needs no quotes

  The placeholder handles quoting itself.

  Use it when: %s queries.

• format needs your quotes

  Put single quotes around text and dates in the query string.

  Use it when: format() queries.

• Comma for one

  A single-value tuple is written (x,).

  Use it when: DELETE with one value.

INSERT, UPDATE and DELETE from Python: commit(), %s and format(): solved examples

Ordered from board level to HOTS. Try each one before you open the solution.

city = input("City to remove: ")
cur.execute("DELETE FROM STUDENT WHERE City = %s", (city,))
con.commit()
print(cur.rowcount, "record(s) deleted")
cur.execute("SELECT COUNT(*) FROM STUDENT")
print(cur.fetchone()[0])

Where students lose marks in INSERT, UPDATE and DELETE from Python: commit(), %s and format()

The slips examiners see most often, and what to write instead.

• ✗Mistake: Forgetting commit()

  The change is not saved permanently.

  Wrong working

  cur.execute(q, vals)
  con.close()

  Right working

  cur.execute(q, vals)
  con.commit()
  con.close()

  ✓Do this: Write commit straight after execute for INSERT, UPDATE and DELETE.

  Usually costs: 1 mark

• ✗Mistake: Quoting %s

  The placeholder must stand alone; extra quotes break the query.

  Wrong working

  "... VALUES ('%s', %s)"

  Right working

  "... VALUES (%s, %s)"

  ✓Do this: Remove quotes around %s.

  Usually costs: 1 mark

• ✗Mistake: Missing quotes with format()

  Text values need single quotes inside the query string.

  Wrong working

  "... VALUES ({}, {})".format(1, "Asha")

  Right working

  "... VALUES ({}, '{}')".format(1, "Asha")

  ✓Do this: Check the printed query.

  Usually costs: 1 mark

• ✗Mistake: Calling commit on the cursor

  commit belongs to the connection object.

  Wrong working

  cur.commit()

  Right working

  con.commit()

  ✓Do this: Use the connection name.

  Usually costs: 0.5 mark

Practice questions on INSERT, UPDATE and DELETE from Python: commit(), %s and format()

Write each answer on paper first. Open the hint only if you are stuck, and the answer only when you have finished.

1. Q1 · 1 mark · MCQBoard level

   In the query "UPDATE STUDENT SET Marks = %s WHERE RollNo = %s", what does %s represent?
   (a) a comment (b) a placeholder for a value (c) a table name (d) a wildcard

   Hint

   The values are given in a tuple.

   Answer

   (b) a placeholder for a value

2. Q2 · 1 mark · Very short answerBoard level

   Which attribute of the cursor gives the number of rows affected by a DELETE?

   Hint

   It is also used after fetching.

   Answer

   rowcount

3. Q3 · 2 marks · Short answerHarder board level

   Write a statement to delete the record with roll number 5 using the format() method and commit the change.

   Hint

   A number needs no quotes.

   Answer

   cur.execute("DELETE FROM STUDENT WHERE RollNo = {}".format(5))
   con.commit()

4. Q4 · 1 mark · Assertion-reasonHarder board level

   Assertion (A): In the format() method the text values must be enclosed in single quotes inside the query string.
   Reason (R): Without quotes MySQL reads the text as a column name or a syntax error occurs.
   (a) Both A and R are true and R is the correct explanation of A. (b) Both A and R are true but R is not the correct explanation of A. (c) A is true but R is false. (d) A is false but R is true.

   Hint

   Compare with %s.

   Answer

   (a) Both are true and R explains A.

5. Q5 · 3 marks · Short answerHOTS / competency

   Suppose a program reads rno, name, cls, marks and city and inserts a record using %s. Write the full program (including the connection) and explain what is printed when the roll number already exists in the table.

   Hint

   The primary key rejects a duplicate.

   Answer

   import mysql.connector as m
   con = m.connect(host="localhost", user="root", passwd="pw", database="school")
   cur = con.cursor()
   rno = int(input("Roll: "))
   name = input("Name: ")
   cls = input("Class: ")
   marks = int(input("Marks: "))
   city = input("City: ")
   cur.execute("INSERT INTO STUDENT VALUES (%s, %s, %s, %s, %s)", (rno, name, cls, marks, city))
   con.commit()
   print(cur.rowcount, "record inserted")
   con.close()

   If RollNo is the primary key and the number already exists, MySQL raises an integrity error (mysql.connector.Error) and the program stops before printing; wrap it in try-except to print a message instead.

Test your recall

Answer in your head or on paper first, then open the card. Pulling an answer out of memory makes it stick far better than re-reading.

1. 1.Which method saves INSERT, UPDATE and DELETE?Show answer

   con.commit().

2. 2.Which object does commit() belong to?Show answer

   The connection.

3. 3.Is commit needed after SELECT?Show answer

   No.

4. 4.What must wrap text values in the format() method?Show answer

   Single quotes inside the query string.

5. 5.How is a one-value tuple written?Show answer

   (x,) with a trailing comma.

6. 6.What does rowcount show after UPDATE?Show answer

   The number of rows changed.

INSERT, UPDATE and DELETE from Python: commit(), %s and format(): frequently asked questions