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:
Write a Python program that connects to a MySQL database and inserts a new row into a table with three columns: “id” (an auto-incrementing integer), “name” (a string), and “age” (an integer). The program should ask the user to enter the name and age, and then insert the data into the table.
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()
Write a Python program that connects to a MySQL database and updates the “age” column of a row with a specific “id”. The program should ask the user to enter the id and the new age, and then update the data in the table.
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()
Write a Python program that connects to a MySQL database and deletes a row with a specific “id”. The program should ask the user to enter the id, and then delete the row from the table.
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()
Write a Python program that connects to a MySQL database and selects all rows from a table, then prints the data to the console.
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()
Write a Python program that connects to a MySQL database and selects rows from a table where the “age” column is greater than a specific value. The program should ask the user to enter the age threshold, and then print the data for the matching rows to the console.
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()