In the previous article in this series, 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.
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.
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.
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.
Note: The examples in this article are based on a specific MySQL and Python setup. The last section of the article—“Appendix: Preparing your Python and MySQL environment”—provides 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.
Adding data to a MySQL database from within Python
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:
- Import the
mysql.connectormodule or specific its components. - Define a connection object that establishes a connection to the target database.
- Use the connection object to invoke the
cursormethod and create a cursor object. - Define a SQL statement and save it to a variable.
- Use the cursor object to invoke the
executemethod, passing in the variable as an argument. - Commit your changes to the database.
- Close the cursor and the connection.
These steps are, of course, a simplification of the process, but they should provide you with an idea of what you’re trying to achieve. You can see these steps in the following Python script, which runs an INSERT statement against the manufacturers table in the travel database:
# import connect and Error modules
from mysql.connector import connect, Error
# define connection object
conn = connect(
user='root',
password='mysql_PW@327!xx',
host='localhost',
database='travel')
# open cursor
cursor = conn.cursor()
# try to run code block
try:
# define INSERT statement
add_manufacturer = ('INSERT INTO manufacturers '
'(manufacturer_id, manufacturer) '
'VALUES (101, \'Airbus\')')
# run INSERT statement
cursor.execute(add_manufacturer)
# commit transaction
conn.commit()
# catch exception, roll back transaction, print error message
except Error as err:
conn.rollback()
print('Error message: ' + err.msg)
# close cursor, close connection
finally:
cursor.close()
conn.close()
If you read the previous article, many of these elements should look familiar to you. The script starts by importing the connect and Error methods from the MySQL Connector module (mysql.connector). Next, the script instantiates a connection object and assigns it to the conn variable and then instantiates a cursor object and assigns it to the cursor variable. This is followed by a try block, where you define and run your SQL statement.
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’s 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.
One thing you might notice that’s 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 block, rather than within it. This lets you access the objects outside of the try block, which I’ll explain shortly.
In the try block, I first defined the INSERT statement and assigned it to the add_manufacturer variable. I then used the cursor object to call the execute method, passing in the variable in as an argument. Because it contains the INSERT statement, the method will execute the statement when you run the script.
Next, I used the connection object to invoke the commit method. You must specially commit your changes to the database because by default, the MySQL Connector turns off MySQL’s autocommit feature, so your changes won’t be implemented until you commit them. (Note: you can control this with autocommit property on the connection object if you wish.)
I also used the connection object to invoke the rollback method, which I included in the except block. Because I defined the connection object outside of the try block, I can use the object in other blocks to invoke methods such as rollback. If a data-related error occurs, the transaction will be rolled back, undoing any changes that might have been made within the try block.
Something else I’ve included that wasn’t in the previous article is a finally block. A finally block is often used with a try 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.
When you run the Python script the first time, it should insert the row in the manufacturers 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 value of 101 already exists. The manufacturer_id column is the table’s primary key, so a duplicate key error would be expected. In fact, this is an easy way to verify whether your script’s MySQL error handing is working as it should.
In the previous article, I also demonstrated how to use the %s marker as a placeholder within your SELECT statements. The marker makes it possible to create more dynamic SELECT statements based on user input. You can also use %s markers in your INSERT statements (as well as UPDATE and DELETE statements). The following Python script includes an INSERT statement that contains nine %s markers in the VALUES clause:
# import connect and Error modules
from mysql.connector import connect, Error
# define connection object
conn = connect(
user='root',
password='mysql_PW@327!xx',
host='localhost',
database='travel')
# open cursor
cursor = conn.cursor()
# try to run code block
try:
# define INSERT statement
add_airplane = ('INSERT INTO airplanes '
'(plane_id, plane, manufacturer_id, engine_type, engine_count, '
'wingspan, plane_length, max_weight, icao_code) '
'VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)')
# define plane values for INSERT in a tuple
plane_values = (1001, 'A340-600', 101, 'Jet', 4, 208.17, 247.24, 837756, 'A346')
# run INSERT statement
cursor.execute(add_airplane, plane_values)
# commit transaction
conn.commit()
# catch exception, roll back transaction, print error message
except Error as err:
conn.rollback()
print('Error message: ' + err.msg)
# close cursor, close connection
finally:
cursor.close()
conn.close()
After defining the INSERT 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 variable. (A tuple is an immutable collection of objects.) In the real world, you’ll 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.
After defining my SQL statement and marker values, I used the cursor object to call the execute method and run the INSERT statement. I also included a second argument that specifies the plane_values variable. As a result, the %s markers in the INSERT 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 table with no problem.
In some cases, you’ll 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:
# import connect and Error modules
from mysql.connector import connect, Error
# define connection object
conn = connect(
user='root',
password='mysql_PW@327!xx',
host='localhost',
database='travel')
# open cursor
cursor = conn.cursor()
# try to run code block
try:
# define INSERT statement
add_airplanes = ('INSERT INTO airplanes '
'(plane_id, plane, manufacturer_id, engine_type, engine_count, '
'wingspan, plane_length, max_weight, icao_code) '
'VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)')
# define plane values for INSERT in an list of tuples
plane_values = [
(1002, 'A350-800 XWB', 101, 'Jet', 2, 212.42, 198.58, 546700, 'A358'),
(1003, 'A350-900', 101, 'Jet', 2, 212.42, 219.16, 617295, 'A359'),
(1004, 'A380-800', 101, 'Jet', 4, 261.65, 238.62, 1267658, 'A388'),
(1005, 'A380-843F', 101, 'Jet', 4, 261.65, 238.62, 1300000, 'A38F')]
# run INSERT statement
cursor.executemany(add_airplanes, plane_values)
# commit transaction
conn.commit()
# catch exception, roll back transaction, print error message
except Error as err:
conn.rollback()
print('Error message: ' + err.msg)
# close cursor, close connection
finally:
cursor.close()
conn.close()
The INSERT statement in this script is the same one as in the previous example. However, the tuple assigned to the plane_values variable has been replaced by a list that includes four tuples containing the %s values for the INSERT statement. Each tuple corresponds to one row of data that will be inserted into the airplanes table.
Another difference in this example from the previous one is that the cursor object is now used to invoke the executemany method rather than the execute method. The executemany method makes it easy to use a single INSERT statement to add multiple rows to a table, while still taking advantage of the %s markers. When you run this Python script, it should now add the four rows to the airplanes table.
You can, of course, define an INSERT statement that includes all the rows, without using the %s markers, but then you lose the advantage of the markers and their potential for creating dynamic queries. Even so, it’s good to know that you can create a single statement if you want to go this route.
You can also define multiple SQL statements within your Python code. For example, the following Python script adds a row to the manufacturers table and then adds five rows to the airplanes table for that manufacturer:
# import connect and Error modules
from mysql.connector import connect, Error
# define connection object
conn = connect(
user='root',
password='mysql_PW@327!xx',
host='localhost',
database='travel')
# open cursor
cursor = conn.cursor()
# try to run code block
try:
# define manufacturers INSERT statement
add_manufacturer = ('INSERT INTO manufacturers '
'(manufacturer_id, manufacturer) '
'VALUES (%s, %s)')
# define manufacturer values for INSERT
manufacturer_values = (102, 'Beagle Aircraft Limited')
# run manufacturers INSERT statement
cursor.execute(add_manufacturer, manufacturer_values)
# define airplanes INSERT statement
add_airplanes = ('INSERT INTO airplanes '
'(plane_id, plane, manufacturer_id, engine_type, engine_count, '
'wingspan, plane_length, max_weight, icao_code) '
'VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)')
# define plane values for INSERT
plane_values = [
(1006, 'A.109 Airedale', 102, 'Piston', 1, 36.33, 26.33, 2750, 'AIRD'),
(1007, 'A.61 Terrier', 102, 'Piston', 1, 36, 23.25, 2400, 'AUS6'),
(1008, 'B.121 Pup', 102, 'Piston', 1, 31, 23.17, 1600, 'PUP'),
(1009, 'B.206', 102, 'Piston', 2, 55, 33.67, 7500, 'BASS'),
(1010, 'D.5-108 Husky', 102, 'Piston', 1, 36, 23.17, 2400, 'D5')]
# run airplanes INSERT statement
cursor.executemany(add_airplanes, plane_values)
# commit transaction
conn.commit()
# catch exception, roll back transaction, print error message
except Error as err:
conn.rollback()
print('Error message: ' + err.msg)
# close cursor, close connection
finally:
cursor.close()
conn.close()
The script first defines the INSERT statement for the manufacturers table, adds a tuple for the statement’s %s values, and then invokes the execute method to run the statement. Next, the script defines the INSERT statement for the airplanes table, adds a list of tuples for the %s values, and then invokes the executemany method to run the second statement. After running the two statements, the script commits the transaction.
Updating data in a MySQL database from within Python
The process of updating MySQL data from within Python works much the same as inserting data, except that you define an UPDATE statement instead of an INSERT statement. For example, the following Python script modifies a row in the airplanes table:
# import connect and Error modules
from mysql.connector import connect, Error
# define connection object
conn = connect(
user='root',
password='mysql_PW@327!xx',
host='localhost',
database='travel')
# open cursor
cursor = conn.cursor()
# try to run code block
try:
# define UPDATE statement
update_plane = ("UPDATE airplanes "
"SET plane = 'D.5/108 Husky' WHERE plane_id = %s")
# define plane_id value for UPDATE as a tuple
id_value = (1010,)
# run UPDATE statement
cursor.execute(update_plane, id_value)
# commit transaction
conn.commit()
# catch exception, roll back transaction, print error message
except Error as err:
conn.rollback()
print('Error message: ' + err.msg)
# close cursor, close connection
finally:
cursor.close()
conn.close()
You should recognize most of the elements in this script. The UPDATE statement changes the plane value for the row that has a plane_id value of 1010. This in itself should be fairly straightforward, but take particular note of the id_value variable and its assignment.
The UPDATE statement includes only one %s marker, so you need to define only one value outside the statement. In this case, however, the assigned value, 1010, is followed by a comma and enclosed in parentheses. This is because the execute 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 variable as the second argument of the execute method.
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 statement includes a subquery in its WHERE clause:
# import connect and Error modules
from mysql.connector import connect, Error
# define connection object
conn = connect(
user='root',
password='mysql_PW@327!xx',
host='localhost',
database='travel')
# open cursor
cursor = conn.cursor()
# try to run code block
try:
# define UPDATE statement
update_planes = ("UPDATE airplanes "
"SET wingspan = ROUND(wingspan), plane_length = ROUND(plane_length) "
"WHERE manufacturer_id = "
"(SELECT manufacturer_id FROM manufacturers "
"WHERE manufacturer = %s)")
# define plane_id value for UPDATE
manufacturer = ('Beagle Aircraft Limited',)
# run UPDATE statement
cursor.execute(update_planes, manufacturer)
# commit transaction
conn.commit()
# catch exception, roll back transaction, print error message
except Error as err:
conn.rollback()
print('Error message: ' + err.msg)
# close cursor, close connection
finally:
cursor.close()
conn.close()
The subquery retrieves the manufacturer_id value for the manufacturer specified in the manufacturer variable. The manufacturer_id value is then used to determine which rows in the airplanes table to update. (I covered subqueries in an earlier article in this series, so refer to that if you have any questions about how they work.)
In the previous two examples, the UPDATE statements included only one %s marker, but you can include multiple markers in your statements, as in the following Python script:
# import connect and Error modules
from mysql.connector import connect, Error
# define connection object
conn = connect(
user='root',
password='mysql_PW@327!xx',
host='localhost',
database='travel')
# open cursor
cursor = conn.cursor()
# try to run code block
try:
# define UPDATE statement
update_plane = ("UPDATE airplanes "
"SET wingspan = wingspan + %s, plane_length = plane_length + %s "
"WHERE plane_id = %s")
# define values for UPDATE in a tuple
plane_values = (5, 8, 1005)
# run UPDATE statement
cursor.execute(update_plane, plane_values)
# commit transaction
conn.commit()
# define SELECT statement
select_query = (
"SELECT plane_id, plane, wingspan, plane_length "
"FROM airplanes WHERE plane_id = %s")
# run SELECT statement
cursor.execute(select_query, (plane_values[2],))
results = cursor.fetchone()
# print query results
print('Rows updated:', cursor.rowcount)
print('plane_id:', results[0])
print('plane:', results[1])
print('new wingspan:', results[2])
print('new plane_length:', results[3])
# catch exception, roll back transaction, print error message
except Error as err:
conn.rollback()
print('Error message: ' + err.msg)
# close cursor, close connection
finally:
cursor.close()
conn.close()
The UPDATE statement contains two %s markers in the SET clause and one in the WHERE clause. The values for these markers are in a tuple that I assigned to the plane_values variable. You can use as many %s markers as needed in whatever clauses you deem necessary. Just remember to specify their values in the correct order so they match the statement.
After I ran the UPDATE statement and called the commit method, I defined a SELECT statement to retrieve the newly updated data from the airplanes table. I did this as a way to verify that the data had been correctly modified.
When I called the execute method to run the SELECT statement, I passed in the value from the plane_values tuple, specifying 2 as the value’s 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 method.
I then used the cursor object to run the fetchone method, which retrieves the current row from the query results. Next, I saved the row of values—returned as a single tuple—to the results variable.
I followed this with a series of print statements that display information about the update. The first print statement calls the rowcount property on the cursor object. The property shows the number of rows that were affected by the UPDATE statement. This is followed by four more print statements, one for each value in the row returned by the SELECT statement. Each print statement uses the tuple’s index to specify which value to include. The returned information should look similar to the following:
Rows updated: 1 plane_id: 1005 plane: A380-843F new wingspan: 266.65 new plane_length: 246.62
The original wingspan value was 261.65, and the original plane_length value was 238.62. 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.
Deleting data in a MySQL database from within Python
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 table:
# import connect and Error modules
from mysql.connector import connect, Error
# define connection object
conn = connect(
user='root',
password='mysql_PW@327!xx',
host='localhost',
database='travel')
# open cursor
cursor = conn.cursor()
# try to run code block
try:
# define DELETE statement
delete_plane = ("DELETE FROM airplanes "
"WHERE plane_id = %s")
# define plane_id value for DELETE in a tuple
plane_id = (1010,)
# run DELETE statement
cursor.execute(delete_plane, plane_id)
print('Number of rows deleted: ', cursor.rowcount)
# commit transaction
conn.commit()
# catch exception, roll back transaction, print error message
except Error as err:
conn.rollback()
print('Error message: ' + err.msg)
# close cursor, close connection
finally:
cursor.close()
conn.close()
At this point, you should be familiar with all the elements in this script. I defined a DELETE statement and assigned it to the delete_plane variable. I then assigned a value to the plane_id variable, which will be used for the %s marker. Next, I executed the statement, specifying the two variables, and added a print statement to display the number of rows that were deleted.
Overall, there’s nothing really new about this script except for the use of a DELETE statement rather than INSERT or UPDATE. I simply wanted to show you what deleting data might look like. You can make your DELETE statements as complex as necessary and use more %s markers where needed, as in the next example:
# import connect and Error modules
from mysql.connector import connect, Error
# define connection object
conn = connect(
user='root',
password='mysql_PW@327!xx',
host='localhost',
database='travel')
# open cursor
cursor = conn.cursor()
# try to run code block
try:
# define DELETE statement
delete_plane = ("DELETE FROM airplanes "
"WHERE manufacturer_id = %(mfc_id)s AND max_weight < %(weight)s")
# define values for DELETE in a tuple
plane_values = {'mfc_id': 102, 'weight': 5000}
# run DELETE statement
cursor.execute(delete_plane, plane_values)
print('Number of rows deleted: ', cursor.rowcount)
# commit transaction
conn.commit()
# catch exception, roll back transaction, print error message
except Error as err:
conn.rollback()
print('Error message: ' + err.msg)
# close cursor, close connection
finally:
cursor.close()
conn.close()
In this script, I’ve used a different form of the %s 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 % and the s, as in %(mfc_id)s. You can reference any value in the dictionary, without regard to their order.
Getting started with Python and MySQL data modifications
In this article and the previous one, I’ve 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’ve shown you here. There’s also a lot more to writing data-driven scripts and applications. But the information I’ve 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’ll be able to appreciate what it takes to make these applications work.
Appendix: Preparing your MySQL and Python environment
For the examples in this article, I used a Mac computer that was set up with the following components:
- MySQL 8.0
- Python 3.10
- PyCharm Community Edition IDE
- MySQL Connector/Python 8.0 module
You can find information about installing the module in the MySQL documentation. On my system, I used pip to add the module. Pip is a package installer for Python that makes it very easy to deploy a module.
The Python examples in this article connect to the travel database on a local MySQL instance. The database contains the manufacturers table and airplanes table, which is defined with a foreign key that references the manufacturers table. This is the same database and tables you saw in previous articles in this series, except that you don’t need to insert any data.
If you plan to try out these examples, start by running the following script against your MySQL instance:
DROP DATABASE IF EXISTS travel;
CREATE DATABASE travel;
USE travel;
CREATE TABLE manufacturers (
manufacturer_id INT UNSIGNED NOT NULL,
manufacturer VARCHAR(50) NOT NULL,
create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_update TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (manufacturer_id) );
CREATE TABLE airplanes (
plane_id INT UNSIGNED NOT NULL,
plane VARCHAR(50) NOT NULL,
manufacturer_id INT UNSIGNED NOT NULL,
engine_type VARCHAR(50) NOT NULL,
engine_count TINYINT NOT NULL,
max_weight MEDIUMINT UNSIGNED NOT NULL,
wingspan DECIMAL(5,2) NOT NULL,
plane_length DECIMAL(5,2) NOT NULL,
parking_area INT GENERATED ALWAYS AS ((wingspan * plane_length)) STORED,
icao_code CHAR(4) NOT NULL,
create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
last_update TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (plane_id),
CONSTRAINT fk_manufacturer_id FOREIGN KEY (manufacturer_id)
REFERENCES manufacturers (manufacturer_id) );
The script will create the travel database and add the manufacturers and airplanes tables in the proper order to accommodate the foreign key defined in the airplanes table. If you already created the database and tables for previous articles, I recommend that you re-create them now to ensure that your key values line up correctly with the examples.
The post Modifying MySQL data from within Python appeared first on Simple Talk.
from Simple Talk https://ift.tt/O8RqGrA
via
No comments:
Post a Comment