使用 Python、SQLite 和 SQLAlchemy 进行数据管理

笔记：示例中使用的数据文件可在project/data目录。里面还有一个程序文件project/build_data生成数据的目录。如果您更改数据并希望恢复到已知状态，则该应用程序非常有用。

要访问本节和整个教程中使用的数据文件，请单击下面的链接：

 1def main():
 2    """The main entry point of the program"""
 3    # Get the resources for the program
 4    with resources.path(
 5        "project.data", "author_book_publisher.csv"
 6    ) as filepath:
 7        data = get_data(filepath)
 8
 9    # Get the number of books printed by each publisher
10    books_by_publisher = get_books_by_publisher(data, ascending=False)
11    for publisher, total_books in books_by_publisher.items():
12        print(f"Publisher: {publisher}, total books: {total_books}")
13    print()
14
15    # Get the number of authors each publisher publishes
16    authors_by_publisher = get_authors_by_publisher(data, ascending=False)
17    for publisher, total_authors in authors_by_publisher.items():
18        print(f"Publisher: {publisher}, total authors: {total_authors}")
19    print()
20
21    # Output hierarchical authors data
22    output_author_hierarchy(data)
23
24    # Add a new book to the data structure
25    data = add_new_book(
26        data,
27        author_name="Stephen King",
28        book_title="The Stand",
29        publisher_name="Random House",
30    )
31
32    # Output the updated hierarchical authors data
33    output_author_hierarchy(data)

$ python main.py
Publisher: Simon & Schuster, total books: 4
Publisher: Random House, total books: 4
Publisher: Penguin Random House, total books: 2
Publisher: Berkley, total books: 2

Publisher: Simon & Schuster, total authors: 4
Publisher: Random House, total authors: 3
Publisher: Berkley, total authors: 2
Publisher: Penguin Random House, total authors: 1

Authors
├── Alex Michaelides
│   └── The Silent Patient
│       └── Simon & Schuster
├── Carol Shaben
│   └── Into The Abyss
│       └── Simon & Schuster
├── Isaac Asimov
│   └── Foundation
│       └── Random House
├── John Le Carre
│   └── Tinker, Tailor, Soldier, Spy: A George Smiley Novel
│       └── Berkley
├── Pearl Buck
│   └── The Good Earth
│       ├── Random House
│       └── Simon & Schuster
├── Stephen King
│   ├── Dead Zone
│   │   └── Random House
│   ├── It
│   │   ├── Penguin Random House
│   │   └── Random House
│   └── The Shining
│       └── Penguin Random House
└── Tom Clancy
    ├── Patriot Games
    │   └── Simon & Schuster
    └── The Hunt For Red October
        └── Berkley

def get_data(filepath):
    """Get book data from the csv file"""
    return pd.read_csv(filepath)

def get_books_by_publisher(data, ascending=True):
    """Return the number of books by each publisher as a pandas series"""
    return data.groupby("publisher").size().sort_values(ascending=ascending)

def get_authors_by_publisher(data, ascending=True):
    """Returns the number of authors by each publisher as a pandas series"""
    return (
        data.assign(name=data.first_name.str.cat(data.last_name, sep=" "))
        .groupby("publisher")
        .nunique()
        .loc[:, "name"]
        .sort_values(ascending=ascending)
    )

def add_new_book(data, author_name, book_title, publisher_name):
    """Adds a new book to the system"""
    # Does the book exist?
    first_name, _, last_name = author_name.partition(" ")
    if any(
        (data.first_name == first_name)
        & (data.last_name == last_name)
        & (data.title == book_title)
        & (data.publisher == publisher_name)
    ):
        return data
    # Add the new book
    return data.append(
        {
            "first_name": first_name,
            "last_name": last_name,
            "title": book_title,
            "publisher": publisher_name,
        },
        ignore_index=True,
    )

def output_author_hierarchy(data):
    """Output the data as a hierarchy list of authors"""
    authors = data.assign(
        name=data.first_name.str.cat(data.last_name, sep=" ")
    )
    authors_tree = Tree()
    authors_tree.create_node("Authors", "authors")
    for author, books in authors.groupby("name"):
        authors_tree.create_node(author, author, parent="authors")
        for book, publishers in books.groupby("title")["publisher"]:
            book_id = f"{author}:{book}"
            authors_tree.create_node(book, book_id, parent=author)
            for publisher in publishers:
                authors_tree.create_node(publisher, parent=book_id)

    # Output the hierarchical authors data
    authors_tree.show()

CREATE TABLE author (
    author_id INTEGER NOT NULL PRIMARY KEY,
    first_name VARCHAR,
    last_name VARCHAR
);

CREATE TABLE book (
    book_id INTEGER NOT NULL PRIMARY KEY,
    author_id INTEGER REFERENCES author,
    title VARCHAR
);

CREATE TABLE publisher (
    publisher_id INTEGER NOT NULL PRIMARY KEY,
    name VARCHAR
);

SELECT * FROM author;

$ sqlite3 author_book_publisher.db

sqlite> SELECT * FROM author;
1|Isaac|Asimov
2|Pearl|Buck
3|Tom|Clancy
4|Stephen|King
5|John|Le Carre
6|Alex|Michaelides
7|Carol|Shaben

sqlite> .q

INSERT INTO author
    (first_name, last_name)
VALUES ('Paul', 'Mendez');

UPDATE author
SET first_name = 'Richard', last_name = 'Bachman'
WHERE first_name = 'Stephen' AND last_name = 'King';

DELETE FROM author
WHERE first_name = 'Paul'
AND last_name = 'Mendez';

笔记：为了避免意外删除记录，许多应用程序根本不允许删除。相反，该记录还有另一列来指示它是否正在使用。该列可能被命名为active并包含一个计算结果为 True 或 False 的值，指示在查询数据库时是否应包含该记录。

例如，下面的 SQL 查询将获取所有活动记录的所有列some_table:

SELECT
  *
FROM some_table
WHERE active = 1;

SQLite 没有布尔数据类型， 所以active列由值为 的整数表示0或者1来指示记录的状态。其他数据库系统可能有也可能没有本机布尔数据类型。

sqlite> SELECT
   ...> a.first_name || ' ' || a.last_name AS author_name,
   ...> b.title AS book_title
   ...> FROM author a
   ...> JOIN book b ON b.author_id = a.author_id
   ...> ORDER BY a.last_name ASC;
Isaac Asimov|Foundation
Pearl Buck|The Good Earth
Tom Clancy|The Hunt For Red October
Tom Clancy|Patriot Games
Stephen King|It
Stephen King|Dead Zone
Stephen King|The Shining
John Le Carre|Tinker, Tailor, Soldier, Spy: A George Smiley Novel
Alex Michaelides|The Silent Patient
Carol Shaben|Into The Abyss

CREATE TABLE author_publisher (
    author_id INTEGER REFERENCES author,
    publisher_id INTEGER REFERENCES publisher
);

 1sqlite> SELECT
 2   ...> a.first_name || ' ' || a.last_name AS author_name,
 3   ...> p.name AS publisher_name
 4   ...> FROM author a
 5   ...> JOIN author_publisher ap ON ap.author_id = a.author_id
 6   ...> JOIN publisher p ON p.publisher_id = ap.publisher_id
 7   ...> ORDER BY a.last_name ASC;
 8Isaac Asimov|Random House
 9Pearl Buck|Random House
10Pearl Buck|Simon & Schuster
11Tom Clancy|Berkley
12Tom Clancy|Simon & Schuster
13Stephen King|Random House
14Stephen King|Penguin Random House
15John Le Carre|Berkley
16Alex Michaelides|Simon & Schuster
17Carol Shaben|Simon & Schuster

 1sqlite> SELECT
 2   ...> a.first_name || ' ' || a.last_name AS author_name,
 3   ...> COUNT(b.title) AS total_books
 4   ...> FROM author a
 5   ...> JOIN book b ON b.author_id = a.author_id
 6   ...> GROUP BY author_name
 7   ...> ORDER BY total_books DESC, a.last_name ASC;
 8Stephen King|3
 9Tom Clancy|2
10Isaac Asimov|1
11Pearl Buck|1
12John Le Carre|1
13Alex Michaelides|1
14Carol Shaben|1

 1from sqlalchemy import Column, Integer, String, ForeignKey, Table
 2from sqlalchemy.orm import relationship, backref
 3from sqlalchemy.ext.declarative import declarative_base
 4
 5Base = declarative_base()
 6
 7author_publisher = Table(
 8    "author_publisher",
 9    Base.metadata,
10    Column("author_id", Integer, ForeignKey("author.author_id")),
11    Column("publisher_id", Integer, ForeignKey("publisher.publisher_id")),
12)
13
14book_publisher = Table(
15    "book_publisher",
16    Base.metadata,
17    Column("book_id", Integer, ForeignKey("book.book_id")),
18    Column("publisher_id", Integer, ForeignKey("publisher.publisher_id")),
19)
20
21class Author(Base):
22    __tablename__ = "author"
23    author_id = Column(Integer, primary_key=True)
24    first_name = Column(String)
25    last_name = Column(String)
26    books = relationship("Book", backref=backref("author"))
27    publishers = relationship(
28        "Publisher", secondary=author_publisher, back_populates="authors"
29    )
30
31class Book(Base):
32    __tablename__ = "book"
33    book_id = Column(Integer, primary_key=True)
34    author_id = Column(Integer, ForeignKey("author.author_id"))
35    title = Column(String)
36    publishers = relationship(
37        "Publisher", secondary=book_publisher, back_populates="books"
38    )
39
40class Publisher(Base):
41    __tablename__ = "publisher"
42    publisher_id = Column(Integer, primary_key=True)
43    name = Column(String)
44    authors = relationship(
45        "Author", secondary=author_publisher, back_populates="publishers"
46    )
47    books = relationship(
48        "Book", secondary=book_publisher, back_populates="publishers"
49    )

Column("author_id", Integer, ForeignKey("author.author_id"))

books = relationship("Book", backref=backref("author"))

author_id = Column(Integer, ForeignKey("author.author_id"))

publishers = relationship(
    "Publisher", secondary=author_publisher, back_populates="authors"
)

book = session.query(Book).filter_by(Book.title == "The Stand").one_or_none()
print(f"Authors name: {book.author.first_name} {book.author.last_name}")

results = session.query(Author).all()

author_book_totals = (
    session.query(
        Author.first_name,
        Author.last_name,
        func.count(Book.title).label("book_total")
    )
    .join(Book)
    .group_by(Author.last_name)
    .order_by(desc("book_total"))
    .all()
)

 1def main():
 2    """Main entry point of program"""
 3    # Connect to the database using SQLAlchemy
 4    with resources.path(
 5        "project.data", "author_book_publisher.db"
 6    ) as sqlite_filepath:
 7        engine = create_engine(f"sqlite:///{sqlite_filepath}")
 8    Session = sessionmaker()
 9    Session.configure(bind=engine)
10    session = Session()
11
12    # Get the number of books printed by each publisher
13    books_by_publisher = get_books_by_publishers(session, ascending=False)
14    for row in books_by_publisher:
15        print(f"Publisher: {row.name}, total books: {row.total_books}")
16    print()
17
18    # Get the number of authors each publisher publishes
19    authors_by_publisher = get_authors_by_publishers(session)
20    for row in authors_by_publisher:
21        print(f"Publisher: {row.name}, total authors: {row.total_authors}")
22    print()
23
24    # Output hierarchical author data
25    authors = get_authors(session)
26    output_author_hierarchy(authors)
27
28    # Add a new book
29    add_new_book(
30        session,
31        author_name="Stephen King",
32        book_title="The Stand",
33        publisher_name="Random House",
34    )
35    # Output the updated hierarchical author data
36    authors = get_authors(session)
37    output_author_hierarchy(authors)

 1def get_books_by_publishers(session, ascending=True):
 2    """Get a list of publishers and the number of books they've published"""
 3    if not isinstance(ascending, bool):
 4        raise ValueError(f"Sorting value invalid: {ascending}")
 5
 6    direction = asc if ascending else desc
 7
 8    return (
 9        session.query(
10            Publisher.name, func.count(Book.title).label("total_books")
11        )
12        .join(Publisher.books)
13        .group_by(Publisher.name)
14        .order_by(direction("total_books"))
15    )

def get_authors_by_publishers(session, ascending=True):
    """Get a list of publishers and the number of authors they've published"""
    if not isinstance(ascending, bool):
        raise ValueError(f"Sorting value invalid: {ascending}")

    direction = asc if ascending else desc

    return (
        session.query(
            Publisher.name,
            func.count(Author.first_name).label("total_authors"),
        )
        .join(Publisher.authors)
        .group_by(Publisher.name)
        .order_by(direction("total_authors"))
    )

def get_authors(session):
    """Get a list of author objects sorted by last name"""
    return session.query(Author).order_by(Author.last_name).all()

 1def add_new_book(session, author_name, book_title, publisher_name):
 2    """Adds a new book to the system"""
 3    # Get the author's first and last names
 4    first_name, _, last_name = author_name.partition(" ")
 5
 6    # Check if book exists
 7    book = (
 8        session.query(Book)
 9        .join(Author)
10        .filter(Book.title == book_title)
11        .filter(
12            and_(
13                Author.first_name == first_name, Author.last_name == last_name
14            )
15        )
16        .filter(Book.publishers.any(Publisher.name == publisher_name))
17        .one_or_none()
18    )
19    # Does the book by the author and publisher already exist?
20    if book is not None:
21        return
22
23    # Get the book by the author
24    book = (
25        session.query(Book)
26        .join(Author)
27        .filter(Book.title == book_title)
28        .filter(
29            and_(
30                Author.first_name == first_name, Author.last_name == last_name
31            )
32        )
33        .one_or_none()
34    )
35    # Create the new book if needed
36    if book is None:
37        book = Book(title=book_title)
38
39    # Get the author
40    author = (
41        session.query(Author)
42        .filter(
43            and_(
44                Author.first_name == first_name, Author.last_name == last_name
45            )
46        )
47        .one_or_none()
48    )
49    # Do we need to create the author?
50    if author is None:
51        author = Author(first_name=first_name, last_name=last_name)
52        session.add(author)
53
54    # Get the publisher
55    publisher = (
56        session.query(Publisher)
57        .filter(Publisher.name == publisher_name)
58        .one_or_none()
59    )
60    # Do we need to create the publisher?
61    if publisher is None:
62        publisher = Publisher(name=publisher_name)
63        session.add(publisher)
64
65    # Initialize the book relationships
66    book.author = author
67    book.publishers.append(publisher)
68    session.add(book)
69
70    # Commit to the database
71    session.commit()