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
MySQLdbmodule, written in C. -
mysql-connector-python: Provides the
mysql.connectormodule, written entirely in Python. -
PyMySQL: Provides the
pymysqlmodule, 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
-
Log in to your account via SSH and navigate to your home directory:
cd ~
-
Create a virtual environment:
-
Python 3.x:
python3 -m venv sqlenv-
Python 2.x:
virtualenv sqlenv-
Activate the virtual environment:
source sqlenv/bin/activate-
Update pip:
pip install -U pip-
Install the desired MySQL package:
-
mysqlclient:
pip install mysqlclient-
mysql-connector-python:
pip install mysql-connector-python-
PyMySQL:
pip install pymysqlSample 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):")
import MySQLdb
myConnection = MySQLdb.connect(host=hostname, user=username, passwd=password, db=database)
doQuery(myConnection)
myConnection.close()
print("Using mysql.connector:")
import mysql.connector
myConnection = mysql.connector.connect(host=hostname, user=username, passwd=password, db=database)
doQuery(myConnection)
myConnection.close()
print("Using pymysql:")
import 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/pythonMore information
