Connecting to MySQL Using Python

In this article, we will be discussing how to connect to MySQL using Python. Python is one of the most productive and widely used programming languages in use today. Its simple and elegant syntax makes it ideal for new programmers, while experienced programmers enjoy the extensive list of available modules and functionalities. 

MySQL is a robust open-source SQL-based, relational database management system that is used in many software programs and web servers. This article aims to show how to connect to use Python to connect to MySQL and perform some basic tasks. 

Why Use Python to Connect to MySQL?

You may be asking yourself, why is this information important? The best answer is, these two components complement each other brilliantly! Pythons' ability to manipulate data using sourced information is unrivaled. MySQL or MariaDB contains the data that can be manipulated by Python. Using these two factors to enhance and complement each other only increases the overall synergy between them. 

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',)

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)

Conclusion

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 support@liquidweb.com, by phone (at 800-580-4986), or via a LiveChat or whatever method you prefer. We work hard for you so you can relax.