MySQL Crud by Python DB Class and Error Handling

Estimated reading time: 10 minutes

 

 

 

Download Guideline

• Install Python on Windows by clicking Python Downloads, or search a Python setup pack for Linux.
• The installation package for Windows also contains pip install, which allow you to obtain more Python libraries in the future.

 DOWNLOAD SOURCE

 

 

Using mysqlclient

In Python3, it is suggested to install the Python library mysqlclient for MySQL CRUD access.

$ pip install mysqlclient

In our example, we will create a DB class using Python scripts as below for MySQL database connection. More references about mysqlclient are in MySQLdb’s documentation.

 import MySQLdb as mysql
 # Connect database with configuration
 dbconn = mysql.connect( \
 host="localhost", user="username", passwd="password", db="dbname")

 

CRUD – Create/Read/Update/Delete

It will be presented here that mysqlclient is not complex. For instance, as the following Python scripts using MySQL indicate, we execute an INSERT command, one of CRUD operations, and commit it.

 sql = "INSERT INTO population (country, population) VALUES ({}, {})".format("india", "1,380,004,385")
 # Set cursor
 cursor = dbconn.cursor()
 cursor.execute(sql)
 # Commit
 dbconn.commit()

You will find that the placeholder formatting in Python string generate a MySQL command with arguments. These codes below perform a CRUD operation by SELECT command, and reply with data of tuple format in Python. We call it .fetchone().

 sql = "SELECT * FROM population WHERE country={}".format("india")
 # Set cursor
 cursor = dbconn.cursor()
 cursor.execute(sql)
 # Fetch one record
 atuple = cursor.fetchone()

("india", "1,380,004,385")

These codes show the result of tuples in a tuple. We call it .fetchall(). In our Python DB class, we convert tuples into a Python list format.

 sql = "SELECT * FROM population"
 # Set cursor
 cursor = dbconn.cursor()
 cursor.execute(sql)
 # Fetch all records
 tuples = cursor.fetchall()

(("india", "1,380,004,385"),("United States of America", "331,002,651"))

The UPDATE and DELETE commands perform the MySQL CRUD operations using Python scripts, which is straightforward because of not considering exception handling. To handle exceptions is essential, and we suggest database class will make it not difficult.

 sql = "UPDATE population SET population={} WHERE country={}".format("1,380,004,385", "india")
 # Set cursor
 cursor = dbconn.cursor()
 cursor.execute(sql)
 # Commit
 dbconn.commit()

 sql = "DELETE FROM population WHERE country={}".format("india")
 # Set cursor
 cursor = dbconn.cursor()
 cursor.execute(sql)
 # Commit
 dbconn.commit()

 

Python MySQL Exception Handling

We propose a Python database class that throws MySQL exception including both error type and error message.

except MySQLdb.Error as e :
    self.__error = "{} {}".format(type(e), e)

For example, MySQL connection errors usually result from login configuration. Furthermore, the UNIQUE KEY column cannot be added more than once.

<class 'MySQLdb._exceptions.OperationalError'>
(1045, "Access denied for user 'myuser'@'localhost' (using password: YES)")
or
<class 'MySQLdb._exceptions.IntegrityError'> (1062, "Duplicate entry 'India' for key 'country'")

 

Open-source Python DB Class

The entire codes in the Python DB class are as below. To use mysqlclient library, you have to import it at the first beginning. The variables leading with two under scores like __error and __rowcount are private, but not referenced from external scripts outside the class.

We define two properties of error and rowcount. Specially, the latter one allows developers to check whether the SQL statement execution has affected rows or not.

import MySQLdb
class EasyDB :
    def __init__(self, conf):
        # Connect with parameters
        try :
            self.__dbconn = MySQLdb.connect( \
            host=conf[0], user=conf[1], passwd=conf[2], db=conf[3])
            self.__error = ""
        except MySQLdb.Error as e :
            self.__error = "{} {}".format(type(e), e)
    @property
    def error(self) :
        # Report errors
        return self.__error
    @property
    def rowcount(self) :
        # Return the number of affected rows
        return self.__rowcount
    def fetchall(self, sql) :
        try :
            # Set cursor
            cursor = self.__dbconn.cursor()
            cursor.execute(sql)
            # Fetch all
            tuples = cursor.fetchall()
            self.__rowcount = cursor.rowcount
            self.__error = ""
            # Convert tuple to list
            thelist = list()
            for atuple in tuples:
                row = list(atuple)
                thelist.append(row)
            if thelist == None : return ""
            return thelist
        except MySQLdb.Error as e :
            self.__error = "{} {}".format(type(e), e)
    def fetchone(self, sql) :
        try :
            # Set cursor
            cursor = self.__dbconn.cursor()
            cursor.execute(sql)
            # Fetch one and convert tuple to list
            atuple = cursor.fetchone()
            self.__rowcount = cursor.rowcount
            self.__error = ""
            if atuple == None : return ""
            return list(atuple)
        except MySQLdb.Error as e :
            self.__error = "{} {}".format(type(e), e)
    def execute(self, sql) :
        try :
            # Set cursor
            cursor = self.__dbconn.cursor()
            cursor.execute(sql)
            # Commit
            self.__dbconn.commit()
            self.__rowcount = cursor.rowcount
            self.__error = ""
        except MySQLdb.Error as e :
            self.__error = "{} {}".format(type(e), e)

Unfortunately, mysqlclient always returns data in Python tuple format when reading database. We convert results to a preferred Python format, List, in the MySQL class.

 

 

Deriving a Child Class and Customizing It

One of the advantages for object-oriented programming in this example is to derive a child class from EasyDB class as listed above. Inheriting from it, we create a class populationDB in the following.

The parent class EasyDB is in a file dbclass.py under the directory lib. Therefore, Python scripts import the parent class from lib.dbclass as indicated below. Here is a good example for you to learn how class files in a project can be organized.

from lib.dbclass import EasyDB
class populationDB(EasyDB) :
    def __init__(self):
        EasyDB.__init__(self, ["localhost", "myuser", "12345678", "mydb"])
    # create
    def create(self, country, population):
        sql = "INSERT INTO population (country, population) VALUES ('{}', '{}')".format(country, population)
        self.execute(sql)
    # read
    def read(self, country):
        if country == "All" :
            sql = "SELECT * FROM population"
            return self.fetchall(sql)
        else :
            sql = "SELECT * FROM population WHERE country='{}' ".format(country)
            return self.fetchone(sql)
    # update
    def update(self, country, population):
        sql = "UPDATE population SET population='{}' WHERE country='{}' ".format(population, country)
        self.execute(sql)
    # delete
    def delete(self, country):
        if country == "All" :
            sql = "DELETE FROM population"
        else :
            sql = "DELETE FROM population WHERE country='{}' ".format(country)
        self.execute(sql)

Initially, the parameters for MySQL connection customize the populationDB class with host name, user name, password, and database name.

There are 4 methods for CRUD operations, respectively. All of them manipulate only the table population in MySQL database. Moreover, placeholders in the SQL statement are implemented by Python string formatting.

 

Using a Child Class

According to the scenario in this example, when you select a country, select an option such as Create in browser, and then click CRUD operation button, a request will be sent to the server site.

The server-site Python scripts scrape the web to get the country’s population, and add a row, one of CRUD operations, to MySQL database.

To fulfill CRUD operations using child process populationDB, mydb = populationDB() is the first step for connection. Seriously, to check MySQL exception, you can add more Python scripts as the following.

if mydb.error != "" :
    print(mydb.error)
    quit()

For example, wrong passwords would cause such an error when connecting to MySQL. At the moment, Python DB class throws an error such as the following.

<class 'MySQLdb._exceptions.OperationalError'>
(1045, "Access denied for user 'myuser'@'localhost' (using password: YES)")

The Python codes for both CREATE and UPDATE operations call get_population_from_web(country) to get country’s populations. For web scraping, more information are in Python Web Scraping using BeautifulSoup in 3 Steps.

 # connect
 mydb = populationDB()
 if mydb.error != "" :
    print(mydb.error)
    quit()
 # Create
 if action == "Create" :
    result = get_population_from_web(country)
    mydb.create(result[0], result[1])
    if mydb.error != "" :
        print(mydb.error)
        quit()
    print(result)
    print(" ... Created")
 # Read
 elif action == "Read" :
    result = mydb.read(country)
    if mydb.error != "" :
        print(mydb.error)
        quit()
    print(result)
 # Update
 elif action == "Update" :
    result = get_population_from_web(country)
    mydb.update(result[0], result[1])
    if mydb.error != "" :
        print(mydb.error)
        quit()
    print(result)
    if mydb.rowcount == 0 :
        print(" ... Not Found or Not Changed")
    else :
        print(" ... Updated")
 # Delete
 elif action == "Delete" :
    mydb.delete(country)
    if mydb.error != "" :
        print(mydb.error)
        quit()
    print(country)
    if mydb.rowcount == 0 :
        print(" ... Not Found")
    else :
        print(" ... Deleted")

 

 

Python Accepting JavaScript Requests

Python web applications take advantages of receiving messages directly from JS scripts in browser clients. As you did it, not only PHP, but also Python plays a role of offering web services.

Instead of providing an isolated Python web application supporting by software like Flask, we have introduced an approach of configuring Apache CGI for Python web applications about Windows, Ubuntu, and CentOS.

First of all, you have to specify the path of Python executable file in Python script such as the following. Otherwise, you will get HTTP 500 errors in the browser’s console log.

 #!C:/Users/user/AppData/Local/Programs/Python/Python39/python.exe
 #Replace the above location with yours. E.g #!/usr/bin/python3 for Linux

 

Scenario in The Example

Let’s create a row in database for India. However, if you do it twice, errors will appear in HTML layout. The reason is duplication of UNIQUE KEY.

Also, we create rows for USA and Germany. Make a browser request to read database for countries by choosing All Selected Countries.

Provided that the population statistics in the official web site change after years, Make a browser request to update database. Critically, by using the property rowcount, you can detect whether the population has been changed or not. If it’s value is zero, there is not any affected rows.

The above demo scenario has appeared in browser’s console log as below. When you are studying the example, it is suggested to switch F12 Inspect for learning.

 

 

Learning Tips

Let us suggest a excellent way to learn HTML scripts here. Using Google Chrome F12 Inspect or Inspect Element will help you study the codes.

In Google Chrome, there are two ways to inspect a web page using the browser built-in Chrome DevTools:

• Right-click an element on the page or in a blank area, then select Inspect.
• Go to the Chrome menu, then select More Tools > Developer Tools.

 

Suggested Reading

• Small PHP CRUD Class for OOP using MySQL
• Python Web Scraping using BeautifulSoup in 3 Steps