In today’s post, we’ll show you how to connect to a MySQL database using the Python programming language.

Python is one of the most widely used programming languages, its simple and elegant syntax makes it ideal for beginner programmers, while experienced programmers can enjoy the extensive list of available modules and features.

On the other hand, there is MySQL – a robust open source relational database management system based on the Structured Query Language (SQL). It is used in many applications and web servers.

Table of contents

• Installing the module: mysql-connector-python
• Connection to MySQL database

Installing the module: mysql-connector-python

Before connecting to the MySQL database, you need to prepare a Python virtual environment and install the mysql-connector-python module.

1. Connect to your hosting package using SSH (instructions).

2. Create the virtual environment using the following two commands.

cd ~
virtualenv sqlenv

virtualenv -p /home/username/bin/python3.8 sqlenv

3. Enter the command below to activate the virtual environment.

source sqlenv/bin/activate

4. Use the following command to update the taps in the virtual environment.

pip install -U pip

Then install the mysql-connector-python package using the command below.

pip install mysql-connector-python

So, now you are ready to connect to the MySQL database using Python.

Connecting to the MySQL database

To connect to the MySQL database, use the code below. When doing so, make sure you record:

• user_name is replaced by the username of your database.
• replacethe password with the password of the database user.
• replacedatabase_name with the name of the database you want to connect to.

import axis
import sys
import mysql.connector

sys.path.insert(0, os.path.dirname(__file__))

def application(environ, start_response) :
start_response('200 OK', [('Content-Type', 'text/plain')])

hostname = 'localhost'
username = 'username_name'
password = 'password'
database = 'database'

# Open database connection
MySQLconnection = mysql.connector.connect( host=hostname, user=username, passwd=password, db=database )

if MySQLconnection.is_connected():
# Prepare a cursor object using cursor() method
cursor = MySQLconnection.cursor()

# CREATE table Table with ID and Name attributes using execute() method
cursor.execute("CREATE TABLE `Table` (ID int NOT NULL, Name CHAR(20));")

# INSERT data in table Table
cursor.execute("INSERT INTO `Table` (`ID`, `Name`) VALUES ('1', 'NEOSERV'), ('2', 'Python'), ('3', 'App');")

else:
# ERROR message
message = 'Error connection to database!'

# Message of SUCCESS
message = 'TABLE TABLE WITH ATTRIBUTES ID AND NAME WAS SUCCESSFULLY CREATED.\n RECORD:\n'

# SELECT data from table Table
cursor.execute("SELECT Name FROM `Table`;")

# SAVE data from SQL to variable
names = cursor.fetchall()

all_names = ''
for name in names:
all_names = all_names + str(name)
answer = '\n'.join([message,all_names])

# Close Cursor
cursor.close()

# Close database connection
MySQLconnection.close()
return [answer.encode()]

Once you have a Connection object connected to the database, you can create a Cursor object. This allows you to use the execute() method to execute SQL statements.