Connect to MySQL using Python

Python and MySQL are a powerful pairing. Combining Python’s data manipulation capabilities with MySQL’s reliable data storage creates a practical synergy – Python excels at processing and analyzing data, while MySQL provides the structured foundation to store it. When used together, these complementary strengths open up new possibilities for your applications.

In this article, we will be discussing how to do just that. But first, you’ll need to ensure you’re meeting a few prerequisites: 

1. Python installed on your system
2. The MySQL database connector for Python (install using pip install mysql-connector-python)
3. MySQL server installed and running
4. Database connection details:
   • Host address
   • Port number (default is 3306)
   • Username
   • Password
   • Database name

Once you’ve met these requirements, you’ll be ready to connect Python and MySQL

How to set up your environment

Before diving into Python and MySQL integration, you’ll need to set up your development environment properly. This involves three key steps: installing the necessary connector, importing the right modules, and configuring your database connection. 

Let’s walk through each step to ensure you’re ready to start building

Step 1. Installing the connector module

Let’s begin by installing the connector module. The first step in connecting MySQL with Python is to install the Pip Python module. If you do not have pip installed already, detailed instructions for installing pip under multiple operating systems can be found in the Liquid Web Knowledge Base. Once we have pip installed, we then need to install the mysql-connector-python driver using the following command. 

root@host:~# pip install mysql-connector-python 
Collecting mysql-connector-python
  Downloading mysql_connector_python-8.0.21-cp27-cp27mu-manylinux1_x86_64.whl (16.0 MB)
     |################################| 16.0 MB 13.7 MB/s 
Collecting protobuf>=3.0.0
  Downloading protobuf-3.12.2-cp27-cp27mu-manylinux1_x86_64.whl (1.3 MB)
     |################################| 1.3 MB 17.0 MB/s 
Requirement already satisfied: setuptools in /usr/local/lib/python2.7/dist-packages (from protobuf>=3.0.0->mysql-connector-python) (44.1.1)
Collecting six>=1.9
  Downloading six-1.15.0-py2.py3-none-any.whl (10 kB)
Installing collected packages: six, protobuf, mysql-connector-python
Successfully installed mysql-connector-python-8.0.21 protobuf-3.12.2 six-1.15.0

In the example above, pip checks for other modules that the mysql-connector-python driver may require, which will then be installed if necessary. 

Step 2. Import connector

The next step is to import the mysql-connector-python module using this command within your code.

import mysql.connector

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

Step 3. Connect MySQL to the server

Our next step is to call the mysql.connector.connect() method to create a connection to the server. 

import mysql.connector

db = mysql.connector.connect(
	host='localhost',
	user='username',
	password='password'
)

Usually, when communicating with a MySQL database, we use a MySQLcursor object (which is part of the mysql-connector-python module). Think of this object as a type of CLI (command-line interface) where we can type in SQL queries used to interact with the server. This communication is accomplished using the cursor method (cursor = db.cursor() ), calling on the db object that we created in the last step with the connect method:

import mysql.connector

db = mysql.connector.connect(
        host='localhost',
        user='username',
        password='password''
)

cursor = db.cursor()

A db.cursor object allows us to execute SQL queries. This query returns an object which we can iterate over with a for loop like so.

import mysql.connector

db = mysql.connector.connect(
        host='localhost',
        user='username',
        password='password''
)

cursor = db.cursor()

cursor.execute("show databases")

for x in cursor:
	print(x)

There is a preexisting database which was set up in a previous KB tutorial about SQL Views. It contains information about a fictional stock car series. Using the above script, the results would look like this:

# python mysql-test.py 
(u'information_schema',)
(u'races',)
(u'sys',)

We can use other commands with the current cursor (db.cursor()) to interact with this database. Here, we pull a list of the tables and views from the same database.

import mysql.connector

db = mysql.connector.connect(
        host='localhost',
        user='username',
        password='password'
)

cursor = db.cursor()


cursor.execute("use races")
cursor.execute("show tables")
for x in cursor:
        print(x)


The output results look like this.

# python mysql-test.py 
(u'all_finishes',)
(u'drivers',)
(u'finishes',)
(u'race_winners',)
(u'races',)
(u'standings_leader',)
(u'tracks',)

[su_box title=”Note:” style=”glass” box_color=”#3ac6eb” radius=”20″]The “u” in front of the result indicates that it is a Unicode string.[/su_box]

After setting up your environment, you’ll want to learn the fundamental database operations. These core actions – inserting, selecting, updating, and deleting data (often called CRUD operations – Create, Read, Update, Delete) – form the backbone of database interaction. Let’s explore each operation, starting with how to add new data using MySQL’s cursor object.

Inserting data with the MySQL cursor object

Now that we can retrieve the structure of the database, we can use the cursor object to execute other commands. There was a section where the drivers for the racing season were inserted into the database. It was done with using this SQL query.

insert into drivers (name,car_number) values
  ('Buddy Baker',28),
  ('Dale Earnhardt Jr.',8),
  ('Ricky Rudd',88);

To run this same SQL query using Python, we simply pass this string to the execute method of our cursor. A good method to practice using this is to assign a variable as the text of the query and then call execute on the cursor object. You also have to instruct mysql to commit the changes by calling db.commit() like so.

db = mysql.connector.connect(
	host='localhost',
	user='username',
	password='password'
)

cursor = db.cursor()
cursor.execute("use races")


query = "insert into drivers (name,car_number) values ('Buddy Baker',28),('Dale Earnhardt Jr.',8),('Ricky Rudd',88);"

cursor.execute(query)

db.commit()

The results:

When we insert multiple rows, the interface offers the method “executemany”, which allows us to create an array of values to be inserted and a string specially formatted with the %s symbol replacing the values from the arrays. This example is identical to the previous insert:

db = mysql.connector.connect(
	host='localhost',
	user='username',
	password='password'
)

cursor = db.cursor()
cursor.execute("use races")


query = "insert into drivers (name,car_number) values ('Buddy Baker',28),('Dale Earnhardt Jr.',8),('Ricky Rudd',88);"

cursor.execute(query)

db.commit()

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

Using select

Like other SQL statements, we can use the cursor object to execute selects. After a select, a cursor gains a few new methods, including fetchall() and fetchone(). The fetchall() returns a list of all the results. Each result is a list with the corresponding columns in the order they were selected in. The fetchone() method returns the next result from the result set.

sql = "select name,car_number from drivers"

cursor.execute(sql)

results = cursor.fetchall()

for x in results:
        print(x)

Results:

(u'Buddy Baker', 28)
(u'Dale Earnhardt Jr.', 8)
(u'Ricky Rudd', 88)

If we want one result at a time, we can use fetchone()

sql = "select name,car_number from drivers"

cursor.execute(sql)

results = cursor.fetchone()

print(results)

Results:

(u'Buddy Baker', 28)

Updating and deleting data

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

Update:

sql = "update drivers set car_number = 1 where car_number = 88"
cursor.execute(sql)
db.commit()

sql = "select name,car_number from drivers"

cursor.execute(sql)

results = cursor.fetchall()
for x in results:
        print(x)

(u'Buddy Baker', 28)
(u'Dale Earnhardt Jr.', 8)
(u'Ricky Rudd', 1)

Delete:

sql = "delete from drivers where car_number = 8"
cursor.execute(sql)
db.commit()

sql = "select name,car_number from drivers"

cursor.execute(sql)

results = cursor.fetchall()
for x in results:
        print(x)

(u'Buddy Baker', 28)
(u'Ricky Rudd', 1)

Manage your data seamlessly with Python and MySQL

So, what are the takeaways of this article? Using Python to interact with MySQL is a simple and effective way to manipulate data in ways that complement each system.

Have questions? We pride ourselves on being The Most Helpful Humans In Hosting™! Our technical support staff is always available to assist with any issues related to this article, 24 hours a day, 7 days a week 365 days a year.

We are available, via our ticketing systems at [email protected], by phone (at 800-580-4986), or via a LiveChat or whatever method you prefer. We work hard for you so you can relax.