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()
