{"id":96508,"date":"2023-04-03T23:38:55","date_gmt":"2023-04-03T23:38:55","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=96508"},"modified":"2023-07-19T15:03:28","modified_gmt":"2023-07-19T15:03:28","slug":"modifying-mysql-data-from-within-python","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/mysql\/modifying-mysql-data-from-within-python\/","title":{"rendered":"Modifying MySQL data from within Python"},"content":{"rendered":"

This article is part of Robert Sheldon's continuing series on Learning MySQL. To see all of the items in the series, click here<\/a>.<\/strong><\/p>\n<\/p>\n

In the previous article in this series<\/a>, I introduced you to how to access MySQL data from within a Python script. The article described how to use the MySQL Connector to establish a connection with a database and then retrieve data through that connection. In this article, I continue the discussion by demonstrating how to insert, update, and delete data in a MySQL database, again working with Python and the MySQL Connector.<\/p>\n

The process of modifying MySQL data from within Python is, in many respects, similar to querying data. You must define a connection object that links to the database and define a cursor object based on that connection. You can then use the cursor object to execute your SQL statement. If you are not familiar with these concepts, I recommend that you read my previous article before launching into this one.<\/p>\n

Although I focus specifically on Python and the MySQL Connector in this article (and in the last one), the process of connecting to a MySQL database and querying data is similar in most object-oriented programming environments, at least in the broader sense.<\/p>\n

Each combination of programming language and database connector is unique, and you must understand how they work in your specific environment to use them effectively. That said, seeing how to interface with MySQL from within Python can still provide you with a conceptual understanding of what database access looks like in a programming language.<\/p>\n

Note: The examples in this article are based on a specific MySQL and Python setup. The last section of the article\u2014\u201cAppendix: Preparing your Python and MySQL environment\u201d\u2014provides information about how I set up my system and includes a SQL script for creating the database and tables on which the examples are based.<\/p>\n

Adding data to a MySQL database from within Python<\/h2>\n

The steps you take to insert data in a MySQL database are similar to those you follow to update or delete data. In each case, you generally take the following steps:<\/p>\n

    \n
  1. Import the mysql.connector<\/code> module or specific its components.<\/li>\n
  2. Define a connection object that establishes a connection to the target database.<\/li>\n
  3. Use the connection object to invoke the cursor<\/code> method and create a cursor object.<\/li>\n
  4. Define a SQL statement and save it to a variable.<\/li>\n
  5. Use the cursor object to invoke the execute<\/code> method, passing in the variable as an argument.<\/li>\n
  6. Commit your changes to the database.<\/li>\n
  7. Close the cursor and the connection.<\/li>\n<\/ol>\n

    These steps are, of course, a simplification of the process, but they should provide you with an idea of what you\u2019re trying to achieve. You can see these steps in the following Python script, which runs an INSERT<\/code> statement against the manufacturers<\/code> table in the travel<\/code> database:<\/p>\n

    # import connect and Error modules\r\nfrom mysql.connector import connect, Error\r\n\r\n# define connection object\r\nconn = connect(\r\n  user='root',\r\n  password='mysql_PW@327!xx',\r\n  host='localhost',\r\n  database='travel')\r\n\r\n# open cursor\r\ncursor = conn.cursor()\r\n\r\n# try to run code block\r\ntry:\r\n  # define INSERT statement\r\n  add_manufacturer = ('INSERT INTO manufacturers '\r\n    '(manufacturer_id, manufacturer) '\r\n    'VALUES (101, \\'Airbus\\')')\r\n\r\n  # run INSERT statement\r\n  cursor.execute(add_manufacturer)\r\n\r\n  # commit transaction\r\n  conn.commit()\r\n\r\n# catch exception, roll back transaction, print error message\r\nexcept Error as err:\r\n  conn.rollback()\r\n  print('Error message: ' + err.msg)\r\n\r\n# close cursor, close connection\r\nfinally:\r\n  cursor.close()\r\n  conn.close()<\/pre>\n
    If you read the previous article, many of these elements should look familiar to you. The script starts by importing the connect<\/code> and Error<\/code> methods from the MySQL Connector module (mysql.connector<\/code>). Next, the script instantiates a connection object and assigns it to the conn<\/code> variable and then instantiates a cursor object and assigns it to the cursor<\/code> variable. This is followed by a try<\/code> block, where you define and run your SQL statement.<\/p>\n
    Note: I do want to remind you again that putting passwords, especially the root password, in a script that you store somewhere is not best security practice. A discussion of security is far beyond the scope of this article. It\u2019s up to you to take the steps necessary to secure your application from the wide range of potential threats that can put your data at risk.<\/p>\n
    One thing you might notice that\u2019s different in this script from the examples you saw in the previous article is that the connection and cursor objects are defined prior to the try<\/code> block, rather than within it. This lets you access the objects outside of the try<\/code> block, which I\u2019ll explain shortly.<\/p>\n
    In the try<\/code> block, I first defined the INSERT<\/code> statement and assigned it to the add_manufacturer<\/code> variable. I then used the cursor object to call the execute<\/code> method, passing in the variable in as an argument. Because it contains the INSERT<\/code> statement, the method will execute the statement when you run the script.<\/p>\n
    Next, I used the connection object to invoke the commit<\/code> method. You must specially commit your changes to the database because by default, the MySQL Connector turns off MySQL\u2019s autocommit feature, so your changes won\u2019t be implemented until you commit them. (Note: you can control this with autocommit property<\/a> on the connection object if you wish.)<\/p>\n
    I also used the connection object to invoke the rollback<\/code> method, which I included in the except<\/code> block. Because I defined the connection object outside of the try<\/code> block, I can use the object in other blocks to invoke methods such as rollback<\/code>. If a data-related error occurs, the transaction will be rolled back, undoing any changes that might have been made within the try<\/code> block.<\/p>\n
    Something else I\u2019ve included that wasn\u2019t in the previous article is a finally<\/code> block. A finally<\/code> block is often used with a try<\/code> block to run statements that should be executed whether or not an error occurs. In this way, you can ensure that the cursor and connection get closed even if there is a MySQL exception.<\/p>\n
    When you run the Python script the first time, it should insert the row in the manufacturers<\/code> table with no problem. However, if you try to run the script a second time, MySQL will return a duplicate key error because the manufacturer_id<\/code> value of 101<\/code> already exists. The manufacturer_id<\/code> column is the table\u2019s primary key, so a duplicate key error would be expected. In fact, this is an easy way to verify whether your script\u2019s MySQL error handing is working as it should.<\/p>\n
    In the previous article, I also demonstrated how to use the %s<\/code> marker as a placeholder within your SELECT<\/code> statements. The marker makes it possible to create more dynamic SELECT<\/code> statements based on user input. You can also use %s<\/code> markers in your INSERT<\/code> statements (as well as UPDATE<\/code> and DELETE<\/code> statements). The following Python script includes an INSERT<\/code> statement that contains nine %s<\/code> markers in the VALUES<\/code> clause:<\/p>\n
    # import connect and Error modules\r\nfrom mysql.connector import connect, Error\r\n\r\n# define connection object\r\nconn = connect(\r\n  user='root',\r\n  password='mysql_PW@327!xx',\r\n  host='localhost',\r\n  database='travel')\r\n\r\n# open cursor\r\ncursor = conn.cursor()\r\n\r\n# try to run code block\r\ntry:\r\n  # define INSERT statement\r\n  add_airplane = ('INSERT INTO airplanes '\r\n    '(plane_id, plane, manufacturer_id, engine_type, engine_count, '\r\n      'wingspan, plane_length, max_weight, icao_code) '\r\n    'VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)')\r\n\r\n  # define plane values for INSERT in a tuple\r\n  plane_values = (1001, 'A340-600', 101, 'Jet', 4, 208.17, 247.24, 837756, 'A346')\r\n\r\n  # run INSERT statement\r\n  cursor.execute(add_airplane, plane_values)\r\n\r\n  # commit transaction\r\n  conn.commit()\r\n\r\n# catch exception, roll back transaction, print error message\r\nexcept Error as err:\r\n  conn.rollback()\r\n  print('Error message: ' + err.msg)\r\n\r\n# close cursor, close connection\r\nfinally:\r\n  cursor.close()\r\n  conn.close()<\/pre>\n
    After defining the INSERT<\/code> statement, I created a tuple that contains the values that will be assigned to the markers, in the order they need to be inserted. I then assigned the tuple to the plane_values<\/code> variable. (A tuple is an immutable collection of objects.) In the real world, you\u2019ll likely want to capture the marker values through user interaction or other means, rather than hard-coding them in this way, but this approach should be enough to demonstrate how the markers work.<\/p>\n
    After defining my SQL statement and marker values, I used the cursor object to call the execute<\/code> method and run the INSERT<\/code> statement. I also included a second argument that specifies the plane_values<\/code> variable. As a result, the %s<\/code> markers in the INSERT<\/code> statement will be replaced with the tuple values during statement execution. When you run the script the first time, the row should be added to the airplanes<\/code> table with no problem.<\/p>\n
    In some cases, you\u2019ll want to add multiple rows to a table in a single operation. You can do this in Python by making a few adjustments to the code, as shown in the following script:<\/p>\n
    # import connect and Error modules\r\nfrom mysql.connector import connect, Error\r\n\r\n# define connection object\r\nconn = connect(\r\n  user='root',\r\n  password='mysql_PW@327!xx',\r\n  host='localhost',\r\n  database='travel')\r\n\r\n# open cursor\r\ncursor = conn.cursor()\r\n\r\n# try to run code block\r\ntry:\r\n  # define INSERT statement\r\n  add_airplanes = ('INSERT INTO airplanes '\r\n    '(plane_id, plane, manufacturer_id, engine_type, engine_count, '\r\n      'wingspan, plane_length, max_weight, icao_code) '\r\n    'VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)')\r\n\r\n  # define plane values for INSERT in an list of tuples\r\n  plane_values = [\r\n    (1002, 'A350-800 XWB', 101, 'Jet', 2, 212.42, 198.58, 546700, 'A358'),\r\n    (1003, 'A350-900', 101, 'Jet', 2, 212.42, 219.16, 617295, 'A359'),\r\n    (1004, 'A380-800', 101, 'Jet', 4, 261.65, 238.62, 1267658, 'A388'),\r\n    (1005, 'A380-843F', 101, 'Jet', 4, 261.65, 238.62, 1300000, 'A38F')]\r\n\r\n  # run INSERT statement\r\n  cursor.executemany(add_airplanes, plane_values)\r\n\r\n  # commit transaction\r\n  conn.commit()\r\n\r\n# catch exception, roll back transaction, print error message\r\nexcept Error as err:\r\n  conn.rollback()\r\n  print('Error message: ' + err.msg)\r\n\r\n# close cursor, close connection\r\nfinally:\r\n  cursor.close()\r\n  conn.close()<\/pre>\n
    The INSERT<\/code> statement in this script is the same one as in the previous example. However, the tuple assigned to the plane_values<\/code> variable has been replaced by a list that includes four tuples containing the %s<\/code> values for the INSERT<\/code> statement. Each tuple corresponds to one row of data that will be inserted into the airplanes<\/code> table.<\/p>\n
    Another difference in this example from the previous one is that the cursor object is now used to invoke the executemany<\/code> method rather than the execute<\/code> method. The executemany<\/code> method makes it easy to use a single INSERT<\/code> statement to add multiple rows to a table, while still taking advantage of the %s<\/code> markers. When you run this Python script, it should now add the four rows to the airplanes<\/code> table.<\/p>\n
    You can, of course, define an INSERT<\/code> statement that includes all the rows, without using the %s<\/code> markers, but then you lose the advantage of the markers and their potential for creating dynamic queries. Even so, it\u2019s good to know that you can create a single statement if you want to go this route.<\/p>\n
    You can also define multiple SQL statements within your Python code. For example, the following Python script adds a row to the manufacturers<\/code> table and then adds five rows to the airplanes<\/code> table for that manufacturer:<\/p>\n
    # import connect and Error modules\r\nfrom mysql.connector import connect, Error\r\n\r\n# define connection object\r\nconn = connect(\r\n  user='root',\r\n  password='mysql_PW@327!xx',\r\n  host='localhost',\r\n  database='travel')\r\n\r\n# open cursor\r\ncursor = conn.cursor()\r\n\r\n# try to run code block\r\ntry:\r\n  # define manufacturers INSERT statement\r\n  add_manufacturer = ('INSERT INTO manufacturers '\r\n    '(manufacturer_id, manufacturer) '\r\n    'VALUES (%s, %s)')\r\n\r\n  # define manufacturer values for INSERT\r\n  manufacturer_values = (102, 'Beagle Aircraft Limited')\r\n\r\n  # run manufacturers INSERT statement\r\n  cursor.execute(add_manufacturer, manufacturer_values)\r\n\r\n  # define airplanes INSERT statement\r\n  add_airplanes = ('INSERT INTO airplanes '\r\n    '(plane_id, plane, manufacturer_id, engine_type, engine_count, '\r\n      'wingspan, plane_length, max_weight, icao_code) '\r\n    'VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)')\r\n\r\n  # define plane values for INSERT\r\n  plane_values = [\r\n    (1006, 'A.109 Airedale', 102, 'Piston', 1, 36.33, 26.33, 2750, 'AIRD'),\r\n    (1007, 'A.61 Terrier', 102, 'Piston', 1, 36, 23.25, 2400, 'AUS6'),\r\n    (1008, 'B.121 Pup', 102, 'Piston', 1, 31, 23.17, 1600, 'PUP'),\r\n    (1009, 'B.206', 102, 'Piston', 2, 55, 33.67, 7500, 'BASS'),\r\n    (1010, 'D.5-108 Husky', 102, 'Piston', 1, 36, 23.17, 2400, 'D5')]\r\n\r\n  # run airplanes INSERT statement\r\n  cursor.executemany(add_airplanes, plane_values)\r\n\r\n  # commit transaction\r\n  conn.commit()\r\n\r\n# catch exception, roll back transaction, print error message\r\nexcept Error as err:\r\n  conn.rollback()\r\n  print('Error message: ' + err.msg)\r\n\r\n# close cursor, close connection\r\nfinally:\r\n  cursor.close()\r\n  conn.close()<\/pre>\n
    The script first defines the INSERT<\/code> statement for the manufacturers<\/code> table, adds a tuple for the statement\u2019s %s<\/code> values, and then invokes the execute<\/code> method to run the statement. Next, the script defines the INSERT<\/code> statement for the airplanes<\/code> table, adds a list of tuples for the %s<\/code> values, and then invokes the executemany<\/code> method to run the second statement. After running the two statements, the script commits the transaction.<\/p>\n
    Updating data in a MySQL database from within Python<\/h2>\n
    The process of updating MySQL data from within Python works much the same as inserting data, except that you define an UPDATE<\/code> statement instead of an INSERT<\/code> statement. For example, the following Python script modifies a row in the airplanes<\/code> table:<\/p>\n
    # import connect and Error modules\r\nfrom mysql.connector import connect, Error\r\n\r\n# define connection object\r\nconn = connect(\r\n  user='root',\r\n  password='mysql_PW@327!xx',\r\n  host='localhost',\r\n  database='travel')\r\n\r\n# open cursor\r\ncursor = conn.cursor()\r\n\r\n# try to run code block\r\ntry:\r\n  # define UPDATE statement\r\n  update_plane = (\"UPDATE airplanes \"\r\n    \"SET plane = 'D.5\/108 Husky' WHERE plane_id = %s\")\r\n\r\n  # define plane_id value for UPDATE as a tuple\r\n  id_value = (1010,)\r\n\r\n  # run UPDATE statement\r\n  cursor.execute(update_plane, id_value)\r\n\r\n  # commit transaction\r\n  conn.commit()\r\n\r\n# catch exception, roll back transaction, print error message\r\nexcept Error as err:\r\n  conn.rollback()\r\n  print('Error message: ' + err.msg)\r\n\r\n# close cursor, close connection\r\nfinally:\r\n  cursor.close()\r\n  conn.close()<\/pre>\n
    You should recognize most of the elements in this script. The UPDATE<\/code> statement changes the plane<\/code> value for the row that has a plane_id<\/code> value of 1010<\/code>. This in itself should be fairly straightforward, but take particular note of the id_value<\/code> variable and its assignment.<\/p>\n
    The UPDATE<\/code> statement includes only one %s<\/code> marker, so you need to define only one value outside the statement. In this case, however, the assigned value, 1010<\/code>, is followed by a comma and enclosed in parentheses. This is because the execute<\/code> method will accept only a list, tuple, or dictionary for the second argument, not a simple string or integer. To accommodate this requirement, I created a tuple by adding the trailing comma and then enclosing the value and comma in parentheses. Now I can then use the id_value<\/code> variable as the second argument of the execute<\/code> method.<\/p>\n
    When modifying MySQL data, you can create more complex SQL statements than what I showed you in the previous script. For example, the following UPDATE<\/code> statement includes a subquery in its WHERE<\/code> clause:<\/p>\n
    # import connect and Error modules\r\nfrom mysql.connector import connect, Error\r\n\r\n# define connection object\r\nconn = connect(\r\n  user='root',\r\n  password='mysql_PW@327!xx',\r\n  host='localhost',\r\n  database='travel')\r\n\r\n# open cursor\r\ncursor = conn.cursor()\r\n\r\n# try to run code block\r\ntry:\r\n  # define UPDATE statement\r\n  update_planes = (\"UPDATE airplanes \"\r\n    \"SET wingspan = ROUND(wingspan), plane_length = ROUND(plane_length) \"\r\n    \"WHERE manufacturer_id = \"\r\n      \"(SELECT manufacturer_id FROM manufacturers \"\r\n      \"WHERE manufacturer = %s)\")\r\n\r\n  # define plane_id value for UPDATE\r\n  manufacturer = ('Beagle Aircraft Limited',)\r\n\r\n  # run UPDATE statement\r\n  cursor.execute(update_planes, manufacturer)\r\n\r\n  # commit transaction\r\n  conn.commit()\r\n\r\n# catch exception, roll back transaction, print error message\r\nexcept Error as err:\r\n  conn.rollback()\r\n  print('Error message: ' + err.msg)\r\n\r\n# close cursor, close connection\r\nfinally:\r\n  cursor.close()\r\n  conn.close()<\/pre>\n
    The subquery retrieves the manufacturer_id<\/code> value for the manufacturer specified in the manufacturer<\/code> variable. The manufacturer_id<\/code> value is then used to determine which rows in the airplanes<\/code> table to update. (I covered subqueries in an earlier article<\/a> in this series, so refer to that if you have any questions about how they work.)<\/p>\n
    In the previous two examples, the UPDATE<\/code> statements included only one %s<\/code> marker, but you can include multiple markers in your statements, as in the following Python script:<\/p>\n
    # import connect and Error modules\r\nfrom mysql.connector import connect, Error\r\n\r\n# define connection object\r\nconn = connect(\r\n  user='root',\r\n  password='mysql_PW@327!xx',\r\n  host='localhost',\r\n  database='travel')\r\n\r\n# open cursor\r\ncursor = conn.cursor()\r\n\r\n# try to run code block\r\ntry:\r\n  # define UPDATE statement\r\n  update_plane = (\"UPDATE airplanes \"\r\n    \"SET wingspan = wingspan + %s, plane_length = plane_length + %s \"\r\n    \"WHERE plane_id = %s\")\r\n\r\n  # define values for UPDATE in a tuple\r\n  plane_values = (5, 8, 1005)\r\n\r\n  # run UPDATE statement\r\n  cursor.execute(update_plane, plane_values)\r\n\r\n  # commit transaction\r\n  conn.commit()\r\n\r\n  # define SELECT statement\r\n  select_query = (\r\n    \"SELECT plane_id, plane, wingspan, plane_length \"\r\n    \"FROM airplanes WHERE plane_id = %s\")\r\n\r\n  # run SELECT statement\r\n  cursor.execute(select_query, (plane_values[2],))\r\n  results = cursor.fetchone()\r\n\r\n  # print query results\r\n  print('Rows updated:', cursor.rowcount)\r\n  print('plane_id:', results[0])\r\n  print('plane:', results[1])\r\n  print('new wingspan:', results[2])\r\n  print('new plane_length:', results[3])\r\n\r\n# catch exception, roll back transaction, print error message\r\nexcept Error as err:\r\n  conn.rollback()\r\n  print('Error message: ' + err.msg)\r\n\r\n# close cursor, close connection\r\nfinally:\r\n  cursor.close()\r\n  conn.close()<\/pre>\n
    The UPDATE<\/code> statement contains two %s<\/code> markers in the SET<\/code> clause and one in the WHERE<\/code> clause. The values for these markers are in a tuple that I assigned to the plane_values<\/code> variable. You can use as many %s<\/code> markers as needed in whatever clauses you deem necessary. Just remember to specify their values in the correct order so they match the statement.<\/p>\n
    After I ran the UPDATE<\/code> statement and called the commit<\/code> method, I defined a SELECT<\/code> statement to retrieve the newly updated data from the airplanes<\/code> table. I did this as a way to verify that the data had been correctly modified.<\/p>\n
    When I called the execute<\/code> method to run the SELECT<\/code> statement, I passed in the value from the plane_values<\/code> tuple, specifying 2<\/code> as the value\u2019s index number. (Tuples use a 0-based index.) I also added a comma after the index number and enclosed the entire value in parentheses so it would be treated as its own tuple, as required by the execute<\/code> method.<\/p>\n
    I then used the cursor object to run the fetchone<\/code> method, which retrieves the current row from the query results. Next, I saved the row of values\u2014returned as a single tuple\u2014to the results<\/code> variable.<\/p>\n
    I followed this with a series of print<\/code> statements that display information about the update. The first print<\/code> statement calls the rowcount<\/code> property on the cursor object. The property shows the number of rows that were affected by the UPDATE<\/code> statement. This is followed by four more print<\/code> statements, one for each value in the row returned by the SELECT<\/code> statement. Each print<\/code> statement uses the tuple\u2019s index to specify which value to include. The returned information should look similar to the following:<\/p>\n
    Rows updated: 1\r\nplane_id: 1005\r\nplane: A380-843F\r\nnew wingspan: 266.65\r\nnew plane_length: 246.62<\/pre>\n
    The original wingspan<\/code> value was 261.65<\/code>, and the original plane_length<\/code> value was 238.62<\/code>. When you add 5 and 8 to these values, respectively, you get the new totals returned by the script. You can also add logic to your script to capture the old values before updating them. In that way, you can return both the old and new values at the end of your script and pass those onto your application.<\/p>\n
    Deleting data in a MySQL database from within Python<\/h2>\n
    The process of deleting MySQL data from within Python is much the same as inserting or updating it. For example, the following Python script deletes a row from the airplanes<\/code> table:<\/p>\n
    # import connect and Error modules\r\nfrom mysql.connector import connect, Error\r\n\r\n# define connection object\r\nconn = connect(\r\n  user='root',\r\n  password='mysql_PW@327!xx',\r\n  host='localhost',\r\n  database='travel')\r\n\r\n# open cursor\r\ncursor = conn.cursor()\r\n\r\n# try to run code block\r\ntry:\r\n  # define DELETE statement\r\n  delete_plane = (\"DELETE FROM airplanes \"\r\n    \"WHERE plane_id = %s\")\r\n\r\n  # define plane_id value for DELETE in a tuple\r\n  plane_id = (1010,)\r\n\r\n  # run DELETE statement\r\n  cursor.execute(delete_plane, plane_id)\r\n  print('Number of rows deleted: ', cursor.rowcount)\r\n\r\n  # commit transaction\r\n  conn.commit()\r\n\r\n# catch exception, roll back transaction, print error message\r\nexcept Error as err:\r\n  conn.rollback()\r\n  print('Error message: ' + err.msg)\r\n\r\n# close cursor, close connection\r\nfinally:\r\n  cursor.close()\r\n  conn.close()<\/pre>\n
    At this point, you should be familiar with all the elements in this script. I defined a DELETE<\/code> statement and assigned it to the delete_plane<\/code> variable. I then assigned a value to the plane_id<\/code> variable, which will be used for the %s<\/code> marker. Next, I executed the statement, specifying the two variables, and added a print<\/code> statement to display the number of rows that were deleted.<\/p>\n
    Overall, there\u2019s nothing really new about this script except for the use of a DELETE<\/code> statement rather than INSERT<\/code> or UPDATE<\/code>. I simply wanted to show you what deleting data might look like. You can make your DELETE<\/code> statements as complex as necessary and use more %s<\/code> markers where needed, as in the next example:<\/p>\n
    # import connect and Error modules\r\nfrom mysql.connector import connect, Error\r\n\r\n# define connection object\r\nconn = connect(\r\n  user='root',\r\n  password='mysql_PW@327!xx',\r\n  host='localhost',\r\n  database='travel')\r\n\r\n# open cursor\r\ncursor = conn.cursor()\r\n\r\n# try to run code block\r\ntry:\r\n  # define DELETE statement\r\n  delete_plane = (\"DELETE FROM airplanes \"\r\n    \"WHERE manufacturer_id = %(mfc_id)s AND max_weight < %(weight)s\")\r\n\r\n  # define values for DELETE in a tuple\r\n  plane_values = {'mfc_id': 102, 'weight': 5000}\r\n\r\n  # run DELETE statement\r\n  cursor.execute(delete_plane, plane_values)\r\n  print('Number of rows deleted: ', cursor.rowcount)\r\n\r\n  # commit transaction\r\n  conn.commit()\r\n\r\n# catch exception, roll back transaction, print error message\r\nexcept Error as err:\r\n  conn.rollback()\r\n  print('Error message: ' + err.msg)\r\n\r\n# close cursor, close connection\r\nfinally:\r\n  cursor.close()\r\n  conn.close()<\/pre>\n
    In this script, I\u2019ve used a different form of the %s<\/code> markers. When I defined the marker values, I created a dictionary rather than a tuple or list. A dictionary makes it possible to assign a label to each value. You can then reference that label within your SQL statement. To do so, insert the label name, enclosed in parentheses, between the %<\/code> and the s<\/code>, as in %(mfc_id)s<\/code>. You can reference any value in the dictionary, without regard to their order.<\/p>\n
    Getting started with Python and MySQL data modifications<\/h2>\n
    In this article and the previous one, I\u2019ve tried to provide you with a foundation for using the MySQL Connector in your Python scripts. There is, of course, much more to the connector and MySQL that what I\u2019ve shown you here. There\u2019s also a lot more to writing data-driven scripts and applications. But the information I\u2019ve provided should at least help you better understand how to connect to a MySQL database from within Python and manipulate the data. You might also find this information useful when working with other programming languages, at least from a conceptual vantage point. Data is at the heart of most applications, and the more insight you have into how the pieces fit together, the greater you\u2019ll be able to appreciate what it takes to make these applications work.<\/p>\n
    Appendix: Preparing your MySQL and Python environment<\/h2>\n
    For the examples in this article, I used a Mac computer that was set up with the following components:<\/p>\n