Question 33 (ii) - Teachoo Sample Paper - Computer Science Class 12 - Solutions to CBSE Sample Paper - Computer Science Class 12

Riya wants to write a program in Python to update the following record in the table named Product in MYSQL database, SHOP:

pid(Product ID) - integer

pname(Product Name) - string

price(Product Price) – float

Note the following to establish connectivity between Python and MySQL:

• Username - root

• Password - tiger

• Host - localhost

The value of pid has to be accepted from the user and the price has to be increased by 10%. Help Riya to write the program in Python.

Answer:

Answer by student

Detailed answer by teachoo

-lock-

• To write a program in Python to update a record in a MySQL database, we need to import the mysql.connector module. This module provides the interface between Python and MySQL.
• We also need to assign the value of pid (product ID) to a variable by taking input from the user. We use the int() function to convert the input string to an integer.
• Next, we need to establish a connection between Python and MySQL using the connect() function of the mysql.connector module. This function takes the following parameters:
• user: the username for accessing the database
• password: the password for accessing the database
• host: the name or IP address of the server where the database is hosted
• database: the name of the database to connect to
• The connect() function returns a connection object that represents the connection to the database. We assign this object to a variable called conn.
• To execute SQL queries on the database, we need to create a cursor object using the cursor() method of the connection object. We assign this object to a variable called cur.
• To update the record in the Product table, we need to use the UPDATE statement in SQL. The syntax of this statement is:

UPDATE table_name SET column_name = new_value WHERE condition;

• In this case, we want to update the price column of the Product table by increasing it by 10%. We also want to apply this update only to the record where pid matches the user input. So, we write:

UPDATE Product SET price = price * 1.1 WHERE pid = %s;

• Here, %s is a placeholder that will be replaced by the actual value of pid at runtime. This is done to prevent SQL injection attacks and ensure data security.
• To execute this query on the database, we use the execute() method of the cursor object. This method takes two arguments:
• The SQL query as a string
• A tuple of values that will replace the placeholders in the query
• In this case, we pass:

cur.execute("UPDATE Product SET price = price * 1.1 WHERE pid = %s", (pid,))

• Here, (pid,) is a tuple with one element, which is the value of pid.
• To make sure that the changes made by the query are saved in the database, we need to use the commit() method of the connection object. This method commits or finalizes any pending transactions on the database.
• Finally, we need to close the connection and cursor objects using their respective close() methods. This frees up any resources used by them and terminates the connection to the database.

The final code is:

-endlock-