Generic base class for MySQL in python

Whenever I write some code in python that involves a MySQL database, I use this base class that contains all the generic boilerplate code for the basic CRUD operations. This allows me to focus on writing the queries instead of writing the same code every time. Plus I don't have to google how to use MySQL in python because I usually forget how it works. Just wanted to share it so that others can use it too.

Methods

__execute and __select are private methods used internally in the class.

execute is intended for insert, update and delete statements and returns the number of affected rows in the table.

select_all is for select statements and returns a list of the rows that satisfy the query condition(s).

select_one is also for select statements and returns the first row that satisfies the query condition(s).

dispose closes the connection to the database.

import MySQLdb as mdb

class SQL_Base(object):
    def __init__(self, host, user, password, database):
        try:
            self.connection = mdb.connect(host, user, password, database)
        except mdb.Error as e:
            print("Init error %d: %s" % (e.args[0], e.args[1]))

    def __execute(self, query, parameters=[]):
        try:
            with self.connection:
                cursor = self.connection.cursor()
                cursor.execute(query, parameters)
                return cursor
        except mdb.Error as e:
            print("Execute error %d: %s" % (e.args[0], e.args[1]))

    def __select(self, query, parameters):
        return self.__execute(query, parameters)
  
    def execute(self, query, parameters=[]):
        return self.__execute(query, parameters).rowcount

    def select_all(self, query, parameters=[]):
        cursor = self.__select(query, parameters)
        return cursor.fetchall()
  
    def select_one(self, query, parameters=[]):
        cursor = self.__select(query, parameters)
        return cursor.fetchone()
  
    def dispose(self):
        if self.connection:
            self.connection.close()

Example

Create a class that inherits the SQL_Base class and call the base constructor with the database credentials. In this example I have made a user repository with examples of the CRUD operations by utilizing execute, select_one and select_all.

from sql_base import SQL_Base
 
class User_repository(SQL_Base):
    def __init__(self):
        SQL_Base.__init__(self, "host", "user", "password", "database")
 
    def insert(self, username, email):
        query = "INSERT INTO users(username, email) VALUES (%s,%s)"
        return self.execute(query, [username, email])
         
    def get(self, username):
        query = "SELECT id, username, email FROM users WHERE username=%s"
        result = self.select_one(query, [username])
        return {'id': result[0],
                'username': result[1],
                'email': result[2]} 
     
    def get_all(self):
        query = "SELECT id, username, email FROM users"
        result = self.select_all(query)
        users = []
        for row in result:
            users.append({'id': row[0],
                          'username': row[1],
                          'email': row[2]})
        return users

    def update(self, username, email):
        query = "UPDATE users SET email=%s WHERE username=%s"
        return self.execute(query, [email, username])
         
    def delete(self, username):
        query = "DELETE FROM users WHERE username=%s"
        return self.execute(query, [username])

 

Add comment

Loading