Python is a popular and open-source programming language that lets people integrate systems more effectively. It can connect to database systems, read and also, modify files.
Connecting Python to MySQL databases enables webmasters to access their servers remotely on their local computers. Using Python also allows them to have multiple separate working environments through the same connection to the database.
We’ve put together this article to provide a quick and straightforward guide on connecting to your MySQL database using Python.
The article will be guiding you through all you’ll need to know on:
- Setting up a Python virtual environment
- Installing MySQL packages, and
- Creating the MySQL connection
Let’s dive in.
Install a Pip Python Module
To connect MySQL to Python, you’d need to install the Pip Python module.
A Pip module is the standard package manager for Python. It allows you to install and manage additional packages and extensions that are part of the standard Python library distribution.
Install at least one of these packages in a virtual environment to connect MySQL using Python.
- Mysql-client: this client package allows you to connect to a MySQL server and access the command-line program. It comes with utilities that enable you to easily backup data, restore and administer the server. This package contains the MySQLdb module.
- Mysql-connector-python: this package is a MySQL driver that enables Python programs to access MySQL databases using an API. The package contains the mysql.connector module.
- PyMySQL: this package provides an interface for connecting to the MySQL database server. It contains the pymysql module.
Setting Up a Python Virtual Environment
A virtual environment allows webmasters to create and manage separate environments for Python projects. Each environment uses a different version of Python for execution.
Follow these steps to set up a python virtual environment.
Log in to your server using secure shell (SSH) access. Doing this allows you to access the command line remotely.
Type this command to create a python virtual environment.
cd ~
virtualenv envname
Replace envname with the name of the virtual environment you intend to create.
After creating the virtual environment, you’d need to activate it. Use command to execute the task.
source envname/bin/activate
Upon activation, the command prompt will now start with envname , indicating you are working on a Python virtual environment named envname.
Any time you feel like updating pip in the virtual environment, then run this command to execute the task
pip install -U pip
Installing The MySQL Packages
After creating and activating the python virtual environment, the next thing is to install the MySQL packages. It’s advisable to install all three packages.
Installing multiple modules allows you to switch between modules anytime. The modules use the portable SQL database API interface; this will enable you to reuse codes without any modification.
Run these codes to install the packages.
- Run this code to install the mysqlclient package pip install mysqlclient
- Use this code to install the mysql-connector-python package pip install mysql-connector-python
- And, this to install the pymysql packages pip install pymysql
Creating The Connection
After installing the packages, you can connect to your MySQL databases and run commands through any of those modules.
Run this code to establish a MySQL Python connection using the MySQL connector module.
mydb = mysql.connector.connect(
host=“localhost”,
user=“yourusername”,
password=“yourpassword”
)
print(mydb)
Replace yourusername and yourpassword with those of the database you want to connect to.
To set up series of Python connection that opens the same database using the different MySQL packages, then run the below sample code.
#!/usr/bin/python
from __future__ import print_function
hostname = ‘localhost‘
username = ‘yourusername‘
password = ‘yourpassword‘
database = ‘yourdbname‘
# Simple routine to run a query on a database and print the results:
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=localhost, user=yourusername, passwd=yourpassword, db=yourdbname )
doQuery( myConnection )
myConnection.close()
print( “Using mysql.connector:” )
import mysql.connector
myConnection = mysql.connector.connect( host=localhost, user=yourusername, passwd=yourpassword, db=yourdbname )
doQuery( myConnection )
myConnection.close()
print( “Using pymysql:” )
import pymysql
myConnection = pymysql.connect( host=localhost, user=yourusername, passwd=yourpassword, db=yourdbname )
doQuery( myConnection )
myConnection.close()
Replace yourusername with the username of the MySQL database you want to connect to, yourpassword with the database user’s password, and yourdbname with the database’s name.
Wrapping It Up
You can connect to your MySQL databases and run commands remotely using Python programs. This guide has described the quick steps to establish the connection between Python and MySQL.
Use it to get yourself up and running.
