How to output csv data to terminal with python

Question

I am writing a script that reads a csv file to a psql table and then sorts the data and exports it to a new file. How do I print the same data that is exported to the csv files, to the terminal to display it like a table?

Here is the code I have:

import psycopg2
import pandas as pd


class Products(object):

    def __init__(self):
        self.db_connection = psycopg2.connect("host=localhost dbname=rd_assignment user=postgres")
        self.db_cur = self.db_connection.cursor()

    def add_column(self):
        """This method adds a column to the products database table. It checks if the table is empty before
        inserting the csv file."""
        self.db_cur.execute("ALTER TABLE products ADD COLUMN IF NOT EXISTS is_active BOOLEAN NOT NULL;")
        self.db_cur.execute("SELECT COUNT(*) FROM products;")
        rows = self.db_cur.fetchall()
        if rows[0][0] < 20:
            self.db_cur.execute(
                "COPY products FROM '/Users/leroy/PycharmProjects/rd_assignment/products.csv' DELIMITERS ',' CSV;")
            self.db_connection.commit()

    def sort_highest(self):
        """This method outputs the products to a csv file according to the highest amount"""
        sort_highest = "COPY (SELECT * FROM products order by amount desc) TO STDOUT DELIMITER ';' CSV HEADER"
        with open("highest_amount.csv", "w") as file:
            self.db_cur.copy_expert(sort_highest, file)
        r = pd.read_csv('/Users/leroy/PycharmProjects/rd_assignment/highest_amount.csv')
        print(r.head(20))

    def get_active(self):
        """This method outputs the active products in the database to a csv file"""
        sort_active = "COPY (SELECT * FROM products WHERE is_active = True) TO STDOUT DELIMITER ';' CSV HEADER"
        with open("active_products.csv", "w") as file:
            self.db_cur.copy_expert(sort_active, file)

    def get_inactive(self):
        """This method outputs the inactive products in the database to a csv file"""
        sort_inactive = \
            "COPY (SELECT description, amount FROM products WHERE is_active = False) TO STDOUT DELIMITER ';' CSV HEADER"
        with open("inactive_products.csv", "w") as file:
            self.db_cur.copy_expert(sort_inactive, file)

    def __del__(self):
        self.db_connection.close()


if __name__ == '__main__':
    instance = Products()
    instance.add_column()
    instance.sort_highest()
    instance.get_active()
    instance.get_inactive()

The csv file that is imported looks like this:

101,9/25/2018,9/25/2018,"Sinotec 40"" FHD LED TV",5,TRUE
102,9/25/2018,9/25/2018,Playstation 4 1TB Console - Marvel Spider-man,5,TRUE
103,9/25/2018,9/25/2018,Mellerware - 3.5 Litre Tempo Slow Cooker,6,FALSE
104,9/25/2018,9/25/2018,Samsung Galaxy S9 64GB - Black,12,TRUE
105,9/25/2018,9/25/2018,Cougar Armor Gaming Chair - Black,3,FALSE
106,9/25/2018,9/25/2018,Destiny 2 Legendary Collection(PS4),2,TRUE
107,9/25/2018,9/25/2018,"AIWA 43"" Full HD LED TV",3,FALSE
108,9/25/2018,9/25/2018,Ibanez PF17ECE-LG Acoustic/Electric Guitar,2,FALSE
109,9/25/2018,9/25/2018,Plantronics Audio 355 Stereo headset - Black,6,TRUE
110,9/25/2018,9/25/2018,Speck Presidio Case for Apple iPhone 7/8,6,FALSE
111,9/25/2018,9/25/2018,Skone Langebaan Key Hole UV400 Sunglasses,6,TRUE
112,9/25/2018,9/25/2018,Fadecase Karambit Elite Gamma Doppler Phase 2,6,TRUE
113,9/25/2018,9/25/2018,JBL GO Portable Bluetooth Speaker - Blue,6,TRUE
114,9/25/2018,9/25/2018,"WD Blue 250GB 2.5"" 3D NAND SATA SSD",8,FALSE
115,9/25/2018,9/25/2018,Philips - Metal Kettle - Red,8,TRUE
116,9/25/2018,9/25/2018,Apple AirPods,2,FALSE
117,9/25/2018,9/25/2018,Apple Watch Series 3 GPS 42mm,5,FALSE
118,9/25/2018,9/25/2018,Gigabyte GeForce GTX 1080 G1 Gaming Edition,8,FALSE
119,9/25/2018,9/25/2018,HTC Vive Eco Black VR Goggles (PC),11,FALSE
120,9/25/2018,9/25/2018,Corsair Vengeance LED 32GB Memory Kit - Red,10,TRUE

Answer 1

I would suggest using tabulate. It can output the data in various table formats.

from tabulate import tabulate
import pandas as pd

df = pd.read_csv('data.csv')
print(tabulate(df, headers='keys', tablefmt='psql'))

Example:

from tabulate import tabulate
import pandas as pd

df = pd.DataFrame({'col_two' : [0.0001, 1e-005 , 1e-006, 1e-007],
                   'column_3' : ['ABCD', 'ABCD', 'long string', 'ABCD']})
print(tabulate(df, headers='keys', tablefmt='psql'))


+----+-----------+-------------+
|    |   col_two | column_3    |
|----+-----------+-------------|
|  0 |    0.0001 | ABCD        |
|  1 |    1e-05  | ABCD        |
|  2 |    1e-06  | long string |
|  3 |    1e-07  | ABCD        |
+----+-----------+-------------+

Supported table formats are:

• "plain"
• "simple"
• "github"
• "grid"
• "fancy_grid"
• "pipe"
• "orgtbl"
• "jira"
• "presto"
• "psql"
• "rst"
• "mediawiki"
• "moinmoin"
• "youtrack"
• "html"
• "latex"
• "latex_raw"
• "latex_booktabs"
• "textile"

Answer 2

Try prettytable.

from prettytable import PrettyTable

x = PrettyTable()

x.field_names = ["Sl.n0", "date1", "date2", "comments","Boolean"]

with open('file.csv') as f:
  line = f.readline()
  while line:
    x.add_row(line.rstrip().split(','))
    line = f.readline()
print x

Install with pip : pip install PrettyTable

Find examples here

Answer 3

I don't think there's much support built-in support for pretty printing csv files to terminal. Here's a quick script that will pretty print a given csv file:

import csv

def pad_col(col, max_width):
    return col.ljust(max_width)

with open('test.csv') as csvfile:
    reader = csv.reader(csvfile)
    all_rows = []
    for row in reader:
        all_rows.append(row)

max_col_width = [0] * len(all_rows[0])
for row in all_rows:
    for idx, col in enumerate(row):
        max_col_width[idx] = max(len(col), max_col_width[idx])

for row in all_rows:
    to_print = ""
    for idx, col in enumerate(row):
        to_print += pad_col(col, max_col_width[idx]) + " | "
    print("-"*len(to_print))
    print(to_print)

if test.csv contains the csv input above, you'll get the following output:

-------------------------------------------------------------------------------------------
101 | 9/25/2018 | 9/25/2018 | Sinotec 40" FHD LED TV                        | 5  | TRUE  |
-------------------------------------------------------------------------------------------
102 | 9/25/2018 | 9/25/2018 | Playstation 4 1TB Console - Marvel Spider-man | 5  | TRUE  |
-------------------------------------------------------------------------------------------
103 | 9/25/2018 | 9/25/2018 | Mellerware - 3.5 Litre Tempo Slow Cooker      | 6  | FALSE |
-------------------------------------------------------------------------------------------
104 | 9/25/2018 | 9/25/2018 | Samsung Galaxy S9 64GB - Black                | 12 | TRUE  |
-------------------------------------------------------------------------------------------
105 | 9/25/2018 | 9/25/2018 | Cougar Armor Gaming Chair - Black             | 3  | FALSE |
-------------------------------------------------------------------------------------------
106 | 9/25/2018 | 9/25/2018 | Destiny 2 Legendary Collection(PS4)           | 2  | TRUE  |
-------------------------------------------------------------------------------------------
107 | 9/25/2018 | 9/25/2018 | AIWA 43" Full HD LED TV                       | 3  | FALSE |
-------------------------------------------------------------------------------------------
108 | 9/25/2018 | 9/25/2018 | Ibanez PF17ECE-LG Acoustic/Electric Guitar    | 2  | FALSE |
-------------------------------------------------------------------------------------------
109 | 9/25/2018 | 9/25/2018 | Plantronics Audio 355 Stereo headset - Black  | 6  | TRUE  |
-------------------------------------------------------------------------------------------
110 | 9/25/2018 | 9/25/2018 | Speck Presidio Case for Apple iPhone 7/8      | 6  | FALSE |
-------------------------------------------------------------------------------------------
111 | 9/25/2018 | 9/25/2018 | Skone Langebaan Key Hole UV400 Sunglasses     | 6  | TRUE  |
-------------------------------------------------------------------------------------------
112 | 9/25/2018 | 9/25/2018 | Fadecase Karambit Elite Gamma Doppler Phase 2 | 6  | TRUE  |
-------------------------------------------------------------------------------------------
113 | 9/25/2018 | 9/25/2018 | JBL GO Portable Bluetooth Speaker - Blue      | 6  | TRUE  |
-------------------------------------------------------------------------------------------
114 | 9/25/2018 | 9/25/2018 | WD Blue 250GB 2.5" 3D NAND SATA SSD           | 8  | FALSE |
-------------------------------------------------------------------------------------------
115 | 9/25/2018 | 9/25/2018 | Philips - Metal Kettle - Red                  | 8  | TRUE  |
-------------------------------------------------------------------------------------------
116 | 9/25/2018 | 9/25/2018 | Apple AirPods                                 | 2  | FALSE |
-------------------------------------------------------------------------------------------
117 | 9/25/2018 | 9/25/2018 | Apple Watch Series 3 GPS 42mm                 | 5  | FALSE |
-------------------------------------------------------------------------------------------
118 | 9/25/2018 | 9/25/2018 | Gigabyte GeForce GTX 1080 G1 Gaming Edition   | 8  | FALSE |
-------------------------------------------------------------------------------------------
119 | 9/25/2018 | 9/25/2018 | HTC Vive Eco Black VR Goggles (PC)            | 11 | FALSE |
-------------------------------------------------------------------------------------------
120 | 9/25/2018 | 9/25/2018 | Corsair Vengeance LED 32GB Memory Kit - Red   | 10 | TRUE  |

This solution is not particularly performant or compact. You could spend all day generalizing this to pretty print csvs. I'll leave these improvement items as an exercise to the reader...

HTH, should at least get you started.