{"id":96142,"date":"2023-03-02T21:48:13","date_gmt":"2023-03-02T21:48:13","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=96142"},"modified":"2026-05-08T08:36:43","modified_gmt":"2026-05-08T08:36:43","slug":"retrieving-mysql-data-python","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/mysql\/retrieving-mysql-data-python\/","title":{"rendered":"Python MySQL: How to Query &#038; Retrieve Data"},"content":{"rendered":"<p><strong>To retrieve data from a MySQL database in Python, install the MySQL Connector\/Python module, create a connection using mysql.connector.connect() with your host, user, password, and database, then use a cursor object to execute SQL queries and fetch results with fetchall(), fetchone(), or fetchmany(). The cursor converts query results into Python tuples by default, or dictionaries if you use cursor_class=MySQLCursorDict. <\/strong><\/p>\n<p><strong>This guide walks through each step with working code examples, including connection management, error handling, parameterized queries to prevent SQL injection, and reading connection details from option files.<\/strong><\/p>\n<p style=\"margin: 0in; font-family: Calibri; font-size: 11.0pt;\"><p><strong>This article is part of Robert Sheldon's continuing series on Learning MySQL. To see all of the items in the series, <a href=\"https:\/\/www.red-gate.com\/simple-talk\/mysql-coding-basics\/\">click here<\/a>.<\/strong><\/p>\n<\/p>\n<p>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.<\/p>\n<p>The approach you take when accessing MySQL will depend on the programming language you\u2019re 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.<\/p>\n<p>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.<\/p>\n<p>When I created the examples in this article, I used a Mac computer that was set up with the following components:<\/p>\n<ul>\n<li>MySQL 8.0<\/li>\n<li>Python 3.10<\/li>\n<li>PyCharm Community Edition IDE<\/li>\n<li>MySQL Connector\/Python 8.0 module<\/li>\n<\/ul>\n<p>If you want to try out the Python code snippets for yourself, you\u2019ll need to set up a comparable environment, including the MySQL Connector module. You can find information about installing the module in the <a href=\"https:\/\/dev.mysql.com\/doc\/connector-python\/en\/connector-python-installation.html\">MySQL documentation<\/a>. On my system, I used pip to add the module. <a href=\"https:\/\/packaging.python.org\/en\/latest\/key_projects\/&quot; \\l &quot;pip\">Pip<\/a> is a package installer for Python that makes it very easy to deploy a module.<\/p>\n<p>Note: If you want to follow along, the table definition and initial set of data is included as an appendix to this article.<\/p>\n<h2>Defining a connection to MySQL<\/h2>\n<p>When connecting to a MySQL database in Python, you need to take several basic steps:<\/p>\n<ol>\n<li>Import the <code>connect<\/code> method from the MySQL Connector module.<\/li>\n<li>Use the <code>connect<\/code> method to create a connection object that includes your connection details.<\/li>\n<li>Use the connection object to run your data-related code.<\/li>\n<li>Close the connection.<\/li>\n<\/ol>\n<p>When you create your connection, Python will generate either a <code>MySQLConnection<\/code> object or <code>CMySQLConnection<\/code> 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 <code>CMySQLConnection<\/code> object when I ran the example code; otherwise, it would have generated a <code>MySQLConnection<\/code> object.<\/p>\n<p>You can find more information about the C extension <a href=\"https:\/\/dev.mysql.com\/doc\/connector-python\/en\/connector-python-cext.html\">here<\/a>, but you don\u2019t really need to worry about it for the exercises in this article. I point it out only so you\u2019ll 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 <code>travel<\/code> database:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\"># import the connect method\n    from mysql.connector import connect\n\n# define a connection object\nconn = connect(\n      user = 'root',\n      password = 'SqlPW_py@310!ab',\n      host = 'localhost',\n      database = 'travel')\n\nprint('A connection object has been created.')\n\n# close the database connection\nconn.close()<\/pre>\n<p>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\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<p>The code starts by running a <code>from<\/code> statement that imports the <code>connect<\/code> method from the MySQL Connector module (<code>mysql.connector<\/code>). The code then uses the method to create a connection object, which is assigned to the <code>conn<\/code> variable. In this case, the <code>connect<\/code> method takes four arguments: <code>user<\/code>, <code>password<\/code>, <code>host<\/code>, and <code>database<\/code>, all of which should be self-explanatory. The four arguments are enclosed in parentheses and separated by commas.<\/p>\n<p>The <code>connect<\/code> method supports many more arguments than what I\u2019ve included here. You can find details about the arguments in this <a href=\"https:\/\/dev.mysql.com\/doc\/connectors\/en\/connector-python-connectargs.html\">MySQL topic<\/a>, along with other information about connecting to MySQL.<\/p>\n<p>After creating the connection object, I run a <code>print<\/code> statement. I added this statement primarily as a placeholder to show you where you would normally insert your data-related code, which I\u2019ll be discussing later in the article. For now, the <code>print<\/code> statement simply returns a sentence stating that a connection object has been created.<\/p>\n<p>The final step is to close the connection. For this, you need only call the <code>close<\/code> method on the connection object. Otherwise, that\u2019s all you need to do to establish a connection with a MySQL database. In most cases, you should find the process fairly straightforward.<\/p>\n<p>For more details on connecting to MySQL via the connect method of the connector object, <a href=\"https:\/\/dev.mysql.com\/doc\/connector-python\/en\/connector-python-example-connecting.html\">check here in the MySQL documentation.<\/a><\/p>\n<h2>Reading connection data from a file<\/h2>\n<p>In the previous example, the <code>connect<\/code> 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 <strong>connectors.cnf<\/strong> and added the following information to the file:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">[client]\nuser = root\npassword = SqlPW_py@310!ab\nhost = localhost\ndatabase = travel<\/pre>\n<p>These are the same arguments you saw in the previous example, without the quotation marks or commas. The arguments are grouped together under the <code>[client]<\/code> option group. By default, the <code>connect<\/code> method reads from the <code>[client]<\/code> and <code>[connector_python]<\/code> groups in the referenced option file.<\/p>\n<p>Once you have your option file in place, you can access it from within your Python code by specifying the <code>option_files<\/code> argument in your <code>connect<\/code> method, as shown in the following example:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\"># import the connect method\nfrom mysql.connector import connect\n\n# define a connection object\nconn = connect(option_files =\n  '\/users\/mac\/documents\/config\/connectors.cnf')\n\n# verify the connection information\nprint('The user ' + conn.user + ' is connected to the '\n       + conn.database + ' database.')\n\n# close the database connection\nconn.close()<\/pre>\n<p>Instead of listing the individual arguments in the <code>connect<\/code> method, I\u2019ve added the <code>option_files<\/code> argument, along with the path where I saved the <strong>connectors.cnf<\/strong> file. When I ran the Python code, it pulled the connection information from the file and used it when creating the connection object.<\/p>\n<p>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.<\/p>\n<p>For this example, I also modified the <code>print<\/code> statement. I did this to verify that I\u2019m 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 <code>conn<\/code> variable to specify the <code>user<\/code> property and <code>database<\/code> property, which are defined on the connection object. I then concatenated these values with the string values necessary to make up the returned statement.<\/p>\n<p><strong>Read also:<\/strong> <br \/><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/mysql\/exporting-data-from-a-mysql-database-using-selectinto-outfile\/\">Exporting MySQL data using SELECT INTO OUTFILE<\/a><\/p>\n<h2>Catching errors in your MySQL connection<\/h2>\n<p>When accessing MySQL from your Python applications, you can include exception handling that\u2019s specific to your MySQL connection. To add exception handling, enclose the connection code in a <code>try<\/code> block and then add an <code>except<\/code> statement to catch any MySQL errors, as shown in the following example:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\"># import the connect and Error methods\nfrom mysql.connector import connect, Error\n\n# try to run the block of code\ntry:\n\n  # define a connection object\n  conn = connect(option_files =\n    '\/users\/mac3\/documents\/config\/connectors.cnf')\n\n  # verify the connection information\n  print('The user ' + conn.user + ' is connected to the '\n    + conn.database + ' database.')\n\n  # close the database connection\n  conn.close()\n\n# catch exception and print error message\nexcept Error as err:\n  print('Error message: ' + err.msg)<\/pre>\n<p>Notice that the code now imports the <code>Error<\/code> method, along with the <code>connect<\/code> method. The code then includes a <code>try<\/code> block that contains the connection-related code and the <code>print<\/code> statement. This is the same code you saw in the previous example.<\/p>\n<p>After the <code>try<\/code> block, the code includes an <code>except<\/code> statement that uses the <code>Error<\/code> method to catch the MySQL exception and create an error object, which is assigned to the <code>err<\/code> alias. This is followed by a <code>print<\/code> statement that returns the contents of the <code>msg<\/code> property that is defined on the error object.<\/p>\n<p>When you run this code, Python will first try to execute the code in the <code>try<\/code> 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\u2019t exist, you\u2019ll receive a message stating that the specified database is unknown.<\/p>\n<p>Note that the error handling I\u2019ve 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 <code>print<\/code> 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.<\/p>\n<h2>Querying data in a MySQL database<\/h2>\n<p>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\u2019ll 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:<\/p>\n<ol>\n<li>Use the connection object to invoke the <code>cursor<\/code> method and create a cursor object.<\/li>\n<li>Define your query and save it to a variable.<\/li>\n<li>Use the cursor object to invoke the <code>execute<\/code> method and run your query.<\/li>\n<li>Use the cursor object to invoke a method such as <code>fetchall<\/code> to capture the query results and save them to a variable.<\/li>\n<li>Use the variable with the results to carry out other data-specific operations.<\/li>\n<li>Close the cursor.<\/li>\n<\/ol>\n<p>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 <code>airplanes<\/code> table in the <code>travel<\/code> database:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\"># import the connect and Error methods\nfrom mysql.connector import connect, Error\n\n# try to run the block of code\ntry:\n\n  # define a connection object\n  conn = connect(option_files =\n    '\/users\/mac3\/documents\/config\/connectors.cnf')\n\n  # open cursor, define and run query, fetch results\n  cursor = conn.cursor()\n  query = 'SELECT plane_id, plane, max_weight FROM airplanes'\n  cursor.execute(query)\n  result = cursor.fetchall()\n\n  # print the results in each row\n  for r in result:\n    print(r)\n\n  # close the cursor and database connection\n  cursor.close()\n  conn.close()\n\n# catch exception and print error message\nexcept Error as err:\n  print('Error message: ' + err.msg)<\/pre>\n<p>After defining the connection, I used the <code>conn<\/code> variable to call the <code>cursor<\/code> method and create a cursor object, which I assigned to the <code>cursor<\/code> variable. I then defined a <code>SELECT<\/code> statement and assigned it to the <code>query<\/code> variable.<\/p>\n<p>Next, I used the cursor object to call the <code>execute<\/code> method, passing in the <code>query<\/code> variable in as an argument. I then used the cursor object to call the <code>fetchall<\/code> method in order to retrieve all the rows returned by the <code>SELECT<\/code> statement. You can also use the <code>fetchall<\/code> 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 <code>result<\/code> variable, which stores the rows of data as a series of tuples.<\/p>\n<p>After you capture the result set in a variable, you can use the variable\u2019s data in your application however you need to. For this example, I created a simple <code>for<\/code> loop that iterates through the result set and assigns data to the <code>r<\/code> variable one row at a time. The contents of the <code>r<\/code> variable are then printed out for each loop. Finally, I used the cursor object to call the <code>close<\/code> method in order to close the cursor. On my system, Python returned the following results after I ran the code:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">(1001, 'A340-600', 837756)\n(1002, 'A350-800 XWB', 546700)\n(1003, 'A350-900', 617295)\n(1004, 'A380-800', 1267658)\n(1005, 'A380-843F', 1300000)\n(1006, 'A.109 Airedale', 2750)\n(1007, 'A.61 Terrier', 2400)\n(1008, 'B.121 Pup', 1600)\n(1009, 'B.206', 7500)\n(1010, 'D.5-108 Husky', 2400)\n(1011, 'Baron 56 TC Turbo Baron', 5990)\n(1012, 'Baron 58 (and current G58)', 5500)\n(1013, 'Beechjet 400 (same as MU-300-10 Diamond II)', 15780)\n(1014, 'Bonanza 33 (F33A)', 3500)\n(1015, 'Bonanza 35 (G35)', 3125)\n(1016, '747-8F', 987000)\n(1017, '747-SP', 696000)\n(1018, '757-300', 270000)\n(1019, '767-200', 315000)\n(1020, '767-200ER', 395000)<\/pre>\n<p>Once you understand the logic of how to retrieve data, the process itself is fairly straightforward. In many cases, however, your <code>SELECT<\/code> 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:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\"># import the connect and Error methods\nfrom mysql.connector import connect, Error\n\n# try to run the block of code\ntry:\n\n  # define a connection object\n  conn = connect(option_files =\n    '\/users\/mac3\/documents\/config\/connectors.cnf')\n\n  # open cursor, define and run query, fetch results\n  cursor = conn.cursor()\n  query = ('SELECT plane_id, plane, max_weight FROM airplanes ' \n    'WHERE max_weight &gt; 100000 ' \n    'ORDER BY max_weight DESC')\n  cursor.execute(query)\n  result = cursor.fetchall()\n\n  # print the results in each row\n  for r in result:\n    print(r)\n\n  # close the cursor and database connection\n  cursor.close()\n  conn.close()\n\n# catch exception and print error message\nexcept Error as err:\n  print('Error message: ' + err.msg)<\/pre>\n<p>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:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">(1005, \u2018A380-843F\u2019, 1300000)\n(1004, \u2018A380-800\u2019, 1267658)\n(1016, \u2018747-8F\u2019, 987000)\n(1001, \u2018A340-600\u2019, 837756)\n(1017, \u2018747-SP\u2019, 696000)\n(1003, \u2018A350-900\u2019, 617295)\n(1002, \u2018A350-800 XWB\u2019, 546700)\n(1020, \u2018767-200ER\u2019, 395000)\n(1019, \u2018767-200\u2019, 315000)\n(1018, \u2018757-300\u2019, 270000)<\/pre>\n<p>As you can see, the results include only airplanes with a <code>max_weight<\/code> value greater than <code>10000<\/code>. The results are also sorted in descending order based on the <code>max_weight<\/code> values.<\/p>\n<p><strong>Read also:\u00a0<\/strong><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/a-guide-to-insert-update-and-delete-statements-in-oracle\/\"><span data-sheets-root=\"1\">Oracle INSERT, UPDATE, and DELETE syntax<\/span><\/a><\/p>\n<h2>Passing variable values into your query<\/h2>\n<p>When using the MySQL Connector to query data, it can often be useful to pass variable values into your <code>SELECT<\/code> statement (or whichever SQL statement you\u2019re defining). You can do this by including special <code>%s<\/code> markers as placeholders where the variable values should be inserted. The following example shows a <code>SELECT<\/code> statement that contains two of these markers:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\"># import the connect and Error methods\nfrom mysql.connector import connect, Error\n\n# try to run the block of code\ntry:\n\n  # define a connection object\n  conn = connect(option_files =\n    '\/users\/mac3\/documents\/config\/connectors.cnf')\n\n  # open cursor and define query\n  cursor = conn.cursor()\n  query = ('SELECT plane_id, plane, max_weight FROM airplanes '\n    'WHERE max_weight BETWEEN %s AND %s '\n    'ORDER BY max_weight DESC')\n\n  # define input variables\n  weight_start = 1000\n  weight_end = 10000\n\n  # pass variables into query and fetch results\n  cursor.execute(query, (weight_start, weight_end))\n  result = cursor.fetchall()\n\n  # print the results in each row\n  for r in result:\n    print(r)\n\n  # close the cursor and database connection\n  cursor.close()\n  conn.close()\n\n# catch exception and print error message\nexcept Error as err:\n  print('Error message: ' + err.msg)<\/pre>\n<p>I added the <code>%s<\/code> markers to the <code>WHERE<\/code> clause as part of a <code>BETWEEN<\/code> operator. The variable values will be inserted in place of the markers when the <code>SELECT<\/code> statement is executed.<\/p>\n<p>For this example, I hard-coded the variable values and assigned them to the <code>weight_start<\/code> and <code>weight_end<\/code> variables. In most cases, you\u2019ll 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.<\/p>\n<p>After you define your variables, you can pass them in as an argument to the <code>execute<\/code> 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:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">(1009, 'B.206', 7500)\n(1011, 'Baron 56 TC Turbo Baron', 5990)\n(1012, 'Baron 58 (and current G58)', 5500)\n(1014, 'Bonanza 33 (F33A)', 3500)\n(1015, 'Bonanza 35 (G35)', 3125)\n(1006, 'A.109 Airedale', 2750)\n(1007, 'A.61 Terrier', 2400)\n(1010, 'D.5-108 Husky', 2400)\n(1008, 'B.121 Pup', 1600)<\/pre>\n<p>As expected, the results include only those airplanes with a <code>max_weight<\/code> value between <code>1000<\/code> and <code>10000<\/code>. 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.<\/p>\n<h2>Getting started with Python and MySQL<\/h2>\n<p>In this article, I\u2019ve 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\u2019ve 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\u2019ve shown you should still give you a general idea of how to retrieve MySQL data within your application.<\/p>\n<p>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.<\/p>\n<h2>Appendix: Preparing your MySQL environment<\/h2>\n<p>The Python examples in this article connect to the <code>travel<\/code> database on a local MySQL instance and retrieve data from the <code>airplanes<\/code> table, which includes a foreign key that references the <code>manufacturers<\/code> table. This is the same database and tables you saw in previous articles in this series, although I\u2019ve limited the data that I inserted into the tables to only a small amount, which is all that\u2019s 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:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">DROP DATABASE IF EXISTS travel;\n\nCREATE DATABASE travel;\n\nUSE travel;\n\nCREATE TABLE manufacturers (\n  manufacturer_id INT UNSIGNED NOT NULL,\n  manufacturer VARCHAR(50) NOT NULL,\n  create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,\n  last_update TIMESTAMP NOT NULL \n    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n  PRIMARY KEY (manufacturer_id) );\n\nCREATE TABLE airplanes (\n  plane_id INT UNSIGNED NOT NULL,\n  plane VARCHAR(50) NOT NULL,\n  manufacturer_id INT UNSIGNED NOT NULL,\n  engine_type VARCHAR(50) NOT NULL,\n  engine_count TINYINT NOT NULL,\n  max_weight MEDIUMINT UNSIGNED NOT NULL,\n  wingspan DECIMAL(5,2) NOT NULL,\n  plane_length DECIMAL(5,2) NOT NULL,\n  parking_area INT GENERATED ALWAYS AS ((wingspan * plane_length)) STORED,\n  icao_code CHAR(4) NOT NULL,\n  create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,\n  last_update TIMESTAMP NOT NULL \n    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n  PRIMARY KEY (plane_id),\n  CONSTRAINT fk_manufacturer_id FOREIGN KEY (manufacturer_id) \n    REFERENCES manufacturers (manufacturer_id) );<\/pre>\n<p>The script will create the <code>travel<\/code> database and add the <code>manufacturers<\/code> and <code>airplanes<\/code> tables in the proper order to accommodate the foreign key defined in the <code>airplanes<\/code> table. After you the create the tables, you can then run the following <code>INSERT<\/code> statements:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk \">INSERT INTO manufacturers (manufacturer_id, manufacturer)\nVALUES (101,'Airbus'), (102,'Beagle Aircraft Limited'), \n  (103,'Beechcraft'), (104,'Boeing');\n\nINSERT INTO airplanes \n  (plane_id, plane, manufacturer_id, engine_type, engine_count, \n    wingspan, plane_length, max_weight, icao_code)\nVALUES\n  (1001,'A340-600',101,'Jet',4,208.17,247.24,837756,'A346'),\n  (1002,'A350-800 XWB',101,'Jet',2,212.42,198.58,546700,'A358'),\n  (1003,'A350-900',101,'Jet',2,212.42,219.16,617295,'A359'),\n  (1004,'A380-800',101,'Jet',4,261.65,238.62,1267658,'A388'),\n  (1005,'A380-843F',101,'Jet',4,261.65,238.62,1300000,'A38F'),\n  (1006,'A.109 Airedale',102,'Piston',1,36.33,26.33,2750,'AIRD'),\n  (1007,'A.61 Terrier',102,'Piston',1,36,23.25,2400,'AUS6'),\n  (1008,'B.121 Pup',102,'Piston',1,31,23.17,1600,'PUP'),\n  (1009,'B.206',102,'Piston',2,55,33.67,7500,'BASS'),\n  (1010,'D.5-108 Husky',102,'Piston',1,36,23.17,2400,'D5'),\n  (1011,'Baron 56 TC Turbo Baron',103,'Piston',2,37.83,28,5990,'BE56'),\n  (1012,'Baron 58 (and current G58)',103,'Piston',2,37.83,29.83,5500,'BE58'),\n  (1013,'Beechjet 400 (same as MU-300-10 Diamond II)',103,'Jet',2,43.5,48.42,15780,'BE40'),\n  (1014,'Bonanza 33 (F33A)',103,'Piston',1,33.5,26.67,3500,'BE33'),\n  (1015,'Bonanza 35 (G35)',103,'Piston',1,32.83,25.17,3125,'BE35'),\n  (1016,'747-8F',104,'Jet',4,224.42,250.17,987000,'B748'),\n  (1017,'747-SP',104,'Jet',4,195.67,184.75,696000,'B74S'),\n  (1018,'757-300',104,'Jet',2,124.83,178.58,270000,'B753'),\n  (1019,'767-200',104,'Jet',2,156.08,159.17,315000,'B762'),\n  (1020,'767-200ER',104,'Jet',2,156.08,159.17,395000,'B762');<\/pre>\n<p>The <code>INSERT<\/code> statements first populate the <code>manufacturers<\/code> table and then the <code>airplanes<\/code> table. Be sure to run the statements in the order shown here because of the foreign key.<\/p>\n\n\n<section id=\"faq\" class=\"faq-block my-5xl\">\n    <h2>Frequently Asked Questions (FAQs)<\/h2>\n\n                        <h3 class=\"mt-4xl\">1. How do you connect to MySQL from Python?<\/h3>\n            <div class=\"faq-answer\">\n                <p>Import the connect method from mysql.connector, then call mysql.connector.connect() with your host, user, password, and database name as arguments. The method returns a connection object you use to create cursors and execute queries. Always close the connection when you\u2019re done. For production code, read connection details from an option file (a .cnf file with a [client] section) instead of hardcoding credentials, and wrap your connection in a try\/except block to handle MySQL-specific errors.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">2. What is the difference between fetchall, fetchone, and fetchmany in Python MySQL?<\/h3>\n            <div class=\"faq-answer\">\n                <p>After executing a query with cursor.execute(), you retrieve results using one of three methods: fetchall() returns all remaining rows as a list of tuples, fetchone() returns the next single row (or None if no rows remain), and fetchmany(size) returns the specified number of rows. Use fetchone() or fetchmany() for large result sets to avoid loading everything into memory at once. Use fetchall() when you need all results and the dataset is manageable.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">3. How do you prevent SQL injection in Python MySQL queries?<\/h3>\n            <div class=\"faq-answer\">\n                <p>Use parameterized queries instead of string concatenation. Pass your SQL statement with %s placeholders to cursor.execute(), and provide the actual values as a tuple in the second argument: cursor.execute(\u201cSELECT * FROM users WHERE id = %s\u201d, (user_id,)). MySQL Connector handles escaping and quoting automatically. Never use f-strings or string formatting to insert user input into SQL queries.<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"<p>Learn how to connect to MySQL from Python and retrieve data using MySQL Connector. Covers connections, cursors, fetchall\/fetchone, parameterized queries, error handling, and option files with working code examples.&hellip;<\/p>\n","protected":false},"author":221841,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,145792],"tags":[],"coauthors":[6779],"class_list":["post-96142","post","type-post","status-publish","format-standard","hentry","category-featured","category-mysql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/96142","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221841"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=96142"}],"version-history":[{"count":9,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/96142\/revisions"}],"predecessor-version":[{"id":110340,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/96142\/revisions\/110340"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=96142"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=96142"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=96142"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=96142"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}