How to connect to mysql database using python

Different ways to connect to mysql using python

Photo by Campaign Creators on Unsplash

Introduction:

This article discusses how to connect to a mysql database using python. We will review three ways of connecting to mysql using python

If you’re looking for an introduction to basic SQL queries, please read this post.

Connect to mysql using sql connector:

The connection to mysql can be established using mysql-connector-python library.

$pip install mysql-connector-python

Collecting mysql-connector-python
  Using cached mysql_connector_python-8.0.30-cp39-cp39-macosx_11_0_x86_64.whl (5.1 MB)
Collecting protobuf<=3.20.1,>=3.11.0
  Using cached protobuf-3.20.1-cp39-cp39-macosx_10_9_x86_64.whl (962 kB)
Installing collected packages: protobuf, mysql-connector-python
Successfully installed mysql-connector-python-8.0.30 protobuf-3.20.1
import mysql.connector


def connect_to_mysql() -> mysql.connector:
    """
    Establish the database connection.
    """

    cnx = mysql.connector.connect(
        user="username",
        password="password",
        host="hostname",
        database="databasename",
        connection_timeout=10000
    )

    return cnx

Connect to mysql using MySQLConnectionPool:

from mysql.connector.pooling import PooledMySQLConnection, MySQLConnectionPool

def connect_to_mysql_pool() -> MySQLConnectionPool:
    """
    Establish the database connection.
    """

    connection_pool = MySQLConnectionPool(pool_name="mainpool",
                                          pool_size=3,
                                          pool_reset_session=True,
                                          user="username",
                                          password="password",
                                          host="hostname",
                                          database="databasename",
                                          allow_local_infile=True
                                         )
    return connection_pool

• The mysql.connector.pooling module implements pooling.
• A pool opens a number of connections and handles thread safety when providing connections to requesters.
• The size of a connection pool is configurable at pool creation time. It cannot be resized thereafter.
• A connection pool can be named at pool creation time. If no name is given, one is generated using the connection parameters.
• The connection pool name can be retrieved from the connection pool or connections obtained from it.
• To release a pooled connection obtained from a connection pool, invoke its close() method, just as for any unpooled connection. However, for a pooled connection, close() does not actually close the connection but returns it to the pool and makes it available for subsequent connection requests.

Connect to mysql using SQLAlchemy:

In most of the projects, we will be using an ORM predominantly to connect to databases. ORMs are not only convenient but also database agnostic which means less code change. The only changes may be the connection string or driver.

We will be using pymysql driver to connect to mysql using SQL Alchemy.

Install pymysql

$ pip install pymysql

Code to connect to mysql using SQL Alchemy

from sqlalchemy import create_engine
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import sessionmaker
from urllib.parse import quote


def connect_to_mysql_orm():

    connection_url = "mysql+pymysql://username:{}hostname/dbname".format(quote("pa$$w0RD@"))

    try:
        engine = create_engine(url=connection_url)

        # create a session
        session = sessionmaker(bind=engine)
        conn = session()

        if session:
            return conn

    except SQLAlchemyError as se:
        print(se)

Originally published at https://dock2learn.com on September 28, 2022.