Insert and Delete in MySQL with Python

To study this lesson well, you need to know how to create a database and table in MySQL in Python. You can read the article Creating a database and table in MySQL with Python to learn how to create a database and table in MySQL.

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

We will use the table “gochocit_posts” in the database “gochocit” to demonstrate how to insert and delete data. You can find this database and table in the article Creating a database and table in MySQL with Python. You need to read it to see the database structure and the table.

1. Insert in MySQL with Python

Use Python to execute the SQL statement "INSERT INTO" to add data to the table.

import mysql.connector
# create connection to MySQL
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="",
  database = "gochocit"
)
mycursor = mydb.cursor()
sql = """INSERT INTO gochocit_posts (post_author, post_title, post_content, post_date)
          VALUES ("Vinh Le", "This is post number 1", "Hello everyone!", now())
      """
mycursor.execute(sql)
mydb.commit()
# disconnect from MySQL
mydb.close()

With the above method, we execute with only one SQL statement to add data. In this statement, the function now() is a function in SQL that helps get the date time at the time of executing the SQL statement to add to the table “gochocit_posts“. The “gochocit_posts” table has an automatically increasing ID column, so inserting this value into the table is unnecessary.

Note: We need to call the mydb.commit() statement to save the changes to the table when inserting data into the table.

We can separate the INSERT INTO statement into 2 parts: the table and column to be inserted, and the values to be inserted.

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

sql = """INSERT INTO gochocit_posts (post_author, post_title, post_content, post_date)
          VALUES (%s, %s, %s, %s)
      """
now = datetime.now()
formatted_datetime = now.strftime('%Y-%m-%d %H:%M:%S')
val = ("Vinh Le", "This is post number 1", "Hello everyone!", formatted_datetime)

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

The sql string uses %s to reserve a place for the values to be inserted into the table. The value to be inserted is in a tuple. The value of the “post_date” column is a string in the format yyyy-mm-dd h:m:s, taken from the datetime.now() function in Python.

To add multiple rows of data to the table, we use the executemany() function in the MySQL Connector module. Its parameter is also like the execute() function, except its second parameter is a list of tuples containing the data to be inserted into the table.

import mysql.connector

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

sql = """INSERT INTO gochocit_posts (post_author, post_title, post_content, post_date)
          VALUES (%s, %s, %s, %s)
      """

val = [
        ("Vinh Le", "This is post number 1", "Content of post number 1", "2022-03-27 19:46:05"),
        ("Vinh Le", "This is post number 2", "Content of post number 2", "2022-03-27 19:47:06"),
        ("Vinh Le", "This is post number 3", "Content of post number 3", "2022-03-27 19:48:07"),
        ("Vinh Le", "This is post number 4", "Content of post number 4", "2022-03-27 19:49:08"),
        ("Vinh Le", "This is post number 5", "Content of post number 5", "2022-03-27 19:50:09")
      ]

mycursor.executemany(sql, val)
mydb.commit()
print(mycursor.rowcount, "record(s) was inserted.")
# disconnect from MySQL
mydb.close()

Result

5 record(s) was inserted.

2. Delete in MySQL with Python

We can delete data rows in the table with the "DELETE FROM" statement.

import mysql.connector

# create connection to MySQL
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  password="",
  database = "gochocit"
)
mycursor = mydb.cursor()
sql = "DELETE FROM gochocit_posts WHERE ID = %s"
adr = ("5", )

mycursor.execute(sql, adr)
mydb.commit()
print(mycursor.rowcount, "record(s) deleted")
# disconnect from MySQL
mydb.close()

Result

1 record(s) deleted

The WHERE clause helps specify which data row will be deleted. If the WHERE statement is not used, and all data rows in the table will be deleted.