Python simple Class with sqlite and unit testing

Question

I am trying to learn best practices for OOP and unit testing. I have implemented a simple User class here which checks if the user exists in the database. If yes, then it loads the user data into the instance. If not, then it should create a new entry in the database.

My questions:

1. How should I create unit tests for the methods? For example, for check_user_exists() should I mock for conn or cursor or exists or three of them? I tried to research for this but none of the tutorials for mocking in Python really gave me any true understanding of how good mocking is done.
2. In each of the methods, all the database connection related codes are repeated (i.e. conn.cursor(), conn.close()). Can these be refactored away? My guess would be to create a Database class, but I'm not sure about that.
3. Is it weird for User class to have a database_path attribute? So far this is the best that I have come up with. Please give any suggestions on how to structure a class where it has to always query databases.

user.py

import sqlite3


class User:
    def __init__(self, username, database_path):
        self.username = username
        self.database_path = database_path

        if not self.check_user_exists():
            self.create_user()
        else:
            self.get_user()

    def check_user_exists(self):
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute('SELECT rowid FROM user WHERE name = ?', (self.username,))
        exists = cursor.fetchone()

        conn.close()

        if exists is None:
            return False
        else:
            return True

    def create_user(self):
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute("INSERT INTO user (name) VALUES (?)", (self.username,))

        conn.commit()
        conn.close()

    def get_user(self):
        conn = sqlite3.connect(self.database_path)
        cursor = conn.cursor()

        cursor.execute("SELECT data FROM user WHERE name=?", (self.username,))
        self.data = cursor.fetchone()[0]

        conn.close()

Answer 1

This is an interesting question. Thanks for posting it!

IMO, you don't actually have a User class here. A User would be a system object that has a name, maybe an id, a password, some relationships to material, perhaps one or more privilege levels.

What you have here is an object that provides an interface to a database table - specifically, the user table. This is a Table Data Gateway in the parlance of Martin Fowler.

Let's write some sample code:

from private_data import database_path

username = 'yanxun'
user = User(username, database_path)

if user.check_user_exists():
    print("User {} is in the database".format(user.username))

That looks pretty clunky. If you rename your class to UserTable, it starts to make more sense:

from private_data import database_path

username = 'yanxun'
users = UserTable(database_path)

if username in users:      # implemented by .__contains__ method
    print("User {} is in the database.".format(username))
else:
    print("User {} not found. Adding to the database.".format(username))

    users.add(username)

Note that I am treating the table as a collection, specifically, like a set, and presuming you write method names that are inspired by existing Python collection names.

Now, with that out of the way, let me propose that you should be lazy in how you handle creating and deleting database connections. Specifically, make the caller do it!

Making the caller pass you in a connection is the best way to write code that you want to test, as well as the best way to write code that is maximally portable. If the caller mocks a database, you don't know and don't care. If the caller connects you to Oracle instead of sqlite, you don't know and don't care (provided your SQL syntax is valid).

def __init__(self, dbconn):
    self.dbconn = dbconn

Now you don't have to worry about database username/password, database path, or anything else. You can just request a cursor from the connection, and do your thing.

(Note: transaction support, and two-phase-commit support, are connection level operations, rather than cursor operations. That's why I suggest passing in a connection, rather than a cursor. See PEP-249 for details.