Accessing MySQL database using MySQLdb

The first thing you need to do is create a connection to the database using the connect method. After that, you will need a cursor that will operate with that connection.

Use the execute method of the cursor to interact with the database, and every once in a while, commit the changes using the commit method of the connection object.

Once everything is done, don’t forget to close the cursor and the connection.

Here is a Dbconnect class with everything you’ll need.

import MySQLdb

class Dbconnect(object):

    def __init__(self):

        self.dbconection = MySQLdb.connect(host='host_example',
                                           port=int('port_example'),
                                           user='user_example',
                                           passwd='pass_example',
                                           db='schema_example')
        self.dbcursor = self.dbconection.cursor()

    def commit_db(self):
        self.dbconection.commit()

    def close_db(self):
        self.dbcursor.close()
        self.dbconection.close()

Interacting with the database is simple. After creating the object, just use the execute method.

db = Dbconnect()
db.dbcursor.execute('SELECT * FROM %s' % 'table_example')

If you want to call a stored procedure, use the following syntax. Note that the parameters list is optional.

db = Dbconnect()
db.callproc('stored_procedure_name', [parameters] )

After the query is done, you can access the results multiple ways. The cursor object is a generator that can fetch all the results or be looped.

results = db.dbcursor.fetchall()
for individual_row in results:
    first_field = individual_row[0]

If you want a loop using directly the generator:

for individual_row in db.dbcursor:
    first_field = individual_row[0]

If you want to commit changes to the database:

db.commit_db()

If you want to close the cursor and the connection:

db.close_db()