Connecting to MySQL Using Python

Reading Time: 5 minutes

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',)
Note:
The “u” in front of the result indicates that it is a Unicode string.

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:

id name

car number

1 Buddy Baker

28

2

Dale Earnhardt Jr.

8
3

Ricky Rudd

88

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.

How To Set Up A Python Virtual Environment On Windows 10

Reading Time: 4 minutes

A Virtual Environment or a “venv” is a Python module that creates a unique environment for each task or project. It installs the packages we need that are unique to that setting while keeping your projects neatly organized. Additionally, venv never actually modifies the system’s default Python versions or modules that are installed on the system. Using venv essentially allows for a unique working environment while avoiding any disruptions to other variants of Python that are used, but not related to our project.

Continue reading “How To Set Up A Python Virtual Environment On Windows 10”

How to Install PIP on Windows

Reading Time: 4 minutes

Pip is one of the best tools to install and manage Python packages. Pip has earned its fame by the number of applications using this tool. Used for its capabilities in handling binary packages over the easily installed package manager, Pip enables 3rd party package installations. Though the newest versions of Python come with pip installed as a default, this tutorial will show how to install Pip, check its version, and show some basic commands for its use. Watch the video below or review the following article for additional instructions.

Continue reading “How to Install PIP on Windows”

How to Install Taiga on Ubuntu 16.04

Reading Time: 5 minutes
taiga logo

Taiga is a free, open-source project management system. The back end consists of an API written in Python3 and Django, and the front end is written in AngularJS and CoffeeScript. Taiga can manage simple and complex projects, and also monitors the progress of a project. Taiga maintains logs that are displayed in the form of a worklist with all the functions and user stories added to the project. 

Continue reading “How to Install Taiga on Ubuntu 16.04”

How to Install Python on Ubuntu 18.04

Reading Time: 3 minutes

Python is fast becoming one of the most popular programming languages worldwide. Its low entry barrier for new programmers and simple, elegant syntax makes it a fantastic language to start learning. Python is excellent for task automation, and thankfully most Linux distributions come with Python installed right out of the box. This is true of Ubuntu 18.04; however, the Python package distributed with Ubuntu 18.04 is version 3.6.8. This article will cover how to install a newer version of Python, specifically, the latest stable version 3.8.3.

Continue reading “How to Install Python on Ubuntu 18.04”

How to Install Pyenv-virtualenv on Ubuntu 18.04

Reading Time: 3 minutes
python-logo

Pyenv is an outstanding tool for managing multiple Python installations. Pyenv-virtualenv is a pyenv plugin that facilitates the creation and management of Python virtual environments with pyenv. This is a compelling proposition, making it possible to manage multiple Python versions with pyenv and provide the means to control the Python environment in a more granular manner.

Continue reading “How to Install Pyenv-virtualenv on Ubuntu 18.04”

How to Install Pyenv on Ubuntu 18.04

Reading Time: 3 minutes

What is Pyenv?

Pyenv is a fantastic tool for installing and managing multiple Python versions. It enables a developer to quickly gain access to newer versions of Python and keeps the system clean and free of unnecessary package bloat. It also offers the ability to quickly switch from one version of Python to another, as well as specify the version of Python a given project uses and can automatically switch to that version. This tutorial covers how to install pyenv on Ubuntu 18.04.

Continue reading “How to Install Pyenv on Ubuntu 18.04”

Creating a Virtual Environment for Python on Ubuntu 16.04

Reading Time: 2 minutes

What Is Virtualenv?

Virtualenv is a tool that creates an isolated environment separate from other projects. In this instance, we will be installing different Python versions, including their dependencies.  Creating a virtual environment allows us to work on a Python project without affecting other projects that also use Python. It will utilize Python’s core files on the global environment to run, thus saving you disk space while providing the freedom to use different Python versions for separate apps or projects.

Continue reading “Creating a Virtual Environment for Python on Ubuntu 16.04”

How to Install Pip on Ubuntu 16.04 LTS

Reading Time: 2 minutesArguably one of the easiest tools to use for installing and managing Python packages, Pip has earned its notoriety by the number of applications utilizing this tool. Fancied for its capabilities in handling binary packages over the easy_installed packages manager, Pip enables 3rd party package installations. Though Python does sometimes come with Pip as a default, this tutorial will show how to install, check its version as well as some basic commands for using Pip on Ubuntu 16.04.

Continue reading “How to Install Pip on Ubuntu 16.04 LTS”

How to Install Keras

Reading Time: 6 minutes
keras.logo.3.12.20

Keras is a Python-based high-level neural networks API that is capable of running on top TensorFlow, CNTK, or Theano frameworks used for machine learning. It can be said that Keras acts as the Python Deep Learning Library. Keras was created with emphasis on being user-friendly since the main principle behind it is “designed for human beings, not machines.” The core data structure of Keras is a model, or a way to organize layers.

Continue reading “How to Install Keras”