MySql And Python | Creating Database Using Python — ML For Lazy 2021

Hey guys, welcome back to this amazing and important post of Python and MySql, which we will look at in this upcoming post. So in this series on MySql and Python, we will see how we can work with MySQL and Python. The data can be present in several ways — The data can be present in CSV files, The data can be present in Excel files, The data can be present in many, many more formats and ways. Most importantly, data can be present in databases.

Since we know that data plays an important role in machine learning, we will focus on the database part of the data where we have data stored in the database. We will see how we can use MySQL to work with databases and retrieve the data stored in databases to work with that data in our environment.

So in this part of the series, we will focus on installing MySQL in our system, and then we will see how to install a package with which we can work with MySQL and the corresponding database. We will go through basic commands to insert the data into the database, retrieve the data from the database, and finally see how we can store and access that data within our environment and work with that data.

Finally, we will convert that data into pandas dataframe object, use all the functions available to us that the pandas give us, and apply those functions and methods to that data.

So let’s start. So, first of all, what we need to do is to go to the browser, and we need to install MySQL.

Installing MySql

So we need to search for MySQL Download in the google search bar and go to the MySql website. There, We will go and download MySQL.

After clicking there, you will be redirected to the new page, choosing among the many offered products. Click on the MySQL community server and choose your operating system.

Most of the time, the website chooses it on its own if not, click the dropdown in the green box below and then download as per the operating system.

When the download is finished, you will have to install the server package. While installing, you will be asked to set the password for the server, set the password you would remember because that password will be needed.

If you are on mac os, go to settings, and there you will see the Mysql server below in the last section. Click here and proceed further.

But if you are on windows, you can find it in the start menu or search in the search bar alongside the start menu.

Additionally, you can initialize the new database and set a new password for this database if you don’t remember the previous password. Let us do that. Click on Initialize database and Set the new password for this Database and Click on Ok. Here you can click on ‘Use Legacy Password’ (Optional).

After all these steps, Start the MySql server, and You are good to go and work on the databases.

Since we will be using Python to work with the databases, we need to install a Package, which lets us work with Mysql Databases using Python. The name of the package is — MySQL-connector-python .

Here we can install it inside the Jupyter Notebook or in the terminal. For the Jupyter notebook, we have to write a command like in the image below.

In the case of the terminal — Which is common -

pip install mysql-connector-python

After installing, we are fully wrapped up and ready to work with MySql and Python.

The first thing that we need to do is to create the connection with the MySQL database and which is done using the following code -

import mysql.connector as connection

Secondly, we have to connect with the database server, which is done using the connect method of the Package. In the connect method, we have to pass the following parameters -

1. Host — “localhost”
2. User — “root”
3. Password — “password you have set.”
4. Database — “name of the database to be worked on.”

Since we have not created any database yet, so we need not pass the last parameter. When we have created the database, and when we will work on individual databases, at that time, we have to pass the database name while creating the connection with the server.

conn = connection.connect(host="localhost", user="root", password="your password")

Let us see what is in the conn variable.

Here we can see, it is the connection object of the Mysql connector.

For executing the Queries, we must have a Cursor object, which is used to execute queries and, in short, for performing operations on the database. It is like a computer cursor. What that cursor does — lets us execute the stuff by pointing on things and executing the stuff. The same function is for this cursor.

cursor = conn.cursor(buffered=True)

Now, let us execute the first query, i.e., Create a database. For executing the query, we have to pass the query to the execute method of the cursor.

cursor.execute("create DATABASE employees")

When this query executes, our database gets created. Let us see them.

cursor.execute("SHOW DATABASES")

All the present databases are stored in the cursor object, and we can see them as follows.

for database in cursor: print(database)

When we have done the operations, we need to close the connection to the server.

Conclusion

Thus, we saw how we could download and connect the database server. Move your hands and get it going on your laptop or pc or whatever you have.

In the next post, we will see how to create a table and Insert data into that table.

If you like this post and want to read more, follow this blog and share the posts, that would be greatly encouraged.

My previous post, Polynomial Regression in Python — Check that out here.

I am a Postgrad student from Kashmir in Computer Science. In these covid days, I turn towards spreading the information about machine learning which is my Passion and Future studies. The aim is to make people understand and understand the basic concepts of Machine and Deep learning myself, which are crucial to further success in this field.

Originally published at https://mlforlazy.in on June 12, 2021.