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 seperate 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

https://docs.sqlalchemy.org/en/14/orm/declarative_mapping.html

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-sqlddl

Generate SQL DDL representation

gen-sqlddl [OPTIONS] YAMLFILE

Options

--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

Include metadata in output

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

--mergeimports, --no-mergeimports

Merge imports into source file (default=mergeimports)

Arguments

YAMLFILE

Required argument

Code

class linkml.generators.sqlalchemygen.SQLAlchemyGenerator(schema: Union[str, TextIO, linkml_runtime.linkml_model.meta.SchemaDefinition], dialect='sqlite', **kwargs)[source]
serialize(**kwargs) str

Generate output in the required format

Parameters

kwargs – Generater specific parameters

Returns

Generated output