Python:读取数据库以显示具有相等列的表 [英] Python: Read database to display table with equal columns

问题描述

我当前有一个程序从数据库中读取，可以在此处找到.我让用户选择他们想要显示的特定记录，以便SQL命令将执行该记录.现在，我有一个当前显示这些记录的表，但是，这些列并不是我想要的样子，因为我正在处理很长的数据字符串.

I have a program when it currently reads from a database, which can be found here. I have users choose a specific record they want to display so the SQL command will execute that record. Now I have a table that currently displays those records, however, the columns are not how I wanted to look because I am dealing with long strings of data.

我没有使用任何扩展库.除了某些表格(例如供应商"，订单"，类别")外，其他表格的表格显示是相同的.每列的宽度太长.占用太多空间.

I am not using any extended libraries. My table display is sort of equal for some tables except tables like Suppliers, Orders, Categories. The width for each column is way too long. It takes too much space.

我认为我的代码当前从整个表中获取最长的字符串，并为每一列设置其宽度.我想要它做的是为每一列而不是整个表采用最长的字符串，因此每列的宽度由该列的字符串长度(当然还有一点额外的空间)设置.

I think my code currently takes the longest string from the whole table and set the width of that for each column. What I want it to do instead is to take the longest string for each column and NOT the whole table, so the width of each column is set by string length (with like a little extra space of course) of that column.

到目前为止，这是我的代码:

Here is my code so far:

import sqlite3

def read_display(record):
  database = 'data.db'
  connection = sqlite3.connect(database)
  c = connection.cursor()
  # selects a table to display
  sql = "SELECT * FROM {0}".format(record)
  cursor.execute(sql)
  conn.commit()
  results = cursor.fetchall()

  header = [i[0] for i in c.description]
  data = [header] + list(tuple(results))
  width = max((len(str(x)) for d in data for x in d))

  for i, d in enumerate(data):
      line = ' | '.join(str(x).ljust(width) for x in d)
      print(line)
      if i == 0:
         print('-' * len(line))

我试图弄乱 width 变量，但似乎无法弄清楚.我尝试查看其他StackOverflow q，但它们处理的数据很少，因此更容易设置宽度.该数据包含巨大的字符串.

I tried to mess with the width variable, but I can't seem to figure it out. I tried looking at other StackOverflow q's but they are dealing with little data, so it is easier to set the width. This data contains huge strings.

非常感谢！

推荐答案

评论:#2 中的 _ 是什么意思?

阅读 Python中单个下划线"_"变量的用途是什么?
在这里，"3.作为通用的丢弃"变量名" ，我们不需要 range(... 值.

Read What is the purpose of the single underscore "_" variable in Python?
Here, "3. As a general purpose "throwaway" variable name", we don't need the range(... value.

问题:将数据库记录显示为具有相等列的表

Question: display database records as table with equal columns

1. 获取列名，并将其添加到结果之前.

    header = tuple(i[0] for i in cur.description)
    data = [header] + results

使用 {'width':0} 作为 list 初始化 config .
创建与数据库表中的列一样多的 dict 项.

    config = [{'width':0} for _ in range(len(data[0]))]

循环整个数据，该数据是 tuple 的 list .
找到 max len 并将其存储在 config dict

Loop the whole data, which is list of tuple.
Find the max len and store it in the columns config dict

    for rec in data:
        for c, value in enumerate(rec):
            config[c]['width'] = max(config[c]['width'], len(str(value)))

使用 config

    format_ = []
    for f in config:
        format_.append('{:<' + str(f['width']) + '}')

使用'|'作为分隔符，将列表格式_ 转换为 str .

Convert the list format_ to str using a '|' as delimiter.

    format_ = ' | '.join(format_)

环绕 tuple 的 data ， list ，使用 format _ 字符串进行打印.

Loop the data, list of tuple, print using the format_ string.

    for rec in data:
        print(format_.format(*rec))

使用Python测试:3.5

Tested with Python: 3.5

这篇关于Python:读取数据库以显示具有相等列的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！