SQL Alchemy

SQL Alchemy is an Object Relational Mapper (ORM) for Python

The SQL Alchemy generator will generate Python code for mapping an Object Model to a SQL Database. The structure of the SQL Database structure is consistent with the one generated by SQL DDL

In principle, two styles of mappings are possible:

  1. Declarative, in which SQLA-style classes are generated, with combined mappings

  2. Imperative, in which separate mappings are created for a pre-existing object model, such as one created by the existing python or pydantic generators

Currently only the Declarative style is fully supported

Overview

Declarative Mode

See declarative mapping docs on SQL Alchemy site

Given input such as:

Person:
  is_a: NamedThing
  description: >-
    A person (alive, dead, undead, or fictional).
  class_uri: schema:Person
  mixins:
    - HasAliases
  slots:
    - primary_email
    - birth_date
    - age_in_years
    - gender
    - current_address
    - has_employment_history
    - has_familial_relationships
    - has_medical_history
Event:
  slots:
    - started_at_time
    - ended_at_time
    - duration
    - is_current
MedicalEvent:
  is_a: Event
  slots:
    - in_location
    - diagnosis
    - procedure

The generated Python will look like:

class Person(NamedThing):
    """
    A person (alive, dead, undead, or fictional).
    """
    __tablename__ = 'Person'

    primary_email = Column(Text())
    birth_date = Column(Text())
    age_in_years = Column(Integer())
    gender = Column(Enum('nonbinary man', 'nonbinary woman', 'transgender woman', 'transgender man', 'cisgender man', 'cisgender woman', name='GenderType'))
    id = Column(Text(), primary_key=True)
    name = Column(Text())
    description = Column(Text())
    image = Column(Text())
    Container_id = Column(Text(), ForeignKey('Container.id'))
    current_address_id = Column(Text(), ForeignKey('Address.id'))
    current_address = relationship("Address", uselist=False)
    has_employment_history = relationship( "EmploymentEvent", foreign_keys="[EmploymentEvent.Person_id]")
    has_familial_relationships = relationship( "FamilialRelationship", foreign_keys="[FamilialRelationship.Person_id]")
    has_medical_history = relationship( "MedicalEvent", foreign_keys="[MedicalEvent.Person_id]")
    aliases_rel = relationship( "Person_alias" )
    aliases = association_proxy("aliases_rel", "alias",
                                  creator=lambda x_: Person_alias(alias=x_))
    has_news_events = relationship( "NewsEvent", secondary="Person_has_news_event")

The first line of the class is a Declarative Table declaration, the maps the class to the table name. This should correspond to the table from the SQL DDL generator.

The use of the relationship construct is described in mapped properties section of the SQLA docs.

Note that class slots are “rolled down”.

Docs

Command Line

gen-sqla

Generate SQL DDL representation

gen-sqla [OPTIONS] YAMLFILE

Options

-V, --version

Show the version and exit.

--use-foreign-keys, --no-use-foreign-keys

Emit FK declarations

Default:

True

--pydantic, --no-pydantic

If True, generate Pydantic classes (imperative mode only)

Default:

False

--generate-classes, --no-generate-classes

If True, generate Python datamodel (imperative mode only)

Default:

False

--declarative, --no-declarative

Generate SQL Alchemy declarative vs imperative

Default:

True

-f, --format <format>

Output format

Default:

'sqla'

Options:

sqla

--metadata, --no-metadata

Include metadata in output

Default:

True

--useuris, --metauris

Use class and slot URIs over model uris

Default:

True

-im, --importmap <importmap>

Import mapping file

--log_level <log_level>

Logging level

Default:

'WARNING'

Options:

CRITICAL | ERROR | WARNING | INFO | DEBUG

-v, --verbose

Verbosity. Takes precedence over –log_level.

--mergeimports, --no-mergeimports

Merge imports into source file (default=mergeimports)

--stacktrace, --no-stacktrace

Print a stack trace when an error occurs

Default:

False

Arguments

YAMLFILE

Required argument

Code

class linkml.generators.sqlalchemygen.SQLAlchemyGenerator(schema: str | ~typing.TextIO | ~linkml_runtime.linkml_model.meta.SchemaDefinition | ~linkml.utils.generator.Generator | ~pathlib.Path, schemaview: ~linkml_runtime.utils.schemaview.SchemaView | None = None, format: str | None = None, metadata: bool = True, useuris: bool | None = None, log_level: int | None = 30, mergeimports: bool | None = True, source_file_date: str | None = None, source_file_size: int | None = None, logger: ~logging.Logger | None = None, verbose: bool | None = None, output: str | None = None, namespaces: ~linkml_runtime.utils.namespaces.Namespaces | None = None, directory_output: bool = False, base_dir: str = None, metamodel_name_map: dict[str, str] = None, importmap: str | ~collections.abc.Mapping[str, str] | None = None, emit_prefixes: set[str] = <factory>, metamodel: ~linkml.utils.schemaloader.SchemaLoader = None, stacktrace: bool = False, include: str | ~pathlib.Path | ~linkml_runtime.linkml_model.meta.SchemaDefinition | None = None, template: ~linkml.generators.sqlalchemygen.TemplateEnum | None = None, original_schema: ~linkml_runtime.linkml_model.meta.SchemaDefinition | str = None)[source]

Generates SQL Alchemy classes

See also: SQLTableGenerator

serialize(**kwargs) str[source]

Generate output in the required format

Parameters:

kwargs – Generator specific parameters

Returns:

Generated output