Python - How do you pad the output of a mysql table
Two columnsGiven 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 columnsFor 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
Best Way to Get the Max Value in a Spark Dataframe Column
Python Pandas Valueerror Arrays Must Be All Same Length
Export Pandas Dataframe into a Pdf File Using Python
Importing Modules from Parent Folder
Json.Decoder.Jsondecodeerror: Expecting Value: Line 1 Column 1 (Char 0) Python
Importing Local Module (Python Script) in Airflow Dag
How to Count the Amount of Sentences in a Paragraph in Python
How to Suppress Scientific Notation When Printing Float Values
How to Delete All Columns in Dataframe Except Certain Ones
How to Print Float to N Decimal Places Including Trailing 0S
Using Python, How to Access a Shared Folder on Windows Network
Regular Expression: Match Everything After a Particular Word
Python: Fastest Way to Compare Arrays Elementwise
How to Get All Users in a Telegram Channel Using Telethon
Python - Regex Match Multiple Patterns in Multiple Lines
Counting the No. of Black to White Pixels in the Image Using Opencv
How to Convert Strings With Billion or Million Abbreviation into Integers in a List