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, mariadb, or oracle.

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)
    locatedIn_id: Mapped[int] = mapped_column(ForeignKey("library.id"), nullable=False)

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


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

#--- Relationships of the book table
Book.writtenBy: Mapped[List["Author"]] = relationship("Author", secondary=book_author_assoc, back_populates="publishes")
Book.locatedIn: Mapped["Library"] = relationship("Library", back_populates="has", foreign_keys=[Book.locatedIn_id])

#--- Relationships of the library table
Library.has: Mapped[List["Book"]] = relationship("Book", back_populates="locatedIn", foreign_keys=[Book.locatedIn_id])

# Database connection
DATABASE_URL = "sqlite:///Library_model.db"  # SQLite connection
engine = create_engine(DATABASE_URL, echo=True)

# Create tables in the database
Base.metadata.create_all(engine, checkfirst=True)

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

Note that for DBMS different to SQLite, 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.

Warning

By default, inheritance is implemented using the Joined Table Inheritance. Other strategies like Concrete Table Inheritance is implemented only when you mark a parent class as abstract in the B-UML model, but there are limitations in Concrete inheritance, for example: the definition of relationships involving abstract classes are not supported. So only if the abstract class does not have relationships, it will be treated as a concrete parent class, otherwise, it will be treated as a parent class using Joined Table Inheritance.