# Connecting to MySQL using Python

> Connect a Python application to a MySQL database using one of three common packages, set up in a Python virtual environment.

Source: https://helpcenter.ruachost.com/developers/databases/connecting-to-mysql-using-python/

**Connecting to MySQL using Python**\
This article describes three methods for connecting to a MySQL database using Python.

**Tip:** MySQL databases and users must already exist before using these methods.

**Available Python MySQL Packages**\
Before connecting to MySQL, you must install one or more of the following packages in a Python virtual environment:

- **mysqlclient:** Provides the `MySQLdb` module, written in C.
- **mysql-connector-python:** Provides the `mysql.connector` module, written entirely in Python.
- **PyMySQL:** Provides the `pymysql` module, written entirely in Python.

All three packages use Python's portable SQL database API, allowing you to switch modules with minimal code changes.

## Setting up the Python virtual environment and installing a MySQL package

1. Log in to your account via SSH and navigate to your home directory:

```
cd ~
```

2. Create a virtual environment:

- Python 3.x:

```
python3 -m venv sqlenv
```

- Python 2.x:

```
virtualenv sqlenv
```

3. Activate the virtual environment:

```
source sqlenv/bin/activate
```

4. Update pip:

```
pip install -U pip
```

5. Install the desired MySQL package:

- mysqlclient:

```
pip install mysqlclient
```

- mysql-connector-python:

```
pip install mysql-connector-python
```

- PyMySQL:

```
pip install pymysql
```

**Sample Python code to connect to MySQL**\
Replace `username`, `password`, and `dbname` with your database credentials. The sample demonstrates usage with all three modules:

```
#!/usr/bin/python
from __future__ import print_function

hostname = 'localhost'
username = 'username'
password = 'password'
database = 'dbname'

def doQuery(conn):
    cur = conn.cursor()
    cur.execute("SELECT fname, lname FROM employee")
    for firstname, lastname in cur.fetchall():
        print(firstname, lastname)

print("Using mysqlclient (MySQLdb):")

myConnection = MySQLdb.connect(host=hostname, user=username, passwd=password, db=database)
doQuery(myConnection)
myConnection.close()

print("Using mysql.connector:")

myConnection = mysql.connector.connect(host=hostname, user=username, passwd=password, db=database)
doQuery(myConnection)
myConnection.close()

print("Using pymysql:")

myConnection = pymysql.connect(host=hostname, user=username, passwd=password, db=database)
doQuery(myConnection)
myConnection.close()
```

This example shows that all three MySQL modules can use the same `doQuery()` function because they implement the portable SQL database API.

**Note:** If using the cPanel Python Selector, modify the first line to reference the virtual environment Python executable, e.g.:

```
#!/home/username/virtualenv/test/3.11/bin/python
```

## More information

- [Python DB API 2.0 (PEP 249)](https://www.python.org/dev/peps/pep-0249)
- [mysqlclient package](https://pypi.org/project/mysqlclient)
- [mysql-connector-python package](https://pypi.python.org/pypi/mysql-connector-python)
- [PyMySQL package](https://pypi.python.org/pypi/PyMySQL)