SQLAlchemy Generator

BESSER provides a code generator to produce the module-level constructors that will form the structure of a database managed with SQLAlchemy. This structure (known as Declarative Mapping) defines the database metadata (using a Python object model) that will represent the input B-UML model.

Now, let’s generate the code for SQLAlchemy of our Structural model example 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()

The sql_alchemy.py file with the Declarative Mapping of the database will be generated in the <<current_directory>>/output folder and it will look as follows.

from typing import List
from typing import Optional
from sqlalchemy import create_engine
from sqlalchemy import Column
from sqlalchemy import ForeignKey
from sqlalchemy import String
from sqlalchemy import Table
from sqlalchemy import Text
from sqlalchemy.orm import column_property
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column
from sqlalchemy.orm import relationship
from sqlalchemy import Boolean, String, Date, Time, DateTime, Float, Integer
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("author_id", ForeignKey("author.id"), primary_key=True),
    Column("book_id", ForeignKey("book.id"), primary_key=True),
)

# Tables definition
class Book(Base):
    
    __tablename__ = "book"
    id: Mapped[int] = mapped_column(primary_key=True)
    pages: Mapped[int] = mapped_column(Integer)
    release: Mapped[datetime] = mapped_column(DateTime)
    title: Mapped[str] = mapped_column(String(100))

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 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 book table
Book.writtenBy: Mapped[List["Author"]] = relationship("Author", secondary=book_author_assoc, back_populates="publishes")
Book.library_id: Mapped["Library"] = mapped_column(ForeignKey("library.id"), nullable=False)
Book.locatedIn: Mapped["Library"] = relationship("Library", back_populates="has")

#--- 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 library table
Library.has: Mapped[List["Book"]] = relationship("Book", back_populates="locatedIn")