Interface of Python with an SQL Database — Part 2: Changing Data and Parameterised Queries: visual lesson, CBSE Class 12 Computer Science

Lesson

Changing data from Python

Part 2 — INSERT, UPDATE, DELETE, commit(), rowcount, and values from variables.

In this lesson you will

Insert, update and delete records from Python and save them with commit()
Use rowcount to find how many rows a statement changed
Build queries with %s placeholders and with format(), and write complete programs

Part 2 at a glance

Diagram

Why commit() matters

mysql.connector does not autocommit by default.

Worked example

Worked example 3: inserting a patient

The clinic adds Tara to the patient table.

import mysql.connector
mycon = mysql.connector.connect(host="localhost", user="root",
                                passwd="your_password", database="clinic")
cur = mycon.cursor()
cur.execute("SELECT COUNT(*) FROM patient")
print("Before:", cur.fetchone()[0])
cur.execute("INSERT INTO patient VALUES (16, 'Tara', 'Ortho', 52)")
mycon.commit()
print("Inserted:", cur.rowcount)
cur.execute("SELECT COUNT(*) FROM patient")
print("After:", cur.fetchone()[0])
mycon.close()

Output produced by running the program.
Line printed	Meaning
`Before: 5`	rows before
`Inserted: 1`	rowcount of the INSERT
`After: 6`	rows after commit()

Cookies