Python MySQL Update and Delete Data from Table

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 :-

1 record(s) affected

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!

You can also search for these topics, python mysql update table with value from another, update table with select from another in python, add primary key with python mysql update table, select the mysql update table using python, create the python mysql update table, update primary key in python mysql table, example for python mysql update table, exists the update table using python mysql, working of python update table to update.

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 :-

2 record(s) affected

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


You can also search for these topics, python prevent sql injection basic, detection of python sql injection, example for python sql injection, python to overflow the sql injection to prevent, replace the prevent sql injection using python, validate the sql injection using python, prevent sql injection and python with regular expression, prevent sql injection without parameter in python.

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.



You can also search for these topics, python mysql delete from by select, using primary key using python mysql delete, check the python limit to mysql delete, python all rows and affected rows with mysql delete, give the connection of python mysql delete, example for python mysql delete, how to get python to delete a data, key value of mysql delete in python.

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 :-

1 record(s) deleted

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!

You can also search for these topics, python delete record from list, how to delete record from file in python, generate the record to python delete, input the delete records in python keyboard, delete the python entry list record, python request the delete read only file, example for delete the records using python.