How to Use MySQL with Python

Stella Sebastian
Posted by Stella Sebastian December 26, 2020

superior_hosting_service

How to Use MySQL with Python by Sujith Kumar

MySQL.png

If you’re a Python Developer or someone who is trying for a database development career or want to scale up and work as a database programmer, knowing how to use MySQL with Python will be an added advantage. MySQL is one such popular database management system that allows you to manage relational databases. MySQL can be better referred to as a Relational Database Management System (RDBMS). However, this blog is intended to help you learn how to use MySQL with Python. 

Before you move on to knowing how to use MySQL with Python, you should know what MySQL is, its advantages and the different areas of its application. 

What is MySQL?

  • It is a database management system and an open source software backed by Oracle. 
  • MySQL has the adaptability to run on various platforms like Linux, UNIX, Windows, etc. 
  • Whether you want to install on a desktop or on a server, it’s possible with MySQL. 

How to Connect to MYSQL Using Python 

When you work with Python, you’ll need to work with different database types and different MySQL Packages. You must install one (or more) of the following packages before you can access MySQL databases using Python in a virtual environment:

MySQL-python: Being one of the most commonly used Python Packages for MySQL, MySQL – Python contains the MySQLdb module written in C. 

mysql-connector-python: Containing the mysql.connector module, this package is written entirely in Python.  

PyMySQL: Written entirely in Python, this package contains the pymysql module and is designed to be a drop-in replacement for the MySQL-python package.

All three of these packages use Python’s portable SQL database API. This allows you to switch from one module to another when you want to reuse almost all of your existing code.

MySQL Connector module of Python is used to connect MySQL databases with the Python programs. It is done using the Python Database API Specification v2.0 (PEP 249). MySQL Connector uses the Python standard library and has no dependencies.

To access the MySQL database Python needs a MySQL driver. 

Therefore, the first step is knowing how to install MySQL driver for Python.

1. Install MySQL

To install a MySQL driver,  you will require a platform specific installation method for each. 

  • On Windows you will need to install MySQLdb using the installer.
  • On Linux install MySQLdb using ” sudo apt-get install python-mysqldb yum install mysql-python” depending on your version.
  • On Mac you may follow the installation instructions from stack overflow. 

Once you finish installing and the MySQL server is running on your PC, you can move onto the next step.

2. Setup the database

Next thing you’ll need to do is the setting up of the database. Make sure you have access to the database. To access the database from the command line type:

mysql -u USERNAME -p

MySQL will then ask your password. Type these commands:

mysql> CREATE DATABASE pythonspot; 
mysql> USE pythonspot;

We will then move on to “create the table” command:

CREATE TABLE IF NOT EXISTS examples (
  id int(11) NOT NULL AUTO_INCREMENT,
  description varchar(45),
  PRIMARY KEY (id)
);

Then we can insert data into the table (these are SQL queries):

INSERT INTO examples(description) VALUES ("Hello World");
INSERT INTO examples(description) VALUES ("MySQL Example");
INSERT INTO examples(description) VALUES ("Flask Example");

Using a SQL query you can now grab all records from the table:

mysql> SELECT * FROM examples;
+----+---------------+
| id | description   |
+----+---------------+
|  1 | Hello World   |
|  2 | MySQL Example |
|  3 | Flask Example |
+----+---------------+
3 rows in set (0.01 sec)

3. Get the data from Python

The third step is to get data from Python. You can directly access the database from Python using the MySQLdB module.

#!/usr/bin/python
import MySQLdb

db = MySQLdb.connect(host="localhost",  # your host 
                     user="root",       # username
                     passwd="root",     # password
                     db="pythonspot")   # name of the database

# Create a Cursor object to execute queries.
cur = db.cursor()

# Select data from table using SQL query.
cur.execute("SELECT * FROM examples")

# print the first and second columns      
for row in cur.fetchall() :
    print row[0], " ", row[1]

Output:

1   Hello World
2   MySQL Example
3   Flask Example

Installing MySQL Connector Package in Python

When we have to use MySQL with other programming languages, a connector is employed. The work of mysql-connector is to provide access to MySQL Driver to the required language. Thus, it generates a connection between the programming language and the MySQL Server.

Prerequisites for Installing MySQL Connector in Python

There are prerequisites before installing MySQL Connector in Python. The first requirement is, Python must be installed on your machine and the installation fails if it is not. At the same time, you must be granted the root or administrator privileges to perform the installation process. 

Python PATH on Unix and Unix-like systems is generally located in a directory included in the default PATH setting.

If Python doesn’t exist in your Windows system PATH, try to manually add the directory containing python.exe yourself.

You can install MySQL Connector Package in Python if you’re using any of the following Platforms: 

  • 64-bit Windows
  • Windows 10, 8, 7
  • Windows Vista or XP
  • Linux- Debian Linux, Ubuntu Linux, Red Hat Linux
  • SUSE Linux, 
  • Fedora
  • MacOs

You could be using Python versions 2 or 3 and MySQL Versions anything greater than 4.1. 

Now, you have the above mentioned platforms and versions of Python and MySQL. You need a database driver (module) to connect to a MySQL server from Python and MySQL Connector Python is the official Oracle-supported driver to connect. 

The Installation Process of MySQL Connector:

To install the Python-mysql-connector module, you must have Python and PIP pre-installed on your system. 

Go through the following instructions to check if your system already contains Python:

Open the Command line(search for cmd in the Run dialog( + R).

Now run the following command:

python --version

You’ll get a message with the Python version available if Python is already installed. 

Different ways to install MySQL Connector Python

There are different ways to install MySQL Connector Python on your machine. A few of the ways follow: 

  • Use the pip command to install MySQL Connector Python.
  • You can install MySQL Connector Python via source code. For example, via ZIP or TAR file. 
  • Use Built Distribution package, created in the native packaging format intended for the given platform.  For example, MSI installer for Windows or RPM packages for Linux. 

Python MySQL Connector Versions

To run the MySQL – Python Connector, you need to install a module that is compatible with your Python version. Refer to the following table to check which of the following MySQL Connector Python versions you have. 

Connector/Python VersionMySQL Server VersionsSupported Python Versions
8.08.0, 5.7, 5.6, 5.53.6, 3.5, 3.4, 2.7
2.25.7, 5.6, 5.53.5, 3.4, 2.7
2.15.7, 5.6, 5.53.5, 3.4, 2.7, 2.6
2.05.7, 5.6, 5.53.5, 3.4, 2.7, 2.6
1.25.7, 5.6, 5.5 (5.1, 5.0, 4.1)3.4, 3.3, 3.2, 3.1, 2.7, 2.6

Choose the version as per your need and get started. 

Pip Command to install MySQL Connector Python

MySQL Connector Python is available on pypi.org so that you can install MySQL Connector Python on any operating system using the pip command. 

You may use the following pip command to install MySQL Connector Python.

pip install mysql-connector-python

If you are facing any problem while installing mysql-connector-python, please mention the version of the module and then try to install again. If you have any doubts regarding versions and installation, refer to the above table to install the correct version.

Verifying MySQL Connector Python installation

Check if you’re getting the following messages after running pip command: 

  • Collecting mysql-connector-python
  • Downloading packages
  • Requirement already satisfied: setup tools in D:python\python 37-32\lib\site-packages
  • Installing collected packages:  mysql-connector-python
  • Successfully installed mysql-connector-python-8.0.13

Top reasons why should you use MySQL Databases with Python

Databases, which usually consist of tables, are part of every organisation and are mostly dependent on when they want the data to be structured. A database system supports the SQL (Structured Query Language), which creates, accesses, and manipulates data. 

Python is a programming language having some ultimate features that are ideal for database programming. When it comes to building Python web applications, MySQL is a viable open source database implementation choice. Python can be used with a variety of databases such as MySQL and Oracle. It also supports Data Definition Language, Data Query Statements, and Data Manipulation Language.  You know the Advantages of learning Python. If you are working in the IT field, you are really missing out if you don’t learn SQL. 

Advantages of Database Programming with Python

When you work using MySQL with Python, you will have the following benefits:

  • Faster and more efficient
  • Platform-independent
  • Support for relational database systems
  • Easy to migrate and port database application interfaces
  • Support for SQL cursors
  • Portable
  • Handles open and closed connections

Conclusion

Python is perfectly fine for most applications as a language for creating database clients like MySQL. MySQL remains a viable database option but I always recommend that new Python developers, in order to work with MySQL using Python, must have some knowledge of SQL. You know the benefits of using Python and working in the IT field. Learn SQL the easy way with SQLite and learn PostgreSQL if you do not already know MySQL. After that you can control MySQL server too, all from Python!

About the Author

Sujith is a leading IT Faculty having more than 18 years of experience with Edoxi Training Institute Dubai. And concentrated on python programming. He schedules classes into facilities and provides software instructions. He spends most of his free time learning new software skills and also interested in driving and reading.

Automated Pentesting Tool

Tags: