PYTHON — SQL Hand in Hand

4 min readJan 22, 2021

Everyone involved in the field of data must be well aware of the importance of Programming as well as the Query Language. Here in the article , I am about to guide you the use of MYSQL and Python together in order to be an efficient problem solver, because that’s what we do.

WHY MYSQL ?:-

For Data Enthusiasts(beginners) , Learning Structured Query Language can be interesting as well as complex at times , but the interface of MYSQL to start with provides a great platform to implement the learnings.

Talking professionally , MYSQL is a fully managed database service to deploy cloud-native applications. Also , For the management of the databases MYSQL is quite handy and provides a robust environment . Major important features of SQL can be easily implemented through the queries used in MYSQL like maintaining the atomicity , consistency, isolation and dependency of the database . Be it, Organization or small businesses MYSQL can be used with absolutely zero cost with very less maintenance .

WHY PYTHON ? :-

Python , as we all know is an interpreted , high-level and general-purpose programming language . It is widely used in the industry because Python is one of the easiest languages to code in , people who have worked in 2–3 languages would agree with me on this hands-down! 😜Also if you explore the inbuilt packages and libraries of Python you would find that there is no domain left untouched by Python and its community . Python usually has answers to all your questions ; you just need to be skilled enough to know its basics and how and what to implement from its packages to get your job done.

PYTHON — MYSQL HAND IN HAND :-

********************************************

Suppose, you are working full stack for any organization or a small business and you need to maintain your database as well as the Modelling of the data all by yourself this is what you need . BE A whole TEAM all by yourself!😃

So , now how would it be to use both of them together ? 😮

In the industry , a good data scientist is one who can make an interesting and an easy to understand story around the data . So, basically connecting MYSQL with Python will give u a lot more options to work around with your data . Like managing the database using MYSQL ,which would be a tough task to do in Python . And Preprocessing , Cleaning, Model building using Python .

HOW DO WE BASICALLY CONNNECT MYSQL WITH PYTHON :-

Open your jupyter notebook , write the command pip install mysql-connector-python . Once you are done installing the package, you can proceed for next steps .
import the package using the command :-

import mysql.connector as mysql
mydb=mysql.connect(host=”localhost”,user=”root”,password=”****",database=”python”)
cur=mydb.cursor()

the above code will import the connector and establish a connection to your mysql server , from which you can access all the databases and tables and can move ahead with your task in hand .

3. Executing the Queries in MYSQL using Python :-

query=”create table mylearning(id int(2), topic varchar(50)”
cur.execute(query)
mydb.commit()

The above code you write in your Python Notebook and you will get the changes reflected on your MYSQL server . This is a basic snippet on how you can access MYSQL from the use of Python Notebooks . Similarly , one can use it for carrying out various complex and tedious tasks.

ACCESSING A DATABASE from MYSQL and making it ready for preprocessing :-

You need not always get the data as a csv file in a ready documented format, sometimes for small businesses and startups it is required to maintain the database by yourself , there comes the actual need to maintain the data in your databases ensuring all the important features intact like consistency and dependency. Below is a code snippet where i have accessed a table from a database and converted it into python data frame using pandas.