CRUD operations with MYSQL database using python

In this article we will be talking about basics of database which is CRUD where C stands for CREATE,R stands for READ, U stands for UPDATE and D stands for DELETE. You can use these basic concepts in any database.

DATABASE

A database is a file or a place where you save your data. The data is saved in a table which consist of rows and columns. The Table is a collection of data and the database is a collection of tables.

 Database is one of the most important things if you want to develop any application or any other thing on web. Without knowing where to store the data you can merely develop any application. So Database Management System is an essential thing. In this article we will be dealing with MySQL which is one of the widely used DBMS servers.

Connect python with MySQL DB

To do the manipulation in the data we first need to integrate python with a database server. So let us try to understand how python is integrated with the backend DB server.

We will be using the MySQL connector Python API. It enables the python program to access MySQL databases. Consider the python program as any web application or website which lies in the frontend and DB server as backend. The frontend python application sends a connection request to the database through MySQL connector python API. Then the database accepts the request and sends a connected message.

Further python application will send cursor connection request. We can think of Connection as a method and cursor as an object through which we can communicate with MySQL DB server, also we can do CRUD operation. Python program/application needs to execute SQL statement or query and DB server will fetch the expected result.

So as the connection is establish between python application and DB server we are good to go with CRUD operation.

CRUD Operation

In order to implement CRUD operation we need to install MySQL Connector package.

pip install mysql-connector

So let us start by creating a database

import mysql.connector
db=mysql.connector.connect(host=”localhost”, user=”root”,password=”password123”)
mycursor=db.cursor()
mycursor.execute(‘create database tonydb’)

To get the list of all the databases available you can use;

mycursor=db.cursor()
mycursor.execute(‘show databases’)
for i in mycursor:
      print(i)

Now to establish the connection in between python program and DB we created here tonydb:

import mysql.connector
db_connection=mysql.connector.connect(
                                 host=’localhost’
                                 username=’root’
                                 passwd=’password123’
                                 database=’tonydb’
                                    )
my_db=db_connection.cursor()

Create operation

My_db.execute(“CREATE TABLE coronavirus(name varchar(200), cases int(20)”)

By using this operation we can create data in mysql database table. In other words we can insert data into our database table. So to insert the data into the table we can use.

sql_code=”INSERT into coronavirus(name,cases) values(%s,%s)”
corona=[(“usa”,1160838),(“India”,39980),(“uk”,182260),]
my_db.executemany(sql_code,corona)
db_connection.commit()

READ Operation

my_db.execute(“SELECT name FROM coronavirus”)
result=my_db.fetchone()
for i in result:
     print(i)

Fetchone will only read the first element from the table. To read all the elements you can use fetchall()

Instead of fetchone().

There is another method available that lets you select the number of rows you need by specifying the size.

fetchmany(size=n)

Where n is the number of rows you want to read.

my_db.execute(“SELECT * FROM coronavirus”)
result=my_db.fetchall()
for i in result:
     print(i)

Above code will read all the data from the table.

UPDATE Operation

up=”UPDATE coronavirus SET cases=40000 WHERE name=’India’ ”
my_db.execute(up)
db_connection.commit()

Using this operation we can update the values in the database table. We can modify the data.

DELETE Operation

dlt=”DELETE FROM coronavirus WHERE name=’uk’ “
my_db.execute(dlt)
db_connection.commit()

using this operation we can delete any data from the DB table.