Update and drop in MySQL with Python

To learn this lesson well, you need to know how to insert data into a table in MySQL with Python. You can read the article Insert and delete data in MySQL with Python to learn how to insert data into a table in MySQL.

Note: We are using MySQL integrated into XAMPP. You need to read the article Installing PHP Web Development Environment with XAMPP to learn how to install XAMPP and start MySQL.

The update and drop examples will be performed on the gochocit_posts table in the gochocit database. This database and table have already been created in the article Create a database and table in MySQL with Python. You need to review the structure of the database and the data in the table.

1. Update data in MySQL with Python

We can update the rows of data that have been inserted into the table in MySQL. Use the "UPDATE" statement to perform the update.

import mysql.connector
# create connection to MySQL
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="",
  database = "gochocit"
)
mycursor = mydb.cursor()

sql = "UPDATE gochocit_posts SET post_author = 'Phuc Tran' WHERE ID = '1'"
mycursor.execute(sql)

mydb.commit()
print(mycursor.rowcount, "record(s) affected")
# disconnect from MySQL
mydb.close()

Result

1 record(s) affected

Note: We must call the mydb.commit() statement to save changes to the table when updating data to the table.

The WHERE clause helps specify which row of data to update. If the WHERE statement is not used, all rows of data in the table will be updated.

We can use %s to place the value to be updated and the value to filter the data in the WHERE clause.

import mysql.connector
# create connection to MySQL
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="",
  database = "gochocit"
)
mycursor = mydb.cursor()

sql = "UPDATE gochocit_posts SET post_author = %s WHERE ID = %s"
val = ('Dung Le', '5')
mycursor.execute(sql, val)

mydb.commit()
print(mycursor.rowcount, "record(s) affected")
# disconnect from MySQL
mydb.close()

Result

1 record(s) affected

2. Drop in MySQL with Python

We can delete tables that have been created in the database. Use the "DROP TABLE" statement to delete the table. Add "IF EXISTS" to check if a table exists or not, if it already exists, delete the table.

import mysql.connector
# create connection to MySQL
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="",
  database = "gochocit"
)
mycursor = mydb.cursor()

mycursor = mydb.cursor()

sql = "DROP TABLE IF EXISTS gochocit_posts"

mycursor.execute(sql)
# disconnect from MySQL
mydb.close()

If the above code executes without error, the table has been deleted. You use a web browser and access the address http://localhost/phpmyadmin/ to check if the table has been deleted or not.