Python MySQL UPDATE - CodersLegacy

Description

Once you’ve entered data into the table, there might come a time when you want to change or update the information in it. This brings us to the Python MySQL UPDATE statement which has the ability to update values in a table. When it is coupled with the WHERE clause it becomes more precise.

Syntax

The SET column_name determines the value to be inserted to into a column.

UPDATE tablename SET column_name = value

CodersLegacy Python MySQL UPDATE database — Database model

Example

Remember to import the mysql.connector and create a connection to your database as shown below before attempting anything. Follow up by creating a cursor object which we’ll be using to manipulate to the database.

import mysql.connector

db = mysql.connector.connect(
  host = "localhost",
  user = "root",
  password = "*********",
  database = "mydatabase"
)

cursor = db.cursor()
cursor.execute("UPDATE students SET age = 18 WHERE id = 5")

db.commit()

Here, we write the code for a program that will update the age of the person whose id is 5. It’s essential that you keep a WHERE clause otherwise all the records in the Database will be updated and you’ll lose all your data. There is no way to retrieve this data unless you have made backups.

Remember to include the db.commit() command to save the changes to the database.

CodersLegacy Python MySQL UPDATE database picture — Python MySQL Database

Here’s the image of our table after running that code. See that the value of Rico’s age has changed from 17 to 18.

Preventing SQL injection

While you’re working on the localhost, this isn’t a problem. However, when working with databases connected to the internet or on web servers, this is a glaring issue. SQL injection is when a hacker injects SQL code into your database using a vulnerability. Luckily, there is an easy fix for this.

Remember the %s placeholder character? Using this prevents SQL injection.

import mysql.connector

db = mysql.connector.connect(
  host = "localhost",
  user = "root",
  password = "******",
  database = "mydatabase"  )

cursor = db.cursor() 

sql = "SELECT * FROM student WHERE gender = %s"
gender = ("Male")

cursor.execute(sql, gender)

results = cursor.fetchall()
print(results)

This marks the end of our Python MySQL UPDATE Article. Let us know if you have any suggestions or corrections in this Article to make. Contributions to help grow CodersLegacy are more than welcome.

Use this link to head back to main Python MySQL page: link