Python simple Class with sqlite and unit testing
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.