Applications of all types commonly access MySQL to retrieve, add, update, or delete data. The applications might be written in Python, Java, C#, or another programming language. Most languages support multiple methods for working with a MySQL database and manipulating its data.
The approach you take when accessing MySQL will depend on the programming language you’re using and the connector you choose for interfacing with the database. Whatever approach you take, the same basic principles generally apply to each environment. You must establish a connection to the database and then issue the commands necessary to retrieve or modify the data.
Because MySQL can play such an important role in application development, I wanted to provide you with an overview how to access MySQL data from within your application code. This article demonstrates how to use the MySQL Connector from within Python to establish a connection and run a query. Although I focus on only one approach to accessing MySQL, the examples should give you a general idea of what it takes to connect to a database and retrieve the information you need.
When I created 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
If you want to try out the Python code snippets for yourself, you’ll need to set up a comparable environment, including the MySQL Connector 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.
Note: If you want to follow along, the table definition and initial set of data is included as an appendix to this article.
Defining a connection to MySQL
When connecting to a MySQL database in Python, you need to take several basic steps:
- Import the
connectmethod from the MySQL Connector module. - Use the
connectmethod to create a connection object that includes your connection details. - Use the connection object to run your data-related code.
- Close the connection.
When you create your connection, Python will generate either a MySQLConnection object or CMySQLConnection object. The object generated depends on whether you have the Connector C extension installed on your system. The extension interfaces with the MySQL C client library, which can help improve performance for large result sets. Because the extension was installed on my system, Python generated a CMySQLConnection object when I ran the example code; otherwise, it would have generated a MySQLConnection object.
You can find more information about the C extension here, but you don’t really need to worry about it for the exercises in this article. I point it out only so you’ll understand why your IDE might indicate one object type over the other, if you happen to notice the difference. With that in mind, take a look at the following Python code snippet, which establishes a connection to the travel database:
# import the connect method
from mysql.connector import connect
# define a connection object
conn = connect(
user = 'root',
password = 'SqlPW_py@310!ab',
host = 'localhost',
database = 'travel')
print('A connection object has been created.')
# close the database connection
conn.close()
Note: Be careful where you include your password, especially the root user password in your code. 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.
The code starts by running a from statement that imports the connect method from the MySQL Connector module (mysql.connector). The code then uses the method to create a connection object, which is assigned to the conn variable. In this case, the connect method takes four arguments: user, password, host, and database, all of which should be self-explanatory. The four arguments are enclosed in parentheses and separated by commas.
The connect method supports many more arguments than what I’ve included here. You can find details about the arguments in this MySQL topic, along with other information about connecting to MySQL.
After creating the connection object, I run a print statement. I added this statement primarily as a placeholder to show you where you would normally insert your data-related code, which I’ll be discussing later in the article. For now, the print statement simply returns a sentence stating that a connection object has been created.
The final step is to close the connection. For this, you need only call the close method on the connection object. Otherwise, that’s all you need to do to establish a connection with a MySQL database. In most cases, you should find the process fairly straightforward.
For more details on connecting to MySQL via the connect method of the connector object, check here in the MySQL documentation.
Reading connection data from a file
In the previous example, the connect method included the arguments necessary to connect to the MySQL instance. However, the method can also pull the arguments from a MySQL option file, which is a text file that usually takes the .cnf extension. To demonstrate how this works, I created a file named connectors.cnf and added the following information to the file:
[client] user = root password = SqlPW_py@310!ab host = localhost database = travel
These are the same arguments you saw in the previous example, without the quotation marks or commas. The arguments are grouped together under the [client] option group. By default, the connect method reads from the [client] and [connector_python] groups in the referenced option file.
Once you have your option file in place, you can access it from within your Python code by specifying the option_files argument in your connect method, as shown in the following example:
# import the connect method
from mysql.connector import connect
# define a connection object
conn = connect(option_files =
'/users/mac/documents/config/connectors.cnf')
# verify the connection information
print('The user ' + conn.user + ' is connected to the '
+ conn.database + ' database.')
# close the database connection
conn.close()
Instead of listing the individual arguments in the connect method, I’ve added the option_files argument, along with the path where I saved the connectors.cnf file. When I ran the Python code, it pulled the connection information from the file and used it when creating the connection object.
Note: My use of an option file is by no means meant to suggest that this approach is enough to secure your Python/MySQL connections. As stated previously, this code is to demo the capabilities that are possible.
For this example, I also modified the print statement. I did this to verify that I’m pulling the correct information from the option file. The statement returns the name of the user and database, which I assumed was enough to verify that everything was working properly. To retrieve this information, I used the conn variable to specify the user property and database property, which are defined on the connection object. I then concatenated these values with the string values necessary to make up the returned statement.
Catching errors in your MySQL connection
When accessing MySQL from your Python applications, you can include exception handling that’s specific to your MySQL connection. To add exception handling, enclose the connection code in a try block and then add an except statement to catch any MySQL errors, as shown in the following example:
# import the connect and Error methods
from mysql.connector import connect, Error
# try to run the block of code
try:
# define a connection object
conn = connect(option_files =
'/users/mac3/documents/config/connectors.cnf')
# verify the connection information
print('The user ' + conn.user + ' is connected to the '
+ conn.database + ' database.')
# close the database connection
conn.close()
# catch exception and print error message
except Error as err:
print('Error message: ' + err.msg)
Notice that the code now imports the Error method, along with the connect method. The code then includes a try block that contains the connection-related code and the print statement. This is the same code you saw in the previous example.
After the try block, the code includes an except statement that uses the Error method to catch the MySQL exception and create an error object, which is assigned to the err alias. This is followed by a print statement that returns the contents of the msg property that is defined on the error object.
When you run this code, Python will first try to execute the code in the try block. If there are no errors, the code runs until completion (when the connection is closed). If a MySQL error is encountered, code execution stops, and Python returns the error message. For example, if the database doesn’t exist, you’ll receive a message stating that the specified database is unknown.
Note that the error handling I’ve included here is specific to the MySQL Connector. If an error occurs for another reason, such as a problem in the Python syntax, the error will not be returned by the print statement. Python will treat the error as it would normally treat other types of errors. If you want to include error handling for the Python code itself, you must specifically add it to your application.
Querying data in a MySQL database
Up to this point, the examples in this article have been concerned only with connecting to a MySQL database, essentially verifying that such connections are possible. In the real world, however, you’ll want to do a lot more with your applications than simply check whether you can connect to a database. One of the most common scenarios is to retrieve data from the database, which you can do in a few basic steps:
- Use the connection object to invoke the
cursormethod and create a cursor object. - Define your query and save it to a variable.
- Use the cursor object to invoke the
executemethod and run your query. - Use the cursor object to invoke a method such as
fetchallto capture the query results and save them to a variable. - Use the variable with the results to carry out other data-specific operations.
- Close the cursor.
These steps are in addition to establishing the basic connection. In fact, you need the connection object to create your cursor, which makes it possible to run queries against the target database. The following Python code shows an example of how I used these steps to retrieve data from the airplanes table in the travel database:
# import the connect and Error methods
from mysql.connector import connect, Error
# try to run the block of code
try:
# define a connection object
conn = connect(option_files =
'/users/mac3/documents/config/connectors.cnf')
# open cursor, define and run query, fetch results
cursor = conn.cursor()
query = 'SELECT plane_id, plane, max_weight FROM airplanes'
cursor.execute(query)
result = cursor.fetchall()
# print the results in each row
for r in result:
print(r)
# close the cursor and database connection
cursor.close()
conn.close()
# catch exception and print error message
except Error as err:
print('Error message: ' + err.msg)
After defining the connection, I used the conn variable to call the cursor method and create a cursor object, which I assigned to the cursor variable. I then defined a SELECT statement and assigned it to the query variable.
Next, I used the cursor object to call the execute method, passing in the query variable in as an argument. I then used the cursor object to call the fetchall method in order to retrieve all the rows returned by the SELECT statement. You can also use the fetchall method to fetch a certain number of rows if the results of the query are very large. Then you would fetch N rows, the output those rows, then fetch N more rows. Finally, I assigned the results to the result variable, which stores the rows of data as a series of tuples.
After you capture the result set in a variable, you can use the variable’s data in your application however you need to. For this example, I created a simple for loop that iterates through the result set and assigns data to the r variable one row at a time. The contents of the r variable are then printed out for each loop. Finally, I used the cursor object to call the close method in order to close the cursor. On my system, Python returned the following results after I ran the code:
(1001, 'A340-600', 837756) (1002, 'A350-800 XWB', 546700) (1003, 'A350-900', 617295) (1004, 'A380-800', 1267658) (1005, 'A380-843F', 1300000) (1006, 'A.109 Airedale', 2750) (1007, 'A.61 Terrier', 2400) (1008, 'B.121 Pup', 1600) (1009, 'B.206', 7500) (1010, 'D.5-108 Husky', 2400) (1011, 'Baron 56 TC Turbo Baron', 5990) (1012, 'Baron 58 (and current G58)', 5500) (1013, 'Beechjet 400 (same as MU-300-10 Diamond II)', 15780) (1014, 'Bonanza 33 (F33A)', 3500) (1015, 'Bonanza 35 (G35)', 3125) (1016, '747-8F', 987000) (1017, '747-SP', 696000) (1018, '757-300', 270000) (1019, '767-200', 315000) (1020, '767-200ER', 395000)
Once you understand the logic of how to retrieve data, the process itself is fairly straightforward. In many cases, however, your SELECT statement will be much longer than the one in this example. If you want to spread your statement over multiple lines, you can enclose each line in quotes and then enclose all lines in parentheses, as shown in the following example:
# import the connect and Error methods
from mysql.connector import connect, Error
# try to run the block of code
try:
# define a connection object
conn = connect(option_files =
'/users/mac3/documents/config/connectors.cnf')
# open cursor, define and run query, fetch results
cursor = conn.cursor()
query = ('SELECT plane_id, plane, max_weight FROM airplanes '
'WHERE max_weight > 100000 '
'ORDER BY max_weight DESC')
cursor.execute(query)
result = cursor.fetchall()
# print the results in each row
for r in result:
print(r)
# close the cursor and database connection
cursor.close()
conn.close()
# catch exception and print error message
except Error as err:
print('Error message: ' + err.msg)
When your query spans multiple lines in this way, be sure to include the necessary space at the end of each line (except the last) so your statement is submitted to MySQL correctly. For this example, the Python code returns the following results:
(1005, ‘A380-843F’, 1300000) (1004, ‘A380-800’, 1267658) (1016, ‘747-8F’, 987000) (1001, ‘A340-600’, 837756) (1017, ‘747-SP’, 696000) (1003, ‘A350-900’, 617295) (1002, ‘A350-800 XWB’, 546700) (1020, ‘767-200ER’, 395000) (1019, ‘767-200’, 315000) (1018, ‘757-300’, 270000)
As you can see, the results include only airplanes with a max_weight value greater than 10000. The results are also sorted in descending order based on the max_weight values.
Passing variable values into your query
When using the MySQL Connector to query data, it can often be useful to pass variable values into your SELECT statement (or whichever SQL statement you’re defining). You can do this by including special %s markers as placeholders where the variable values should be inserted. The following example shows a SELECT statement that contains two of these markers:
# import the connect and Error methods
from mysql.connector import connect, Error
# try to run the block of code
try:
# define a connection object
conn = connect(option_files =
'/users/mac3/documents/config/connectors.cnf')
# open cursor and define query
cursor = conn.cursor()
query = ('SELECT plane_id, plane, max_weight FROM airplanes '
'WHERE max_weight BETWEEN %s AND %s '
'ORDER BY max_weight DESC')
# define input variables
weight_start = 1000
weight_end = 10000
# pass variables into query and fetch results
cursor.execute(query, (weight_start, weight_end))
result = cursor.fetchall()
# print the results in each row
for r in result:
print(r)
# close the cursor and database connection
cursor.close()
conn.close()
# catch exception and print error message
except Error as err:
print('Error message: ' + err.msg)
I added the %s markers to the WHERE clause as part of a BETWEEN operator. The variable values will be inserted in place of the markers when the SELECT statement is executed.
For this example, I hard-coded the variable values and assigned them to the weight_start and weight_end variables. In most cases, you’ll likely want to capture these values through user interaction or other means and then assign them dynamically to the variables at runtime, but for now, hard-coding them is enough to demonstrate how this process works.
After you define your variables, you can pass them in as an argument to the execute method that runs your query. The variables should be added as a second argument, enclosed in parenthesis and separated with a comma. In addition, they must be specified in the same order that you want them applied to the query. The Python code should now return the following results:
(1009, 'B.206', 7500) (1011, 'Baron 56 TC Turbo Baron', 5990) (1012, 'Baron 58 (and current G58)', 5500) (1014, 'Bonanza 33 (F33A)', 3500) (1015, 'Bonanza 35 (G35)', 3125) (1006, 'A.109 Airedale', 2750) (1007, 'A.61 Terrier', 2400) (1010, 'D.5-108 Husky', 2400) (1008, 'B.121 Pup', 1600)
As expected, the results include only those airplanes with a max_weight value between 1000 and 10000. If you had built your application to capture user input, the user might have specified different variable values, which would have been passed into the application dynamically.
Getting started with Python and MySQL
In this article, I’ve introduced you to how to use the MySQL Connector in Python to access a MySQL database and retrieve data from one of its tables. As with most aspects of application development, the approach I’ve taken here is only one of a variety of methods you can use to achieve the same results. You could have constructed your code differently, used a different connector, or even used a different programming language. Even so, what I’ve shown you should still give you a general idea of how to retrieve MySQL data within your application.
Be aware, however, that this article is by no means meant to be a comprehensive tutorial on how to use Python to develop a data-driven application. Although Python might be considered a lightweight language when compared to a one such as Java or C#, it is still a comprehensive language with many of its own complexities and nuances. Before you build an application that connects to MySQL, you should have a solid foundation in the language and what it takes to create an efficient and secure application. This article is meant only as a starting point for accessing MySQL data, although it should be enough to show you how easily you can interface with a MySQL database and retrieve the necessary data.
Appendix: Preparing your MySQL environment
The Python examples in this article connect to the travel database on a local MySQL instance and retrieve data from the airplanes table, which includes a foreign key that references the manufacturers table. This is the same database and tables you saw in previous articles in this series, although I’ve limited the data that I inserted into the tables to only a small amount, which is all that’s needed to demonstrate the concepts in this article. If you plan to try out the 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. After you the create the tables, you can then run the following INSERT statements:
INSERT INTO manufacturers (manufacturer_id, manufacturer)
VALUES (101,'Airbus'), (102,'Beagle Aircraft Limited'),
(103,'Beechcraft'), (104,'Boeing');
INSERT INTO airplanes
(plane_id, plane, manufacturer_id, engine_type, engine_count,
wingspan, plane_length, max_weight, icao_code)
VALUES
(1001,'A340-600',101,'Jet',4,208.17,247.24,837756,'A346'),
(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'),
(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'),
(1011,'Baron 56 TC Turbo Baron',103,'Piston',2,37.83,28,5990,'BE56'),
(1012,'Baron 58 (and current G58)',103,'Piston',2,37.83,29.83,5500,'BE58'),
(1013,'Beechjet 400 (same as MU-300-10 Diamond II)',103,'Jet',2,43.5,48.42,15780,'BE40'),
(1014,'Bonanza 33 (F33A)',103,'Piston',1,33.5,26.67,3500,'BE33'),
(1015,'Bonanza 35 (G35)',103,'Piston',1,32.83,25.17,3125,'BE35'),
(1016,'747-8F',104,'Jet',4,224.42,250.17,987000,'B748'),
(1017,'747-SP',104,'Jet',4,195.67,184.75,696000,'B74S'),
(1018,'757-300',104,'Jet',2,124.83,178.58,270000,'B753'),
(1019,'767-200',104,'Jet',2,156.08,159.17,315000,'B762'),
(1020,'767-200ER',104,'Jet',2,156.08,159.17,395000,'B762');
The INSERT statements first populate the manufacturers table and then the airplanes table. Be sure to run the statements in the order shown here because of the foreign key.
The post Retrieving MySQL data from within Python appeared first on Simple Talk.
from Simple Talk https://ift.tt/QvBLGOw
via
No comments:
Post a Comment