Printing data in MySQL format using Python

(I originally posted what follows as an answer to a question on StacOverflow that remained unanswered during 2 years)

The aim is is to have Python output that looks in MySQL format:

mysql> SHOW COLUMNS FROM begueradj FROM begueradj;
+-----------------+-------------+------+-----+---------+-------+
| Field           | Type        | Null | Key | Default | Extra |
+-----------------+-------------+------+-----+---------+-------+
| Reg_exp         | varchar(20) | NO   |     | NULL    |       |
| Token           | varchar(20) | NO   |     | NULL    |       |
| Integer_code    | int(2)      | NO   |     | NULL    |       |
| Attribute_value | varchar(2)  | NO   |     | NULL    |       |
+-----------------+-------------+------+-----+---------+-------+

It is good to see the world as a set of objects, so my solution will be done in a class where we need to save the connexion parameters to MySQL server within a Python dictionary in the class consutructor  __init__(self):

self.config = { 'user':'begueradj',
                'passwd':'begueradj',
                'host':'127.0.0.1',
                'db':'begueradj',
               }

Of course, one needs to change these parameters to his ones.
Of course, trying to do a hack by yourself is not necessarily the best idea. For my solution, I opted for the use of texttable which you can install by:

• First download the compressed module.
• Uncompress the file and change the directory to it.
• Finally, type this command: sudo python setup.py install

After executing the MySQL query (self.sqlquery = """SELECT * FROM begueradj"""), you will need the MySQLCursor.description Property to get the columns' names in a tuple format:

# Get columns' names
self.columns = [i[0] for i in self.cursor.description]

Note that is useful to transform the tuples to lists as texttable module works on lists.

Python program:

I commented almost each line of my program solution below:

'''
Created on Mar 3, 2016

@author: begueradj
'''
import MySQLdb
import texttable

class Begueradj:
    """ Display MySQL table's content along with
    table's columns name as in pure MySQL format.
    """
    def __init__(self):
        """ Initialize MySQL server login parameters.
        Try to connect to communicate with MySQL database.
        """
        self.config = {'user':'begueradj',
                       'passwd':'begueradj',
                       'host':'127.0.0.1',
                       'db':'begueradj',
                       }
        # Try to log to MySQL server
        try:
            self.dbconnexion = MySQLdb.connect(**self.config)
        except MySQLdb.Error:
            print "Database connexion failure!"

        # Read the content of the MySQL table
        self.sqlquery = """SELECT * FROM beg"""

    def begueradj(self):
        """ Display MySQL table data.
        """
        self.cursor = self.dbconnexion.cursor()
        self.cursor.execute(self.sqlquery)

        # Get columns' names
        self.columns = [i[0] for i in self.cursor.description]

        self.tab = texttable.Texttable()
        self.tablerow = [[]]

       # Fetch all the rows from the query
        self.data = self.cursor.fetchall()

        # Must transform each tuple row to a list
        for r in self.data:
            self.tablerow.append(list(r))

        # Get the number of columns of the table
        self.tab.add_rows(self.tablerow)
        # Align displayed data within cells to left
        self.tab.set_cols_align(['l','l','l','l'])
        # Once again, convert each tuple  row to a list
        self.tab.header(list(self.columns))
        # Display the table (finally)
        print self.tab.draw()

        # Don't forget to close the connexion.
        self.dbconnexion.close()

# Main program
if __name__=="__main__":
    b=Begueradj()
    b.begueradj()

Demo: