How we connect MySQL with Python and how we store data in Database, how we create tables, update, delete, fetch...we will see all the process in this topic.

What is the installation step in Database?

First of all, you will use PYMySQL.

We will have to install PYMySQL first of all.

How can you install it? You just have to write only 1 command in CMD that is

Pip Install PYMySQL.

Let’s see how to do it.

We opened a CMD & we have to write the command that I highlighted here which is Pip Install PYMySQL.

How do we have to write it? Pip Install PYMySQL.

The installation process will be done as soon as we enter the command.

Then we have to close CMD.

Then we have to install the software SQLYOG, it is for your database where it will be visible, the interface will be visible.

I’ll show you how to install that too!

Before installing SQLYOG, you’ll have to download 2 softwares which are - MySQL Essential 5.1 & SQLyog, community version.

You have to download both the softwares from Google.

You have to install MySQL Essential first.

Double click > Run > Next > Accept > Next > Select Typical > Next > Install.

I’ll enter my password: Network.

Click Next here too. Next & Finish.

Now configuration will start.

As soon as you press Finish, it will start configuring.

Next > Next > Next > Next > keep on clicking Next.

You can select Port number from the 4 options here.

By default it will select 3306.

Next > Next. Here in Install as Windows Service, you have to select MySQl only.

If you want to keep a password, you can keep it from here.

And if you don’t want to then untick this box.

Next > click on Execute.

Your services will start and then Finish.

Next you have to install SQLyog.

It is very necessary that you install MySQL Essential.

Then only SQLyog will be installed.

Next > Install.

Installation done & Next.

Then when you Run it, your SQLyog Interface will open up.

Click on New. You can edit the name to something else as well…

I’m going to keep it as a New Connection only.

Okay.

All the details are here.

We will now do Test Connection.

A message of “connection successful” popped up.

Ok & Connect.

Just as you connect it, the interface will open up like this.

This is our Database Interface.

Meaning whatever Database you will create now on, will be created here in this Software.

First of all, how will we work with Database? Let’s check it.

Step for working with Database.

First off, we have to create a database in SQLyog.

For that, open SQLyog.

Right click on root@localhost.

Select the Create Database option.

I’ll give the database name as LearnVern.

The database opened up here.

In which you have Tables, Views, Stored Procedures, Functions, Trigger & Events.

So we can’t see any table here right now but we have to create it using Python.

You have to create the database in this manner first in the SQLyog community.

Next up, you have to import the API module.

Then you have to acquire a connection with the database.

Meaning first you have to import the API, the PYMySQL we installed.

We have to import that in our code.

Then we have to create a connection with the database so we will create a function there.

Then we will issue the SQL statement meaning we will fire our queries.

Then we will close our connection. We will check one by one how to import the API.

So whenever you want to import API, you have to write import PYMySQL.

Write what? Import PYMySQL.

Later when you are going to create a connection with Database, you have to create a variable named Connect = PYMySQL . (dot) connect method.

Which method do you have to use? Connect method.

In which you have to pass 5 attributes.

1st is Host. Host works on a particular database server.

What will be our server? It will be a local host.

Next is Database. What is the name of the database?

Next is the user. The by default user is root.

Then Password. If you’ve kept a password then you have to write the password.

If you don’t have one then you have to keep a blank.

And then you have to use Port = 3306.

So, I’ll show you practically how we do both the things.

I have opened the Jupyter.

First of all, I will import.

Import PYMySQL API.

Let’s see how to do it.

You simply have to write Import PYMySQL. Only this much.

Automatically PYMySQL will be imported.

Next you have to create the connection.

I’ll make another tab here.

I’ll show you how to create a connection.

Creating connection with Database.

All you have to do is create a function.

How do we make a function? By using the Def keyword.

I’ll write the function name. Suppose Create Conn.

We made a function here.

Now this function will return it. Return what?

Our PYMySQL . (dot) connect method.

PYMySQL . (dot) connect.

The 5 attributes we saw...which ones?

First of all, it was the Host.

So write Host = Localhost.

Localhost means our normal PC server.

Then we have to write a database.

We had made a database with the name Learnvern.

Then we have to write User. User is the root by default.

Next will be Password. I’ll keep the password blank as I don’t have a password.

Then we want a Port number. So Port = 3306.

So we made a create connection function where we passed all the 5 attributes with values.

Okay? I hope you understood so far.

Now that you have made both of these things, you have to make a cursor, write a query, and then execute using the cursor . (dot) execute function.

But cursor is used for what?

Cursor is used to execute your queries, okay?

And we will write our queries in our query variable according to our requirement.

And there is a method called Execute which will execute your query.

At last you have to close the connection where you have to use Connection . (dot) commit…

Commit method saves in your database and connection . (dot) close will close your connection.

Did you understand how all of this works in Database?

So in the next video we will learn how to create & insert a table in database.

Thank you guys.

FAQs

Two of the most popular open source databases for storing Python web application data are PostgreSQL and MySQL. SQLite is a built-in feature of Python, however it can only be accessed by one connection at a time.

To connect to a MySQL database, Python requires a MySQL driver. To install "MySQL Connector," we propose using PIP. Your Python system most likely already has PIP installed.

Using Python to create a MySQL database You have the option of using an existing database or creating your own. To build or delete a MySQL database, you'll need particular permissions. You can build any database if you have access to the root user.

hierarchical database systems. network database systems. object-oriented database systems.

Because you don't have to install any external Python SQL databases or types or SQL database modules, SQLite is probably the most obvious and popular SQL database to connect with a Python programme.