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