对列表列表执行SQLstyle操作

Question

我有一个动态生成的列表列表，类似于以下内容：

[
['author', 'title', 'series', 'series_order', 'genre', 'isbn'],
['Adams, Douglas', 'Mostly Harmless', 'Hitchhiker\'s Guide to the Galaxy', '5', 'Science Fiction', '9780345418777'],
['Simmons, Dan', 'Hyperion', 'Hyperion Cantos', '1', 'Science Fiction', '9780553283686'],
['Herbert, Frank', 'Dune', 'Dune Chronicles', '1', 'Science Fiction', '9780441172719']
]

有没有一种方法可以对它运行下面的SQL查询？Edit：对不起，我的意思是我想在上面运行Python等价的以下SQL代码。你知道吗

SELECT
    author, title, series, series_order, genre, isbn
ORDER BY
    `author` ASC, `series` ASC, `series_order` ASC, `title` ASC;

我目前的计划是动态地将它们添加到一个临时SQLite数据库中，运行查询，完成后删除SQLite数据库。你知道吗

有人有更好的主意吗？你知道吗

Answer 1

使代码更具可读性的一个选项是^{}：

>>> from collections import namedtuple
>>> Book = namedtuple("Book", l[0])
>>> books = [Book(*b) for b in l[1:]]
>>> books[0].author
'Adams, Douglas'
>>> books[1]
Book(author='Simmons, Dan', title='Hyperion', series='Hyperion Cantos', series_order='1', genre='Science Fiction', isbn='9780553283686')

注意，我使用了第一个子列表（头）来定义namedtuple的属性，然后将其余子列表转换为新的Book对象。现在您的排序变得更加清晰，因为您可以使用attrgetter和属性名称，而不是itemgetter和索引：

>>> from operator import attrgetter
>>> sorted(books, key=attrgetter("author", "series", "series_order", "title"))
[Book(author='Adams, Douglas', title='Mostly Harmless', series="Hitchhiker's Guide to the Galaxy", series_order='5', genre='Science Fiction', isbn='9780345418777'), 
 Book(author='Herbert, Frank', title='Dune', series='Dune Chronicles', series_order='1', genre='Science Fiction', isbn='9780441172719'), 
 Book(author='Simmons, Dan', title='Hyperion', series='Hyperion Cantos', series_order='1', genre='Science Fiction', isbn='9780553283686')]

您还可以执行其他SQL样式的操作，例如WHERE：

>>> filter(lambda b: b.author == "Herbert, Frank", books))
[Book(author='Herbert, Frank', title='Dune', series='Dune Chronicles', series_order='1', genre='Science Fiction', isbn='9780441172719')]

---

或者，一个Book类可能是合适的，然后您可以实现自己的rich comparison methods，直接使用sorted：

class Book(object):

    def __init__(self, author, title, series, series_order,
                 genre, isbn):
        self.author = author
        ...

     def __eq__(self, other):
         return (self.author == other.author and
                 self.title == other.title and
                 ...)

     def __lt__(self, other):
         return ((self.author, self.series, self.series_order, self.title) <
                 (other.author, other.series, other.series_order, other.title))

Answer 2

列表理解与itemgetter排序相结合是SQLesque。你知道吗

from operator import itemgetter

books = [['author', 'title', 'series', 'series_order', 'genre', 'isbn'], ['Adams, Douglas', 'Mostly Harmless', "Hitchhiker's Guide to the Galaxy", '5', 'Science Fiction', '9780345418777'], ['Simmons, Dan', 'Hyperion', 'Hyperion Cantos', '1', 'Science Fiction', '9780553283686'], ['Herbert, Frank', 'Dune', 'Dune Chronicles', '1', 'Science Fiction', '9780441172719'], ['Brown, Dan', 'The Da Vinci Code', 'Robert Langdon', '2', 'Mystery', '0307474275'], ['Brown, Dan', 'Angels & Demons: A Novel', 'Robert Langdon', '1', 'Mystery', '074349346X']]

sorted([book for book in books[1:] if book[4] == "Science Fiction"], key=itemgetter(0,2,3,1))
>>> [['Adams, Douglas', 'Mostly Harmless', "Hitchhiker's Guide to the Galaxy", '5', 'Science Fiction', '9780345418777'], ['Herbert, Frank', 'Dune', 'Dune Chronicles', '1', 'Science Fiction', '9780441172719'], ['Simmons, Dan', 'Hyperion', 'Hyperion Cantos', '1', 'Science Fiction', '9780553283686']]

sorted([book for book in books[1:] if book[4] == "Mystery"], key=itemgetter(0,2,3,1))
>>> [['Brown, Dan', 'Angels & Demons: A Novel', 'Robert Langdon', '1', 'Mystery', '074349346X'], ['Brown, Dan', 'The Da Vinci Code', 'Robert Langdon', '2', 'Mystery', '0307474275']]

sorted([book for book in books[1:] if book[2] == "Robert Langdon"], key=itemgetter(0,2,3,1))
>>> [['Brown, Dan', 'Angels & Demons: A Novel', 'Robert Langdon', '1', 'Mystery', '074349346X'], ['Brown, Dan', 'The Da Vinci Code', 'Robert Langdon', '2', 'Mystery', '0307474275']]