Python MySQLdb - Connection in a class

Question

I am making a Python project where I have to seek and retreive data from a database.
I tried making a class, in which I declare the connection and do my queries, here is moreless what I have so far.

import MySQLdb
dbc =("localhost","root","1234","users")
class sql:
    db = MySQLdb.connect(dbc[0],dbc[1],dbc[2],dbc[3])
    cursor = db.cursor()

    def query(self,sql):
        sql.cursor.execute(sql)
        return sql.cursor.fetchone()

    def rows(self):
        return sql.cursor.rowcount

sqlI = sql()
print(sqlI.query("SELECT `current_points` FROM `users` WHERE `nick` = 'username';"))

So, the main problem is that the variable db and cursor are not callable from other def's/functions from the same Class. What I'd like to get, is a polished query, where I can make queries and retreive it's content. This would summarize my code, therefore I should do.

Answer 1

I usually use psycopg2 / postgres, but this is the basic DB class that I often use, with Python's SQLite as an example:

import sqlite3

class Database:
    def __init__(self, name):
        self._conn = sqlite3.connect(name)
        self._cursor = self._conn.cursor()

    def __enter__(self):
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.close()

    @property
    def connection(self):
        return self._conn

    @property
    def cursor(self):
        return self._cursor

    def commit(self):
        self.connection.commit()

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

    def execute(self, sql, params=None):
        self.cursor.execute(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()

This will let you use the Database class either normally like db = Database('db_file.sqlite) or in a with statement:

with Database('db_file.sqlite') 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 transactions_by_date(self, date):
    sql = "SELECT * FROM transactions WHERE transaction_date = ?"
    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('my_db.sqlite') 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')
    print(comments)

I hope this helps!