Best post on Update query and Delete query : Python + MySQL-4

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.

update query

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

BEFORE DELETE

OUTPUT ON PYTHON IDLE

DELETE

OUTPUT OF SELECT QUERY AFTER EXECUTING DELETE QUERY

AFTER DELETE

Leave a Comment