CODEWITHSUNDEEP
pythondatabasesqliteprinting
user6041520
user6041520

Reputation:

How do I print tables from an SQLite databse in python?

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?

Upvotes: 0

Views: 4084

Answers (1)

ChrisP
ChrisP

Reputation: 5942

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))

Upvotes: 1

Related Questions