How to access MySQL database using Python

Python can be used to access MySQL databases with the help of an additional module. There are a few available Python modules to allow MySQL database access, and each come with their pros and cons.

This method also works with MariaDB databases as MariaDB is for the most part is just a drop-in replacement for MySQL.

This example will be based on mysql-connector module, though other modules would function the same with only minor differences.

Steps to access MySQL database using Python:

Install pip for your system.

How to install pip on Ubuntu and Debian
How to install pip on CentOS / Red Hat / Fedora

Install mysql-connector module using pip.

$ pip3 install mysql-connector
Collecting mysql-connector
  Downloading mysql-connector-2.2.9.tar.gz (11.9 MB)
     |████████████████████████████████| 11.9 MB 11.6 MB/s
Building wheels for collected packages: mysql-connector
  Building wheel for mysql-connector (setup.py) ... done
  Created wheel for mysql-connector: filename=mysql_connector-2.2.9-cp38-cp38-linux_x86_64.whl size=247948 sha256=4baaca9e6150eb5bac152a3ec95dee71ff2235f83fdd41b83bf5c89346adef29
  Stored in directory: /home/user/.cache/pip/wheels/57/e4/98/5feafb5c393dd2540e44b064a6f95832990d543e5b4f53ea8f
Successfully built mysql-connector
Installing collected packages: mysql-connector
Successfully installed mysql-connector-2.2.9

Run your preferred Python shell.

$ ipython3
Python 3.8.2 (default, Apr 27 2020, 15:53:34)
Type 'copyright', 'credits' or 'license' for more information
IPython 7.13.0 -- An enhanced Interactive Python. Type '?' for help.

Import mysql.connector module.
```
In [1]: import mysql.connector
```

Establish connection to your MySQL or MariaDB database.

In [2]: conn = mysql.connector.connect(host="localhost", user="user", password="password", database="mysql")

Create a cursor for your connection.
```
In [3]: cursor = conn.cursor()
```
Create an SQL statement.
```
In [4]: stmt = "SELECT * FROM user"
```
Execute your SQL statement.
```
In [5]: cursor.execute(stmt)
```
Get records from executed statement.
```
In [6]: records = cursor.fetchall()
```

Process records accordingly.

In [7]: print("Total users: ", cursor.rowcount)
Total users:  2
In [8]: for record in records:
   ...:     print("User: ", record[1])
   ...:
User:  bytearray(b'root')
User:  bytearray(b'user')

Close connection to database.
```
In [9]: conn.close()
```
Close cursor.
```
In [10]: cursor.close()
Out[10]: True
```

Combine steps into a Python script.

access-mysql-database.py

#!/usr/bin/env python3
 
#Import required module
import mysql.connector
 
#Establish connection to database
conn = mysql.connector.connect(host="localhost", user="user", password="password", database="mysql")
 
#Create cursor
cursor = conn.cursor()
 
#Create an SQL statement
stmt = "SELECT * FROM user"
 
#Execute SQL statement
cursor.execute(stmt)
 
#Get records from executed statement
records = cursor.fetchall()
 
#Process
print("Total users: ", cursor.rowcount)
 
for record in records:
    print("User: ", record[1])
 
#Close connection to database
conn.close()
 
#Close cursor
cursor.close()