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

Carvia Tech | February 12, 2020 | 2 min read | 223 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. Python Flask Interview Questions
  2. Top 100 interview questions on Data Science & Machine Learning
  3. Python coding challenges for interviews
  4. Google Data Scientist interview questions with answers
  5. Connect to Cassandra with Python 3.x and get Pandas Dataframe
  6. Connect to Postgresql with Python 3.x and get Pandas Dataframe
  7. Google Colab: import data from google drive as pandas dataframe


Find more on this topic:
Machine Learning image
Machine Learning

Data science, machine learning, python, R, big data, spark, the Jupyter notebook, and much more

Last updated 1 week ago


Recommended books for interview preparation:

This website uses cookies to ensure you get the best experience on our website. more info