使用Python,SQLite和SQLAlchemy管理数据

6个月前 283次点击 来自 其他

收录专题: Python边学边译

标签: Python

原文链接: Data Management With Python, SQLite, and SQLAlchemy

所有程序都以多种形式处理数据,并且程序应具有多次保存和检索该数据的能力。Python, SQLiteSQLAlchemy 为您的程序提供数据库功能,使您无需数据库服务器即可将数据存储在单个文件中。

您可以使用多种格式的平面文件(flat files) 来获得类似的结果,包括CSV,JSON,XML甚至是自定义格式。平面文件通常是人类可读的文本文件(尽管它们也可以是二进制数据),其结构可以由计算机程序解析。在下面,您将探索如何使用SQL数据库和平面文件进行数据存储和处理,并了解如何决定使用哪种方式更适合您的程序。

在本教程中,您将学习如何使用:

  • 使用平面文件(Flat files) 数据存储的优缺点
  • 使用SQL 改善对持久数据的访问
  • 使用SQLite 数据存储
  • 使用SQLAlchemy 将数据转换为Python对象

使用平面文件进行数据存储

平面文件包含数据,但其没有内部层次结构,并且通常没有对外部文件的引用。平面文件包含人类可读的字符,对于创建和读取数据非常有用。由于平面文件不必使用固定的字段宽度,因此它们通常使用其他结构来使程序能够解析文本。

例如,CSV(comma-separated value)是以逗号分隔的纯文本文件,其中逗号字符用于分隔数据元素。每一行文本代表一行数据,每个逗号分隔的值是该行内的一个字段。逗号分隔符指示数据值之间的边界。

Python擅长读取和保存文件。使用Python读取数据文件,使您在以后重新运行应用程序时,将其恢复到有用的状态。将数据保存在文件中,使您可以在应用程序用户和站点之间共享程序中的数据信息。

在程序可以读取数据文件之前,它必须能够理解数据。通常,这意味着数据文件需要具有某种结构,应用程序可以使用该结构来读取和解析文本。

以下是本教程中第一个示例程序使用CSV文件author_book_publisher.csv

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

第一行提供以逗号分隔的字段列表,这些字段是其余各行后面数据的列名。其余各行包含数据,每行代表一条记录。

平面文件的优点

使用平面文件中的数据是可管理的,并且易于实现。以人类可读的格式存储数据不仅有助于使用文本编辑器创建数据文件,而且有助于检查数据并查找任何不一致或问题。

许多应用程序可以导出文件生成数据的平面文件版本。例如,Excel可以在电子表格中导入或导出CSV文件。如果您要共享数据,则平面文件还具有自包含(self-contained)和可传输的优点。

几乎每种编程语言都有工具和库,这些工具和库使处理CSV文件更加容易。Python具有内置csv模块和功能强大的pandas模块,使CSV文件的使用成为有效的解决方案。

平面文件的缺点

随着数据量变大,使用平面文件的优势开始减弱。大文件仍然是人类可读的(human-readable),但是编辑它们,创建数据或查找问题变得更加困难。如果您的应用程序将更改文件中的数据,则一种解决方案是将整个文件读入内存,进行更改,然后将数据写到另一个文件中。

使用平面文件的另一个问题是,您将需要在文件语法内显式创建和维护部分数据与应用程序之间的关系。此外,您需要在应用程序中生成代码以使用这些关系。

最麻烦的是,您想要与之共享数据文件的人,也需要了解并根据您在数据中创建的结构和关系采取行动。要访问信息,这些用户不仅需要了解数据的结构,还需要了解访问数据所必需的编程工具。

在此,我们仅对平面文件的优缺点做简单介绍,我们将直接略过使用平面文件处理的过程,有兴趣的读者可跳转至原文进行阅读。

使用SQL构建数据库

要利用SQL的强大功能,您需要对author_book_publisher.csv文件中的数据应用一些数据库规范化。为此,您需要将作者,书籍和出版者分成单独的数据库表。

从概念上讲,数据以二维表结构存储在数据库中。每个表由记录的行组成,每个记录由包含数据的列或字段组成。

字段中包含的数据是预定义的类型,包括文本,整数,浮点数等。CSV文件有所不同,因为所有字段均为文本,并且必须由程序进行解析才能为它们分配数据类型。

表中的每个记录都有一个主键,该键定义为给记录一个唯一的标识符。主键类似于Python字典中的键。数据库引擎本身通常为插入到数据库表中的每个记录生成主键,作为递增的整数值。

尽管主键通常由数据库引擎自动生成,但不一定必须如此。如果存储在字段中的数据在该字段中表中的所有其他数据之间是唯一的,则它可以是主键。例如,包含有关书籍数据的表可以使用书籍的ISBN作为主键。

创建数据库

首先,您需要具备一些sqlite3命令行工具 的知识, 并在您的电脑上安装和配置Sqlite客户端。

创建一个新的数据库author_book_publisher.db,命令如下:

$ sqlite3 author_book_publisher.db

使用SQL创建表

使用SQL语句创建CSV文件中代表作者,书籍和出版者的三个表:

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

author_book_publisher.csv文件中的作者数据创建并填充了该表后,即可使用SQL语句对其进行访问。以下语句(也称为query)使用通配符(*)获取author表中的所有数据并将其输出:

SELECT * FROM author;

在打开数据库的情况下运行SQLite命令行工具后,即可输入SQL命令。这是上面的SQL命令及其输出,然后输入命令·q退出程序:

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

请注意,每个作者在表中仅存在一次。与CSV文件不同,CSV文件为某些作者有多个条目,在这里,每个作者只需要一个唯一的记录。

使用SQL维护数据库

SQL通过插入新数据以及更新或删除现有数据,提供了使用现有数据库和表的方法。这是用于在author表中插入新作者的示例SQL语句:

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

这个SQL语句插入值“ Paul”和“ Mendez”到相应的列first_name和last_name的的author表。

请注意,author_id未指定该列。因为该列是主键,所以数据库引擎会生成该值并将其作为语句执行的一部分插入。

更新数据库表中的记录是一个简单的过程。例如,假设斯蒂芬·金(Stephen King)希望以他的笔名Richard Bachman出名。这是一条用于更新数据库记录的SQL语句:

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

SQL语句查找要'Stephen King'使用条件语句的单个记录WHERE first_name = 'Stephen' AND last_name = 'King',然后使用新值更新first_name和last_name字段。SQL使用等号(=)作为比较运算符和赋值运算符。

您也可以从数据库中删除记录。这是从author表中删除记录的示例SQL语句:

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

这个SQL语句从删除单行author表,其中first_name等于'Paul'和last_name等于'Mendez'。

删除记录时要小心!您设置的条件必须尽可能具体。范围太大的条件可能导致删除比您期望的更多的记录。例如,如果条件仅基于line first_name = 'Paul',则将从数据库中删除所有名字为Paul的作者。

建立关系

关系型数据库使您可以将数据分为多个表并在它们之间建立连接。

一对多关系

一对多的关系就像是客户在线订购的物品,一个客户可以有多个订单,但是每个订单都属于一个客户。该author_book_publisher.db数据库具有作者和书籍形式的一对多关系。每个作者可以写很多书,但是每本书都是由一个作者写的。

正如您在上面的表创建中所看到的,这些单独实体的实现是将每个实体放入数据库表中,一个用于作者,一个用于书籍。但是,如何实现这两个表之间的一对多关系?

请记住,数据库中的每个表都有一个字段指定为该表的主键。上面的每个表格都有一个使用以下模式命名的主键字段:<table name>_id

book上面显示的表包含一个author_id引用该author表的字段。该author_id字段在作者和书籍之间建立了一对多关系。

下面的SQL查询使用SQLite命令行应用程序将author和book表连接在一起:

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

上面的SQL查询通过使用两者之间建立的关系将表连接在一起,从而从author和book表中收集信息。SQL字符串串联将作者的全名分配给别名author_name。查询收集的数据按last_name字段升序排序。

SQL语句中有一些注意事项。首先,在同一列中按作者的全名显示作者,并按姓氏排序。另外,由于一对多的关系,作者多次出现在输出中。对于他们在数据库中写的每本书,作者的名字都是重复的。

通过为作者和书籍创建单独的表并建立它们之间的关系,可以减少数据的冗余。现在,您只需要在一个地方编辑作者的数据,该更改就会显示在访问该数据的所有SQL查询中。

多对多关系

author_book_publisher.db数据库中存在作者与出版者之间以及书籍与出版者之间的多对多关系。一位作者可以与许多发布者合作,而一位发布者可以与许多作者合作。同样,一本书可以由许多出版商出版,而一出版商可以出版许多书。

与一对多关系相比,在数据库中处理这种情况涉及更多的工作,因为这种关系是双向的。多对多关系是由关联表创建的,该关联表充当两个相关表之间的桥梁。

关联表至少包含两个外键字段,它们是两个关联表中每个表的主键。该SQL语句创建与author和publisher表相关的关联表:

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

SQL语句创建一个author_publisher引用现有表author和publisher表的主键的新表。该author_publisher表是建立作者和发行者之间关系的关联表。

由于关系是两个主键之间的关系,因此无需为关联表本身创建主键。两个相关密钥的组合为一行数据,并创建了唯一的标识符。

和以前一样,您可以使用JOIN关键字将两个表连接在一起。将author表连接到publisher表是一个两步过程:

  1. author_publisherJOIN关联author
  2. publisherJOIN关联author_publisher

这是一个示例SQL查询,该查询返回作者列表和出版其书籍的出版商:

 sqlite> SELECT
  ...> a.first_name || ' ' || a.last_name AS author_name,
  ...> p.name AS publisher_name
  ...> FROM author a
  ...> JOIN author_publisher ap ON ap.author_id = a.author_id
  ...> JOIN publisher p ON p.publisher_id = ap.publisher_id
  ...> ORDER BY a.last_name ASC;
 Isaac Asimov|Random House
 Pearl Buck|Random House
Pearl Buck|Simon & Schuster
Tom Clancy|Berkley
Tom Clancy|Simon & Schuster
Stephen King|Random House
Stephen King|Penguin Random House
John Le Carre|Berkley
Alex Michaelides|Simon & Schuster
Carol Shaben|Simon & Schuster

上面的语句执行以下操作:

  • Line 1 开始一条SELECT语句,以从数据库中获取数据。

  • Line 2 使用author表的a别名从表中选择名字和姓氏 ,并将它们与空格字符连接在一起。

  • Line 3 设置发布者名称的别名为publisher_name

  • Line 4 使用author表作为检索数据的第一个来源,并分配别名a

  • Line 5 author_publisherJOIN关联author表。

  • Line 6 publisherJOIN关联author_publisher表。

  • Line 7 按作者姓氏将数据按字母升序排序,并结束SQL查询。

下面的SQL查询返回作者列表和他们写的书数。该列表首先按书籍数降序排列,然后按作者姓名按字母顺序排列:

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

在上面的示例中,您利用SQL进行聚合计算并将结果排序为有用的顺序。数据库根据其内置的数据组织能力执行计算,通常比在Python中对原始数据集执行相同类型的计算要快。

使用SQLAlchemy

SQLAlchemy 是一个功能强大的Python数据库访问工具套件,其对象关系映射器(ORM)是其最著名的组件之一。

当您使用像Python这样的面向对象的语言进行工作时,从对象的角度进行思考通常会很有用。可以将SQL查询返回的结果映射到对象,但是这样做会违反数据库的工作原理。坚持使用SQL提供的标量结果会影响Python开发人员的工作方式。这个问题被称为对象关系阻抗不匹配

SQLAlchemy提供的ORM位于SQLite数据库和Python程序之间,并转换数据库引擎和Python对象之间的数据流。SQLAlchemy允许您根据对象进行思考,并且仍然保留数据库引擎的强大功能。

模型(Model)

创建SQLAlchemy与数据库的基本要素之一就是创建模型。该模型是一个Python类,用于定义数据库查询结果返回的Python对象与基础数据库表之间的数据映射。

以下是models.py用于创建代表author_book_publisher.db数据库模型文件:

from sqlalchemy import Column, Integer, String, ForeignKey, Table
from sqlalchemy.orm import relationship, backref
from sqlalchemy.ext.declarative import declarative_base
 
Base = declarative_base()

author_publisher = Table(
    "author_publisher",
    Base.metadata,
    Column("author_id", Integer, ForeignKey("author.author_id")),
    Column("publisher_id", Integer, ForeignKey("publisher.publisher_id")),
)

book_publisher = Table(
    "book_publisher",
    Base.metadata,
    Column("book_id", Integer, ForeignKey("book.book_id")),
    Column("publisher_id", Integer, ForeignKey("publisher.publisher_id")),
)

class Author(Base):
    __tablename__ = "author"
    author_id = Column(Integer, primary_key=True)
    first_name = Column(String)
    last_name = Column(String)
    books = relationship("Book", backref=backref("author"))
    publishers = relationship(
        "Publisher", secondary=author_publisher, back_populates="authors"
    )

class Book(Base):
    __tablename__ = "book"
    book_id = Column(Integer, primary_key=True)
    author_id = Column(Integer, ForeignKey("author.author_id"))
    title = Column(String)
    publishers = relationship(
        "Publisher", secondary=book_publisher, back_populates="books"
    )

class Publisher(Base):
    __tablename__ = "publisher"
    publisher_id = Column(Integer, primary_key=True)
    name = Column(String)
    authors = relationship(
        "Author", secondary=author_publisher, back_populates="publishers"
    )
    books = relationship(
        "Book", secondary=book_publisher, back_populates="publishers"
    )

上面代码为author_book_publisher.db数据库中五个表的映射。关于TableForeignKeyrelationship(),和backref具体的使用规则,请跳转原文链接或查阅相关文档。

查询

在SQLAlchemy执行SELECT * FROM author;查询 :

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

**session**是Python程序中SQLAlchemy与SQLite用于通信的对象。

使用SQLAlchemy,您可以执行前面显示的更复杂的聚合查询,例如查询作者列表和他们所写书的数量,如下所示:

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

结论

本文在原文基础上只截取了有关数据库,SQLite,SQL和SQLAlchemy的基本应用,并阐述了为何从平面文件存储数据到使用关系型数据库存储,如何使用SQLAlchemy方便地进行数据映射和查询等操作!

本文在具体使用上并没有深究,原文在更多细节上进行了探讨,包括Flask中结合SQLAlchemy的使用,有兴趣的读者请跳转至原文阅读。

Card image cap
开发者雷

尘世间一个小小的开发者,每天增加一些无聊的知识,就不会无聊了

要加油~~~

技术文档 >> 系列应用 >>
热推应用
Let'sLearnSwift
学习Swift的入门教程
PyPie
Python is as good as Pie
标签