Python | A Database interaction class using pymysql

This article is to introduce you to pymysql and talks about how to use it.

I have this simple class in Python which can help you to connect to Mysql DB and fetch or execute your sql statements. Hope this will be useful to all.

This class uses pymysql package to do the database interaction and has functions to connect and disconnect the connections.

Functions in the Class

Fetch : Will help you fetch any results from the DB when a valid sql query is provided as input.

Execute : This will help you execute any DML / DDL statements.

import pymysql

class DBHelper:

    def __init__(self):
        self.host = "127.0.0.1"
        self.user = "root"
        self.password = ""
        self.db = "emp"

    def __connect__(self):
        self.con = pymysql.connect(host=self.host, user=self.user, password=self.password, db=self.db, cursorclass=pymysql.cursors.
                                   DictCursor)
        self.cur = self.con.cursor()

    def __disconnect__(self):
        self.con.close()

    def fetch(self, sql):
        self.__connect__()
        self.cur.execute(sql)
        result = self.cur.fetchall()
        self.__disconnect__()
        return result

    def execute(self, sql):
        self.__connect__()
        self.cur.execute(sql)
        self.__disconnect__()

About Pymysql

PyMySQL is a Python library that provides a convenient interface for interacting with MySQL databases. It bridges Python and MySQL, enabling seamless connections, queries, and manipulations of MySQL databases in Python applications.

One of the key advantages of PyMySQL is its simplicity and ease of use. It abstracts MySQL protocol complexities, offering user-friendly API. Connecting with PyMySQL is simple: few lines to establish connection, execute queries.

It also offers strong support for data security. It incorporates various mechanisms to prevent SQL injection attacks, a common security vulnerability in database applications. Developers safeguard apps with parameterized queries and input escaping, preventing SQL injection. PyMySQL’s efficiency suits diverse apps. Performance excels in bulk operations with optimized methods.

Developers can easily integrate PyMySQL into their projects through package managers like pip. After installation, utilize by importing, employing classes/methods for CRUD operations—creating, retrieving, updating, and deleting MySQL data. In conclusion,

PyMySQL plays a vital role in the Python ecosystem by enabling seamless interaction with MySQL databases. It’s user-friendly interface, security focus, and efficient performance suit developers building robust database-driven apps. It empowers personal, business, and web projects, leveraging Python’s versatility and power.

You can read the same article on my medium blog here