Table of Contents
Update and Delete query in Python
In this blog we are going to use update query to modify data in table and delete query to remove data from table using python script to access MySQL. Database connectivity plays important role in developing dynamic applications.
After establishing connection with SQL, we can create new databases and tables using python script to automate work.
In previous post we have learnt about create and insert query to create database and table in MySQL using python also we have learnt about select query for reading and searching data.
Now let us learn how to read data from MySQL and how to search data from it using Python interface.
Update query to modify data in MySQL using python
Update Query is used to modify records from existing table or database using python interface.
We can modify records using different conditions as per need.
Syntax:
cur.execute("Update tablename set column_name = value where condition" )Here,
set : is used to assign updated or modified value to existing column.
where : is used to give desired condition
Note:
Before using update query it is mandatory to use or create database and related table. Also values must be present to be modified from table else no rows will be affected.
Example to update
'''
Update operation in SUSCRIBERS table
'''
import mysql.connector
db=mysql.connector.connect(user='root',passwd='root',host='127.0.0.1',database='itvoyagers')
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to UPDATE required records
sql = "UPDATE SUSCRIBERS SET AGE = AGE + 1 WHERE GENDER = 'F'"
try:
# Execute the SQL command
cursor.execute(sql)
# Commit your changes in the database
db.commit()
except:
# Rollback in case there is any error
db.rollback()
# disconnect from server
db.close()
OUTPUT
To check output you can go to MySQL Command Line Client –> use required database name–> use select query on your table and see the change.
Note: In above output first select query was executed before running update query via python script and second select query is executed after running update query via python script.
Delete query to remove data in MySQL using python
Delete Query is used to remove records from existing table or database using python interface.
We can either delete single record or multiple records from table using where clause.
Syntax:
cur.execute("Delete * from tablename where condition")Here,
where : is used to give desired condition
Note:
Before using delete query it is mandatory to use or create database and related table. Also values must be present to be removed from table else no rows will be affected.
Example to delete
Before executing delete query using python script, one more record is inserted in SUSCRIBERS table using insert query direct from MySQL Command Line Client.
eg: insert into suscribers values(‘Peter’,’Parker’,25,’M’,’NWH’);
'''
DELETE operation in SUSCRIBERS table
'''
import mysql.connector
db=mysql.connector.connect(user='root',passwd='root',host='127.0.0.1',database='itvoyagers')
# prepare a cursor object using cursor() method
cursor = db.cursor()
# Prepare SQL query to UPDATE required records
sql = "DELETE FROM SUSCRIBERS WHERE AGE > 23 "
try:
# Execute the SQL command
cursor.execute(sql)
print ("Data Deleted SuccessFully..!")
# Commit your changes in the database
db.commit()
except:
# Rollback in case there is any error
db.rollback()
# disconnect from server
db.close()
OUTPUT
To check output you can go to MySQL Command Line Client –> use required database name–> use select query on your table and see the change.
OUTPUT OF SELECT QUERY BEFORE EXECUTING DELETE QUERY
OUTPUT ON PYTHON IDLE
OUTPUT OF SELECT QUERY AFTER EXECUTING DELETE QUERY
