(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:
