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.

Just think about this line... sql.cursor.execute(sql)... Which sql variable is going to be used to get the cursor? The parameter, not the class — OneCricketeer
– OneCricketeer, Commented Jun 28, 2016 at 12:37
You should remove the unnecessary tags from your question. It's either python 2.7 or 3, or unrelated to a version. — RvdK
– RvdK, Commented Jun 28, 2016 at 12:46

carusot42 · Accepted Answer · 2020-04-23 21:37:43Z

90

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!

edited Apr 23, 2020 at 21:37

answered Jun 28, 2016 at 14:09

carusot42

1,21112 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Marshall Over a year ago

Gorgeous answer! Thank you so much, I sitll not understanding the __enter__ statment, when you return the DBase, what would happen.

carusot42 Over a year ago

The __enter__ and __exit__ "magic methods" let a class use the with statement. It's basically saying return an instantiated version of this class when it's used in a with DBase() as db: context.

carusot42 Over a year ago

More info here: stackoverflow.com/questions/1984325/…

rain_ Over a year ago

Should'nt you close also the cursor in DBase.__exit__() ? if not, why? thanks!

carusot42 Over a year ago

In pyscopg2 at least (the postgres client for Python), the cursor will close automatically when you close the connection, so I don't bother to do it explicitly. It's very likely that this will be taken care of for you.

|

Daniel Roseman · Accepted Answer · 2016-06-28 12:34:19Z

16

That's not how you write classes in Python. You need to define your connection and cursor inside the __init__ method, and refer to them via self.

class sql:

    dbc = ("localhost","root","1234","users")

    def __init__(self):
        db = MySQLdb.connect(*self.dbc)
        self.cursor = db.cursor()

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

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

answered Jun 28, 2016 at 12:34

Daniel Roseman

602k68 gold badges910 silver badges923 bronze badges

3 Comments

Marshall Over a year ago

This helped me soo much! Thanks, I didn't realised I could add them in the __init__

DeveloperLV Over a year ago

How would you call this classs? Like: print(sql.query("select * from test"))

Preston Over a year ago

@Daniel Roseman Thanks for this solution, do we need to worry about closing the connection/ cursor?

Ashish Mishra · Accepted Answer · 2018-07-22 03:53:50Z

2

You can use constructor for the connection. When the object of class will created the constructor will invoke automatically.

import MySQLdb

class Connection:
    def __init__(self):
        self.con=MySQLdb.connect("127.0.0.1","root","","db_name",3306)
        self.cmd=self.con.cursor()
obj=Connection()

edited Jul 22, 2018 at 3:53

answered Jul 21, 2018 at 9:28

Ashish Mishra

312 bronze badges

1 Comment

qräbnö Over a year ago

Welcome to SO. :) How is this different and even better than the existing answers?

Hersel Giannella · Accepted Answer · 2020-06-01 20:57:47Z

0

from config import Config
import MySQLdb

class Connection:
    def __init__(self):
        self.db=MySQLdb.connect(
            Config.DATABASE_CONFIG['server'],
            Config.DATABASE_CONFIG['user'],
            Config.DATABASE_CONFIG['password'],
            Config.DATABASE_CONFIG['name']
            )
        self.db.autocommit(True)
        self.db.set_character_set('utf8mb4')
        self.cur=self.db.cursor()

EXAMPLE CONFIG CLASS(config.py):

class Config(object):

  DATABASE_CONFIG = {
          'server': 'localhost',
          'user': 'dbuser',
          'password': 'password',
          'name': 'dbname',
          }

answered Jun 1, 2020 at 20:57

Hersel Giannella

295 bronze badges

Collectives™ on Stack Overflow

Python MySQLdb - Connection in a class

4 Answers 4

12 Comments

3 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

12 Comments

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related