CRUD operation in Python with MYSQL using OOP

python-with-mysql
Spread the love

Python with Mysql tutorial, I will show you how to implement basic CRUD operations using OOP. In this tutorial, I will show you a couple of functions related to CRUD. So here we go.

Before jumping this tutorial, You have to knowledge about OOP in python. Also, you have to know the basic commands of MYSQL. I assume that you have knowledge about OOP and MYSQL.

MYSQL

SQL is a standard language for storing, manipulating, and retrieving data in databases.

OOP

Python is an object-oriented programming language. Almost everything in Python is an object, with its properties and methods. A Class is like an object constructor or a “blueprint” for creating objects.

Read More : Implement Quick Sort using Python and C++ & Time complexity

Well, let’s start our code. First, you have to install a PIP package that is a Mysql connector. Python needs a MySQL driver to access the MySQL database. I recommend that you use PIP to install “MySQL Connector”. PIP is most likely already installed in your Python environment. Navigate your command line to the location of PIP, and type the following

pip install mysql-connector-python

Well, after installing this MySQL connector then you have to import it into your project.

import mysql.connector

Well, now create a class whose name is Database. You can give any name whatever you want. Follow this bunch of code.

class Database:
    my_db = my_cursor = None

    def __init__(self):
        global my_db, my_cursor
        my_db = mysql.connector.connect(
            host="localhost",
            user="root",
            password="",
            database="py_sms"
        )
        my_cursor = my_db.cursor()

    def __del__(self):
        my_db.commit()

Well, in this Database class first I took two properties my_db and my_cursor. Initially, those are none. Then I took a constructor, inside the constructor I have connected MYSQL. I forgot to mention that, first you have to create a database in your local host. The MySQL connector function takes four arguments. They are host, user, password, and database name. Then store the function in a variable that I took before. Then call cursor function.

Well, Our database connection has been done. Now notice in destructor, I call another function (commit). You have to call this function. Otherwise, your data will not save or modify.

Well, Now create another function for resources. I have created a Student class that extends our base class (Database). In this class, we will define a couple of functions related to Student CRUD operation. Let’s see the following code. Don’t worry, I will explain you.

 def all_students(self, mode='DESC'):
        sql = "SELECT * FROM students ORDER BY id {}".format(mode)

        try:
            my_cursor.execute(sql)
            result = my_cursor.fetchall()
        except Exception as e:
            return e

        return result

    def insert(self, data):

        sql = "INSERT INTO students (name,roll,address) VALUES (%s , %s, %s)"

        try:
            my_cursor.execute(sql, data)
        except Exception as e:
            return e

        return my_cursor.lastrowid

    def insert_many(self, data):
        sql = "INSERT INTO students (name,roll,address) VALUES (%s , %s, %s)"

        try:
            my_cursor.executemany(sql, data)
        except Exception as e:
            return e

    def delete(self, id):
        sql = "DELETE FROM students WHERE id = {}".format(id)

        try:
            my_cursor.execute(sql)
        except Exception as e:
            return e

    def update(self, id, data):

        sql = "UPDATE students SET name = %s ,roll = %s, address = %s WHERE id = {}".format(
            id)

        val = (data[0], data[1], data[2])

        try:
            my_cursor.execute(sql, val)

        except Exception as e:
            return e

    def truncate(self):

        sql = "TRUNCATE TABLE students"
        try:
            my_cursor.execute(sql)
        except Exception as e:
            return e

        return True

Above, You can see many functions. They are all_students, insert, insert_many, update, delete and truncate.

Read More : Implement Quick Sort using Python and C++ & Time complexity

The all_student function returns all students from the database. Insert and InserMany functions insert data in the database. Update and delete function delete and update data.

Now create an object and check all functions like below.


db = Database()
st = Student()


# sql = "INSERT INTO students (name, roll,address) VALUES ('kabir',505, 'India')"

# print(db.insert(sql))
# print(st.all_students())
# print(st.insert(('Omi', 1032, 'Dhaka')))
# print(st.truncate())

data = [
    ('Shahin', 101, 'Chandpur'),
    ('Asik', 102, 'Pabna'),
    ('Tanzim', 105, 'Sylhet'),
    ('Nasif', 103, 'Cumilla'),
]

# st.insert_many(data)

print(st.all_students())

# st.delete(5)

st.update(4, (
    'Tanzim',
    1110,
    'Dhaka'
))
print(st.all_students())

Full Code :

import mysql.connector


class Database:
    my_db = my_cursor = None

    def __init__(self):
        global my_db, my_cursor
        my_db = mysql.connector.connect(
            host="localhost",
            user="root",
            password="",
            database="py_sms"
        )
        my_cursor = my_db.cursor()

    def __del__(self):
        my_db.commit()


class Student(Database):

    def all_students(self, mode='DESC'):
        sql = "SELECT * FROM students ORDER BY id {}".format(mode)

        try:
            my_cursor.execute(sql)
            result = my_cursor.fetchall()
        except Exception as e:
            return e

        return result

    def insert(self, data):

        sql = "INSERT INTO students (name,roll,address) VALUES (%s , %s, %s)"

        try:
            my_cursor.execute(sql, data)
        except Exception as e:
            return e

        return my_cursor.lastrowid

    def insert_many(self, data):
        sql = "INSERT INTO students (name,roll,address) VALUES (%s , %s, %s)"

        try:
            my_cursor.executemany(sql, data)
        except Exception as e:
            return e

    def delete(self, id):
        sql = "DELETE FROM students WHERE id = {}".format(id)

        try:
            my_cursor.execute(sql)
        except Exception as e:
            return e

    def update(self, id, data):

        # sql = "UPDATE customers SET name = %s,roll = %s,address = %s WHERE id = {}".format(
        #     id)
        sql = "UPDATE students SET name = %s ,roll = %s, address = %s WHERE id = {}".format(
            id)

        val = (data[0], data[1], data[2])

        try:
            my_cursor.execute(sql, val)

        except Exception as e:
            return e

    def truncate(self):

        sql = "TRUNCATE TABLE students"
        try:
            my_cursor.execute(sql)
        except Exception as e:
            return e

        return True


db = Database()
st = Student()


# sql = "INSERT INTO students (name, roll,address) VALUES ('kabir',505, 'India')"

# print(db.insert(sql))
# print(st.all_students())
# print(st.insert(('Omi', 1032, 'Dhaka')))
# print(st.truncate())

data = [
    ('Shahin', 101, 'Chandpur'),
    ('Asik', 102, 'Pabna'),
    ('Tanzim', 105, 'Sylhet'),
    ('Nasif', 103, 'Cumilla'),
]

# st.insert_many(data)

print(st.all_students())

# st.delete(5)

st.update(4, (
    'Tanzim',
    1110,
    'Dhaka'
))
print(st.all_students())

Learn Laravel


Spread the love

About Anisur Rahman Shahin

Hello. My name is Shahin. I'm a tech enthusiast guy. Personally, I’m an Optimistic and always in hurry kinda person. I'm a freelance web developer. I am working at Zakir Soft as Laravel Developer. My Portfolio website: https://tutspack.com

View all posts by Anisur Rahman Shahin →

Leave a Reply

Your email address will not be published.