Knowledge Base
MilesWeb / MySQL

How to Connect to MySQL Using Python?

Approx. read time : 4 min

In this article, you will learn to connect to MySQL using Python. Python is a programming language that is highly productive and widely used today. The simple and unique syntax makes it perfect for new programmers, while experienced programmers can take the benefit of the available functionalities and modules.

A robust open-source SQL-based, relational database management system, MySQL is used in various software programs and web servers. In this article, you will learn to connect to use Python for connecting to MySQL and perform some basic tasks.

Reasons to Use Python to Connect to MySQL

You might be wondering if this information is really important. This is because Python and MySQL complement each other in an excellent way. You can compare Python’s ability to manipulate data with the help of sourced information. MariaDB or MySQL includes the data that can be modified by Python. As these two factors can be used to complement and enhance each other, it increases the complete association between them.

Step 1. Installing the Connector Module

Let’s start with the installation of the connector module. At first you need to install the pip Python module. After installing pip, install the mysql-connector-python driver using the below command:

In the example above, pip verifies other modules that the mysql-connector-python driver may need, which will then be installed if essential.

Step 2. Importing Connector

Now import the mysql-connector-python module with the below command within your code:

This command enables Python to load and enable all the functions and objects related to and used by the MySQL connector module.

Step 3. Connecting MySQL to the Server

Then call the mysql.connector.connect() method for creating a connection to the server.

Usually, a MySQLcursor object (which is part of the mysql-connector-python module) is used for communicating with a MySQL database. Imagine this object as a type of CLI (command-line interface) where we can type in SQL queries used for interacting with the server. You can achieve this connection using the cursor method (cursor = db.cursor() ), calling on the db object that we created in the previous step using the connect method:

We can execute SQL queries with a db.cursor object. With this query, an object is returned which can be iterated over with a for loop like so.

If you have a preexisting database, using the above script, the results would look as below:

Other commands can be used with the current cursor (db.cursor()) to interact with this database. Here, we will get a list of the tables and views from the same database.

The output results look as below:

Note: The “u” in front of the result stands for a Unicode string.

Insert Data with the MySQL Cursor Object

It is now possible to retrieve the structure of the database and so, we can use the cursor object to execute other commands. Let’s take an example of database that contains drivers for the racing season. It can be done by using the below SQL query:

For running this same SQL query using Python, just pass this string to the execute method of our cursor. It is good to practice using this for assigning a variable as the text of the query and then calling execute on the cursor object. It is also required to instruct mysql to run the changes by calling db.commit() like so.

The results:

IDNameCar Number
1John Brown29
2Kim Dart Jr.9
3Sam Smith79

When multiple rows are inserted, the interface offers the method “executemany”, which enables us to create an array of values that need to be inserted and a string specially formatted with the %s symbol that replaces the values from the arrays. The below example is similar to the previous insert:

The values from the array ‘drivers’ are accepted one by one into the ‘sql’ statement and then accepted into ‘executemany()’.

Using Select

Similar to other SQL statements, we can use the cursor object to run selects. After a select, a cursor gets a few new methods that include fetchall() and fetchone(). The fetchall() returns a list containing all the results. Each result comprises of a list of corresponding columns in their selected order. The fetchone() method offers the next result from the result set.

Results:

For getting one result at a time, use fetchone():

Results:

Updating and Deleting Data

Similar to the insert command, the delete and update commands use a cursor object and must call db.commit(); or else, they are similar to other SQL commands.

Update:

Delete:

Conclusion

It is a simple and effective way to use Python to interact with MySQL for manipulating data in ways that complement each system.

Also Read

How can I connect to SQLite using Python?
Learn to Empty a MySQL Database in Simple Steps

Pallavi Godse
Pallavi is a Digital Marketing Executive at MilesWeb and has an experience of over 4 years in content development. She is interested in writing engaging content on business, technology, web hosting and other topics related to information technology.
Need help? We’re always here for you.
Register Your Free Domain Name
OFFER EXPIRES IN
04
min
59
sec