How to Connect to MySQL Using Python?

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:

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 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:

import mysql.connector

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.

import mysql.connector

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

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:

import mysql.connector

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

cursor = db.cursor()

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.

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)

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

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

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.

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 as below:

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

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:

insert into drivers (name,car_number) values
('John Brown',29),
('Kim Dart Jr.',9),
('Sam Smith',79);

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.

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 ('John Brown',29),(‘Kim Dart Jr.’,9),('Sam Smith',79);"

cursor.execute(query)

db.commit()

The results:

ID	Name	Car Number
1	John Brown	29
2	Kim Dart Jr.	9
3	Sam Smith	79

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:

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 ('John Brown',29),(‘Kim Dart Jr.’,9),('Sam Smith',79);"

cursor.execute(query)

db.commit()

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.

sql = "select name,car_number from drivers"

cursor.execute(sql)

results = cursor.fetchall()

for x in results:
print(x)

Results:

(u'John Brown', 29)
(u'Kim Dart Jr.', 9)
(u'Sam Smith', 79)

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

sql = "select name,car_number from drivers"

cursor.execute(sql)

results = cursor.fetchone()

print(results)

Results:

(u'John Brown', 29)

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:

sql = "update drivers set car_number = 1 where car_number = 79"
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'John Brown', 29)
(u'Kim Dart Jr.', 9)
(u'Sam Smith', 1)

Delete:

sql = "delete from drivers where car_number = 9"
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'John Brown', 29)
(u'Sam Smith', 1)

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