为可读性排序SQL结果？

Question

新观众：这个问题已经解决了

我需要更改SQL查询结果的可视化布局，我不能使用外部库，只能使用python/idle。你知道吗

我正在做一个程序，它应该采取用户的选择，并建立一个SQL语句，其中将查询数据库，并给用户所有匹配的结果。有一个关键字搜索，用户可以指定要搜索关键字的列。这些都不让我担心，我其实是被困在了不同的东西上：

我的数据库只是一个有9列的表，所有的表都是chars或int。为了测试，我只输入了三个“记录”。在适当的数据库中会有大约100多个。查询数据库，例如：

select distinct label,format,catnum,year,artist,relname from testtable

给出：

[('PENN', 'LP', '002', '1972', 'Frank Penn', "'72"), ('GBI', 'LP', '104', '1974', 'The Mustangs', 'On Tha Rocks'), ('GBI', 'Single', '235', '1978', 'Willpower', 'Love Makes It Alright')]

我很高兴知道在输出SQL查询结果时如何每行输出一条记录，如下所示：

[('PENN', 'LP', '002', '1972', 'Frank Penn', "'72"), 
('GBI', 'LP', '104', '1974', 'The Mustangs', 'On Tha Rocks'), 
('GBI', 'Single', '235', '1978', 'Willpower', 'Love Makes It Alright')]

或者，更好的是，像这样去掉语音标记和方括号：

(PENN, LP, 002, 1972, Frank Penn, '72), 
(GBI, LP, 104, 1974, The Mustangs, On Tha Rocks), 
(GBI, Single, 235, 1978, Willpower, Love Makes It Alright)

以下是我目前的代码：

import sqlite3

conn = sqlite3.connect(':memory:')

cursor = conn.cursor()

cursor.execute("""CREATE TABLE testtable (
        label char,
        format char,
        catnum char,
        sidecat char,
        year char,
         artist char,
        relname char,
        trackname char,
        composer char,
        collection int
        )""")

conn.commit()

cursor.execute('''INSERT INTO testtable VALUES ("PENN", "LP", "002", "002 (B)", "1972", "Frank Penn", "'72", "The Time For Loving Is Now", "Frank Penn", 1)''')
conn.commit()
cursor.execute('''INSERT INTO testtable VALUES ("GBI", "LP", "104", "104 (B)", "1974", "The Mustangs", "On Tha Rocks", "Someday We'll Be Together", "Unknown", 1)''')
conn.commit()
cursor.execute('''INSERT INTO testtable VALUES ("GBI", "Single", "235", "235 (B)", "1978", "Willpower", "Love Makes It Alright", "The Mail", "Eddie Minnis", 1)''')
conn.commit()


cursor.execute("select distinct label,format,catnum,year,artist,relname from testtable")
print(cursor.fetchall())


conn.close()

编辑：这是我实现的解决方案，基于用户nikpod的建议：

cursor.execute("select distinct label,format,catnum,year,artist,relname from testtable")
foo = cursor.fetchall()
foo = (str(foo).replace("), ","),\n").strip('[]'))
print(foo)
conn.close()

给予：

('PENN', 'LP', '002', '1972', 'Frank Penn', "'72"),
('GBI', 'LP', '104', '1974', 'The Mustangs', 'On Tha Rocks'),
('GBI', 'Single', '235', '1978', 'Willpower', 'Love Makes It Alright')

我发现可以使用“replace”命令删除几乎所有不需要的内容：

foo = (str(foo).replace("), ","),\n").strip('[]'))
foo = (str(foo).replace("('",""))
foo = (str(foo).replace('("',""))
foo = (str(foo).replace("',",","))
foo = (str(foo).replace(", '",", "))
foo = (str(foo).replace("'),",""))
foo = (str(foo).replace("')",""))
foo = (str(foo).replace('", ',", "))
foo = (str(foo).replace(', "',", "))
foo = (str(foo).replace('"),',""))

当然，使用了很多行代码，但最重要的是总体思路。使用这样的方法，可以使输出更好一些：

PENN, LP, 002, 1972, Frank Penn, '72
GBI, LP, 104, 1974, The Mustangs, On Tha Rocks
GBI, Single, 235, 1978, Willpower, Love Makes It Alright

感谢大家的意见，原来这个问题的措辞很差

Answer 1

您应该将元组列表打印为字符串，并根据需要对其进行操作。
这是一个仍包含引号的示例，但符合您的第一个要求：

foo = [('PENN', 'LP', '002', '1972', 'Frank Penn', "'72"), ('GBI', 'LP', '104', '1974', 'The Mustangs', 'On Tha Rocks'), ('GBI', 'Single', '235', '1978', 'Willpower', 'Love Makes It Alright')]
print(str(foo).replace("), ","),\n").strip('[]'))

结果如下：

('PENN', 'LP', '002', '1972', 'Frank Penn', "'72"),
('GBI', 'LP', '104', '1974', 'The Mustangs', 'On Tha Rocks'),
('GBI', 'Single', '235', '1978', 'Willpower', 'Love Makes It Alright')

Answer 2

不确定这是否对你有帮助，但可能会。你知道吗

example = [("index", "0", "contains", "these"),("index", "1", "contains", "these")]

list1 = example[0]  # 1st index is 0, this grabs ("index", "0", "contains", "these")
length = len(example)  # sets length to 2 for example as there are 2 lists

# you can access the elements in each list by either
example[0][1]  # gives "1"

# or the same thing by using list1 created earlier
list1[1]  # also gives "1"

# to work through all lists and all elements in the list
for each_list in example:
    # do something at the start of a list
    for each_element in each_list:
        print(each_element)
        # do something to each element in a list
    # do something at the end of a list