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'},
},
],
}