How to Update Values in MySQL Table using Python

Often times you’ll need to update values in MySQL table using Python.

How would you then accomplish that task?

In this short tutorial, I’ll show you the steps to update values in MySQL table using Python.

But before we begin, here is the general syntax that can be used to update values in MySQL:

 

UPDATE Database_Name.Table_Name
SET Column1_Namevalue1,  Column2_Name = value2, ...
WHERE condition

 

We’ll later see how to apply that generic syntax using a simple example.

Steps to Update Values in MySQL Table using Python

Step 1: Create a database and table in MySQL:

Suppose that you created a simple database in MySQL, where:

  • The database name is: test_database
  • This database has only one table called: names_table
  • The names_table contains the following data and fields:

 

First_NameLast_NameAge
JonSnow22
MariaSmith34
EmmaJones51
BillYu63
JackGreen27

 

We’ll later see how to update one of the records within the names_table.

Step 2: Confirm that the data is reflected correctly in MySQL

You may want to run the following SELECT query in MySQL to ensure that the records in the table are reflected correctly:

 

SELECT * FROM test_database.names_table

 

And this is the result that you should see in MySQL:

 

How to Update Values in MySQL Table using Python

Step 3: Connect Python to MySQL

Next, you’ll need to establish a connection between MySQL and Python.

To establish such a connection, you’ll need to specify the:

  • host – in my case, the host name is: ‘localhost’
  • user – the user that I used is: ‘root’
  • passwd – for instance, that password that I used is: ‘1q2w3e4r’
  • db – which is the database name. In our example, it is: ‘test_database’

You’ll need to adjust those 4 parameters to fit your connection information.

You may want to check the following source to find out more about establishing a connection between MySQL and Python.

Here is the Python code that I used:

 

import MySQLdb
 
db = MySQLdb.connect(host='localhost',    
                     user='root',         
                     passwd='1q2w3e4r',  
                     db='test_database')        
 
cur = db.cursor()
 
cur.execute('SELECT * FROM test_database.names_table')
 
for row in cur.fetchall():
    print row
 
db.close()

 

Once I ran that code, I got the following result, which matches with the data that we saw in steps 1 and 2:

 

Update Values in MySQL Table using Python

Step 4: Update values in MySQL table using Python

Let’s say that you want to update the second record (associated with Maria), where:

  • The last name will be updated to ‘Smith-Jackson’
  • The age will be updated to 35

You may then use the generic syntax to update values in MySQL (which we saw at the beginning of this tutorial).

And here is the full code to update values in MySQL table using Python:

 

import MySQLdb
 
db = MySQLdb.connect(host='localhost',    
                     user='root',         
                     passwd='1q2w3e4r',  
                     db='test_database')        
 
cur = db.cursor()
 
cur.execute('''
            UPDATE test_database.names_table
            
            SET Last_Name = 'Smith-Jackson',
                Age = 35
                
            WHERE First_Name = 'Maria'        
            ''')

db.commit()

 

Don’t forget to add db.commit() at the end of the code to make sure that the changes would be applied.

Step 5: Verify the results

You may want to run the following SELECT query in MySQL to verify that the record was updated:

 

SELECT * FROM test_database.names_table

 

As you can see, the second record was indeed updated:

 

Steps to Update Values in MySQL Table using Python