Inserting, Updating, and Deleting Data

Welcome to the “Inserting, Updating, and Deleting Data” lesson of the “Introduction to Python” course. In this lesson, you will learn how to insert, update, and delete data in a database using Python.

Before we get started, it is important to note that in order to perform these operations, you must first establish a connection to a database. If you are not familiar with how to do this, you can refer to the “Connecting to a Database” lesson in this course.

Inserting Data

To insert data into a database, you can use the “INSERT” SQL query. The basic syntax for inserting a row into a table is as follows:

INSERT INTO table_name (column_1, column_2, ...) VALUES (value_1, value_2, ...)

Here is an example of inserting a row into a table using Python and MySQL Connector/Python:

import mysql.connector

cnx = mysql.connector.connect(user="username", password="password", host="localhost", database="database_name")

cursor = cnx.cursor()

query = "INSERT INTO table_name (column_1, column_2) VALUES (%s, %s)"
values = ("value_1", "value_2")

cursor.execute(query, values)
cnx.commit()

print("Inserted successfully!")

cnx.close()

In the above example, we first import the “mysql.connector” module and establish a connection to the database. Then, we create a cursor object and define the “INSERT” query, including placeholders for the values to be inserted. Finally, we execute the query and commit the changes to the database.

Updating Data

To update data in a database, you can use the “UPDATE” SQL query. The basic syntax for updating a row in a table is as follows:

UPDATE table_name
SET column_1 = value_1, column_2 = value_2, ...
WHERE condition

Here is an example of updating a row in a table using Python and MySQL Connector/Python:

import mysql.connector

cnx = mysql.connector.connect(user="username", password="password", host="localhost", database="database_name")

cursor = cnx.cursor()

query = "UPDATE table_name SET column_1 = %s, column_2 = %s WHERE condition"
values = ("new_value_1", "new_value_2")

cursor.execute(query, values)
cnx.commit()

print("Updated successfully!")

cnx.close()

In the above example, we first import the “mysql.connector” module and establish a connection to the database. Then, we create a cursor object and define the “UPDATE” query, including placeholders for the new values to be set and a condition to specify which rows should be updated. Finally, we execute the query and commit the changes to the database.

Deleting Data

To delete data from a database, you can use the “DELETE” SQL query. The basic syntax for deleting rows from a table is as follows:

DELETE FROM table_name WHERE condition

Here is an example of deleting rows from a table using Python and MySQL Connector/Python:

import mysql.connector

cnx = mysql.connector.connect(user="username", password="password", host="localhost", database="database_name")

cursor = cnx.cursor()

query = "DELETE FROM table_name WHERE condition"

cursor.execute(query)
cnx.commit()

print("Deleted successfully!")

cnx.close()

In the above example, we first import the “mysql.connector” module and establish a connection to the database. Then, we create a cursor object and define the “DELETE” query, including a condition to specify which rows should be deleted. Finally, we execute the query and commit the changes to the database.

Exercises

Here are some exercises with solutions to help you practice what you just learned:

import mysql.connector

cnx = mysql.connector.connect(user="username", password="password", host="localhost", database="database_name")

cursor = cnx.cursor()

name = input("Enter name: ")
age = input("Enter age: ")

query = "INSERT INTO table_name (name, age) VALUES (%s, %s)"
values = (name, age)

cursor.execute(query, values)
cnx.commit()

print("Inserted successfully!")

cnx.close()

import mysql.connector

cnx = mysql.connector.connect(user="username", password="password", host="localhost", database="database_name")

cursor = cnx.cursor()

id = input("Enter id: ")
age = input("Enter new age: ")

query = "UPDATE table_name SET age = %s WHERE id = %s"
values = (age, id)

cursor.execute(query, values)
cnx.commit()

print("Updated successfully!")

cnx.close()

import mysql.connector

cnx = mysql.connector.connect(user="username", password="password", host="localhost", database="database_name")

cursor = cnx.cursor()

id = input("Enter id: ")

query = "DELETE FROM table_name WHERE id = %s"
values = (id,)

cursor.execute(query, values)
cnx.commit()

print("Deleted successfully!")

cnx.close()

import mysql.connector

cnx = mysql.connector.connect(user="username", password="password", host="localhost", database="database_name")

cursor = cnx.cursor()

query = "SELECT * FROM table_name"

cursor.execute(query)

for row in cursor:
    print(row)

cnx.close()

import mysql.connector

cnx = mysql.connector.connect(user="username", password="password", host="localhost", database="database_name")

cursor = cnx.cursor()

age = input("Enter age threshold: ")

query = "SELECT * FROM table_name WHERE age > %s"
values = (age,)

cursor.execute(query, values)

for row in cursor:
    print(row)

cnx.close()