SQLAlchemy Generator

BESSER provides a code generator that creates SQLAlchemy models to define the structure of a relational database. This structure (known as Declarative Mapping) defines the database metadata (using a Python object model) that will represent the input structural B-UML model.

Now, let’s generate the code for SQLAlchemy of our Structural model example. For this, you should create the generator, provide the Structural model, and use the generate method as follows:

from besser.generators.sql_alchemy import SQLAlchemyGenerator

generator: SQLAlchemyGenerator = SQLAlchemyGenerator(model=library_model)
generator.generate(dbms="sqlite")

The dbms parameter specifies the target database management system. In this example, we use sqlite, but you can also specify postgresql, mysql, mssql, or mariadb.

Generated Output

The sql_alchemy.py file will be generated inside the <<current_directory>>/output folder and it will look as follows.

import enum
from typing import List, Optional
from sqlalchemy import (
    create_engine, Column, ForeignKey, Table, Text, Boolean, String, Date, 
    Time, DateTime, Float, Integer, Enum
)
from sqlalchemy.orm import (
    column_property, DeclarativeBase, Mapped, mapped_column, relationship
)
from datetime import datetime, time, date

class Base(DeclarativeBase):
    pass



# Tables definition for many-to-many relationships
book_author_assoc = Table(
    "book_author_assoc",
    Base.metadata,
    Column("writtenBy", ForeignKey("author.id"), primary_key=True),
    Column("publishes", ForeignKey("book.id"), primary_key=True),
)

# Tables definition
class Author(Base):
    __tablename__ = "author"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(100))
    email: Mapped[str] = mapped_column(String(100))

class Book(Base):
    __tablename__ = "book"
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(100))
    pages: Mapped[int] = mapped_column(Integer)
    release: Mapped[date] = mapped_column(Date)

class Library(Base):
    __tablename__ = "library"
    id: Mapped[int] = mapped_column(primary_key=True)
    address: Mapped[str] = mapped_column(String(100))
    name: Mapped[str] = mapped_column(String(100))


#--- Foreign keys and relationships of the author table
Author.publishes: Mapped[List["Book"]] = relationship("Book", secondary=book_author_assoc, back_populates="writtenBy")

#--- Foreign keys and relationships of the book table
Book.writtenBy: Mapped[List["Author"]] = relationship("Author", secondary=book_author_assoc, back_populates="publishes")
Book.locatedIn: Mapped["Library"] = mapped_column(ForeignKey("library.id"), nullable=False)

#--- Foreign keys and relationships of the library table
Library.has: Mapped[List["Book"]] = relationship("Book", back_populates="locatedIn")

# Database connection

DATABASE_URL = "sqlite:///LibraryModel.db"  # SQLite connection

engine = create_engine(DATABASE_URL, echo=True)

# Create tables in the database
Base.metadata.create_all(engine)

Creating the Database

The generated code contains the SQLAlchemy classes that represent the database metadata. If you are using SQLite as DBMS, you can create the database and the tables by running the file as follows:

python sql_alchemy.py

This will create a SQLite database named Librarymodel.db in the same directory where the file is executed. The database looks like this:

Note

If you want to use another DBMS such as postgresql or mysql, you should change the connection string in the generated code, providing the necessary information to connect to the database: username, password, host, port, and database name.