使用pymysql读取mysql游标数据集------对结果集进行行列显示，并左右对齐显示

1.连接数据库，并从cursor.description中获取列名

import pymysql as mysql
import pandas as pd


def make_lalign_formatter(df, cols=None):
    """
    Construct formatter dict to left-align columns.
    Parameters
    ----------
    df : pandas.core.frame.DataFrame
        The DataFrame to format
    cols : None or iterable of strings, optional
        The columns of df to left-align. The default, cols=None, will
        left-align all the columns of dtype object
    Returns
    -------
    dict
        Formatter dictionary
    """
    if cols is None:
       cols = df.columns[df.dtypes == 'object']

    return {col: f'{{:<{df[col].str.len().max()}s}}'.format for col in cols}


# 连接MySQL数据库
host = 'localhost'
port = 3306
db = 'levis_cn_testing'
user = 'root'
password = 'root'


connection = mysql.connect(host=host, port=port, user=user, password=password, database=db, charset='utf8mb4')
cursor=connection.cursor()
sql="SELECT store_code_id, store_code,name, store_type FROM `store`  limit 10;"
cursor.execute(sql)
result = cursor.fetchall()
total_fields = len(cursor.description)
fields_names = [i[0] for i in cursor.description]
print("*************** get column name *************")
print(fields_names)
print("*************** show result with column name *************")

 

2.通过逐个读取字段，拼接的形式显示行列

print("*************** show result with column name *************")
print("%s\t %s\t%s\t%s" % (fields_names[0], fields_names[1], fields_names[2], fields_names[3]))
for row in result:
    #print(row)
    print("%d\t %s\t%s\t%s"%(row[0] ,row[1] ,row[2] ,row[3]   ) )

 

 

 

 

3.使用pandas的read_sql_query或 read_sql获取数据集，会返回一个数据表的DataFrame格式，这里调用了自定义make_lalign_formatter方法来进行对齐显示

print("*************** Print Table with column name using Pandas.Frame *************")
df = pd.read_sql_query(sql,connection)
# Left align all columns
print(df.to_string(formatters=make_lalign_formatter(df),
                   index=False,
                   justify='left'))

 

 

 

 

4.引用tabulate，使用tabulate来进行对齐显示方法来进行对齐显示，注意到这里字符串列进行了左对齐，数字列还是右对齐

print("***************使用tabulate进行对齐显示 *************")
from tabulate import tabulate
print(tabulate(df, showindex=False, headers=df.columns,stralign='left'))

 

 

 

 

 

5.关闭游标和连接

cursor.close()
connection.close()