Python MySQL Update and Delete Data from Table
Update Data In A Table
The "UPDATE" statement can update existing records in a table.
The following syntax is used to change the data in a table :
UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value
Take note of the WHERE
clause in the UPDATE
syntax :
The WHERE
clause defines which record or records should be modified. If you
omit the WHERE clause, all records will be updated!
Let's look at the "books" table :
ID | Name | Price |
---|---|---|
14 | Python Networking | 160.00 |
15 | Web Programming | 70.00 |
16 | The Web with Python | 120.00 |
Example :- Update a bookname where id = 15 :
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="myusername",
password="mypassword",
database="mydatabase"
)
mycursor = mydb.cursor()
sql = "UPDATE books SET name = 'Web Programming in Python' WHERE id = 15"
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, "record(s) affected")
Output :-
Important!: note the declaration: mydb.commit()
. Changes are required, otherwise the table is not altered.
Note the UPDATE syntax WHERE clause: Which records or records to update is indicated by the WHERE clause. All records shall be updated if you skip the WHERE Clause!
Prevent SQL Injection
The escape of any query values, even in update statements, is regarded good practise.
This prevents SQL injections, a common approach for web-based hacking to corrupt your database.
The mysql.connector
module utilises the placeholder %s
in the delete statement to escape values :
Example :- Update price=200 where the word "web" is found in the name column :
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="myusername",
password="mypassword",
database="mydatabase"
)
mycursor = mydb.cursor()
sql = "UPDATE books SET price = %s WHERE name like %s"
val = (200, "%web%")
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "record(s) affected")
Output :-
After executing the above program, the "books" table look like :
ID | Name | Price |
---|---|---|
14 | Python Networking | 160.00 |
15 | Web Programming in Python | 200.00 |
16 | The Web with Python | 200.00 |
Related Links
Python MySQL Delete From By
To remove records from a table, use the DELETE
statement :
DELETE FROM table_name
WHERE some_column = some_value
Note in the DELETE
syntax, the WHERE
clause: The WHERE
clause sets out the record or records to be removed. If you skip the WHERE
clause, All records are erased.
Related Links
Delete Record
The "DELETE FROM" statement allows you to remove data from an existing table :
Let's look at the "books" table :
ID | Name | Price |
---|---|---|
14 | Python Networking | 160.00 |
15 | Web Programming in Python | 200.00 |
16 | The Web with Python | 200.00 |
Example :- Remove the record where name is "Python Networking" :
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="myusername",
password="mypassword",
database="mydatabase"
)
mycursor = mydb.cursor()
sql = "DELETE FROM books WHERE name = 'Python Networking'"
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, "record(s) deleted")
Output :-
Important! : note the declaration: mydb.commit()
. Changes are required, otherwise the table is not altered.
In DELETE syntax, note the WHERE clause: The WHERE clause indicates which record(s) to remove. All records will be removed if the WHERE clause is omitted!