Gestion des données avec Python, SQLite et SQLAlchemy – Real Python

Relations plusieurs-à-plusieurs

Plusieurs à plusieurs des relations existent dans le author_book_publisher.db base de données entre auteurs et éditeurs ainsi qu'entre livres et éditeurs. Un auteur peut travailler avec de nombreux éditeurs, et un éditeur peut travailler avec plusieurs auteurs. De même, un livre peut être publié par de nombreux éditeurs et un éditeur peut publier plusieurs livres.

La gestion de cette situation dans la base de données est plus complexe qu'une relation un-à-plusieurs car la relation va dans les deux sens. Les relations plusieurs-à-plusieurs sont créées par un table d'association agissant comme un pont entre les deux tables liées.

La table d'association contient au moins deux champs de clé étrangère, qui sont les clés primaires de chacune des deux tables associées. Cette instruction SQL crée la table d'association reliant le auteur et éditeur les tables:

CRÉER TABLE author_publisher (
    author_id ENTIER LES RÉFÉRENCES auteur,
    publisher_id ENTIER LES RÉFÉRENCES éditeur
);

Les instructions SQL créent un nouveau author_publisher table référençant les clés primaires de l'existant auteur et éditeur les tables. le author_publisher table est une table d'association établissant des relations entre un auteur et un éditeur.

Étant donné que la relation se situe entre deux clés primaires, il n’est pas nécessaire de créer une clé primaire pour la table d’association elle-même. La combinaison des deux clés associées crée un identifiant unique pour une ligne de données.

Comme auparavant, vous utilisez le JOINDRE mot-clé pour connecter deux tables ensemble. Connecter le auteur table to the publisher table is a two-step process:

1. JOIN les auteur table with the author_publisher table.
2. JOIN les author_publisher table with the publisher table.

le author_publisher association table provides the bridge through which the JOIN connects the two tables. Here’s an example SQL query returning a list of authors and the publishers publishing their books:

    1 sqlite> SELECT
    2             ...> a.first_name || ' ' || a.last_name AS author_name,
    3             ...> p.name AS publisher_name
    4             ...> FROM auteur 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;
    8 Isaac Asimov|Random House
    9 Pearl Buck|Random House
dix Pearl Buck|Simon & Schuster
11 Tom Clancy|Berkley
12 Tom Clancy|Simon & Schuster
13 Stephen King|Random House
14 Stephen King|Penguin Random House
15 John Le Carre|Berkley
16 Alex Michaelides|Simon & Schuster
17 Carol Shaben|Simon & Schuster

The statements above perform the following actions:

• Line 1 starts a SELECT statement to get data from the database.

• Line 2 selects the first and last name from the auteur table using the a alias for the auteur table and concatenates them together with a space character.

• Line 3 selects the publisher’s name aliased to publisher_name.

• Line 4 uses the auteur table as the first source from which to retrieve data and assigns it to the alias a.

• Line 5 is the first step of the process outlined above for connecting the auteur table to the publisher table. It uses the alias ap for the author_publisher association table and performs a JOIN operation to connect the ap.author_id foreign key reference to the a.author_id primary key in the auteur table.

• Line 6 is the second step in the two-step process mentioned above. It uses the alias p for the publisher table and performs a JOIN operation to relate the ap.publisher_id foreign key reference to the p.publisher_id primary key in the publisher table.

• Line 7 sorts the data by the author’s last name in ascending alphabetical order and ends the SQL query.

• Lines 8 to 17 are the output of the SQL query.

Note that the data in the source auteur et publisher tables are normalized, with no data duplication. Yet the returned results have duplicated data where necessary to answer the SQL query.

The SQL query above demonstrates how to make use of a relationship using the SQL JOIN keyword, but the resulting data is a partial re-creation of the author_book_publisher.csv CSV data. What’s the win for having done the work of creating a database to separate the data?

Here’s another SQL query to show a little bit of the power of SQL and the database engine:

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

The SQL query above returns the list of authors and the number of books they’ve written. The list is sorted first by the number of books in descending order, then by the author’s name in alphabetical order:

• Line 1 begins the SQL query with the SELECT keyword.

• Line 2 selects the author’s first and last names, separated by a space character, and creates the alias author_name.

• Line 3 counts the number of books written by each author, which will be used later by the ORDER BY clause to sort the list.

• Line 4 selects the auteur table to get data from and creates the a alias.

• Line 5 connects to the related livre table through a JOIN to the author_id and creates the b alias for the livre table.

• Line 6 generates the aggregated author and total number of books data by using the GROUP BY keyword. GROUP BY is what groups each author_name and controls what books are tallied by COUNT() for that author.

• Line 7 sorts the output first by number of books in descending order, then by the author’s last name in ascending alphabetical order.

• Lines 8 to 14 are the output of the SQL query.

In the above example, you take advantage of SQL to perform aggregation calculations and sort the results into a useful order. Having the database perform calculations based on its built-in data organization ability is usually faster than performing the same kinds of calculations on raw data sets in Python. SQL offers the advantages of using set theory embedded in RDBMS databases.

Entity Relationship Diagrams

An entity-relationship diagram (ERD) is a visual depiction of an entity-relationship model for a database or part of a database. le author_book_publisher.db SQLite database is small enough that the entire database can be visualized by the diagram shown below:

This diagram presents the table structures in the database and the relationships between them. Each box represents a table and contains the fields defined in the table, with the primary key indicated first if it exists.

The arrows show the relationships between the tables connecting a foreign key field in one table to a field, often the primary key, in another table. The table book_publisher has two arrows, one connecting it to the livre table and another connecting it to the publisher table. The arrow indicates the many-to-many relationship between the livre et publisher tables. le author_publisher table provides the same relationship between auteur et publisher.

Working With SQLAlchemy and Python Objects

SQLAlchemy is a powerful database access tool kit for Python, with its object-relational mapper (ORM) being one of its most famous components, and the one discussed and used here.

When you’re working in an object-oriented language like Python, it’s often useful to think in terms of objects. It’s possible to map the results returned by SQL queries to objects, but doing so works against the grain of how the database works. Sticking with the scalar results provided by SQL works against the grain of how Python developers work. This problem is known as object-relational impedance mismatch.

The ORM provided by SQLAlchemy sits between the SQLite database and your Python program and transforms the data flow between the database engine and Python objects. SQLAlchemy allows you to think in terms of objects and still retain the powerful features of a database engine.

    1 de sqlalchemy import Column, Integer, String, ForeignKey, Table
    2 de sqlalchemy.orm import relationship, backref
    3 de sqlalchemy.ext.declarative import declarative_base
    4 
    5 Base = declarative_base()
    6 
    7 author_publisher = Table(
    8     "author_publisher",
    9     Base.metadata,
dix     Column("author_id", Integer, ForeignKey("author.author_id")),
11     Column("publisher_id", Integer, ForeignKey("publisher.publisher_id")),
12 )
13 
14 book_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 
21 class Auteur(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 
31 class 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 
40 class 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"
)

livre = session.query(Book).filter_by(Book.title == "The Stand").one_or_none()
impression(f"Authors name: livre.auteur.first_name livre.auteur.last_name")

résultats = session.query(Auteur).tout()

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

    1 def main():
    2     """Main entry point of program"""
    3     # Connect to the database using SQLAlchemy
    4     avec resources.path(
    5         "project.data", "author_book_publisher.db"
    6     ) comme sqlite_filepath:
    7         engine = create_engine(f"sqlite:///sqlite_filepath")
    8     Session = sessionmaker()
    9     Session.configure(bind=engine)
dix     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     pour row dans books_by_publisher:
15         impression(f"Publisher: row.name, total books: row.total_books")
16     impression()
17 
18     # Get the number of authors each publisher publishes
19     authors_by_publisher = get_authors_by_publishers(session)
20     pour row dans authors_by_publisher:
21         impression(f"Publisher: row.name, total authors: row.total_authors")
22     impression()
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)

    1 def get_books_by_publishers(session, ascending=True):
    2     """Get a list of publishers and the number of books they've published"""
    3     si ne pas isinstance(ascending, bool):
    4         raise ValueError(f"Sorting value invalid: ascending")
    5 
    6     direction = asc si ascending else desc
    7 
    8     return (
    9         session.query(
dix             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"""
    si ne pas isinstance(ascending, bool):
        raise ValueError(f"Sorting value invalid: ascending")

    direction = asc si ascending else desc

    return (
        session.query(
            Publisher.name,
            func.count(Auteur.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(Auteur).order_by(Auteur.last_name).tout()

    1 def 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     livre = (
    8         session.query(Book)
    9         .join(Auteur)
dix         .filtre(Book.title == book_title)
11         .filtre(
12             and_(
13                 Auteur.first_name == first_name, Auteur.last_name == last_name
14             )
15         )
16         .filtre(Book.publishers.tout(Publisher.name == publisher_name))
17         .one_or_none()
18     )
19     # Does the book by the author and publisher already exist?
20     si livre est ne pas Aucun:
21         return
22 
23     # Get the book by the author
24     livre = (
25         session.query(Book)
26         .join(Auteur)
27         .filtre(Book.title == book_title)
28         .filtre(
29             and_(
30                 Auteur.first_name == first_name, Auteur.last_name == last_name
31             )
32         )
33         .one_or_none()
34     )
35     # Create the new book if needed
36     si livre est Aucun:
37         livre = Book(title=book_title)
38 
39     # Get the author
40     auteur = (
41         session.query(Auteur)
42         .filtre(
43             and_(
44                 Auteur.first_name == first_name, Auteur.last_name == last_name
45             )
46         )
47         .one_or_none()
48     )
49     # Do we need to create the author?
50     si auteur est Aucun:
51         auteur = Auteur(first_name=first_name, last_name=last_name)
52         session.add(auteur)
53 
54     # Get the publisher
55     publisher = (
56         session.query(Publisher)
57         .filtre(Publisher.name == publisher_name)
58         .one_or_none()
59     )
60     # Do we need to create the publisher?
61     si publisher est Aucun:
62         publisher = Publisher(name=publisher_name)
63         session.add(publisher)
64 
65     # Initialize the book relationships
66     livre.auteur = auteur
67     livre.publishers.append(publisher)
68     session.add(livre)
69 
70     # Commit to the database
71     session.commit()

Providing Access to Multiple Users

To this point, you’ve seen how to use pandas, SQLite, and SQLAlchemy to access the same data in different ways. For the relatively straightforward use case of the author, book, and publisher data, it could still be a toss-up whether you should use a database.

One deciding factor when choosing between using a flat file or a database is data and relationship complexity. If the data for each entity is complicated and contains many relationships between the entities, then creating and maintaining it in a flat file might become more difficult.

Another factor to consider is whether you want to share the data between multiple users. The solution to this problem might be as simple as using a sneakernet to physically move data between users. Moving data files around this way has the advantage of ease of use, but the data can quickly get out of sync when changes are made.

The problem of keeping the data consistent for all users becomes even more difficult if the users are remote and want to access the data across networks. Even when you’re limited to a single language like Python and using pandas to access the data, network file locking isn’t sufficient to ensure the data doesn’t get corrupted.

Providing the data through a server application and a user interface alleviates this problem. The server is the only application that needs file-level access to the database. By using a database, the server can take advantage of SQL to access the data using a consistent interface no matter what programming language the server uses.

The last example program demonstrates this by providing a web application and user interface to the Chinook sample SQLite database. Peter Stark generously maintains the Chinook database as part of the SQLite Tutorial site. If you’d like to learn more about SQLite and SQL in general, then the site is a great resource.

The Chinook database provides artist, music, and playlist information along the lines of a simplified Spotify. The database is part of the example code project in the project/data dossier.

Using Flask With Python, SQLite, and SQLAlchemy

le examples/example_3/chinook_server.py program creates a Flask application that you can interact with using a browser. The application makes use of the following technologies:

• Flask Blueprint is part of Flask and provides a good way to follow the separation of concerns design principle and create distinct modules to contain functionality.

• Flask SQLAlchemy is an extension for Flask that adds support for SQLAlchemy in your web applications.

• Flask_Bootstrap4 packages the Bootstrap front-end tool kit, integrating it with your Flask web applications.

• Flask_WTF extends Flask with WTForms, giving your web applications a useful way to generate and validate web forms.

• python_dotenv is a Python module that an application uses to read environment variables from a file and keep sensitive information out of program code.

Though not necessary for this example, a .env file holds the environment variables for the application. le .env file exists to contain sensitive information like passwords, which you should keep out of your code files. However, the content of the project .env file is shown below since it doesn’t contain any sensitive data:

SECRET_KEY = "you-will-never-guess"
SQLALCHEMY_TRACK_MODIFICATIONS = False
SQLAlCHEMY_ECHO = False
DEBUG = True

The example application is fairly large, and only some of it is relevant to this tutorial. For this reason, examining and learning from the code is left as an exercise for the reader. That said, you can take a look at an animated screen capture of the application below, followed by the HTML that renders the home page and the Python Flask route that provides the dynamic data.

Here’s the application in action, navigating through various menus and features:

The animated screen capture starts on the application home page, styled using Bootstrap 4. The page displays the artists in the database, sorted in ascending order. The remainder of the screen capture presents the results of clicking on the displayed links or navigating around the application from the top-level menu.

Here’s the Jinja2 HTML template that generates the home page of the application:

    1 % extends "base.html" %
 2 
    3 % block content %
 4 <div class="container-fluid">
    5   <div class="m-4">
    6     <div class="card" style="width: 18rem;">
    7       <div class="card-header">Create New Artist</div>
    8       <div class="card-body">
    9         <form method="POST" action="url_for('artists_bp.artists')">
dix            form.csrf_token 
11            render_field(form.name, placeholder=form.name.label.text) 
12           <button type="submit" class="btn btn-primary">Create</button>
13         </form>
14       </div>
15     </div>
16     <table class="table table-striped table-bordered table-hover table-sm">
17       <caption>List of Artists</caption>
18       <thead>
19         <tr>
20           <e>Artist Name</e>
21         </tr>
22       </thead>
23       <tbody>
24         % for artist in artists %
25         <tr>
26           <td>
27             <a href="url_for('albums_bp.albums', artist_id=artist.artist_id)">
28                artist.name 
29             </a>
30           </td>
31         </tr>
32         % endfor %
33       </tbody>
34     </table>
35   </div>
36 </div>
37 % endblock %

Here’s what’s going on in this Jinja2 template code:

• Line 1 uses Jinja2 template inheritance to build this template from the base.html template. le base.html template contains all the HTML5 boilerplate code as well as the Bootstrap navigation bar consistent across all pages of the site.

• Lines 3 to 37 contain the block content of the page, which is incorporated into the Jinja2 macro of the same name in the base.html base template.

• Lines 9 to 13 render the form to create a new artist. This uses the features of Flask-WTF to generate the form.

• Lines 24 to 32 create a pour loop that renders the table of artist names.

• Lines 27 to 29 render the artist name as a link to the artist’s album page showing the songs associated with a particular artist.

Here’s the Python route that renders the page:

    1 de flask import Blueprint, render_template, redirect, url_for
    2 de flask_wtf import FlaskForm
    3 de wtforms import StringField
    4 de wtforms.validators import InputRequired, ValidationError
    5 de app import db
    6 de app.models import Artiste
    7 
    8 # Set up the blueprint
    9 artists_bp = Blueprint(
dix     "artists_bp", __name__, template_folder="templates", static_folder="static"
11 )
12 
13 def does_artist_exist(form, field):
14     artiste = (
15         db.session.query(Artiste)
16         .filtre(Artiste.name == field.data)
17         .one_or_none()
18     )
19     si artiste est ne pas Aucun:
20         raise ValidationError("Artist already exists", field.data)
21 
22 class CreateArtistForm(FlaskForm):
23     name = StringField(
24         label="Artist's Name", validators=[[[[InputRequired(), does_artist_exist]
25     )
26 
27 @artists_bp.route("/")
28 @artists_bp.route("/artists", methods=[[[["GET", "POST"])
29 def artists():
30     form = CreateArtistForm()
31 
32     # Is the form valid?
33     si form.validate_on_submit():
34         # Create new artist
35         artiste = Artiste(name=form.name.data)
36         db.session.add(artiste)
37         db.session.commit()
38         return redirect(url_for("artists_bp.artists"))
39 
40     artists = db.session.query(Artiste).order_by(Artiste.name).tout()
41     return render_template("artists.html", artists=artists, form=form,)

Let’s go over what the above code is doing:

• Lines 1 to 6 import all the modules necessary to render the page and initialize forms with data from the database.

• Lines 9 to 11 create the blueprint for the artists page.

• Lines 13 to 20 create a custom validator function for the Flask-WTF forms to make sure a request to create a new artist doesn’t conflict with an already existing artist.

• Lines 22 to 25 create the form class to handle the artist form rendered in the browser and provide validation of the form field inputs.

• Lines 27 to 28 connect two routes to the artists() function they decorate.

• Line 30 creates an instance of the CreateArtistForm() classe.

• Line 33 determines if the page was requested through the HTTP methods GET or POST (submit). If it was a POST, then it also validates the fields of the form and informs the user if the fields are invalid.

• Lines 35 to 37 create a new artist object, add it to the SQLAlchemy session, and commit the artist object to the database, persisting it.

• Line 38 redirects back to the artists page, which will be rerendered with the newly created artist.

• Line 40 runs an SQLAlchemy query to get all the artists in the database and sort them by Artist.name.

• Line 41 renders the artists page if the HTTP request method was a GET.

You can see that a great deal of functionality is created by a reasonably small amount of code.

Creating a REST API Server

You can also create a web server providing a REST API. This kind of server offers URL endpoints responding with data, often in JSON format. A server providing REST API endpoints can be used by JavaScript single-page web applications through the use of AJAX HTTP requests.

Flask is an excellent tool for creating REST applications. For a multi-part series of tutorials about using Flask, Connexion, and SQLAlchemy to create REST applications, check out Python REST APIs With Flask, Connexion, and SQLAlchemy.

If you’re a fan of Django and are interested in creating REST APIs, then check out Django Rest Framework – An Introduction and Create a Super Basic REST API with Django Tastypie.

It’s well worth considering SQLite for your Python application, no matter what it is. Using a database gives your application versatility, and it might create surprising opportunities to add additional features.

Conclusion

You’ve covered a lot of ground in this tutorial about databases, SQLite, SQL, and SQLAlchemy! You’ve used these tools to move data contained in flat files to an SQLite database, access the data with SQL and SQLAlchemy, and provide that data through a web server.

In this tutorial, you’ve learned:

• Why an SQLite database can be a compelling alternative to flat-file data storage
• How to normalize data to reduce data redundancy and increase data integrity
• How to use SQLAlchemy to work with databases in an object-oriented manner
• How to build a web application to serve a database to multiple users

Working with databases is a powerful abstraction for working with data that adds significant functionality to your Python programs and allows you to ask interesting questions of your data.

You can get all of the code and data you saw in this tutorial at the link below:

Lectures complémentaires

This tutorial is an introduction to using databases, SQL, and SQLAlchemy, but there’s much more to learn about these subjects. These are powerful, sophisticated tools that no single tutorial can cover adequately. Here are some resources for additional information to expand your skills: