Connect to MySQL with Python 3.x and get Pandas Dataframe

Carvia Tech | February 12, 2020 | 2 min read | 421 views


We will be learning

  • How to connect to MySQL in Python

  • How to get output of query as Pandas Dataframe

Connect to MySQL in Python

We will be using mysql-connector-python Library to make connection.

Install library for connecting to MySQL

Create virtual environment in project directory

virtualenv -p python3 venv

Activate virtual environment

source venv/bin/activate

To know more about virtual environment, click here

Install package

pip install mysql-connector-python

Define a class for connecting to MySQL

from mysql.connector import pooling
import mysql.connector


class MySqlDs(object):

    def __init__(self):
        self.connection_pool = None
        self.db_config = db_credentials //(1)
        self.create_pool()

    def create_pool(self):
        self.connection_pool = mysql.connector.pooling.MySQLConnectionPool(pool_name="pynative_pool",
                                                                        pool_size=4,
                                                                        pool_reset_session=False,
                                                                        **self.db_config)

    def get_connection(self):
        return self.connection_pool.get_connection()
  1. db_credentials should be stored in dict or json format. Let’s say, they are stored in config.py script.

E.g.

db_credentials = {
    'user': "root",
    'password': 'password',
    'host': 'localhost',
    'database': 'mydatabase',
    'charset': 'utf8'
}

import config.py from config folder like

from config.config import db_credentials

Now, we will be making another class to initiate the mysql connection

import pandas as pd

class mySQlDao(object):

    def __init__(self, sqlDb):
        self.sqlDb = sqlDb
        self.connection = sqlDb.get_connection()

    def __del__(self):
        print("Closing db connection")
        self.connection.close()

    def read_table(self,query):
        if self.connection.is_connected():
            dataframe = pd.read_sql_query(query, self.connection)
            return dataframe
main.py
dataSource = MySqlDs()
dbclass = mySQlDao(dataSource)

query = """
            select * from table
            """
data = dbclass.read_table(query)

Output of data will be a pandas dataframe. Happy Working!!

In case, you are looking to connect to PostGreSQL Database then follow this article


Top articles in this category:
  1. Connect to Postgresql with Python 3.x and get Pandas Dataframe
  2. Connect to Cassandra with Python 3.x and get Pandas Dataframe
  3. Google Colab: import data from google drive as pandas dataframe
  4. Top 100 interview questions on Data Science & Machine Learning
  5. Google Data Scientist interview questions with answers
  6. Python - Get Google Analytics Data
  7. Python send GMAIL with attachment

Recommended books for interview preparation:

Find more on this topic:
Buy interview books

Java & Microservices interview refresher for experienced developers.