Connect to MySQL in Python (3 Best Libraries)

Python programmers connect to databases, write new data, or update existing data regularly throughout the day. Python has rich libraries to connect to databases (both SQL and NoSQL).

There are a lot of ways to connect to the MySQL database itself. In this article, we will cover the best ways to connect to MySQL in Python.

Libraries to connect to MySQL in Python

Below Python libraries support functions to connect to MySQL db in Python:

• Python MySQL Connector
• PyMySQL
• SQLAlchemy
• MySQLdb
• oursql
• aiomysql

Recommended Libraries

We recommend the below libraries to connect to MySQL based on regular updates, community, and documentation.

• SQLAlchemy
• Python MySQL Connector
• PyMySQL

1. Using Python MySQL Connector

Install Python MySQL Connector

We can install the MySQL Connector with the below command using pip3. Alternatively, You can visit our complete guide on Python MySQL Connector installation on different platforms.

pip3 install mysql-connector-python

Connection using Python MySQL Connector

Using Python MySQL connector library, we can establish the database connection in two ways.

1. Using mysql.connector.connect() function
2. Using mysql.connector.MySQLConnection() class

Both the methods are pretty much straight-forward and there are no performance related differences. However, mysql.connector.connect() method require fewer lines of code compared to other method.

In the below examples, we are going to use connector.connect() function or mysql.connector.MySQLConnection() to create a MySQL database connection.

Both of these provides a lot of options on how do we connect and query the DB. Here are a few important parameters this function accepts.

• host: Host name or IP address of the MySQL database server
• port: Port number on which MySQL service is running. The default port for MySQL is 3306.
• user: User name of the database connection
• password: Password for the user

The other allowed parameters includes auto_commit, raw, connection_timeout etc.

1. Connection without connection pooling

This is a plain way of connecting to MySQL DB from Python code. It can be used in personal projects but not a good idea to connect to MySQL directly without a connection pool in a production level code base.

Code Thread:

from mysql import connector

try:
    connection = connector.connect(host='host', port='port', database='database', user='user', password='password')
    print('Connected to MySQL:', connection.is_connected())
except Exception as e:
    print('Error connecting to MySQL Database:', e)

Output:

Connected to MySQL: True

2. Connection with connection pooling

When you pass any one of the pool_name or pool_size to connect() function, the connection pool will be created automatically.

Code Thread 1:

from mysql import connector

try:
    # keeping credentials in the code is a potential risk. instead you should store them in a configuration file, environment variables.
    connection = connector.connect(host='host', port='port', database='db', user='user', password='password', pool_name='my_pool', pool_size=8)

    print('Connected to MySQL:', connection.is_connected())
except Exception as e:
    print('Error connecting to MySQL Database:', e)

Output:

Connected to MySQL: True

Code Thread2 – pool connection:

Here is the example code for MySQL connection using MySQLConnectionPool class instead of connect() method.

from mysql.connector import pooling

try:
    pool = pooling.MySQLConnectionPool(host='host', port='post', database='db', user='user',
                            password='pwd', pool_name= 'my_pool', pool_size= 8)

    connection = pool.get_connection()

    print('db server version:', connection.get_server_info())

except Exception as e:
    print('Error creating a pool:', e)

Output:

db server info: 8.0.26

Possible errors to watch out for:

One of the common error you will face while connecting to the MySQL database is the below one.

Error connecting to MySQL Database: 2003 (HY000): Can't connect to MySQL server on 'X.X.X.X:X'

The following things need to be checked out when you encounter the above error.

• Firewall enabled: Check if the firewall is enabled on the MySQL port.
• Accepting only local connections: Check the file mysqld.cnf on the server in the path /etc/mysql/mysqld.conf.d. update the bind-address field to 0.0.0.0
• IP is not granted permission to access MySQL DB: Grant permission to your IP to the MySQL database with the below command.

GRANT ALL ON database.* TO [email protected] IDENTIFIED BY 'password'

If you have done all the above three things, don’t forget to restart the MySQL server with the below command.

sudo systemctl restart mysql

---

2. Using PyMySQL

Install Python PyMySQL

Use the below commands to install PyMySQL to install in case you haven’t installed it.

sudo pip3 install PyMySQL

python3 -m pip install PyMySQL

Using PyMySQL in Python

Code Thread:

import pymysql

try:

    connection = pymysql.connect(host='host', user='wordpress', password='passwrd', database='db')
    with connection.cursor() as cursor:
        sql = "SELECT `id`, `name` FROM `employee` WHERE `email`=%s"
        cursor.execute(sql, ('[email protected]',))
        result = cursor.fetchone()
        print(result)

except Exception as e:
    print('Error connecting to MySQL:', e)

Output:

(1,mbappe)

---

3. Use SQLAlchemy

The SQLAlchemy offers database agnostic functions. The same methods would work for different database vendors like MySQL, Postgres, Oracle etc.

It offers ORM style of SQL Queris there by code is much type safe and readable.

Let’s say we have an Employee table with ID, name, email and employment_type fields. We can create ORM mapping for the table and connect to MySQL DB from Python to query this. Below is the code for this.

Code Thread:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column, String, Integer, Boolean, DateTime, Numeric
from sqlalchemy.ext.declarative import declarative_base

base = declarative_base()


class Employee(base):
    __tablename__ = 'employee'
    __table_args__ = {'extend_existing': True}

    ID = Column(Integer, primary_key=True, autoincrement=True, nullable=True)
    name = Column(String)
    email = Column(String)
    employment_type = Column(String)


password='pwd'
user = 'user'
ip = 'ip'
db = 'db'
db_string = "mysql+pymysql://" + user + ":" + password + "@" + ip + ":3306/" + db
db = create_engine(db_string, pool_size=10, max_overflow=0)
Session = sessionmaker(bind=db)

with Session() as session:
    result = session.query(Employee.name).filter(Employee.email == '[email protected]', Employee.employment_type == 'full-time').all()
    print(result)

Output:

[('Mbappe',)]

SQLAlchemy is one of the most used and preferred way to connect to MySQL in Python.

Best Practices to store database credentials

It is not a good idea to keep database credentials in the code. If you keep them in the code, they may go to GitHub and vulnerable for leakage. Instead, You can use one of the following options to store credentials securely.

1. Use config file
2. Use Environment variables

1. Read Credentials from config file

Credentials in config.py file:

Host = "host"
Port = "post"
Username = "username"
Password = "password"
Database = "Database"

Reading config.py file:

Reading config.py file and making a MySQL Connection in Python using SQLAlchemy. You can read the config.py file the same way in other ways of connecting to MySQL as well.

Code Thread:

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy import Column, String, Integer, Boolean, DateTime, Numeric
from sqlalchemy.ext.declarative import declarative_base
import config


base = declarative_base()


class Employee(base):
    __tablename__ = 'employee'
    __table_args__ = {'extend_existing': True}

    ID = Column(Integer, primary_key=True, autoincrement=True, nullable=True)
    name = Column(String)
    email = Column(String)
    employment_type = Column(String)


password = config.Password
user = config.Username
ip = config.Host
db = config.Database

db_string = "mysql+pymysql://" + user + ":" + password + "@" + ip + ":3306/" + db
db = create_engine(db_string, pool_size=10, max_overflow=0)
Session = sessionmaker(bind=db)

with Session() as session:
    result = session.query(Employee.name).filter(Employee.email == '[email protected]', Employee.employment_type == 'full-time').all()

    print(result)

Output:

[('MBappe',)]

2. Read Credentials from Environment Variables

Another best way to store the credentials is using the Environment variables. These are accessible only on that particular server and can be set from either code or from command line.

Here is the sample code to set the environment variables from code –

import os

#setting the environment variables using os module
os.environ['USER_NAME'] = 'codethreads'
os.environ['PASSWORD'] = 'codethreads_password'

Once you set these, you can always access them from the code with the below code snippet –

import os

user_name = os.getenv('USER_NAME')
password = os.getenv('PASSWORD')

This will return the values stored in those environment variables.

Hope you have got the gist of connect to MySQL in Python. For more such insightful articles, follow codethreads.dev!!

Frequently Asked Questions