How to format postgreSQL queries in a python script for better readability?

Question

I have another question that is related to a project I am working on for school. I have created a PostgreSQL database, with 5 tables and a bunch of rows. I have created a script that allows a user to search for information in the database using a menu, as well as adding and removing content from one of the tables.

When displaying a table in PostgreSQL CLI itself, it looks pretty clean, however, whenever displaying even a simple table with no user input, it looks really messy. While this is an optional component for the project, I would prefer to have something that looks a little cleaner.

I have tried a variety of potential solutions that I have seen online, even a few from stack overflow, but none of them work. Whenever I try to use any of the methods I have seen and somewhat understand, I always get the error:

TypeError: 'int' object is not subscriptable

I added a bunch of print statements in my code, to try and figure out why it refuses to typecast. It is being dumb. Knowing me it is probably a simple typo that I can't see. Not even sure if this solution will work, just one of the examples I saw online.

try:
        connection = psycopg2.connect(database='Blockbuster36', user='dbadmin')
        cursor = connection.cursor()
except psycopg2.DatabaseError:
        print("No connection to database.")
        sys.exit(1)

cursor.execute("select * from Customer;")
tuple = cursor.fetchone()
List_Tuple = list(tuple)

print("Customer_ID | First_Name | Last_Name | Postal_Code | Phone_Num | Member_Date")
print(List_Tuple)
print()
for item in List_Tuple:
        print(item[0]," "*(11-len(str(item[0]))),"|")
        print(item)
        print(type(item))
        print()
        num = str(item[0])
        print(num)
        print(type(num))
        print(str(item[0]))
        print(type(str(item[0])))

cursor.close()
connection.close()

I uploaded the difference between the output I get through a basic python script and in the PostgreSQL CLI. I have blocked out names in the tables for privacy reasons. https://temporysite.weebly.com/

It doesn't have to look exactly like PostgreSQL, but anything that looks better than the current mess would be great.

Joshua Pierce · Accepted Answer · 2019-04-13 02:15:13Z

1

Use string formatting to do that. You can also set it to pad right or left. As far as the dates use datetime.strftime. The following would set the padding to 10 places:

print(”{:10}|{:10}".format(item[0], item[1]))

answered Apr 13, 2019 at 2:15

Joshua Pierce

1361 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Darren Over a year ago

I still get the same error: TypeError: 'int' object is not subscriptable

Joshua Pierce Over a year ago

You are getting the error because you are using cursor.fetchone() which only returns a single row as a list. You are then trying to iterate thru the first item on the list which is a number. Switch cursor.fetchone() to cursor.fetchall()

Collectives™ on Stack Overflow

How to format postgreSQL queries in a python script for better readability?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related