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:

Declarative, in which SQLA-style classes are generated, with combined mappings
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: ~typing.Dict[str, str] = None, importmap: str | ~typing.Mapping[str, str] | None = None, emit_prefixes: ~typing.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, **_kwargs)[source]#

Generates SQL Alchemy classes