Unlocking The Power of Python for MySQL
Are you a developer who often finds themselves working with MySQL databases? If so, you probably already know that Python is a popular programming language for working with MySQL. Pythons simplicity and easy-to-use syntax make it an excellent choice for developers looking to modify data in MySQL tables. In this article, we will explore how Python can be used to update MySQL tables.
We will cover specific topics, including updating a single row, parameterized queries, updating multiple rows, and updating multiple columns. We will also discuss the Python MySQL Connector, how to connect to a MySQL database from Python, and how to perform database transactions with Python.
Updating a Single Row
The most basic task performed in MySQL is updating a single row. Whether it is to modify a record, remove an entry or add new information, updating a single row is necessary.
To perform an update, we employ the MySQL update query. In Python, you can execute a MySQL update query using the cursor object.
Here’s an example of how to do that:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="mydatabase"
)
mycursor = mydb.cursor()
sql = "UPDATE customers SET address = 'Barcelona' WHERE address = 'Las Vegas'"
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, "record(s) affected")
In the above code, we first create a connection to the MySQL database and then create a cursor object to execute queries. Next, we define a SQL statement that modifies the address column of the customers table to the value Barcelona where the existing address equals Las Vegas.
After that, we instruct the cursor object to execute the SQL statement with the .execute() method. Finally, we commit the changes using the .commit() method of our database object, and we print the number of records updated to the console.
Using A Python Variable In Parameterized Queries
Parameterized queries allow us to use a Python variable as a placeholder for values in our MySQL update queries. This technique helps to prevent SQL injection attacks by ensuring that values passed into the query are sanitized.
The following code demonstrates how we can use parameterized queries to update the table:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="mydatabase"
)
mycursor = mydb.cursor()
sql = "UPDATE customers SET address = %s WHERE name = %s"
val = ("Barcelona", "Amy")
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "record(s) updated")
In this code, we create the same connection and cursor objects as our previous example. Our SQL statement updates the address column to the value of Barcelona where the name column contains the value Amy.
We then define two variables, sql and val.” The sql variable contains the SQL statement with the “%s” placeholders. The val variable is a tuple that contains the values to be substituted into the placeholders.
We call the cursor objects .execute() method with the SQL statement and variable tuple as arguments to execute the query. Finally, we print the number of records that have been updated.
Updating Multiple Rows
In some cases, we may want to update several rows simultaneously. To accomplish that, we can use the cursors .executemany() method.
This method allows us to execute the same query for a list of tuples containing the values that will replace the old values in the table. Here is an example of how this works:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="mydatabase"
)
mycursor = mydb.cursor()
sql = "UPDATE customers SET address = %s WHERE name = %s"
val = [
("Barcelona", "Amy"),
("Lisbon", "Hannah"),
("New York", "Michael"),
("Madrid", "Sandy")
]
mycursor.executemany(sql, val)
mydb.commit()
print(mycursor.rowcount, "records updated")
In this example, we first defined our connection and cursor objects and then defined our query and the values that will replace the old values in the table.
Next, we called the cursors .executemany() method with the query and value list as arguments. Finally, we committed the changes to the database and printed the number of records updated.
Updating Multiple Columns
Sometimes, we need to update multiple columns at once. In MySQL update queries, we can specify these updates using several values separated by commas.
In Python, we can use placeholders and pass an array of values to the cursor objects .execute() method to perform the update. Here’s an example:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="mydatabase"
)
mycursor = mydb.cursor()
sql = "UPDATE customers SET address = %s, phone_number = %s WHERE name = %s"
val = ("Barcelona", "+34 932323232", "Amy")
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "records updated")
In this code, our update statement has been modified to include placeholders for both the address and phone_number columns, which we update to the specified values.
The query is executed by passing the SQL statement and an array of values to the cursors .execute() method. Finally, the changes are committed, and the number of records updated is printed to the console.
Python MySQL Connector
Python MySQL Connector is a module that allows us to connect to a MySQL database from our Python program. It contains functions that let us perform various operations on the database.
The following code demonstrates how to establish a connection using Python MySQL Connector:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="mydatabase"
)
print(mydb)
In the code, we first import the mysql.connector module and then create a connection object using the .connect() method. We pass four parameters the host where the database is located, the MySQL username, the password, and the database name.
Finally, we print the connection object to the console.
Commit and Rollback in MySQL
A database transaction is a series of operations that are executed together as a single unit. If one of these operations fails, we rollback all changes made during the transaction.
On the other hand, if all the operations are completed successfully, we commit the changes to the database. To perform a transaction with Python MySQL Connector, we can use the .commit() and .rollback() methods.
Here’s an example of how to perform a transaction:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="mydatabase"
)
mycursor = mydb.cursor()
try:
# Start transaction
mydb.start_transaction()
sql = "UPDATE customers SET balance = balance + 100 WHERE id = 1"
mycursor.execute(sql)
sql = "UPDATE customers SET balance = balance - 100 WHERE id = 2"
mycursor.execute(sql)
# Commit the transaction
mydb.commit()
except Exception as e:
print(e)
# rollback if any errors
mydb.rollback()
In this code, we first create a new cursor object and wrap our SQL statements in a try-except block. In the try section, we execute two SQL statements to update the balance of two customers in the database.
If any exception occurs, the except block will be executed, and we will rollback the changes made during the transaction to ensure data consistency.
Conclusion
In conclusion, Python is a powerful tool for working with MySQL databases. We covered how to update a single row, use a Python variable in parameterized queries, update multiple rows, update multiple columns, connect to a MySQL database using Python MySQL Connector, and perform database transactions in Python.
By leveraging the power of Python in combination with MySQL, developers can enhance their productivity and create powerful solutions for their business needs.
Example Programs
To further understand how to apply the concepts covered in this article, let us look at some example programs. In this section, we will demonstrate how to update a laptop table, use user input to update a MySQL table, and update the purchase date column.
Updating A Laptop Table
Consider a table in a MySQL database that contains details about different laptop models. Suppose we want to modify the price of a particular laptop model.
Here is a Python program that demonstrates how to accomplish this:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="mydatabase"
)
mycursor = mydb.cursor()
laptop_id = input("Enter laptop id to update: ")
new_price = input("Enter new price: ")
sql = "UPDATE laptop SET price = %s WHERE id = %s"
val = (new_price, laptop_id)
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "record(s) affected")
In this code, we start by creating a connection to our database and instantiating a cursor object. We then prompt the user to input the id of the laptop they want to update and the new price value.
We then define our SQL statement and a tuple of values to update the MySQL table. The cursors .execute() method is then called with the SQL statement and value tuple as arguments.
Finally, we commit the changes to the database and print the number of records affected.
Using Input From User
In some cases, we may want to allow the user to enter data interactively using input() in Python. Suppose we have a MySQL table that stores customer information, and we want to update a customers address and phone number.
Here is a program that demonstrates how to accomplish this:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="mydatabase"
)
mycursor = mydb.cursor()
name = input("Enter name of customer: ")
new_address = input("Enter new address: ")
new_phone_number = input("Enter new phone number: ")
sql = "UPDATE customers SET address = %s, phone_number = %s WHERE name = %s"
val = (new_address, new_phone_number, name)
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "record(s) affected")
In this example, the program prompts the user to enter the name of the customer they want to update, the new address value, and the new phone number value. We define our SQL statement and a tuple of values to update the MySQL table.
Then, the cursors execute() method is called with the SQL statement and the value tuple as arguments. Finally, we commit the changes to the database and print the number of records affected.
Updating Purchase Date Column
Suppose we have a MySQL table that stores information about orders for a particular store. We want to update the purchase date column to reflect the date when each order was made.
Here is a program that demonstrates how to update the purchase date column:
import mysql.connector
import datetime
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="password",
database="mydatabase"
)
mycursor = mydb.cursor()
order_id = input("Enter order id: ")
now = datetime.datetime.now()
formatted_date = now.strftime('%Y-%m-%d %H:%M:%S')
sql = "UPDATE orders SET purchase_date = %s WHERE id = %s"
val = (formatted_date, order_id)
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "record(s) affected")
In this example, we prompt the user to enter the id of the order they want to update. We then create a timestamp using Pythons datetime module and store that value in a variable.
We then format the timestamp to match the MySQL datetime format and define an SQL statement to update the purchase date column of the MySQL table. The cursors execute() method is then called with the SQL statement and value tuple as arguments.
Finally, we commit the changes to the database and print the number of records affected.
Next Steps
After working through these examples, you are now equipped with the skills to apply Python to MySQL database operations. To further enhance your skills in Python database operations, consider working on some database exercise projects.
These practice exercises can help build your experience in solving real-world problems using Python and MySQL. In conclusion, Python is an essential programming language for working with MySQL databases.
This article has covered various topics, including updating single and multiple rows, using parameterized queries, connecting to MySQL databases with Python, and performing database transactions. Additionally, we have explored examples of how to update a laptop table, use user input to update a database, and update the purchase date column.
With these skills, developers can enhance their productivity and create powerful solutions for their business needs. If you are looking to improve your skills in Python database operations, be sure to practice and complete some database exercise projects.
As the world shifts to becoming more data-driven, learning how to work effectively with databases will undoubtedly become increasingly relevant skills in the future.