0

So I have a database of information that I would like to print in a nice table format, held as an SQLite db file.

The only print statements I have seen print the information in a confusing manner, not aligning the attributes of different entities, no column headers etc.

Procedure that creates the table:

def create_table():
c.execute('CREATE TABLE IF NOT EXISTS orders ( '    #CREATES TABLE named 'orders':
          'name TEXT, '                             #name
          'type_ TEXT, '                            #type of product
          'location STRING, '                       #location of product
          'amount INTEGER, '                        #'weight' of item, g, kg, ml, cl, l, etc. 
          'wholesale_cost REAL, '                   #wholesale cost
          'tax REAL, '                              #tax %
          'sale_pre_tax REAL, '                     #sale value before tax
          'sale_post_tax REAL, '                    #sale value after tax
          'quantity REAL, '                         #how many sold
          'total_sale_pre_tax REAL, '               #total sales before tax
          'total_sale_post_tax, '                   #total sales after tax
          'total_tax REAL, '                        #total tax in GBP
          'total_wholesale_cost REAL, '             #total wholesale cos
          'profit REAL)')                           #total sale profit

And this is the print procedure:

def read_from_db():
c.execute ('SELECT * FROM orders ') 
for row in c.fetchall():
    print(row)

When I execute this it prints:

('NORI', 'DRY', 'SHELVES', '50G', 3.4, 20.0, 4.42, 5.303999999999999, 3.0, 13.26, 15.911999999999999, 2.6519999999999992, 10.2, 3.0600000000000005)

('CURRY SAUCE', 'DRY', 'SHELVES', '500G', 5.65, 25.0, 7.345000000000001, 9.18125, 1.0, 7.345000000000001, 9.18125, 1.8362499999999997, 5.65, 1.6950000000000003)

('SALMON', 'CHILLED', 'FRIDGE', '100G', 1.25, 20.0, 1.625, 1.95, 3.0, 4.875, 5.85, 0.9749999999999996, 3.75, 1.125)

('EDAMAME', 'CHILLED', 'FRIDGE', '100G', 3.0, 19.0, 4.0, 5.0, 3.0, 12.0, 15, 3.0, 9.0, 3.0)

Which is the information from my database but is there any way to print this as a table instead?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129

1 Answers1

1

Adding column names to your rows using row_factory is well documented:

import sqlite3

con = sqlite3.Connection('my.db')
con.row_factory = sqlite3.Row

cur = con.cursor()
cur.execute('SELECT * FROM tbl')

for row in cur.fetchall():
     # can convert to dict if you want:
     print(dict(row))

You could then use str.rjust and related functions to print the table, or use a csv.DictWriter with sys.stdout as the "file":

import csv
import sys

wtr = csv.DictWriter(sys.stdout, fieldnames=[i[0] for i in cur.description])
wtr.writeheader()

for row in cur.fetchall():
    wtr.writerow(dict(row))
ChrisP
  • 5,812
  • 1
  • 33
  • 36
  • Added this to my code and its giving me an error: ValueError: dictionary update sequence element #0 has length 6; 2 is required. And when I remove the dict() it just prints as it did before. –  Apr 22 '16 at 14:06
  • This is working for me (Python 3.4.4). I doubt that's an error in the above code; what line is the error on? It seems you are updating a dictionary somewhere with an object that is not a dictionary. See [this question](http://stackoverflow.com/questions/14302248/dictionary-update-sequence-element-0-has-length-3-2-is-required). – ChrisP Apr 22 '16 at 14:08