python

    Python Update MySQL Table

    No Comments8 Mins Read

    Python Modifications to the MySQL Table

    This article demonstrates how to conduct a MySQL UPDATE query from within Python in order to make changes to the data contained within a MySQL table.

    The objectives of this class. Using something called a “MySQL Connector,” you will become familiar with the following MySQL UPDATE actions.

    module.

    • Update single and multiple rows, single and multiple columns
    • Use a Python variable in a parameterized query to update table rows.
    • Also, Update a column with date-time and timestamp values
    • The role of commit and rollback in the update operation.

    Table of contents

    Prerequisite

    Before you go ahead and run the following application, check to see that you have the following components set up.

    −


    • Username

      and

      password

      that you need to connect MySQL

    • MySQL database table name

      which you want to update.

    Example to Update a row of MySQL Table

    In order to execute a SQL UPDATE query from within Python, you will need to complete the steps below: –

    Tutorial on How to Update a Table in MySQL in

    Python


    1. Connect to MySQL from Python

      Refer to

      Python MySQL database connection

      to connect to MySQL database from Python using MySQL Connector module


    2. Prepare a SQL Update Query

      Prepare an update statement query with data to update. FOr example,

      UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;


    3. Execute the UPDATE query, using cursor.execute()

      Execute the UPDATE query using

      cursor.execute()

      method. This method execute the operation stored in the UPDATE query.


    4. Commit your changes

      Make modification persistent into a database using the

      commit()

      of a connection class.


    5. Extract the number of rows affected

      After a successful update operation, use a

      cursor.rowcount

      method to get the number of rows affected. The count depends on how many rows you are updating.


    6. Verify result using the SQL SELECT query

      Execute a

      MySQL select query from Python

      to see the new changes


    7. Close the cursor object and database connection object

      use

      cursor.clsoe()

      and

      connection.clsoe()

      method to close open connections after your work completes.

    Python will update the data in the MySQL table, image.

    Let’s check out the schedule now, shall we? Within this program, we are modifying a table about laptops by moving the price column from the first row to the second.

    row.

    import mysql.connector
try:
    connection = mysql.connector.connect(host='localhost',
                                         database='electronics',
                                         user='pynative',
                                         password='pynative@#29')
    cursor = connection.cursor()
    print("Before updating a record ")
    sql_select_query = """select * from Laptop where id = 1"""
    cursor.execute(sql_select_query)
    record = cursor.fetchone()
    print(record)
    # Update single record now
    sql_update_query = """Update Laptop set Price = 7000 where id = 1"""
    cursor.execute(sql_update_query)
    connection.commit()
    print("Record Updated successfully ")
    print("After updating record ")
    cursor.execute(sql_select_query)
    record = cursor.fetchone()
    print(record)
except mysql.connector.Error as error:
    print("Failed to update table record: {}".format(error))
finally:
    if connection.is_connected():
        connection.close()
        print("MySQL connection is closed")

    Run Output

    :

    Before updating a row
(1, 'Lenovo ThinkPad P71', 6459.0, datetime.date(2019, 8, 14))
Record Updated successfully 
After updating row
(1, 'Lenovo ThinkPad P71', 7000.0, datetime.date(2019, 8, 14))
MySQL connection is closed

    image MySQL table on the laptop after the record was updated

    Use a Python variable in MySQL Update query

    Sometimes we need input from the user, such when they update their password or any other details through the User Interface. In these cases, we ask for the user’s help. Or when you wish to dynamically change the details using Python variables that are passed into a query. For example, utilizing the variable to set the value of the column.

    It is always considered to be the best practice to utilize a parameterized query and prepared statement, also known as placeholders (%s), inside of any SQL statements that involve input from users. This is because these techniques ensure the most accurate results. This helps us avoid SQL injection as well as other SQL-related problems. Learn more about what a parameterized query is and the benefits it offers to performance by reading on.

    Let’s have a look at the sample program, shall we?

    now.

    import mysql.connector
def update_laptop_price(id, price):
    try:
        connection = mysql.connector.connect(host='localhost',
                                             database='electronics',
                                             user='roo',
                                             password='pynative@#29')
        cursor = connection.cursor()
        sql_update_query = """Update laptop set price = %s where id = %s"""
        input_data = (price, id)
        cursor.execute(sql_update_query, input_data)
        connection.commit()
        print("Record Updated successfully ")
    except mysql.connector.Error as error:
        print("Failed to update record to database: {}".format(error))
    finally:
        if connection.is_connected():
            cursor.close()
            connection.close()
            print("MySQL connection is closed")
update_laptop_price(7500, 1)
update_laptop_price(5000, 2)

    Run

    Output:

    –

    Record Updated successfully 
MySQL connection is closed
Record Updated successfully 
MySQL connection is closed

    Let’s get a grasp on the program that was just presented:

    –

    • We used the prepared statement to accept user input using a placeholder, i.e., we put two placeholders in the update query, one for the “Price” column and the other is for the” id” column.
    • Next, we added those two columns value in the tuple format in sequential order and passed SQL update query and input tuple to the

      cursor.execute()

      method. Remember tuple contains user data in the sequential order of placeholders.
    • In the end, we are committing our changes to the database using the

      connection.commit()

      .

    Update Multiple Rows of MySQL Table using Python

    One SQL query can be used to modify the contents of numerous rows at the same time. You might also refer to it as a mass upgrade. If you want to update numerous rows at once in a table, use the cursor.executemany() method of the cursor object. The syntax that is used by the executemany() method

    cursor.executemany(operation, seq_of_params)

    Run

    This method performs the action on each parameter sequence that is contained in the sequence referred to as seq_of_params.

    argument.

    import mysql.connector
try:
    connection = mysql.connector.connect(host='localhost',
                                         database='electronics',
                                         user='pynative',
                                         password='pynative@#29')
    cursor = connection.cursor()
    sql_update_query = """Update Laptop set Price = %s where id = %s"""
    # multiple records to be updated in tuple format
    records_to_update = [(3000, 3), (2750, 4)]
    cursor.executemany(sql_update_query, records_to_update)
    connection.commit()
    print(cursor.rowcount, "Records of a laptop table updated successfully")
except mysql.connector.Error as error:
    print("Failed to update records to database: {}".format(error))
finally:
    if connection.is_connected():
        connection.close()
        print("MySQL connection is closed")

    Run

    Output:

    2 Records of a laptop table updated successfully
connection is closed

    Let’s get a grasp on the program that was just mentioned.

    • We defined a SQL update query with two placeholders (“Price” and “Id” column ). and prepared a list of records to be updated. This List contains a tuple for each row. Here we created two tuples, so we are updated two rows.
    • We used the

      cursor.executemany()

      method to update multiple rows of a database table.
    • Using the

      cursor.rowcount

      we can find how many rows are updated successfully.

    Python update multiple Columns of MySQL table

    We also have the ability to use a single query to modify several columns of the MySQL table. If you want to change numerous columns at once, use a parameterized query with a placeholder. Let’s look at this with a concrete example.

    program.

    import mysql.connector
try:
    connection = mysql.connector.connect(host='localhost',
                                         database='electronics',
                                         user='pynative',
                                         password='pynative@#29')
    cursor = connection.cursor()
    sql_update_query = """Update Laptop set Name = %s, Price = %s where id = %s"""
    name = "HP Pavilion"
    price = 2200
    id = 4
    input = (name, price, id)
    cursor.execute(sql_update_query, input)
    connection.commit()
    print("Multiple columns updated successfully ")
except mysql.connector.Error as error:
    print("Failed to update columns of table: {}".format(error))
finally:
    if connection.is_connected():
        connection.close()
        print("MySQL connection is closed")

    Run

    Output:

    Multiple column updated successfully
MySQL connection is closed

    Update Datetime and timestamp column of a MySQL table from Python

    Imagine that you have a table in MySQL that contains a date column, and that you wish to update a datetime.into this column using the datetime() object. Let’s have a look at how to construct an update query in order to modify the datetime column of a database.

    table

    from datetime import datetime
import mysql.connector
try:
    connection = mysql.connector.connect(host='localhost',
                                         database='electronics',
                                         user='pynative',
                                         password='pynative@#29')
    cursor = connection.cursor()
    sql_update_query = """Update Laptop set Purchase_date = %s where id = %s"""
    current_Date = datetime.now()
    formatted_date = current_Date.strftime('%Y-%m-%d %H:%M:%S')
    id = 2
    input = (formatted_date, id)
    cursor.execute(sql_update_query, input)
    connection.commit()
    print("Purchased Date Updated successfully ")
except mysql.connector.Error as error:
    print("Failed to update purchased date {}".format(error))
finally:
    if connection.is_connected():
        connection.close()
        print("connection is closed")

    Run

    After carrying out the steps outlined above, you ought to see the results shown below.

    code.

    Purchased Date Updated successfully 
connection is closed

    Next Steps

    In order to put everything you’ve learned in this unit into practice, I recommend that you work through a Python Database Exercise project. This will help you become more familiar with the Python Database operations.

    Share.

    Related Posts

    Add A Comment

    Leave A Reply