Using Database with SQLAlchemy#

Hiku provides support for loading data from SQL databases using SQLAlchemy library, but Hiku doesn’t requires to use it’s ORM layer, it requires only Core SQLAlchemy functionality - tables definition and expression language to construct SELECT queries.

Prerequisites#

Note

Source code of this example can be found on GitHub.

We will translate our previous example from the Basics, but now all the data is stored in the SQLite database:

from sqlalchemy import create_engine, MetaData, Table, Column
from sqlalchemy import Integer, String, ForeignKey, select

metadata = MetaData()

character_table = Table(
    'character',
    metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String),
    Column('species', String),
)

actor_table = Table(
    'actor',
    metadata,
    Column('id', Integer, primary_key=True),
    Column('name', String),
    Column('character_id', ForeignKey('character.id'), nullable=False),
)

sa_engine = create_engine('sqlite://')
metadata.create_all(sa_engine)

sa_engine.execute(character_table.insert().values([
    dict(id=1, name='James T. Kirk', species='Human'),
    dict(id=2, name='Spock', species='Vulcan/Human'),
    dict(id=3, name='Leonard McCoy', species='Human'),
]))
sa_engine.execute(actor_table.insert().values([
    dict(id=1, character_id=1, name='William Shatner'),
    dict(id=2, character_id=2, name='Leonard Nimoy'),
    dict(id=3, character_id=3, name='DeForest Kelley'),
    dict(id=4, character_id=1, name='Chris Pine'),
    dict(id=5, character_id=2, name='Zachary Quinto'),
    dict(id=6, character_id=3, name='Karl Urban'),
]))

Graph definition#

Defined tables can be exposed as graph of nodes:

 1from hiku.graph import Graph, Root, Node, Link, Field
 2from hiku.types import TypeRef, Sequence
 3from hiku.engine import pass_context
 4from hiku.sources.sqlalchemy import FieldsQuery, LinkQuery
 5
 6SA_ENGINE_KEY = 'sa-engine'
 7
 8character_query = FieldsQuery(SA_ENGINE_KEY, character_table)
 9
10actor_query = FieldsQuery(SA_ENGINE_KEY, actor_table)
11
12character_to_actors_query = LinkQuery(
13    SA_ENGINE_KEY,
14    from_column=actor_table.c.character_id,
15    to_column=actor_table.c.id,
16)
17
18def direct_link(ids):
19    return ids
20
21@pass_context
22def to_characters_query(ctx):
23    query = select([character_table.c.id])
24    return [row.id for row in ctx[SA_ENGINE_KEY].execute(query)]
25
26@pass_context
27def to_actors_query(ctx):
28    query = select([actor_table.c.id])
29    return [row.id for row in ctx[SA_ENGINE_KEY].execute(query)]
30
31GRAPH = Graph([
32    Node('Character', [
33        Field('id', None, character_query),
34        Field('name', None, character_query),
35        Field('species', None, character_query),
36        Link('actors', Sequence[TypeRef['Actor']], character_to_actors_query,
37             requires='id'),
38    ]),
39    Node('Actor', [
40        Field('id', None, actor_query),
41        Field('name', None, actor_query),
42        Field('character_id', None, actor_query),
43        Link('character', TypeRef['Character'],
44             direct_link, requires='character_id'),
45    ]),
46    Root([
47        Link('characters', Sequence[TypeRef['Character']],
48             to_characters_query, requires=None),
49        Link('actors', Sequence[TypeRef['Actor']],
50             to_actors_query, requires=None),
51    ]),
52])

In the previous examples all the data was available as data structures, so no special access method was required. With databases we will require a database connection in order to fetch any data from it. Hiku provides simple and implicit way to solve this issue without using global variables (thread-locals) - by providing query execution context.

Query execution context is a simple mapping, where you can store and read values during query execution. In this example we are using SA_ENGINE_KEY constant [21] as a key to access our SQLAlchemy’s engine. In order to access query context pass_context() decorator should be used [21] and then to_characters_query function [22] will receive it as a first positional argument. SA_ENGINE_KEY constant is used to get SQLAlchemy’s engine from the context [24] in order to execute SQL query.

FieldsQuery [8] and LinkQuery [12-16] are using context in the same manner.

Hiku’s SQLAlchemy support is provided by hiku.sources.sqlalchemy.FieldsQuery and hiku.sources.sqlalchemy.Field to express table columns as fields in the node. And by hiku.sources.sqlalchemy.LinkQuery and hiku.sources.sqlalchemy.Link to express relations between tables as links between nodes.

direct_link [18] is a special case: when one table contains foreign key to the other table - many-to-one relation or one-to-one relation, no additional queries needed to make a direct link between those tables as nodes. character link [43-44] is a good example of such direct link.

Other relation types require to make additional query in order to fetch linked node ids. to_actors_query [12-16] for example. Such queries require selecting only one table, actor_table in this example. SQL query will be looking like this:

SELECT actor.id FROM actor
  WHERE actor.character_id IN (character_ids);

List of character_ids we already know (it is an id field of the current node), all we need is to fetch actor.id column to make a link from Character node to the Actor node. LinkQuery does this for you.

Querying graph#

For testing purposes let’s define helper function execute:

from hiku.engine import Engine
from hiku.result import denormalize
from hiku.readers.graphql import read
from hiku.executors.sync import SyncExecutor

hiku_engine = Engine(SyncExecutor())


def execute(graph, query_string):
    query = read(query_string)
    result = hiku_engine.execute(graph, query, {SA_ENGINE_KEY: sa_engine})
    return denormalize(graph, result)

Testing one to many link:


test_character_to_actors():
result = execute(GRAPH, '{ characters { name actors { name } } }')
assert result == {
    'characters': [
        {
            'name': 'James T. Kirk',
            'actors': [
                {'name': 'William Shatner'},
                {'name': 'Chris Pine'},
            ],
        },
        {
            'name': 'Spock',
            'actors': [
                {'name': 'Leonard Nimoy'},
                {'name': 'Zachary Quinto'},
            ],
        },
        {
            'name': 'Leonard McCoy',
            'actors': [
                {'name': 'DeForest Kelley'},
                {'name': 'Karl Urban'},
            ],
        },
    ],
}

Testing many to one link:


test_actor_to_character():
result = execute(GRAPH, '{ actors { name character { name } } }')
assert result == {
    'actors': [
        {
            'name': 'William Shatner',
            'character': {'name': 'James T. Kirk'},
        },
        {
            'name': 'Leonard Nimoy',
            'character': {'name': 'Spock'},
        },
        {
            'name': 'DeForest Kelley',
            'character': {'name': 'Leonard McCoy'},
        },
        {
            'name': 'Chris Pine',
            'character': {'name': 'James T. Kirk'},
        },
        {
            'name': 'Zachary Quinto',
            'character': {'name': 'Spock'},
        },
        {
            'name': 'Karl Urban',
            'character': {'name': 'Leonard McCoy'},
        },
    ],
}