notebooks/SQL-examples.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "c4fabd79",
   "metadata": {},
   "source": [
    "## LinkML and SQL Alchemy Tutorial\n",
    "\n",
    "This tutorial walks through the use of LinkML in combination with SQLite3 and SQL Alchemy.\n",
    "\n",
    "The steps are:\n",
    "\n",
    " - Create a SQL schema from a LinkML schema (the standard PersonInfo example)\n",
    " - Generate SQL Alchemy \"Models\" from the same LinkML schema\n",
    " - Create instances of these Models and insert into database\n",
    " - Query using direct SQL\n",
    " - Query using SQL Alchemy ORM"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c6d9e679",
   "metadata": {},
   "source": [
    "## PersonInfo schema\n",
    "\n",
    "We use the standard PersonInfo schema bundled in the LinkML repo:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "9f28bdd7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "id: https://w3id.org/linkml/examples/personinfo\r\n",
      "name: personinfo\r\n",
      "description: |-\r\n",
      "  Information about people, based on [schema.org](http://schema.org)\r\n",
      "license: https://creativecommons.org/publicdomain/zero/1.0/\r\n",
      "default_curi_maps:\r\n",
      "  - semweb_context\r\n",
      "imports:\r\n",
      "  - linkml:types\r\n",
      "prefixes:\r\n",
      "  personinfo: https://w3id.org/linkml/examples/personinfo/\r\n",
      "  linkml: https://w3id.org/linkml/\r\n",
      "  schema: http://schema.org/\r\n",
      "  rdfs: http://www.w3.org/2000/01/rdf-schema#\r\n",
      "  prov: http://www.w3.org/ns/prov#\r\n",
      "  GSSO: http://purl.obolibrary.org/obo/GSSO_\r\n",
      "  famrel: https://example.org/FamilialRelations#\r\n",
      "  # DATA PREFIXES\r\n",
      "  P: http://example.org/P/\r\n",
      "  ROR: http://example.org/ror/\r\n",
      "  CODE: http://example.org/code/\r\n",
      "  GEO: http://example.org/geoloc/\r\n",
      "default_prefix: personinfo\r\n",
      "default_range: string\r\n",
      "\r\n",
      "emit_prefixes:\r\n",
      "  - rdf\r\n",
      "  - rdfs\r\n",
      "  - xsd\r\n",
      "  - skos\r\n",
      "\r\n",
      "classes:\r\n",
      "\r\n",
      "  NamedThing:\r\n",
      "    description: >-\r\n",
      "      A generic grouping for any identifiable entity\r\n",
      "    slots:\r\n",
      "      - id\r\n",
      "      - name\r\n",
      "      - description\r\n",
      "      - image\r\n",
      "    close_mappings:\r\n",
      "     - schema:Thing\r\n",
      "\r\n",
      "  Person:\r\n",
      "    is_a: NamedThing\r\n",
      "    description: >-\r\n",
      "      A person (alive, dead, undead, or fictional).\r\n",
      "    class_uri: schema:Person\r\n",
      "    mixins:\r\n",
      "      - HasAliases\r\n",
      "    slots:\r\n",
      "      - primary_email\r\n",
      "      - birth_date\r\n",
      "      - age_in_years\r\n",
      "      - gender\r\n",
      "      - current_address\r\n",
      "      - has_employment_history\r\n",
      "      - has_familial_relationships\r\n",
      "      - has_medical_history\r\n",
      "    slot_usage:\r\n",
      "      primary_email:\r\n",
      "        pattern: \"^\\\\S+@[\\\\S+\\\\.]+\\\\S+\"\r\n",
      "    in_subset:\r\n",
      "      - basic_subset\r\n",
      "  \r\n",
      "  HasAliases:\r\n",
      "    description: >-\r\n",
      "      A mixin applied to any class that can have aliases/alternateNames\r\n",
      "    mixin: true\r\n",
      "    attributes:\r\n",
      "      aliases:\r\n",
      "        multivalued: true\r\n",
      "        exact_mappings:\r\n",
      "          - schema:alternateName\r\n",
      "\r\n",
      "\r\n",
      "  Organization:\r\n",
      "    description: >-\r\n",
      "      An organization such as a company or university\r\n",
      "    is_a: NamedThing\r\n",
      "    class_uri: schema:Organization\r\n",
      "    mixins:\r\n",
      "      - HasAliases\r\n",
      "    slots:\r\n",
      "      - mission_statement\r\n",
      "      - founding_date\r\n",
      "      - founding_location\r\n",
      "\r\n",
      "  Place:\r\n",
      "    mixins:\r\n",
      "      - HasAliases\r\n",
      "    slots:\r\n",
      "      - id\r\n",
      "      - name\r\n",
      "      \r\n",
      "  Address:\r\n",
      "    class_uri: schema:PostalAddress\r\n",
      "    slots:\r\n",
      "      - street\r\n",
      "      - city\r\n",
      "      - postal_code\r\n",
      "\r\n",
      "  Event:\r\n",
      "    slots:\r\n",
      "      - started_at_time\r\n",
      "      - ended_at_time\r\n",
      "      - duration\r\n",
      "      - is_current\r\n",
      "    close_mappings:\r\n",
      "      - schema:Event\r\n",
      "\r\n",
      "  Concept:\r\n",
      "    is_a: NamedThing\r\n",
      "\r\n",
      "  DiagnosisConcept:\r\n",
      "    is_a: Concept\r\n",
      "\r\n",
      "  ProcedureConcept:\r\n",
      "    is_a: Concept\r\n",
      "      \r\n",
      "\r\n",
      "  Relationship:\r\n",
      "    slots:\r\n",
      "      - started_at_time\r\n",
      "      - ended_at_time\r\n",
      "      - related_to\r\n",
      "      - type\r\n",
      "\r\n",
      "  FamilialRelationship:\r\n",
      "    is_a: Relationship\r\n",
      "    slot_usage:\r\n",
      "      type:\r\n",
      "        range: FamilialRelationshipType\r\n",
      "        required: true\r\n",
      "      related to:\r\n",
      "        range: Person\r\n",
      "        required: true\r\n",
      "\r\n",
      "  EmploymentEvent:\r\n",
      "    is_a: Event\r\n",
      "    slots:\r\n",
      "      - employed_at\r\n",
      "\r\n",
      "  MedicalEvent:\r\n",
      "    is_a: Event\r\n",
      "    slots:\r\n",
      "      - in_location\r\n",
      "      - diagnosis\r\n",
      "      - procedure\r\n",
      "\r\n",
      "  WithLocation:\r\n",
      "    mixin: true\r\n",
      "    slots:\r\n",
      "      - in_location\r\n",
      "\r\n",
      "  # TODO: annotate that this is a container/root class\r\n",
      "  Container:\r\n",
      "    tree_root: true\r\n",
      "    slots:\r\n",
      "      - persons\r\n",
      "      - organizations\r\n",
      "\r\n",
      "slots:\r\n",
      "  id:\r\n",
      "    identifier: true\r\n",
      "    slot_uri: schema:identifier\r\n",
      "  name:\r\n",
      "    slot_uri: schema:name\r\n",
      "  description:\r\n",
      "    slot_uri: schema:description\r\n",
      "  image:\r\n",
      "    slot_uri: schema:image\r\n",
      "  gender:\r\n",
      "    slot_uri: schema:gender\r\n",
      "    range: GenderType\r\n",
      "  primary_email:\r\n",
      "    slot_uri: schema:email\r\n",
      "  birth_date:\r\n",
      "    slot_uri: schema:birthDate\r\n",
      "  employed_at:\r\n",
      "    range: Organization\r\n",
      "  is_current:\r\n",
      "    range: boolean\r\n",
      "  has_employment_history:\r\n",
      "    range: EmploymentEvent\r\n",
      "    multivalued: true\r\n",
      "    inlined_as_list: true\r\n",
      "  has_medical_history:\r\n",
      "    range: MedicalEvent\r\n",
      "    multivalued: true\r\n",
      "    inlined_as_list: true\r\n",
      "  has_familial_relationships:\r\n",
      "    range: FamilialRelationship\r\n",
      "    multivalued: true\r\n",
      "    inlined_as_list: true\r\n",
      "  in_location:\r\n",
      "    range: Place\r\n",
      "  current_address:\r\n",
      "    description: >-\r\n",
      "      The address at which a person currently lives\r\n",
      "    range: Address\r\n",
      "  age_in_years:\r\n",
      "    range: integer\r\n",
      "    minimum_value: 0\r\n",
      "    maximum_value: 999\r\n",
      "  related_to:\r\n",
      "  type:\r\n",
      "  street:\r\n",
      "  city:\r\n",
      "  mission_statement:\r\n",
      "  founding_date:\r\n",
      "  founding_location:\r\n",
      "    range: Place\r\n",
      "  postal_code:\r\n",
      "    range: string\r\n",
      "  started_at_time:\r\n",
      "    slot_uri: prov:startedAtTime\r\n",
      "    range: date\r\n",
      "  duration:\r\n",
      "    range: float\r\n",
      "  diagnosis:\r\n",
      "    range: DiagnosisConcept\r\n",
      "    inlined: true\r\n",
      "  procedure:\r\n",
      "    range: ProcedureConcept\r\n",
      "    inlined: true\r\n",
      "\r\n",
      "  ended_at_time:\r\n",
      "    slot_uri: prov:endedAtTime\r\n",
      "    range: date\r\n",
      "\r\n",
      "  persons:\r\n",
      "    range: Person\r\n",
      "    inlined: true\r\n",
      "    inlined_as_list: true\r\n",
      "    multivalued: true\r\n",
      "  organizations:\r\n",
      "    range: Organization\r\n",
      "    inlined_as_list: true\r\n",
      "    inlined: true\r\n",
      "    multivalued: true\r\n",
      "    \r\n",
      "enums:\r\n",
      "  FamilialRelationshipType:\r\n",
      "    permissible_values:\r\n",
      "      SIBLING_OF:\r\n",
      "        meaning: famrel:01\r\n",
      "      PARENT_OF:\r\n",
      "        meaning: famrel:02\r\n",
      "      CHILD_OF:\r\n",
      "        meaning: famrel:01\r\n",
      "  GenderType:\r\n",
      "    permissible_values:\r\n",
      "      nonbinary man:\r\n",
      "        meaning: GSSO:009254\r\n",
      "      nonbinary woman:\r\n",
      "        meaning: GSSO:009253\r\n",
      "      transgender woman:\r\n",
      "        meaning: GSSO:000384\r\n",
      "      transgender man:\r\n",
      "        meaning: GSSO:000372\r\n",
      "      cisgender man:\r\n",
      "        meaning: GSSO:000371\r\n",
      "      cisgender woman:\r\n",
      "        meaning: GSSO:000385\r\n",
      "  DiagnosisType:\r\n",
      "\r\n",
      "subsets:\r\n",
      "  basic_subset:\r\n",
      "    description: A subset of the schema that handles basic information\r\n"
     ]
    }
   ],
   "source": [
    "!cat ../examples/PersonSchema/personinfo.yaml"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "047f2653",
   "metadata": {},
   "source": [
    "## Create a working directory\n",
    "\n",
    "For demonstration purposes we will put all generated artefacts in a folder `tmp`.\n",
    "\n",
    "Note that if you were doing this in a real project, you should use the linkml project cookiecutter\n",
    "to create a standard project structure"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "7865717f",
   "metadata": {},
   "outputs": [],
   "source": [
    "!mkdir -p tmp"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7504cc4d",
   "metadata": {},
   "source": [
    "## Generate SQL CREATE TABLE statements\n",
    "\n",
    "We use the [gen-sqlddl](https://linkml.io/linkml/generators/sqltable.html) command to create a SQL DDL (CREATE TABLE statements):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "id": "daea7df7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INFO:root:Loading schema https://w3id.org/linkml/types from ../examples/PersonSchema/personinfo.yaml\n",
      "INFO:root:Added primary key HasAliases.id\n",
      "INFO:root:Added primary key Address.id\n",
      "INFO:root:Added primary key Event.id\n",
      "INFO:root:Added primary key Relationship.id\n",
      "INFO:root:Added primary key FamilialRelationship.id\n",
      "INFO:root:Added primary key EmploymentEvent.id\n",
      "INFO:root:Added primary key MedicalEvent.id\n",
      "INFO:root:Added primary key WithLocation.id\n",
      "INFO:root:Added primary key Container.id\n"
     ]
    }
   ],
   "source": [
    "!gen-sqlddl ../examples/PersonSchema/personinfo.yaml > tmp/personinfo.ddl.sql"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 133,
   "id": "7c097cd6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CREATE TABLE \"Person\" (\r\n",
      "\tprimary_email TEXT, \r\n",
      "\tbirth_date TEXT, \r\n",
      "\tage_in_years INTEGER, \r\n",
      "\tgender VARCHAR(17), \r\n",
      "\tid TEXT, \r\n",
      "\tname TEXT, \r\n",
      "\tdescription TEXT, \r\n",
      "\timage TEXT, \r\n",
      "\t\"Container_id\" TEXT, \r\n",
      "\tcurrent_address_id TEXT, \r\n",
      "\tPRIMARY KEY (id), \r\n",
      "\tFOREIGN KEY(\"Container_id\") REFERENCES \"Container\" (id), \r\n",
      "\tFOREIGN KEY(current_address_id) REFERENCES \"Address\" (id)\r\n",
      ");\r\n",
      "CREATE TABLE \"Organization\" (\r\n",
      "\tmission_statement TEXT, \r\n",
      "\tfounding_date TEXT, \r\n",
      "\tfounding_location TEXT, \r\n",
      "\tid TEXT, \r\n",
      "\tname TEXT, \r\n"
     ]
    }
   ],
   "source": [
    "!grep -A20 'CREATE TABLE \"Person\"' tmp/personinfo.ddl.sql"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cf093e7f",
   "metadata": {},
   "source": [
    "### Create an empty database\n",
    "\n",
    "Ensuring we start with a fresh database, we initialize it with CREATE TABLE STATEMENTS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "d188dc2f",
   "metadata": {},
   "outputs": [],
   "source": [
    "!test -f tmp/tmp.db && rm tmp/tmp.db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "b0a0b49b",
   "metadata": {},
   "outputs": [],
   "source": [
    "!cat tmp/personinfo.ddl.sql | sqlite3 tmp/tmp.db "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 140,
   "id": "82e19572",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CREATE TABLE IF NOT EXISTS \"Person\" (\r\n",
      "\tprimary_email TEXT, \r\n",
      "\tbirth_date TEXT, \r\n",
      "\tage_in_years INTEGER, \r\n",
      "\tgender VARCHAR(17), \r\n",
      "\tid TEXT, \r\n",
      "\tname TEXT, \r\n",
      "\tdescription TEXT, \r\n",
      "\timage TEXT, \r\n",
      "\t\"Container_id\" TEXT, \r\n",
      "\tcurrent_address_id TEXT, \r\n",
      "\tPRIMARY KEY (id), \r\n",
      "\tFOREIGN KEY(\"Container_id\") REFERENCES \"Container\" (id), \r\n",
      "\tFOREIGN KEY(current_address_id) REFERENCES \"Address\" (id)\r\n",
      ");\r\n"
     ]
    }
   ],
   "source": [
    "!sqlite3 tmp/tmp.db \".schema Person\" \".exit\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7fe9e028",
   "metadata": {},
   "source": [
    "## Generate SQLA Models\n",
    "\n",
    "We use the [gen-sqla](https://linkml.io/linkml/generators/sqlalchemy.html) command to create a SQLA Object Model"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "ac831847",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INFO:root:Loading schema https://w3id.org/linkml/types from ../examples/PersonSchema/personinfo.yaml\n",
      "INFO:root:Added primary key HasAliases.id\n",
      "INFO:root:Added primary key Address.id\n",
      "INFO:root:Added primary key Event.id\n",
      "INFO:root:Added primary key Relationship.id\n",
      "INFO:root:Added primary key FamilialRelationship.id\n",
      "INFO:root:Added primary key EmploymentEvent.id\n",
      "INFO:root:Added primary key MedicalEvent.id\n",
      "INFO:root:Added primary key WithLocation.id\n",
      "INFO:root:Added primary key Container.id\n",
      "INFO:root:Package for dataclasses ==  personinfo\n"
     ]
    }
   ],
   "source": [
    "!gen-sqla ../examples/PersonSchema/personinfo.yaml > tmp/personinfo_sqla.py"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "id": "0d6203da",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\r\n",
      "from sqlalchemy import Column, Index, Table, ForeignKey\r\n",
      "from sqlalchemy.orm import relationship\r\n",
      "from sqlalchemy.sql.sqltypes import *\r\n",
      "from sqlalchemy.orm import declarative_base\r\n",
      "from sqlalchemy.ext.associationproxy import association_proxy\r\n",
      "\r\n",
      "Base = declarative_base()\r\n",
      "metadata = Base.metadata\r\n",
      "\r\n",
      "\r\n",
      "class NamedThing(Base):\r\n",
      "    \"\"\"\r\n",
      "    A generic grouping for any identifiable entity\r\n",
      "    \"\"\"\r\n",
      "    __tablename__ = 'NamedThing'\r\n",
      "    \r\n",
      "    id = Column(Text(), primary_key=True)\r\n",
      "    name = Column(Text())\r\n",
      "    description = Column(Text())\r\n",
      "    image = Column(Text())\r\n",
      "    \r\n",
      "    \r\n",
      "    def __repr__(self):\r\n",
      "        return f\"NamedThing(id={self.id},name={self.name},description={self.description},image={self.image},)\"\r\n",
      "        \r\n",
      "    \r\n",
      "        \r\n",
      "    \r\n",
      "\r\n",
      "\r\n",
      "class HasAliases(Base):\r\n",
      "    \"\"\"\r\n",
      "    A mixin applied to any class that can have aliases/alternateNames\r\n",
      "    \"\"\"\r\n",
      "    __tablename__ = 'HasAliases'\r\n",
      "    \r\n",
      "    id = Column(Integer(), primary_key=True, autoincrement=True )\r\n",
      "    \r\n",
      "    \r\n",
      "    aliases_rel = relationship( \"HasAliasesAliases\" )\r\n",
      "    aliases = association_proxy(\"aliases_rel\", \"aliases\",\r\n",
      "                                  creator=lambda x_: HasAliasesAliases(aliases=x_))\r\n",
      "    \r\n",
      "    \r\n",
      "    def __repr__(self):\r\n",
      "        return f\"HasAliases(id={self.id},)\"\r\n",
      "        \r\n",
      "    \r\n",
      "        \r\n",
      "    \r\n",
      "\r\n",
      "\r\n",
      "class Place(Base):\r\n",
      "    \"\"\"\r\n",
      "    \r\n",
      "    \"\"\"\r\n",
      "    __tablename__ = 'Place'\r\n",
      "    \r\n",
      "    id = Column(Text(), primary_key=True)\r\n",
      "    name = Column(Text())\r\n",
      "    \r\n",
      "    \r\n",
      "    aliases_rel = relationship( \"PlaceAliases\" )\r\n",
      "    aliases = association_proxy(\"aliases_rel\", \"aliases\",\r\n",
      "                                  creator=lambda x_: PlaceAliases(aliases=x_))\r\n",
      "    \r\n",
      "    \r\n",
      "    def __repr__(self):\r\n",
      "        return f\"Place(id={self.id},name={self.name},)\"\r\n",
      "        \r\n",
      "    \r\n",
      "        \r\n",
      "    \r\n",
      "\r\n",
      "\r\n",
      "class Address(Base):\r\n",
      "    \"\"\"\r\n",
      "    \r\n",
      "    \"\"\"\r\n",
      "    __tablename__ = 'Address'\r\n",
      "    \r\n",
      "    id = Column(Integer(), primary_key=True, autoincrement=True )\r\n",
      "    street = Column(Text())\r\n",
      "    city = Column(Text())\r\n",
      "    postal_code = Column(Text())\r\n",
      "    \r\n",
      "    \r\n",
      "    def __repr__(self):\r\n",
      "        return f\"Address(id={self.id},street={self.street},city={self.city},postal_code={self.postal_code},)\"\r\n",
      "        \r\n",
      "    \r\n",
      "        \r\n",
      "    \r\n",
      "\r\n",
      "\r\n",
      "class Event(Base):\r\n",
      "    \"\"\"\r\n",
      "    \r\n",
      "    \"\"\"\r\n",
      "    __tablename__ = 'Event'\r\n",
      "    \r\n",
      "    id = Column(Integer(), primary_key=True, autoincrement=True )\r\n",
      "    started_at_time = Column(Date())\r\n",
      "    ended_at_time = Column(Date())\r\n",
      "    duration = Column(Float())\r\n",
      "    is_current = Column(Boolean())\r\n",
      "    \r\n",
      "    \r\n",
      "    def __repr__(self):\r\n",
      "        return f\"Event(id={self.id},started_at_time={self.started_at_time},ended_at_time={self.ended_at_time},duration={self.duration},is_current={self.is_current},)\"\r\n",
      "        \r\n",
      "    \r\n",
      "        \r\n",
      "    \r\n",
      "\r\n",
      "\r\n",
      "class Relationship(Base):\r\n",
      "    \"\"\"\r\n",
      "    \r\n",
      "    \"\"\"\r\n",
      "    __tablename__ = 'Relationship'\r\n",
      "    \r\n",
      "    id = Column(Integer(), primary_key=True, autoincrement=True )\r\n",
      "    started_at_time = Column(Date())\r\n",
      "    ended_at_time = Column(Date())\r\n",
      "    related_to = Column(Text())\r\n",
      "    type = Column(Text())\r\n",
      "    \r\n",
      "    \r\n",
      "    def __repr__(self):\r\n",
      "        return f\"Relationship(id={self.id},started_at_time={self.started_at_time},ended_at_time={self.ended_at_time},related_to={self.related_to},type={self.type},)\"\r\n",
      "        \r\n",
      "    \r\n",
      "        \r\n",
      "    \r\n",
      "\r\n",
      "\r\n",
      "class WithLocation(Base):\r\n",
      "    \"\"\"\r\n",
      "    \r\n",
      "    \"\"\"\r\n",
      "    __tablename__ = 'WithLocation'\r\n",
      "    \r\n",
      "    id = Column(Integer(), primary_key=True, autoincrement=True )\r\n",
      "    in_location = Column(Text(), ForeignKey('Place.id'))\r\n",
      "    \r\n",
      "    \r\n",
      "    def __repr__(self):\r\n",
      "        return f\"WithLocation(id={self.id},in_location={self.in_location},)\"\r\n",
      "        \r\n",
      "    \r\n",
      "        \r\n",
      "    \r\n",
      "\r\n",
      "\r\n",
      "class Container(Base):\r\n",
      "    \"\"\"\r\n",
      "    \r\n",
      "    \"\"\"\r\n",
      "    __tablename__ = 'Container'\r\n",
      "    \r\n",
      "    id = Column(Integer(), primary_key=True, autoincrement=True )\r\n",
      "    \r\n",
      "    \r\n",
      "    # One-To-Many: OneToAnyMapping(source_class='Container', source_slot='persons', mapping_type=None, target_class='Person', target_slot='Container_id', join_class=None, uses_join_table=None, multivalued=False)\r\n",
      "    persons = relationship( \"Person\", foreign_keys=\"[Person.Container_id]\")\r\n",
      "    \r\n",
      "    \r\n",
      "    # One-To-Many: OneToAnyMapping(source_class='Container', source_slot='organizations', mapping_type=None, target_class='Organization', target_slot='Container_id', join_class=None, uses_join_table=None, multivalued=False)\r\n",
      "    organizations = relationship( \"Organization\", foreign_keys=\"[Organization.Container_id]\")\r\n",
      "    \r\n",
      "    \r\n",
      "    def __repr__(self):\r\n",
      "        return f\"Container(id={self.id},)\"\r\n",
      "        \r\n",
      "    \r\n",
      "        \r\n",
      "    \r\n",
      "\r\n",
      "\r\n",
      "class PersonAliases(Base):\r\n",
      "    \"\"\"\r\n",
      "    \r\n",
      "    \"\"\"\r\n",
      "    __tablename__ = 'Person_aliases'\r\n",
      "    \r\n",
      "    Person_id = Column(Text(), ForeignKey('Person.id'), primary_key=True)\r\n",
      "    aliases = Column(Text(), primary_key=True)\r\n",
      "    \r\n",
      "    \r\n",
      "    def __repr__(self):\r\n",
      "        return f\"Person_aliases(Person_id={self.Person_id},aliases={self.aliases},)\"\r\n",
      "        \r\n",
      "    \r\n",
      "        \r\n",
      "    \r\n",
      "\r\n",
      "\r\n",
      "class HasAliasesAliases(Base):\r\n",
      "    \"\"\"\r\n",
      "    \r\n",
      "    \"\"\"\r\n",
      "    __tablename__ = 'HasAliases_aliases'\r\n",
      "    \r\n",
      "    HasAliases_id = Column(Text(), ForeignKey('HasAliases.id'), primary_key=True)\r\n",
      "    aliases = Column(Text(), primary_key=True)\r\n",
      "    \r\n",
      "    \r\n",
      "    def __repr__(self):\r\n",
      "        return f\"HasAliases_aliases(HasAliases_id={self.HasAliases_id},aliases={self.aliases},)\"\r\n",
      "        \r\n",
      "    \r\n",
      "        \r\n",
      "    \r\n",
      "\r\n",
      "\r\n",
      "class OrganizationAliases(Base):\r\n",
      "    \"\"\"\r\n",
      "    \r\n",
      "    \"\"\"\r\n",
      "    __tablename__ = 'Organization_aliases'\r\n",
      "    \r\n",
      "    Organization_id = Column(Text(), ForeignKey('Organization.id'), primary_key=True)\r\n",
      "    aliases = Column(Text(), primary_key=True)\r\n",
      "    \r\n",
      "    \r\n",
      "    def __repr__(self):\r\n",
      "        return f\"Organization_aliases(Organization_id={self.Organization_id},aliases={self.aliases},)\"\r\n",
      "        \r\n",
      "    \r\n",
      "        \r\n",
      "    \r\n",
      "\r\n",
      "\r\n",
      "class PlaceAliases(Base):\r\n",
      "    \"\"\"\r\n",
      "    \r\n",
      "    \"\"\"\r\n",
      "    __tablename__ = 'Place_aliases'\r\n",
      "    \r\n",
      "    Place_id = Column(Text(), ForeignKey('Place.id'), primary_key=True)\r\n",
      "    aliases = Column(Text(), primary_key=True)\r\n",
      "    \r\n",
      "    \r\n",
      "    def __repr__(self):\r\n",
      "        return f\"Place_aliases(Place_id={self.Place_id},aliases={self.aliases},)\"\r\n",
      "        \r\n",
      "    \r\n",
      "        \r\n",
      "    \r\n",
      "\r\n",
      "\r\n",
      "class Person(NamedThing):\r\n",
      "    \"\"\"\r\n",
      "    A person (alive, dead, undead, or fictional).\r\n",
      "    \"\"\"\r\n",
      "    __tablename__ = 'Person'\r\n",
      "    \r\n",
      "    primary_email = Column(Text())\r\n",
      "    birth_date = Column(Text())\r\n",
      "    age_in_years = Column(Integer())\r\n",
      "    gender = Column(Enum('nonbinary man', 'nonbinary woman', 'transgender woman', 'transgender man', 'cisgender man', 'cisgender woman', name='GenderType'))\r\n",
      "    id = Column(Text(), primary_key=True)\r\n",
      "    name = Column(Text())\r\n",
      "    description = Column(Text())\r\n",
      "    image = Column(Text())\r\n",
      "    Container_id = Column(Text(), ForeignKey('Container.id'))\r\n",
      "    current_address_id = Column(Text(), ForeignKey('Address.id'))\r\n",
      "    current_address = relationship(\"Address\", uselist=False)\r\n",
      "    \r\n",
      "    \r\n",
      "    # One-To-Many: OneToAnyMapping(source_class='Person', source_slot='has_employment_history', mapping_type=None, target_class='EmploymentEvent', target_slot='Person_id', join_class=None, uses_join_table=None, multivalued=False)\r\n",
      "    has_employment_history = relationship( \"EmploymentEvent\", foreign_keys=\"[EmploymentEvent.Person_id]\")\r\n",
      "    \r\n",
      "    \r\n",
      "    # One-To-Many: OneToAnyMapping(source_class='Person', source_slot='has_familial_relationships', mapping_type=None, target_class='FamilialRelationship', target_slot='Person_id', join_class=None, uses_join_table=None, multivalued=False)\r\n",
      "    has_familial_relationships = relationship( \"FamilialRelationship\", foreign_keys=\"[FamilialRelationship.Person_id]\")\r\n",
      "    \r\n",
      "    \r\n",
      "    # One-To-Many: OneToAnyMapping(source_class='Person', source_slot='has_medical_history', mapping_type=None, target_class='MedicalEvent', target_slot='Person_id', join_class=None, uses_join_table=None, multivalued=False)\r\n",
      "    has_medical_history = relationship( \"MedicalEvent\", foreign_keys=\"[MedicalEvent.Person_id]\")\r\n",
      "    \r\n",
      "    \r\n",
      "    aliases_rel = relationship( \"PersonAliases\" )\r\n",
      "    aliases = association_proxy(\"aliases_rel\", \"aliases\",\r\n",
      "                                  creator=lambda x_: PersonAliases(aliases=x_))\r\n",
      "    \r\n",
      "    \r\n",
      "    def __repr__(self):\r\n",
      "        return f\"Person(primary_email={self.primary_email},birth_date={self.birth_date},age_in_years={self.age_in_years},gender={self.gender},id={self.id},name={self.name},description={self.description},image={self.image},Container_id={self.Container_id},current_address_id={self.current_address_id},)\"\r\n",
      "        \r\n",
      "    \r\n",
      "        \r\n",
      "    \r\n",
      "    # Using concrete inheritance: see https://docs.sqlalchemy.org/en/14/orm/inheritance.html\r\n",
      "    __mapper_args__ = {\r\n",
      "        'concrete': True\r\n",
      "    }\r\n",
      "    \r\n",
      "\r\n",
      "\r\n",
      "class Organization(NamedThing):\r\n",
      "    \"\"\"\r\n",
      "    An organization such as a company or university\r\n",
      "    \"\"\"\r\n",
      "    __tablename__ = 'Organization'\r\n",
      "    \r\n",
      "    mission_statement = Column(Text())\r\n",
      "    founding_date = Column(Text())\r\n",
      "    founding_location = Column(Text(), ForeignKey('Place.id'))\r\n",
      "    id = Column(Text(), primary_key=True)\r\n",
      "    name = Column(Text())\r\n",
      "    description = Column(Text())\r\n",
      "    image = Column(Text())\r\n",
      "    Container_id = Column(Text(), ForeignKey('Container.id'))\r\n",
      "    \r\n",
      "    \r\n",
      "    aliases_rel = relationship( \"OrganizationAliases\" )\r\n",
      "    aliases = association_proxy(\"aliases_rel\", \"aliases\",\r\n",
      "                                  creator=lambda x_: OrganizationAliases(aliases=x_))\r\n",
      "    \r\n",
      "    \r\n",
      "    def __repr__(self):\r\n",
      "        return f\"Organization(mission_statement={self.mission_statement},founding_date={self.founding_date},founding_location={self.founding_location},id={self.id},name={self.name},description={self.description},image={self.image},Container_id={self.Container_id},)\"\r\n",
      "        \r\n",
      "    \r\n",
      "        \r\n",
      "    \r\n",
      "    # Using concrete inheritance: see https://docs.sqlalchemy.org/en/14/orm/inheritance.html\r\n",
      "    __mapper_args__ = {\r\n",
      "        'concrete': True\r\n",
      "    }\r\n",
      "    \r\n",
      "\r\n",
      "\r\n",
      "class Concept(NamedThing):\r\n",
      "    \"\"\"\r\n",
      "    \r\n",
      "    \"\"\"\r\n",
      "    __tablename__ = 'Concept'\r\n",
      "    \r\n",
      "    id = Column(Text(), primary_key=True)\r\n",
      "    name = Column(Text())\r\n",
      "    description = Column(Text())\r\n",
      "    image = Column(Text())\r\n",
      "    \r\n",
      "    \r\n",
      "    def __repr__(self):\r\n",
      "        return f\"Concept(id={self.id},name={self.name},description={self.description},image={self.image},)\"\r\n",
      "        \r\n",
      "    \r\n",
      "        \r\n",
      "    \r\n",
      "    # Using concrete inheritance: see https://docs.sqlalchemy.org/en/14/orm/inheritance.html\r\n",
      "    __mapper_args__ = {\r\n",
      "        'concrete': True\r\n",
      "    }\r\n",
      "    \r\n",
      "\r\n",
      "\r\n",
      "class FamilialRelationship(Relationship):\r\n",
      "    \"\"\"\r\n",
      "    \r\n",
      "    \"\"\"\r\n",
      "    __tablename__ = 'FamilialRelationship'\r\n",
      "    \r\n",
      "    id = Column(Integer(), primary_key=True, autoincrement=True )\r\n",
      "    started_at_time = Column(Date())\r\n",
      "    ended_at_time = Column(Date())\r\n",
      "    related_to = Column(Text())\r\n",
      "    type = Column(Enum('SIBLING_OF', 'PARENT_OF', 'CHILD_OF', name='FamilialRelationshipType'))\r\n",
      "    Person_id = Column(Text(), ForeignKey('Person.id'))\r\n",
      "    \r\n",
      "    \r\n",
      "    def __repr__(self):\r\n",
      "        return f\"FamilialRelationship(id={self.id},started_at_time={self.started_at_time},ended_at_time={self.ended_at_time},related_to={self.related_to},type={self.type},Person_id={self.Person_id},)\"\r\n",
      "        \r\n",
      "    \r\n",
      "        \r\n",
      "    \r\n",
      "    # Using concrete inheritance: see https://docs.sqlalchemy.org/en/14/orm/inheritance.html\r\n",
      "    __mapper_args__ = {\r\n",
      "        'concrete': True\r\n",
      "    }\r\n",
      "    \r\n",
      "\r\n",
      "\r\n",
      "class EmploymentEvent(Event):\r\n",
      "    \"\"\"\r\n",
      "    \r\n",
      "    \"\"\"\r\n",
      "    __tablename__ = 'EmploymentEvent'\r\n",
      "    \r\n",
      "    id = Column(Integer(), primary_key=True, autoincrement=True )\r\n",
      "    employed_at = Column(Text(), ForeignKey('Organization.id'))\r\n",
      "    started_at_time = Column(Date())\r\n",
      "    ended_at_time = Column(Date())\r\n",
      "    duration = Column(Float())\r\n",
      "    is_current = Column(Boolean())\r\n",
      "    Person_id = Column(Text(), ForeignKey('Person.id'))\r\n",
      "    \r\n",
      "    \r\n",
      "    def __repr__(self):\r\n",
      "        return f\"EmploymentEvent(id={self.id},employed_at={self.employed_at},started_at_time={self.started_at_time},ended_at_time={self.ended_at_time},duration={self.duration},is_current={self.is_current},Person_id={self.Person_id},)\"\r\n",
      "        \r\n",
      "    \r\n",
      "        \r\n",
      "    \r\n",
      "    # Using concrete inheritance: see https://docs.sqlalchemy.org/en/14/orm/inheritance.html\r\n",
      "    __mapper_args__ = {\r\n",
      "        'concrete': True\r\n",
      "    }\r\n",
      "    \r\n",
      "\r\n",
      "\r\n",
      "class MedicalEvent(Event):\r\n",
      "    \"\"\"\r\n",
      "    \r\n",
      "    \"\"\"\r\n",
      "    __tablename__ = 'MedicalEvent'\r\n",
      "    \r\n",
      "    id = Column(Integer(), primary_key=True, autoincrement=True )\r\n",
      "    in_location = Column(Text(), ForeignKey('Place.id'))\r\n",
      "    started_at_time = Column(Date())\r\n",
      "    ended_at_time = Column(Date())\r\n",
      "    duration = Column(Float())\r\n",
      "    is_current = Column(Boolean())\r\n",
      "    Person_id = Column(Text(), ForeignKey('Person.id'))\r\n",
      "    diagnosis_id = Column(Text(), ForeignKey('DiagnosisConcept.id'))\r\n",
      "    diagnosis = relationship(\"DiagnosisConcept\", uselist=False)\r\n",
      "    procedure_id = Column(Text(), ForeignKey('ProcedureConcept.id'))\r\n",
      "    procedure = relationship(\"ProcedureConcept\", uselist=False)\r\n",
      "    \r\n",
      "    \r\n",
      "    def __repr__(self):\r\n",
      "        return f\"MedicalEvent(id={self.id},in_location={self.in_location},started_at_time={self.started_at_time},ended_at_time={self.ended_at_time},duration={self.duration},is_current={self.is_current},Person_id={self.Person_id},diagnosis_id={self.diagnosis_id},procedure_id={self.procedure_id},)\"\r\n",
      "        \r\n",
      "    \r\n",
      "        \r\n",
      "    \r\n",
      "    # Using concrete inheritance: see https://docs.sqlalchemy.org/en/14/orm/inheritance.html\r\n",
      "    __mapper_args__ = {\r\n",
      "        'concrete': True\r\n",
      "    }\r\n",
      "    \r\n",
      "\r\n",
      "\r\n",
      "class DiagnosisConcept(Concept):\r\n",
      "    \"\"\"\r\n",
      "    \r\n",
      "    \"\"\"\r\n",
      "    __tablename__ = 'DiagnosisConcept'\r\n",
      "    \r\n",
      "    id = Column(Text(), primary_key=True)\r\n",
      "    name = Column(Text())\r\n",
      "    description = Column(Text())\r\n",
      "    image = Column(Text())\r\n",
      "    \r\n",
      "    \r\n",
      "    def __repr__(self):\r\n",
      "        return f\"DiagnosisConcept(id={self.id},name={self.name},description={self.description},image={self.image},)\"\r\n",
      "        \r\n",
      "    \r\n",
      "        \r\n",
      "    \r\n",
      "    # Using concrete inheritance: see https://docs.sqlalchemy.org/en/14/orm/inheritance.html\r\n",
      "    __mapper_args__ = {\r\n",
      "        'concrete': True\r\n",
      "    }\r\n",
      "    \r\n",
      "\r\n",
      "\r\n",
      "class ProcedureConcept(Concept):\r\n",
      "    \"\"\"\r\n",
      "    \r\n",
      "    \"\"\"\r\n",
      "    __tablename__ = 'ProcedureConcept'\r\n",
      "    \r\n",
      "    id = Column(Text(), primary_key=True)\r\n",
      "    name = Column(Text())\r\n",
      "    description = Column(Text())\r\n",
      "    image = Column(Text())\r\n",
      "    \r\n",
      "    \r\n",
      "    def __repr__(self):\r\n",
      "        return f\"ProcedureConcept(id={self.id},name={self.name},description={self.description},image={self.image},)\"\r\n",
      "        \r\n",
      "    \r\n",
      "        \r\n",
      "    \r\n",
      "    # Using concrete inheritance: see https://docs.sqlalchemy.org/en/14/orm/inheritance.html\r\n",
      "    __mapper_args__ = {\r\n",
      "        'concrete': True\r\n",
      "    }\r\n",
      "    \r\n",
      "\r\n",
      "\r\n"
     ]
    }
   ],
   "source": [
    "!cat tmp/personinfo_sqla.py"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "623ed98f",
   "metadata": {},
   "source": [
    "## Populate the SQLA Model Objects\n",
    "\n",
    "We will import our object model (remember in production code we would not put this in `tmp`).\n",
    "\n",
    "We will also use the Jupyter builtin help to introspect a class - although the builtin SQLA methods make this\n",
    "a bit verbose and messy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "f1d1b0c2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Help on class Person in module tmp.personinfo_sqla:\n",
      "\n",
      "class Person(NamedThing)\n",
      " |  Person(**kwargs)\n",
      " |  \n",
      " |  A person (alive, dead, undead, or fictional).\n",
      " |  \n",
      " |  Method resolution order:\n",
      " |      Person\n",
      " |      NamedThing\n",
      " |      sqlalchemy.orm.decl_api.Base\n",
      " |      builtins.object\n",
      " |  \n",
      " |  Methods defined here:\n",
      " |  \n",
      " |  __init__(self, **kwargs)\n",
      " |      A simple constructor that allows initialization from kwargs.\n",
      " |      \n",
      " |      Sets attributes on the constructed instance using the names and\n",
      " |      values in ``kwargs``.\n",
      " |      \n",
      " |      Only keys that are present as\n",
      " |      attributes of the instance's class are allowed. These could be,\n",
      " |      for example, any mapped columns or relationships.\n",
      " |  \n",
      " |  __repr__(self)\n",
      " |      Return repr(self).\n",
      " |  \n",
      " |  ----------------------------------------------------------------------\n",
      " |  Data descriptors defined here:\n",
      " |  \n",
      " |  Container_id\n",
      " |  \n",
      " |  age_in_years\n",
      " |  \n",
      " |  aliases_rel\n",
      " |  \n",
      " |  birth_date\n",
      " |  \n",
      " |  current_address\n",
      " |  \n",
      " |  current_address_id\n",
      " |  \n",
      " |  description\n",
      " |  \n",
      " |  gender\n",
      " |  \n",
      " |  has_employment_history\n",
      " |  \n",
      " |  has_familial_relationships\n",
      " |  \n",
      " |  has_medical_history\n",
      " |  \n",
      " |  id\n",
      " |  \n",
      " |  image\n",
      " |  \n",
      " |  name\n",
      " |  \n",
      " |  primary_email\n",
      " |  \n",
      " |  ----------------------------------------------------------------------\n",
      " |  Data and other attributes defined here:\n",
      " |  \n",
      " |  __mapper__ = <Mapper at 0x7fb5d0ff5c40; Person>\n",
      " |  \n",
      " |  __mapper_args__ = {'concrete': True}\n",
      " |  \n",
      " |  __table__ = Table('Person', MetaData(), Column('primary_emai...nKey('A...\n",
      " |  \n",
      " |  __tablename__ = 'Person'\n",
      " |  \n",
      " |  aliases = ColumnAssociationProxyInstance(AssociationProxy('aliases_rel...\n",
      " |  \n",
      " |  ----------------------------------------------------------------------\n",
      " |  Data descriptors inherited from sqlalchemy.orm.decl_api.Base:\n",
      " |  \n",
      " |  __dict__\n",
      " |      dictionary for instance variables (if defined)\n",
      " |  \n",
      " |  __weakref__\n",
      " |      list of weak references to the object (if defined)\n",
      " |  \n",
      " |  ----------------------------------------------------------------------\n",
      " |  Data and other attributes inherited from sqlalchemy.orm.decl_api.Base:\n",
      " |  \n",
      " |  __abstract__ = True\n",
      " |  \n",
      " |  metadata = MetaData()\n",
      " |  \n",
      " |  registry = <sqlalchemy.orm.decl_api.registry object>\n",
      "\n"
     ]
    }
   ],
   "source": [
    "from tmp.personinfo_sqla import Person\n",
    "help(Person)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "daf4a1ce",
   "metadata": {},
   "source": [
    "### Create a SQLA session\n",
    "\n",
    "This is a bit involved, but this is all standard for SQLA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "91e4bcbd",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy.orm import sessionmaker"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "fd1411e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = create_engine(\"sqlite:///tmp/tmp.db\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "ff922693",
   "metadata": {},
   "outputs": [],
   "source": [
    "session_class = sessionmaker(bind=engine)\n",
    "session = session_class()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "468c8796",
   "metadata": {},
   "source": [
    "### Create an object and add it\n",
    "\n",
    "We will populate the database with a single person, then do a sanity check SQL query"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "id": "06421064",
   "metadata": {},
   "outputs": [],
   "source": [
    "p = Person(id=\"P1\", name=\"Rosario\", age_in_years=33)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "id": "134c0227",
   "metadata": {},
   "outputs": [],
   "source": [
    "session.add(p)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "id": "3e893b32",
   "metadata": {},
   "outputs": [],
   "source": [
    "session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "id": "2a0396ca",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "||33||P1|Rosario||||\r\n"
     ]
    }
   ],
   "source": [
    "!sqlite3 tmp/tmp.db \"SELECT * FROM Person;\" \".exit\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "24230694",
   "metadata": {},
   "source": [
    "## Foreign Keys\n",
    "\n",
    "Now we demonstrate use of foreign keys behind the scenes.\n",
    "\n",
    "In the LinkML model, FamilialRelationship is *inlined*, so each Person can have zero to many embedded relationships\n",
    "to other people.\n",
    "\n",
    "We will append a relationship to another person (P2 - note this person is not yet in the database,\n",
    "but it's OK as SQLite3 doesn't enforce referential integrity)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "id": "c196ccff",
   "metadata": {},
   "outputs": [],
   "source": [
    "from tmp.personinfo_sqla import FamilialRelationship"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "id": "f68a5266",
   "metadata": {},
   "outputs": [],
   "source": [
    "p.has_familial_relationships.append(FamilialRelationship(related_to=\"P2\", type=\"SIBLING_OF\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "id": "f49c07cd",
   "metadata": {},
   "outputs": [],
   "source": [
    "session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "e0b77c80",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1|||P2|SIBLING_OF|P1\r\n"
     ]
    }
   ],
   "source": [
    "!sqlite3 tmp/tmp.db \"SELECT * FROM FamilialRelationship;\" \".exit\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "04fc3252",
   "metadata": {},
   "source": [
    "### Generating synthetic data\n",
    "\n",
    "Next we will write some simple code for making synthetic people"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "id": "626014d7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: names in /Users/cjm/Library/Caches/pypoetry/virtualenvs/linkml-lavaHNw6-py3.9/lib/python3.9/site-packages (0.3.0)\n",
      "Note: you may need to restart the kernel to use updated packages.\n"
     ]
    }
   ],
   "source": [
    "%pip install names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "id": "9a825912",
   "metadata": {},
   "outputs": [],
   "source": [
    "import names\n",
    "import random\n",
    "\n",
    "last_id = 2\n",
    "def random_person() -> Person:\n",
    "    \"\"\"\n",
    "    Create a person with a random name and random set of relationships.\n",
    "    \n",
    "    Note that this is just for demo purposes. If we wanted to be more advanced we\n",
    "    could make use of SIBLING_OF to make a more realistic distribution of last name\n",
    "    matches, and we could ensure that SIBLING_OF is symmetric...\n",
    "    \"\"\"\n",
    "    global last_id\n",
    "    last_id += 1\n",
    "    pid = f\"P{last_id}\"\n",
    "    person = Person(id=pid, name=names.get_full_name(), age_in_years=random.randint(30,40))\n",
    "    for n in range(0, random.randint(0, 3)):\n",
    "        rel = FamilialRelationship(related_to=f\"P{last_id+n}\", type=\"SIBLING_OF\")\n",
    "        person.has_familial_relationships.append(rel)\n",
    "    return person"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "id": "868099f1",
   "metadata": {},
   "outputs": [],
   "source": [
    "persons = [random_person() for x in range(1,100)]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "56dae0e1",
   "metadata": {},
   "source": [
    "### Container Object\n",
    "\n",
    "The PersonInfo schema has a class Container whose sole function is to contain all top level objects.\n",
    "\n",
    "We will populate our container with our 100 artificial people."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "id": "d232360a",
   "metadata": {},
   "outputs": [],
   "source": [
    "from tmp.personinfo_sqla import Container"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "id": "af1d61d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "container = Container(persons=persons)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "id": "f45f7985",
   "metadata": {},
   "outputs": [],
   "source": [
    "session.add(container)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 148,
   "id": "cd3cc260",
   "metadata": {},
   "outputs": [],
   "source": [
    "session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "id": "21531acd",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1|||P2|SIBLING_OF|P1\r\n",
      "2|||P5|SIBLING_OF|P5\r\n",
      "3|||P6|SIBLING_OF|P6\r\n",
      "4|||P7|SIBLING_OF|P7\r\n",
      "5|||P8|SIBLING_OF|P7\r\n",
      "6|||P9|SIBLING_OF|P7\r\n",
      "7|||P8|SIBLING_OF|P8\r\n",
      "8|||P9|SIBLING_OF|P8\r\n",
      "9|||P10|SIBLING_OF|P8\r\n",
      "10|||P9|SIBLING_OF|P9\r\n"
     ]
    }
   ],
   "source": [
    "!sqlite3 tmp/tmp.db \"SELECT * FROM FamilialRelationship LIMIT 10;\" \".exit\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8633a6e3",
   "metadata": {},
   "source": [
    "## Use of SQLA ORM to query\n",
    "\n",
    "Next we will demonstrate use of the SQLA Object-Relational Mapper to query for the first 10 objects in the database.\n",
    "\n",
    "Note that the ORM takes care of writing queries involving joins for us, so inlined objects are retrieved naturally"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "id": "1a4799f8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Rosario\n",
      "  SIBLING_OF P2\n",
      "Jerome Horn\n",
      "Carl Dawson\n",
      "  SIBLING_OF P5\n",
      "Ann Flores\n",
      "  SIBLING_OF P6\n",
      "Jolene Chaplin\n",
      "  SIBLING_OF P7\n",
      "  SIBLING_OF P8\n",
      "  SIBLING_OF P9\n",
      "Thelma Lindo\n",
      "  SIBLING_OF P8\n",
      "  SIBLING_OF P9\n",
      "  SIBLING_OF P10\n",
      "Lily Jordan\n",
      "  SIBLING_OF P9\n",
      "  SIBLING_OF P10\n",
      "  SIBLING_OF P11\n",
      "Mildred Sharp\n",
      "  SIBLING_OF P10\n",
      "  SIBLING_OF P11\n",
      "Rana Dyson\n",
      "  SIBLING_OF P11\n",
      "  SIBLING_OF P12\n",
      "  SIBLING_OF P13\n",
      "William Hanover\n",
      "  SIBLING_OF P12\n"
     ]
    }
   ],
   "source": [
    "for p in session.query(Person)[0:10]:\n",
    "    print(p.name)\n",
    "    for frel in p.has_familial_relationships:\n",
    "        print(f\"  {frel.type} {frel.related_to}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "99bb9e05",
   "metadata": {},
   "source": [
    "## Bridging SQLA Models with LinkML Dataclasses or Pydantic\n",
    "\n",
    "If you are already familiar with LinkML use with Python, you will likely be familiar with:\n",
    "\n",
    "- [gen-python](https://linkml.io/linkml/generators/python.html)\n",
    "- [gen-pydantic](https://linkml.io/linkml/generators/pydantic.html)\n",
    "\n",
    "Which create dataclasses and pydantic object models respectively.\n",
    "\n",
    "Unfortunately there is no one single standard for object models in Python. Dataclasses and Pydantic have a lot of advantages over SQLA models, but they don't work as well in conjunction with the ORM.\n",
    "\n",
    "To accommodate this, the LinkML python toolchain has bridging code that will map objects from one system to another."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "id": "f57ecb49",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INFO:root:Loading schema https://w3id.org/linkml/types from ../examples/PersonSchema/personinfo.yaml\r\n"
     ]
    }
   ],
   "source": [
    "!gen-pydantic ../examples/PersonSchema/personinfo.yaml > tmp/personinfo.py"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 151,
   "id": "ae8a0e5b",
   "metadata": {},
   "outputs": [],
   "source": [
    "import tmp.personinfo as personinfo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "id": "0574fb51",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'started_at_time': None,\n",
       " 'ended_at_time': None,\n",
       " 'related_to': None,\n",
       " 'type': <FamilialRelationshipType.SIBLING_OF: 'SIBLING_OF'>}"
      ]
     },
     "execution_count": 167,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "personinfo.FamilialRelationship(type=personinfo.FamilialRelationshipType(\"SIBLING_OF\")).dict()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "id": "896d286f",
   "metadata": {},
   "outputs": [],
   "source": [
    "def random_person_pydantic() -> personinfo.Person:\n",
    "    \"\"\"\n",
    "    Create a person with a random name and random set of relationships.\n",
    "    \n",
    "    Note that this is just for demo purposes. If we wanted to be more advanced we\n",
    "    could make use of SIBLING_OF to make a more realistic distribution of last name\n",
    "    matches, and we could ensure that SIBLING_OF is symmetric...\n",
    "    \"\"\"\n",
    "    global last_id\n",
    "    last_id += 1\n",
    "    pid = f\"P{last_id}\"\n",
    "    person = personinfo.Person(id=pid, name=names.get_full_name(), age_in_years=random.randint(30,40))\n",
    "    for n in range(0, random.randint(0, 3)):\n",
    "        rel = personinfo.FamilialRelationship(related_to=f\"P{last_id+n}\", type=\"SIBLING_OF\")\n",
    "        person.has_familial_relationships.append(rel)\n",
    "    return person"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "id": "9c232d80",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Person(id='P203', name='Jose Stover', description=None, image=None, aliases=[], primary_email=None, birth_date=None, age_in_years=39, gender=None, current_address=None, has_employment_history=[], has_familial_relationships=[FamilialRelationship(started_at_time=None, ended_at_time=None, related_to='P203', type=<FamilialRelationshipType.SIBLING_OF: 'SIBLING_OF'>), FamilialRelationship(started_at_time=None, ended_at_time=None, related_to='P204', type=<FamilialRelationshipType.SIBLING_OF: 'SIBLING_OF'>), FamilialRelationship(started_at_time=None, ended_at_time=None, related_to='P205', type=<FamilialRelationshipType.SIBLING_OF: 'SIBLING_OF'>)], has_medical_history=[])"
      ]
     },
     "execution_count": 153,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "random_person_pydantic()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "id": "d7bc975c",
   "metadata": {},
   "outputs": [],
   "source": [
    "container = personinfo.Container(persons=[random_person_pydantic() for x in range(1,100)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "id": "9bb0e0ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "from linkml.utils.sqlutils import SQLStore"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "id": "45c3fabe",
   "metadata": {},
   "outputs": [],
   "source": [
    "store = SQLStore()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "id": "23b139a4",
   "metadata": {},
   "outputs": [],
   "source": [
    "store.module = personinfo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "id": "4d859d00",
   "metadata": {},
   "outputs": [],
   "source": [
    "container_sqla = store.to_sqla(container)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "id": "2064d837",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "tmp.personinfo.Container"
      ]
     },
     "execution_count": 162,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(container_sqla)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cf570ea8",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}