如何在python中打印SQLite数据库中的表?

cl25kdpy  于 2023-10-23  发布在  SQLite
关注(0)|答案(1)|浏览(136)

所以我有一个信息数据库,我想以一个漂亮的表格格式打印,作为SQLite db文件保存。
我见过的唯一的print语句以一种令人困惑的方式打印信息,没有对齐不同实体的属性,没有列标题等。
创建表的过程:

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

这是打印过程:

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

当我执行这个命令时,它会打印:
('NORI','DRY','SHELVES',' 50G',3.4,20.0,4.42,5.30399999999999,3.0,13.26,15.911999999999999,2.651999999999992,10.2,3.060000000000005)
('咖喱酱','干','架子','500G',5.65,25.0,7.3450000000001,9.18125,1.0,7.34500000000001,9.18125,1.83624999999999997,5.65,1.695000000000003)
('SALMON','CHILLED',' FRIDGE','100G',1.25,20.0,1.625,1.95,3.0,4.875,5.85,0.974999999999996,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)
这是我的数据库中的信息,但有没有办法将其打印为一个表?

tcbh2hod

tcbh2hod1#

使用row_factory将列名添加到行中是有据可查的:

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

然后,您可以使用str.rjust和相关函数来打印表格,或者使用csv.DictWritersys.stdout作为“文件”:

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

相关问题