In order to read data from MySQL in Python, the libraries needed are sqlalchemy, mysql.connector and pymysql.
You can install these libraries using below commands in a cell of your Jupyter notebook. These commands can also be executed in command prompt without the exclamation “!”.
|
1 2 3 4 |
# installing the required libraries to connect with MySQL Database !pip install sqlalchemy !pip install mysql.connector !pip install pymysql |
Once the libraries are installed, it is a two step process to connect to mysql database.
- Establishing Connection: A connection object is created using create_engine() function from sqlalchemy library. This stores all the information required to login to the database.
- Run SQL query: Any valid sql query(simple or complex both) can be executed on the database with the help of above connection and pandas function read_sql()
Below snippet connects to the mysql database and runs a given query. Change the database credentials based on your system.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
from sqlalchemy import create_engine import mysql.connector as sql import pymysql UserName='root' Password='hello@123' DatabaseName='classicmodels' # Creating the database connection db_connection_str = "mysql+pymysql://"+UserName+ ":" +Password +"@localhost/"+ DatabaseName db_connection = create_engine(db_connection_str) import pandas as pd # Employees table must be present in the database DataFromDB = pd.read_sql('SELECT * FROM employees', con=db_connection) DataFromDB.head() |
Sample Output:
Author Details
Lead Data Scientist
Farukh is an innovator in solving industry problems using Artificial intelligence. His expertise is backed with 10 years of industry experience. Being a senior data scientist he is responsible for designing the AI/ML solution to provide maximum gains for the clients. As a thought leader, his focus is on solving the key business problems of the CPG Industry. He has worked across different domains like Telecom, Insurance, and Logistics. He has worked with global tech leaders including Infosys, IBM, and Persistent systems. His passion to teach inspired him to create this website!