Update table in mysql using Python programming language

Python is commonly used for interfacing with databases, particularly MySQL. In this tutorial, we will focus on updating a table in MySQL using Python programming language. Updating a table is an important operation, as it enables us to modify existing data in the database. By the end of this tutorial, you will learn how to use Python and MySQL to update data stored in a table, making it a valuable skill to have in your programming toolbox.

Different ways of updating table in Python :

• Python programming language can be used to update table in MySQL. 
• There are various ways to update table in MySQL. 
• One can either use 
  • raw SQL queries or 
  • use Python's inbuilt mysql connector. 
• Raw SQL queries : 
  • If we are using raw SQL queries then our execute statement will look like 
  •  cursor.execute('UPDATE table_name SET column1=value1, column2=value2 WHERE condition;')  
• Python's inbuilt mysql connector :
  • If you are using Python's inbuilt mysql connector then our execute statement will look like 

cursor.execute("UPDATE table_name SET column1=%s, column2=%s WHERE condition=%s", (value1, value2, condition))
 

How to Update table in MySQL with Python :

• Follow the steps given below to Update table in MySQL with python 
  • Step 1: Install the required Packages
  • Step 2: Establishing a Connection to the MySQL Server
  • Step 3: Create a Cursor Object
  • Step 4: Update table
  • Step 5: Commit the changes

Step 1: Install the Required Packages

• The first step is to ensure that you have installed the required packages on your machine.
• The most popular packages for connecting to MySQL in Python are 
  • mysql-connector and 
  • PyMySQL.
• You can install these packages using pip through the command line.
• Code Sample :

!pip install mysql-connector-python
!pip install PyMySQL

Step 2: Connect to the MySQL Database

• To create a new database in MySQL using Python, we must have a connection to the MySQL server.
• To connect to a MySQL server in Python we can use either 
  • mysql.connector or 
  • PyMySQL package 
• We need to provide the necessary connection parameters to establish the connection.
• We need to pass the following parameters to the Connection object: 
  • host : the hostname  of the MySQL server user 
  • user : username for the MySQL account
  • password : password for the MySQL account database
  • database : The name of the database to connect to 
• To study package specific code for connecting to MySQL read our tutorial on connecting to MySQL server.

Step 3: Create a Cursor Object

• After establishing a connection to the MySQL server, your next step is to create a cursor object.
• This is done using the   cursor()  method provided by mysql.connector or PyMySQL.
• Example:

cursor = mydb.cursor()

Step 4: Update table

• To update data in a MySQL table using Python, use the  UPDATE  statement.
• Use the  execute()  method on the cursor object to execute the query.
• Code Sample :

query = "UPDATE employees SET name = %s WHERE id = %s"
values = ("Jane", 1)
cursor.execute(query, values)

Step 5: Commit the changes

• Finally, make sure to commit the changes to the MySQL table 
• This can be done using the  commit()  method provided by mydb.
• Code Sample :

mydb.commit()

#Print message to confirm changes
print("Changes successfully saved to the database!")