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