Python-DB Connection in Class Wrapper.

sohesh doshi
1 min readJul 27, 2020

This class uses pymysql package to do the database interaction.

from pymysql import connect
from pymysql.cursors import DictCursor

class Database:

def __init__(self, host_name, user_name, password, charset, port):
self._conn = connect(host=host_name, user=user_name, password=password, db=self.db, charset=charset, port=port,
cursorclass=DictCursor, autocommit=True)
self._cursor = self._conn.cursor()

def __enter__(self):
return self

def __exit__(self):

def connection(self):
return self._conn

def cursor(self):
return self._cursor

def commit(self):

def close(self, commit=True):
if commit:

def execute(self, sql, params=None):
self.cursor.execute(sql, params or ())

def executemany(self, sql, params=None):
self.cursor.executemany(sql, params or ())

def fetchall(self):
return self.cursor.fetchall()

def fetchone(self):
return self.cursor.fetchone()

def query(self, sql, params=None):
self.cursor.execute(sql, params or ())
return self.fetchall()

use the Database class either normally like db = Database(**arges) or in a with statement:

with Database(**arges) as db:
# do stuff

and the connection will automatically commit and close when the with statement exits.

Then, you can encapsulate specific queries that you do often in methods and make them easy to access. For example, if you’re dealing with transaction records, you could have a method to get them by date:

def select_comman(self, date):
sql = "SELECT * FROM Admin WHERE create_at= ?"
return self.query(sql, (date,))

Here’s some sample code where we create a table, add some data, and then read it back out:

with Database(**arges) as db:
db.execute('CREATE TABLE comments(pkey INTEGER PRIMARY KEY AUTOINCREMENT, username VARCHAR, comment_body VARCHAR, date_posted TIMESTAMP)')
db.execute('INSERT INTO comments (username, comment_body, date_posted) VALUES (?, ?, current_date)', ('tom', 'this is a comment'))
comments = db.query('SELECT * FROM comments')

I hope this helps!