SQLAlchemy is an engine developed in Python that has several components for working with databases. It follows some of the most frequently used patterns for relational object mapping, where classes can be mapped in the database in multiple ways, which allows you to develop the object model and the database schema in an uncoupled way from the very beginning.

SQLAlchemy includes various tools that are focused on interacting with relational databases, among which we can highlight the following:

• SQLAlchemy Core, which allows you to create a generic and independent interface of the database manager by means of an expression language based on SQL.
• SQLAlchemy ORM, a mapper between objects and relational transactions or ORM (object relational mapper). It includes support for SQLite, MySQL, PostgreSQL, Oracle, and MS SQL, among others. 

To install SQLAlchemy, just execute the following command:

pip install sqlalchemy

This example will show us how to create a table, insert data, and select it from the database by using SQLAlchemy Core and ORM modules. For more information about SQLAlchemy ORM, the official documentation is available at https://docs.sqlalchemy.org/en/latest/orm/tutorial.html.

To illustrate the idea, the following diagram shows a data model for a system that is responsible for managing book records and authors:

Our model consists of the Book and Author tables. The intermediate table, book_author, is used to express the many-to-many relationship among the book and author tables. First, we must map the model or schema of the database by means of SQLAlchemy.

You can find the following code in the models.py file inside the sqlalchemy folder:

#!/usr/local/bin/python3

from sqlalchemy import (create_engine, Column, Date, Integer, ForeignKey, String, Table)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

engine = create_engine('sqlite:///books_authors.db', echo=True)
Base = declarative_base()

#Relation many to many between book and author
author_book = Table('author_book', Base.metadata,
 Column('book_id', Integer, ForeignKey('book.id')),
 Column('author_id', Integer, ForeignKey('author.id'))
)

In the previous code block, we defined the database connection and the relationship between the book and author entities by creating a new table with two columns. Each one is the foreign key to the book and author tables. In the following code block, we will define the Book entity with the init constructor and its relation with the Author table through the author_book relation:

class Book(Base):
    __tablename__ = 'book'
    id = Column(Integer, primary_key=True)
    title = Column(String(120), index=True, nullable=False)
    date = Column(Date)
    isbn = Column(String(13))
    authors = relationship("Author", secondary=author_book)

    def __init__(self, title, date, isbn):
        self.title = title
        self.date = date
        self.isbn = isbn

    def __repr__(self):
        return self.title

Finally, we create the Author model and initialize our database with the create_all() method from the metadata object:

class Author(Base):
    __tablename__ = 'author'
    id = Column(Integer, primary_key=True)
    name = Column(String(120), nullable=False)
    
    def __init__(self, name):
        self.name = name

Base.metadata.create_all(engine)

By executing the models.py script, we can see how the books_authors.db file is generated in your local filesystem.

In the following screenshot, we can see the tables that are created when you visualize this file with the SQLite browser:

Now, let's explain the content of the models.py script. First, we will need to connect to our database. The sqlalchemy.engine.base.Engine class is responsible for instantiating objects, connecting to a database, and in turn mapping the attributes of the objects that are created by the ORM model. To instantiate an object from sqlalchemy.engine.base.Engine, the sqlalchemy.create_engine() function is used with the following syntax:

from sqlalchemy import create_engine
engine = create_engine('sqlite:///books_authors.db', echo=True)

After creating our engine, we need to create our tables. In ORM, the process of creating tables begins with defining the classes that we will use in the mapping process.

Before creating the database, it is necessary to define a model that maps to an object with at least one table in the database. The sqlalchemy.ext.declarative.declarative_base() function allows us to create a model from the subclasses of sqlalchemy.ext.declarative.api.DeclarativeMeta:

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

Now that our base mapper has been declared, we can make a subclass of it to build our declarative maps or models. The Base subclasses correspond to tables within a database. These subclasses have the attribute __tablename__, which corresponds to the name of the table to which you are mapping their attributes. The generic syntax is as follows:

from sqlalchemy import Column, Integer, String

class MyTable(Base):
    __tablename__ = 'table'
    id = Column(Integer, primary_key=True)
    message = Column(String)

To create the database with the defined tables, the Base.metadata.create_all() method is used in the database that's managed by the instantiated object of sqlalchemy.engine.base.Engine:

• If there is no file in the database, it will be created
• In case there are already tables defined in the database, only those that are new will be created and the data they already contain will not be deleted