Python - How to Pad the Output of a MySQL Table

Python - How do you pad the output of a mysql table

Two columns

Given the two columns will never have more than 24 characters, you can use for example:

for user, pwd in myresult:
print('{: <20} {}'.format(user, pwd))

Or if that is not known in advance, we can first determine the maximum size of the first column:

len_user = max(map(lambda x: len(str(x[0])), myresult))
for user, pwd in myresult:
print('{} {}'.format(str(user).ljust(len_user), pwd))

For the sample data, this then yields:

>>> len_user = max(map(lambda x: len(str(x[0])), myresult))
>>> for user, pwd in myresult:
... print('{} {}'.format(str(user).ljust(len_user), pwd))
...
None A***9****
None None
usertest pwtest

You can add more spacing between the two {}s in the formatting to increase the spacing between the elements, for example:

>>> for user, pwd in myresult:
... print('{} {}'.format(str(user).ljust(len_user), pwd))
...
None A***9****
None None
usertest pwtest
Multiple columns

For multiple columns, we can follow the same procedure, and use numpy to calculate the columnwise maximum:

import numpy as np

lens = np.max([[len(str(xi)) for xi in x] for x in myresult], axis=0)
myformat = ' '.join(['{}']*len(lens))

for col in myresult:
print(myformat.format(*map(str.ljust, map(str, col), lens)))

Print results in MySQL format with Python

Use prettytable

x = PrettyTable(["City name", "Area", "Population", "Annual Rainfall"])
x.set_field_align("City name", "l") # Left align city names
x.set_padding_width(1) # One space between column edges and contents (default)
x.add_row(["Adelaide",1295, 1158259, 600.5])
x.add_row(["Brisbane",5905, 1857594, 1146.4])
x.add_row(["Darwin", 112, 120900, 1714.7])
x.add_row(["Hobart", 1357, 205556, 619.5])
x.add_row(["Sydney", 2058, 4336374, 1214.8])
x.add_row(["Melbourne", 1566, 3806092, 646.9])
x.add_row(["Perth", 5386, 1554769, 869.4])
print x

+-----------+------+------------+-----------------+
| City name | Area | Population | Annual Rainfall |
+-----------+------+------------+-----------------+
| Adelaide | 1295 | 1158259 | 600.5 |
| Brisbane | 5905 | 1857594 | 1146.4 |
| Darwin | 112 | 120900 | 1714.7 |
| Hobart | 1357 | 205556 | 619.5 |
| Sydney | 2058 | 4336374 | 1214.8 |
| Melbourne | 1566 | 3806092 | 646.9 |
| Perth | 5386 | 1554769 | 869.4 |
+-----------+------+------------+-----------------+

Pad strings by spaces to certain length

You would use the function rpad():

select rpad(name, 8, ' ')

RPAD mysql fucntion

Your code should be returning an error, because you have pi.value "bare" with no aggregation function. However, the pad is working. You can check this out:

SELECT RPAD(SUM(ABS(pi.value)), 19, ' ') as value,
LENGTH(RPAD(SUM(ABS(pi.value)), 19, ' '))
FROM (SELECT 1 as value) pi;

Here is a db<>fiddle.

What is probably happening is that your interface is removing trailing spaces -- that is somewhat common.

MySQL - how to front pad zip code with "0"?

Store your zipcodes as CHAR(5) instead of a numeric type, or have your application pad it with zeroes when you load it from the DB. A way to do it with PHP using sprintf():

echo sprintf("%05d", 205); // prints 00205
echo sprintf("%05d", 1492); // prints 01492

Or you could have MySQL pad it for you with LPAD():

SELECT LPAD(zip, 5, '0') as zipcode FROM table;

Here's a way to update and pad all rows:

ALTER TABLE `table` CHANGE `zip` `zip` CHAR(5); #changes type
UPDATE table SET `zip`=LPAD(`zip`, 5, '0'); #pads everything


Related Topics



Leave a reply



Submit