Update Table – Хүснэгт шинэчлэх #
You can update existing records in a table by using the “UPDATE” statement:
Example – Жишээ #
Overwrite the address column from “Valley 345” to “Canyon 123”:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="mydatabase"
)
mycursor = mydb.cursor()
sql = "UPDATE customers SET address = 'Canyon 123' WHERE address = 'Valley 345'"
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, "record(s) affected")
Important!: Notice the statement: mydb.commit(). It is required to make the changes, otherwise no changes are made to the table.
Notice the WHERE clause in the UPDATE syntax: The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!
Prevent SQL Injection – SQL халдлагаас урьдчилан сэргийлэх #
It is considered a good practice to escape the values of any query, also in update statements.
This is to prevent SQL injections, which is a common web hacking technique to destroy or misuse your database.
Энэ нь өгөгдлийн санг сүйтгэх эсвэл буруугаар ашиглах нийтлэг вэб хакерын арга болох SQL тарилгад өртөхөөс урьдчилан сэргийлэх зорилготой юм.
The mysql.connector module uses the placeholder %s to escape values in the update statement:
Example – Жишээ #
Escape values by using the placeholder %s method:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="нэвтрэхНэр",
password="НууцҮг",
database="databaseНэр"
)
mycursor = mydb.cursor()
sql = "UPDATE customers SET address = %s WHERE address = %s"
val = ("Valley 345", "Canyon 123")
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "record(s) affected")
