Performing insert, update, delete queries using cursor

Performing insert, update, delete queries using cursor means using python code to execute SQL queries that modify the data in the database tables using the cursor object.

• To do this, you need to have a connection object and a cursor object that represent the connection to the database and the ability to execute queries, respectively. For example, if you are using MySQL, you can write:

import mysql.connector

db_connection = mysql.connector.connect(

    host="localhost",

    user="root",

    passwd="admin",

    database="teachoo"

)

my_database = db_connection.cursor()

• Then, you need to define the SQL query that you want to execute, using %s as a placeholder for the values you want to insert, update, or delete. 

For example, to insert a new record into a table named TEACHOOLOGIN, you can write:

sql_query = "INSERT INTO TEACHOOLOGIN (username, password) VALUES (%s, %s)"

values = ("craig", "1234")

• Next, you need to execute the query using the execute() method of the cursor object , passing the query and the values as arguments. 

For example, to execute the insert query, you can write:

my_database.execute(sql_query, values)

• Similarly, you can execute update or delete queries by changing the query and the values accordingly.

For example, to update the password of a user with username ‘craig’, you can write:

sql_query = "UPDATE TEACHOOLOGIN SET password = %s WHERE username = %s"

values = ("4321", "craig")

my_database.execute(sql_query, values)

• To delete a user with username ‘craig’, you can write:

sql_query = "DELETE FROM TEACHOOLOGIN WHERE username = %s"

values = ("craig",)

my_database.execute(sql_query, values)

Note that for a single value, you need to add a comma after it to make it a tuple.

• After executing the query, you need to commit your changes to the database using the commit() method of the connection object. 

For example, after inserting a new record, you can write:

db_connection.commit()