Print results in MySQL format with Python

by

There is no need for an external library. The prints out the data with the column names. All lines with the ‘columns’ variable can be eliminated if you do not need the column names.

sql = "SELECT * FROM someTable"
cursor.execute(sql)
conn.commit()
results = cursor.fetchall()

widths = []
columns = []
tavnit="|"
separator="+" 

for cd in cursor.description:
    widths.append(max(cd[2], len(cd[0])))
    columns.append(cd[0])

for w in widths:
    tavnit += " %-"+"%ss |" % (w,)
    separator += '-'*w + '--+'

print(separator)
print(tavnit % tuple(columns))
print(separator)
for row in results:
    print(tavnit % row)
print(separator)

This is the output:

+--------+---------+---------------+------------+------------+
| ip_log | user_id | type_id       | ip_address | time_stamp |
+--------+---------+---------------+------------+------------+
| 227    | 1       | session_login | 10.0.0.2   | 1358760386 |
| 140    | 1       | session_login | 10.0.0.2   | 1358321825 |
| 98     | 1       | session_login | 10.0.0.2   | 1358157588 |
+--------+---------+---------------+------------+------------+

The magic lies in the third column of each cursor.description line (called cd[2] in the code). This column represents the length in characters of the longest value. Thus we size the displayed column as the greater between that and the length of the column header itself (max(cd[2], len(cd[0]))).

More Related Contents:

  1. mysql to pdf – python or ruby [closed]
  2. How to insert a space delimited text file into mysql using Python? [closed]
  3. Installing specific package version with pip
  4. Database does not update automatically with MySQL and Python
  5. How can I connect to MySQL in Python 3 on Windows?
  6. Python & MySql: Unicode and Encoding
  7. Authentication plugin ‘caching_sha2_password’ is not supported
  8. How to disable SQLAlchemy caching?
  9. Setting Django up to use MySQL
  10. memory-efficient built-in SqlAlchemy iterator/generator?
  11. How can I insert data into a MySQL database?
  12. How to use MySQLdb with Python and Django in OSX 10.6?
  13. When to close cursors using MySQLdb
  14. How to enable MySQL client auto re-connect with MySQLdb?
  15. How to build a flask application around an already existing database?
  16. Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock
  17. Python MySql Insert not working
  18. What is the best solution for database connection pooling in python?
  19. Python: MySQLdb and “Library not loaded: libmysqlclient.16.dylib”
  20. How to filter (or replace) unicode characters that would take more than 3 bytes in UTF-8?
  21. trapping a MySql warning
  22. Python mySQL Update, Working but not updating table
  23. ImportError: No module named ‘MySQL’
  24. Python import MySQLdb error – Mac 10.6
  25. mysql-python installation problems (on mac os x lion)
  26. Simple Subquery with OuterRef
  27. django.core.exceptions.ImproperlyConfigured: Error loading MySQLdb module: No module named MySQLdb
  28. pip install mysqlclient : Microsoft Visual C++ 14.0 is required
  29. Installing mysql-python on Centos
  30. Context manager for Python’s MySQLdb

Leave a Comment