1

I am trying to print each row from a table using print function to print each row.

select_stmt = "SELECT user,password FROM account"

mycursor.execute(select_stmt)
myresult = mycursor.fetchall()

for row in myresult:
    print (row)

Currently it prints in a haphazard fashion:

(None, 'A***9****')
(None, None)
('usertest', 'pwtest')

How do you pad each column so it looks more legible? Thanks

superx
  • 157
  • 13
  • You store raw passwords in the database? Unless you need this to login in an *external* program/API/..., do *not* do this... Passwords are better *hashed*. – Willem Van Onsem Oct 09 '18 at 18:51
  • This would help: https://stackoverflow.com/questions/10195139/how-to-retrieve-sql-result-column-value-using-column-name-in-python – JR ibkr Oct 09 '18 at 19:00

3 Answers3

2

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)))
Willem Van Onsem
  • 443,496
  • 30
  • 428
  • 555
  • @ Willem Van Onsem Thanks for the answer. For some reason I am not getting the len_com (6th column) to format: print ('{} {} {} {} {} {} {}'.format(str(site).ljust(len_site),str(user).ljust(len_user), str(pwd).ljust(len_pw), str(em1).ljust(len_em1), str(em2).ljust(len_em2), str(com).ljust(len_com), str(dt).ljust(len_dt))) – superx Oct 09 '18 at 20:15
  • @superx: what about the second approach? It needs numpy, but can process any number of columns. – Willem Van Onsem Oct 09 '18 at 20:15
  • @WillemVanOnsem really dude? You are going to suggest him to use numpy for string formatting. Numpy is the framework specifically designed to perform mathematical operations. – JR ibkr Oct 09 '18 at 20:38
  • @JRibkr: but I use numpy for mathematical operations :) The numpy array is only used to make it convenient to - like said in the answer - calculate the columnwise maximum of the length of the rows. Not for rendering the table itself :) – Willem Van Onsem Oct 09 '18 at 20:40
  • @JRibkr: compare it to machine learning: one uses for example tensorflow to train a model and predict data, but you do not use this to render the results on a web app. Numpy is not necessary, but calculating a colum-wise maximum is just a bit "cumbersome", at least compared to numpy's interface. – Willem Van Onsem Oct 09 '18 at 20:42
  • @WillemVanOnsem Good for you but I do not think OP needs numpy. OP use the first approach until you come across numpy. Zen of python says "Simple is better than complex.". (https://www.python.org/dev/peps/pep-0020/) – JR ibkr Oct 09 '18 at 20:46
  • @JRibkr: well do you have a simpler approach to calculate the columnwise maximum? :) – Willem Van Onsem Oct 09 '18 at 20:46
  • @JRibkr: The idea of "simple is better than complex" is, based on reading some articles, the **S** in the [**SOLID**](https://en.wikipedia.org/wiki/SOLID) principles: single responsibility. It means that every function should only tackle one responsibility, not multiple ones. So if I started calculating the columnwise maximum *in* this function, then the above was *complex*. Marcog seems to provide some empricial evidence for this as well: https://stackoverflow.com/a/4568733/67579 – Willem Van Onsem Oct 09 '18 at 20:54
0

In a table, there are exactly specific number of columns. Ultimately, you do not write entire column in the console. You only write selected information in the console.

for user, pwd in myresult:
      print('{:>5} {:>5}'.format(user, pwd))

or

for result in myresult:
      user = myresult['user']
      pwd = myresult['pwd']
      print('{:>5} {:>5}'.format(user, pwd))

Checkout https://docs.python.org/3.4/library/string.html#formatexamples for more infor.

JR ibkr
  • 869
  • 7
  • 24
  • But here the challenge is "*Python - How do you pad the output of a mysql table*", so that means you want that every "column" has the same "width". I agree the question perhaps could be improved, but the idea is that all rows start the same column at the same amount of spacing from the left of the screen. – Willem Van Onsem Oct 09 '18 at 20:59
  • I missed that point. Thanks for reminding me @WillemVanOnsem. Essentially, it's your approach 1. – JR ibkr Oct 09 '18 at 21:18
  • @WillemVanOnsem thanks for both of your passionate inputs. I was able to output and pad the columns except for one. I will try out what both of you supplies tomorrow and let you both know. Because I am doing this as an exercise I decided to print all columns and hence pad each for clarity. But I get both of your reasonings. – superx Oct 10 '18 at 02:12
  • @superx I strongly believe a solution should be simple, cleaner & lightweight. Complicated logic for a simple problem is not an ideal solution. – JR ibkr Oct 10 '18 at 14:17
0

@JR ibkr @Willem Van Onsem I was able to pad all columns using a combination of Willem's method and JR's:

select_stmt = "SELECT site, user,password, email1, email2, comment, date FROM account"
mycursor.execute(select_stmt)

myresult = mycursor.fetchall()

len_site = max(map(lambda x: len(str(x[0])), myresult))     #site
len_user = max(map(lambda x: len(str(x[0])), myresult))     #user
len_pw = max(map(lambda x: len(str(x[0])), myresult))       #pw
len_em1 = max(map(lambda x: len(str(x[0])), myresult))      #email1
len_em2 = max(map(lambda x: len(str(x[0])), myresult))      #email2
#len_com = max(map(lambda x: len(str(x[0])), myresult))     #comments (This did not pad correctly. It protruded into the dates column)
len_dt = max(map(lambda x: len(str(x[0])), myresult))       #date

for site, user, pwd, em1, em2, com, dt in myresult:

    print ('{} {} {} {} {} {:<65} {}'.format(str(site).ljust(len_site),
                        str(user).ljust(len_user), str(pwd).ljust(len_pw), str(em1).ljust(len_em1),
                        str(em2).ljust(len_em2), str(com), str(dt).ljust(len_dt)))

In normal circumstances JR, you are right I'd probably wouldn't want to print all columns but I am doing this strictly for learning. For some reason the comment column was not padding correctly, so I just manually put in the max length of 65. Not sure if this is the most efficient way of doing this, would love to hear both of your takes. Thanks

superx
  • 157
  • 13